The history of SQLite from a single-file prototype to global dominance

Richard Hipp worked as a contractor for Bath Iron Works in 2000, building software for the DDG-79 Oscar Austin battleship. The ship used Informix to manage data for the Automated Common Diagrams program, which helped sailors identify damaged pipes. This database failed whenever the server went down, which produced embarrassing dialog boxes that told sailors they could not connect to the database server. Hipp wanted a database engine that could operate directly from a disk without a separate server. A political stalemate between Newt Gingrich and Bill Clinton halted his government contracts, which gave him time to write the SQLite bytecode engine. This engine translated SQL into executable code. Hipp relied on Donald Knuth’s "The Art of Computer Programming" to implement a search algorithm. In 2001, Motorola requested SQLite for a new cell phone operating system and paid $80,000 for the contract. This money allowed Hipp to hire three people to work on the project. America Online also used SQLite for its free-trial CDs. Users even ran the database on Palm Pilots. Hipp believes that progress depends on the unreasonable man who tries to adapt the world to himself.

Google integrated SQLite into the Android operating system before the iPhone launched. The Android deployment revealed bugs that did not appear in smaller applications, forcing Hipp to adopt testing standards inspired by aviation’s DO-178B quality guidelines. He achieved 100% modified condition/decision coverage (MCDC) through a year of testing. Today, his team runs billions of tests from 100,000 distinct test cases before every release. This reliability helped SQLite win a competitive bake-off against ten other database systems for Symbian. The test included two open source and seven proprietary systems. Symbian then pushed for the creation of the SQLite Consortium to ensure long-term development. Mozilla and Adobe joined Symbian to fund the project.

The web landscape saw SQLite become a standard through the Web SQL Database API. Chrome 4 implemented this API using SQLite 3.6.19 to allow web pages to store data locally. This was a move toward standardization following Google’s Gears API, which also used SQLite. The W3C encountered a roadblock because every interested implementer chose SQLite as their backend, which prevented the specification from moving forward until a different implementation appeared to satisfy the requirement for multiple independent backends. The W3C required multiple independent implementations to proceed with standardization. I find the W3C’s insistence on finding a different backend despite universal adoption to be a complete waste of time. You should know that while Chrome continues to support WebSQL, Safari removed support in the iOS 13 beta. Firefox 3 also used SQLite for its Places feature to manage bookmarks and history. This implementation replaced the Mork format and the corruptible mix of RDF and HTML with a system of journaled writes. This system uses only 250K of memory and proved faster than the previous methods.

Scaling beyond embedded limits

While SQLite runs on small embedded systems, some companies use it for massive, enterprise-scale operations. Expensify uses a custom RDBMS called Bedrock that runs on top of SQLite to manage its data across three data centers. In 2012, founder David Barrett used SQLite for a 40GB database, a decision that caused Richard Hipp to argue that the engine was not designed to replace an enterprise-level RDBMS. Barrett, a peer-to-peer software developer from Red Swooth and Akamai, built the system to handle automatic replication and failover. Bedrock uses a Paxos-style algorithm to ensure data integrity and handle automatic failover. The system works on modern hardware with large SSD-backed RAID drives and generous RAM file caches. This setup allows Expensify to keep all its data in a single database. The company uses its own system settings for maximum performance and avoids the baggage of other databases. This architecture includes a distributed general ledger and a private blockchain element. Because an extra 1% of CPU and memory usage adds up across 5 billion devices, the team optimizes every detail.