SQLite runs on a billion devices and is looked after by two or three people. Richard Hipp, who wrote it, has explained how in two talks. This follows them along the project’s own timeline.
2000. A destroyer and a funding pause
Hipp was a contractor on the software for the USS Oscar Austin, a destroyer. It talked to an Informix database, and when the server was down, which was often, his program took the blame. Then the funding paused.
He spent the pause writing the database he wished he’d had: no server, no network, no process to be down, a library compiled into your program that reads one file. Postgres is a restaurant. SQLite is the kitchen in your apartment. Neither he nor Dan Kennedy, who joined later and is still there, had built a database before, and you can see it.IBM bought Informix in 2001, a year after SQLite’s first release.Tcl is John Ousterhout’s scripting language from 1988. SQLite began as a Tcl extension, its main test harness is still written in Tcl, and Hipp is on the Tcl core team. He says he learned a lot about testing from that community.
2004. Version 3, and a promise you can’t take back
Version 3 changed the file format, with America Online paying for much of the work, and it was the last incompatible change. Once your files sit on other people’s disks, every version you ship has to open them, forever. The 2050 pledge is this promise with a date on it, and most of what follows comes from not being allowed to change one thing.SQLite is in the public domain rather than under a licence. Some legal systems don’t really have a concept of giving a work away, so for those the project sells, for a fee, a signed statement confirming the gift.In 2018 the US Library of Congress added SQLite files to its list of recommended formats for long-term storage of datasets, alongside CSV and XML.
2007 and 2008. Phones
The iPhone shipped with it in 2007, Android in 2008, and a one-in-a-billion bug became a bug report every morning. Every fix can break someone relying on the old behaviour, so the rule: solve more problems than you create. A fix that helps ten and breaks one ships. One that helps one and might break one doesn’t. I’m not sure I’d have the nerve.In late 2007 Mozilla and Symbian formed the SQLite Consortium, Adobe and others joined after, and the membership fees let the two of them work on it full time without having to sell anything.
2009. The year of testing
The phones made one thing obvious: you cannot test the users. Their inputs are infinite and you never see them. The program is finite, every branch and every bit of every bitmask countable. So he set out to exhaust the finite thing instead.
The standard is MC/DC, modified condition/decision coverage: not just every if both ways, but every condition inside it flipping the outcome on its own. Even a bitmask gets each bit tested separately. It took about a year of eighteen-hour days and left 590 lines of test per line of code. His slide’s lesson is a lower bar, ten times the code, which I read as him knowing most people won’t do what he did.1992MC/DC comes from DO-178B, the 1992 standard for software on commercial aircraft. Level A software, where failure would be catastrophic, has to meet it. Hipp picked it up from a customer in avionics.
The tests have three jobs: verify the test cases, verify the source with sanitizers and static analysis, and verify the object code, because SQLite has found bugs in GCC, Clang and MSVC, and a correct C program is not a correct binary. Then the part that changed how I read code: to test all of it, the suite had to move inside the product.“Fly what you test, test what you fly” is an aerospace saying, and it’s on his slide because he means it literally: the tests run against the compiled object code that ships, not only the source.
Around fifteen percent of the shipped source exists only so the rest can be tested: hooks, seams, a swappable allocator, a swappable file system, all idle on your phone. A fifth of the transistors on a CPU exist for factory testing and hardware people are fine with it. In software it gets called dead weight, and I’ve called it that myself.1990The hardware version is called design for test: scan chains, built-in self test, and the JTAG standard of 1990 that gives every chip a dedicated test port. Nobody suggests removing the port before shipping.
The seams are for sabotage. Swap in an allocator that fails on command. Run an operation, fail the first allocation, check everything unwinds and nothing leaks. Fail the second, then the third, until the operation gets through with nothing left to inject. Same for disk writes and power loss. Every error path has actually run.
Then the asserts, seven and a half thousand of them. An assert states an invariant, so if it fires there is a bug, never bad input. Executable comments, he calls them: they can’t drift out of date, because the suite would catch them lying. They’re compiled out of the binary you run, since SQLite is four times faster without them, so shipped and tested code differ by seven thousand statements.The Go team’s FAQ explains why Go has no assert: programmers misuse it for error handling and skip the proper error paths. Hipp has argued with them about it publicly more than once. I suspect both are right about the programs they usually see.
The tests also made the code faster: you can try a risky optimisation when a suite answers within the hour.The same year, 2009, SQLite moved from CVS onto Fossil, a version control system Hipp wrote for it, with bug tracker, wiki and history in one repository. His slide lists “good version control, situational awareness” among the things testing taught him. Fossil still runs the project.
2010. The write-ahead log
The idea I use most: don’t estimate how often the disk fails. Assume it will, at the worst moment, and arrange things so it doesn’t matter.
That’s the write-ahead log. Changes are appended to a separate log, a commit is one marker at its end, and the changes are folded into the main file later. Power goes mid-write, the log holds a half entry with no marker, and on restart it’s ignored. The same pattern is everywhere: the file system lies, so it sits behind an interface the tests can replace with a lying one. Memory runs out, so the allocator fails on command. The compiler has bugs, so the binary is tested. Nothing below the program is trusted.1970sWrite-ahead logging is decades older than SQLite. IBM’s System R used it in the seventies, and the ARIES paper from 1992 describes the form most databases still follow. What Hipp did was fit it into a single file with no server around to coordinate.1984Ken Thompson’s Turing lecture, “Reflections on Trusting Trust”, from 1984, describes a compiler that inserts a back door into the programs it compiles, including into the next version of itself, with nothing visible in any source file. Testing the object code doesn’t defend against that exactly, but it’s the same attitude: the source isn’t the thing you ship.
An interlude about spare parts
SQLite has no redundancy. One file, one library, no replica, no failover, no process boundary, so if it crashes, you crash. The team has none either: two people, sometimes three, and a pledge to 2050. Both should be fragile and neither is, for the same reason. The redundancy moved. Instead of a second copy, 590 lines of tests behind every line; instead of a bench of engineers, a suite that lets two people change anything and know within the hour. His slide says “maintainable with just a few committers”, and he undersells it. The tests are the spare parts, for the software and for the people.
This doesn’t transfer cleanly to a team of forty, where the redundancy is people whether you like it or not. The direction does.1986Knight and Leveson tested the obvious alternative in 1986: write the program several times independently and vote. The versions failed on the same inputs far more often than chance would predict, because people find the same problems hard. So you can’t make software safe by copying it, which leaves proving it, and coverage is about the closest a working programmer gets.There is redundancy inside the tests, though. Three separate harnesses, one proprietary, exercise the same code from different directions with different assumptions. Aviation calls this dissimilar redundancy.
2014 and 2020. Machines that guess badly on purpose
Coverage says every branch has run, not what a stranger’s input does. So from 2014 the project let machines guess: profile-guided fuzzing, then from around 2020 fuzzers generating whole SQL statements, valid and deranged. If you can’t enumerate the inputs you sample them, with something that has no taste.
Two more habits, both ways of distrusting your own suite. Mutation testing: change the code on purpose, a < to a <=, and check that some test fails. And checklists for everything, walked every release, because what a checklist catches is exactly what nobody was thinking about.1978Mutation testing started as a student’s idea, Richard Lipton’s, in 1971, and was written up properly in 1978 by DeMillo, Lipton and Sayward as “Hints on Test Data Selection”. For most of the time since it’s been too expensive to run on anything large.1935The checklist as an engineering tool dates to 30 October 1935, when Boeing’s Model 299, the prototype of the B-17, crashed on a demonstration flight because the crew forgot to release the control locks. The conclusion at the time was that the plane was too complex for one pilot’s memory, and the response was a piece of paper.
2026. The machine finds what the machines missed
Here’s the limit. Hipp puts it in his own talk, which is why I trust the rest.
In June 2026 an AI system reported a bug. SQLite’s median() collected its inputs into an array, quicksorted it, took the middle. A million entries arranged just so and the quicksort recursed a million deep and blew the stack. The function had 100% coverage and every fuzzer above had run against it. Coverage is a map of what you already know. The program is finite and he exhausted it; the inputs are still infinite, and one turned up. This is the edge of the 2009 idea.1961Hoare published quicksort in 1961. In 1978 Robert Sedgewick’s “Implementing Quicksort Programs” gave the fix for exactly this failure, recurse into the smaller partition and loop on the larger, which bounds the stack at log n. So the bug was forty-eight years older than the tool that found it.1969Dijkstra, 1969: “Testing shows the presence, not the absence of bugs.” Hipp’s slide is, in effect, him agreeing.
He calls it an avalanche. AI finds pathological cases fuzzers didn’t, because it reads the code and guesses where it would hurt. Its fixes are naive. Finding was the hard part, though.
It also changed how he thinks about comments, a third of SQLite’s source. The rules were already strict: what every function is for, what every variable means, no boilerplate, written for people not born yet, because human and formal language use different parts of the brain. The new rule, my favourite line of the talk: write the comment as the prompt you’d give a model to generate the code below it. If the prompt wouldn’t produce that code, the comment is wrong or the code is.Knuth’s literate programming from 1984 is the far end of this, the program as a book with the code quoted in it. Hipp stops well short of that, but it’s the same argument about two languages.
2050. The pledge
SQLite promises support through 2050. Two or three people, a file format from 2004, a date twenty-five years out. You can only sign that if you believe the tests will outlive you.
He ends somewhere I didn’t expect. Everything above was necessary, he says, and not sufficient. Being on every phone was the phone makers’ decision. He uses the word providence.
The other half is the kind of person you have to be to stay on one project for twenty-five years, and Hipp is not alone in it. The big open-source projects tend to start the same way, with one person deciding to make their own and asking how hard it could be:
- December 1989, Guido van Rossum: Python
- 17 September 1991, Linus Torvalds: Linux
- 29 May 2000, Richard Hipp: SQLite
- 7 April 2005, Linus Torvalds: Git. He did it twice.
- 2009, Salvatore Sanfilippo: Redis
The question turns out to be easy. Still being there decades later is the exceptional part.
Sources. Richard Hipp, the reliability talk: the error-injection loop at 20:10, Fossil at 11:36, the AI bugs at 48:15. The origin talk: the destroyer at 01:21, the kitchen at 03:21, the test ratio at 07:22. On sqlite.org: How SQLite Is Tested, The Use Of assert() In SQLite, Long Term Support, and the median bug.
Notes on two talks, edited with Claude, which also drew the figures.
Changelog
2026-09-16 first pass from the two talks and my handwritten notes