Skip to content

D. Richard Hipp and SQLite

Abstract

D. Richard Hipp (born 1961) wrote SQLite in 2000 because the database server on a US Navy destroyer kept going down and taking his application with it. His answer was a SQL engine with no server at all: a C library that reads and writes one ordinary file. He put it in the public domain, tested it to avionics standards, and has maintained it with a small team ever since. It now runs in every Android phone, every iPhone, and every major web browser, and its developers estimate that more than a trillion SQLite databases are in use.

D Richard Hipp 2009
D. Richard Hipp, 2009. Image: D. Richard Hipp, CC BY 2.0, via Wikimedia Commons.

A Valve Diagram for a Destroyer

Hipp was born on 9 April 1961 in Charlotte, North Carolina, and grew up in the suburbs of Atlanta. He graduated from Georgia Tech in 1984, spent three years at AT&T, and took a PhD in computer science at Duke in 1992 under Alan Biermann. Afterwards he started a consulting firm in Charlotte, renamed Hipp, Wyrick & Company (Hwaci) in 1994, and took contract work.

One contract was for Bath Iron Works, the General Dynamics shipyard that built the guided-missile destroyer USS Oscar Austin (DDG-79). The software was called Automated Common Diagrams. When a pipe on the ship was damaged, it told the crew which valves to close and which to open to route around the break. The data sat in an Informix database server that was already installed on board and was not Hipp’s to manage. When that server was down, his program put up a dialog box saying it could not connect to the database, and the crew blamed the program they were looking at.

Hipp’s question, as he told the CoRecursive podcast in 2021, was “Why can’t I pull this directly off the disk drive?” The application only read a modest amount of data. It did not need a separate process, a network connection, or an administrator. A colleague told him: “Richard, why don’t you just write one?”

Spring 2000

The chance came when the contract’s funding paused for a few months in 2000. Hipp remembers the gap as fallout from a budget fight in Washington and has linked it to the quarrel between Newt Gingrich and Bill Clinton; the federal shutdowns of that quarrel were in 1995 and 1996, and there was none in 2000. Whatever the cause, he had no billable work, and he used the time to write a SQL engine.

He had a background in compilers, so he built it as one: a front end that translates SQL into bytecode, and a small virtual machine that runs the bytecode against a file. He wrote his own parser generator, Lemon, because Yacc and Bison did not do what he wanted, and he took the B-tree from Knuth’s The Art of Computer Programming, where deletion is left as an exercise for the reader. Version 1.0 was released on 17 August 2000, packaged as an extension to the Tcl scripting language. Someone soon reported on Usenet that he had SQL running on a Palm Pilot.

Version 2.0 followed on 28 September 2001 and version 3.0.0 on 18 June 2004. Every release since 3.0.0 has used the file format introduced then.

No Server, No Configuration, One File

SQLite is linked into the program that uses it. There is no daemon to start, no port to open, no user accounts, and no configuration file. A complete database, with tables, indexes, and schema, is a single file that can be copied between 32-bit and 64-bit machines and between byte orders. The library ships as one C source file with no external dependencies.

The project’s own description of its competition is that SQLite does not compete with client-server databases such as Oracle or PostgreSQL; it competes with fopen(). Most of its deployments replace an application’s home-made file format, not a database server (see The Database Revolution).

Phones

Hipp had not planned a business. The first company to call was Motorola, around 2001, which wanted SQLite in a new mobile phone operating system. Hipp quoted “$80,000 or something like that”, as he later recalled, and brought in three colleagues to do the work. America Online needed a database small enough to fit on its installation CDs. Symbian invited him to London over Thanksgiving and told him SQLite had won a comparison against ten database engines. Later, under a non-disclosure agreement, he debugged SQLite on a prototype phone with what looked like a 3D-printed case, running the system that became Android.

Symbian worried about what its engineers called the bus factor: how many people would have to be hit by a bus before development stopped. It pressed Hipp to set up a consortium. Mitchell Baker of Mozilla advised him on its design: companies pay, developers decide, and members get no vote on the code. Mozilla, Symbian, and Adobe were the first members.

Testing

The avionics supplier Rockwell Collins introduced Hipp to DO-178B, the standard for airborne software, which at its strictest level requires tests that exercise every branch and every condition of the machine code. He decided to meet it. It took, by his account, about a year of 60-hour weeks. Afterwards the stream of bug reports from Android stopped: “We just didn’t really have any bugs for the next eight or nine years.”

The result is a project that is mostly tests. As of version 3.42.0 (May 2023) the library was about 155,800 lines of C. The test code and scripts came to about 92 million lines, 590 times as much. There are four separate harnesses: the original Tcl tests, the proprietary TH3 suite that provides the full branch coverage, a SQL Logic Test that runs 7.2 million queries and compares the answers with those of PostgreSQL, MySQL, SQL Server, and Oracle, and a fuzzer that mutates both the SQL and the database file. TH3 is for sale. Hipp says it has sold exactly zero copies.

Public Domain and Seventy-Two Rules

SQLite has no licence. Hipp dedicated the code to the public domain, and every contributor signs an affidavit doing the same. This turned out to be a problem for corporate lawyers in jurisdictions where an author cannot give up copyright, so Hwaci sells a “Warranty of Title”, a document stating that the authors are the authors and had the right to give the code away. The project page lists among the reasons to buy one: “Your legal department tells you that you must purchase a license.”

The other consequence is that SQLite is open source but, in the project’s words, “not open-contribution”. Patches from outsiders are normally rewritten from scratch, because every line has to be covered by an affidavit. Hipp also keeps the tools in-house. When he was unhappy with CVS, he wrote his own version control system, Fossil, which stores its repositories in SQLite databases.

In 2018 the project attracted wider attention for a document it had written to fill in a box on customers’ supplier-registration forms. Asked for a code of conduct, Hipp had adapted the “instruments of good works” from chapter 4 of the sixth-century Rule of St Benedict: seventy-two rules, from “Do not murder” to “Never despair of God’s mercy”. After an online argument the page was renamed a Code of Ethics. It binds only the developers, who call it a one-way covenant: “We will treat you this way regardless of how you treat us.”

Scale

A 2022 paper in the Proceedings of the VLDB Endowment, co-written by Hipp, his colleagues Dan Kennedy and Larry Brasfield, and three researchers at the University of Wisconsin, calls SQLite the most widely deployed database engine in existence. The project’s reasoning for the trillion figure is simple arithmetic: more than four billion smartphones, each holding hundreds of SQLite files. Its own best guess is that SQLite is the second most widely deployed software library of any kind, after zlib.

The whole thing is maintained by a handful of people in a company most of its users have never heard of, which makes it a frequent example in discussions of volunteer-maintained infrastructure, though Hipp’s case is the unusual one where the maintainers found a way to be paid. His summary of how he came to write a database engine without having been trained to: “Fortunately, I didn’t know any experts and so I did it anyway.”

๐Ÿ“š Sources