There are perhaps a trillion active SQLite databases in the world. Roughly half the filesystem I/O on the phone in your pocket goes through it. And it is maintained by three committers.

That ratio — planetary deployment, three-person team — is the actual subject of this talk. Richard Hipp, SQLite’s creator, spends 54 minutes explaining the machinery that makes it possible, and the answer is not cleverness. It is a testing regime borrowed wholesale from the avionics industry, plus a willingness to redesign the product itself so it can be tested at all.

SQLite exists because Informix did not work

The origin story is a grievance. In the 1990s Hipp was a sub-subcontractor writing client programs against a customer-specified Informix database. When the Informix server went down — a misconfiguration, not his fault — his program had to paint an error dialog, and the end users sent him the bug reports.

“So I thought, do we really need that server? Why can’t we talk directly to the data on disk?”

He looked for something that did that, found nothing, and decided to write his own database engine. “How hard can that be, right? Turns out it’s harder than you’d think.”

Slide listing SQLite’s defining properties

What emerged is a full SQL implementation with power-safe ACID transactions, shipped as a C library rather than a system — “not something that can go wrong.” A database is a single file you can email to a friend. The US Library of Congress designates the SQLite file format as a preferred medium for long-term archival storage of binary data. Everything is public domain. The design lifespan is 50 years; first code landed May 29, 2000, so Hipp has 24 years left on the plan.

His theory of why it went viral is refreshingly unglamorous: it solves more problems than it creates. Which, he notes, is also a decent life philosophy.

DO-178B and the discipline that followed

The turning point was Android. An obscure Mountain View startup began using SQLite around 2004 and started sending Hipp a bug report a day. “I was beginning to realize, well, maybe I can’t write perfect software after all.”

Concurrent work for the avionics manufacturer Rockwell Collins introduced him to DO-178B, the airborne software certification standard — 84 pages, sold online at $6.50 per page. Its central claim reorganized how he thought about the project: if it hasn’t been tested, it doesn’t work.

SQLite’s answer is 100% MC/DC — modified condition/decision coverage. Hipp reduces the formal definition to two operational rules:

  1. Every branch operation at the machine code level has been tested in both directions.
  2. Every bit in a bitmask test makes a difference to the outcome.

Slide showing an MC/DC coverage example with a compound boolean condition

For a condition like a==5 && (b==7 || c==11), that means at least four test cases, exploiting short-circuit evaluation to enumerate the reachable combinations. Bitmask coverage is enforced with a testcase() macro that, under coverage builds only, injects an extra branch the optimizer can’t elide.

The standard also forces a three-way split that most projects collapse into one. You are testing (a) that your test cases are correct and complete, (b) that your source code is correct, and (c) that the delivered object code is correct — because DO-178B does not trust compilers. SQLite has found bugs in GCC, Clang, and MSVC over its history, and workarounds for older versions are still in the tree.

Testing what you can’t normally reach

A library that talks to a filesystem has failure modes you cannot trigger from a normal test program. SQLite’s response was architectural: make the untestable parts pluggable.

Diagram of SQLite’s pluggable VFS and OS interface layer

The OS interface is reached through a table of function pointers, with a published API to swap them. Want open() to fail deterministically? Substitute a wonky open that raises an error on demand, then verify the recovery path.

The same trick handles two other categories:

  • Out-of-memory. On Linux, malloc never really fails — the OOM killer just eats some other process. But SQLite runs on watches. So an alternative allocator is installed that fails the n-th allocation, in a loop: fail the 1st, check; fail the 2nd, check; continue until a run completes with zero failures. This is how the unwind-gracefully-and-don’t-leak paths get exercised.
  • I/O errors. Same loop structure, injecting errors at every I/O call site.

Then there is the hard one. “Power-safe” is a term Hipp coined for the ACID guarantee that survives losing power mid-transaction: either the whole transaction reached disk, or the whole thing rolls back.

Diagram of the crash-simulation test harness

“In Cupertino, Apple has — I don’t know if it’s a room or a building — full of machines hooked up to switches that kill their power. If you have millions of dollars to devote to that, you can do stuff like that. I don’t have that kind of money.”

Instead: a fake filesystem back end records every file operation and snapshots state after each syscall, then corrupts those snapshots the way real power loss would — reordering writes, dropping some of them — and asserts the transaction is either complete or fully rolled back.

You cannot bolt testing onto a finished product

This is the structural lesson. Achieving this coverage required changing SQLite itself, radically. sqlite3_test_control() is a published interface, present in every build you use, documented as “for testing purposes only, you ought not be using it.” It toggles query optimizations, seeds the PRNG for reproducibility, and runs internal unit tests — and it ships in production because otherwise you would not be testing what you fly.

The sqlite3FaultSim() hook returns false in production always, but under test control can be made to return an error on the n-th call — which is how the error path for a failed pthread_create() gets covered. There are 24 such instances in the current source.

Slide on the scale of the TCL test suite

SQLite began life as a TCL extension “that escaped into the wild,” so the original test suite is TCL: about 20,000 cases, parameterized into roughly 14.5 million tests, around 12 CPU-hours for a full run, and 6.5× larger than SQLite itself. But the TCL suite requires special compile-time options — it is a source code test, not an object code test. So Hipp wrote a second harness, TH3, starting in 2008; it hit 100% MC/DC in 2009 after what he calls a year of 80-hour days.

The payoff was immediate and, to him, startling: the Android bug reports simply stopped.

The benefits he didn’t expect

Hipp only wanted the bugs gone. Several other things arrived with them.

Three committers can maintain a world-critical codebase. “How many committers are there in Postgres? A hundred? Two hundred?” With this coverage, the team can strip out entire subsystems and rewrite them from scratch in a point release, with confidence nothing broke.

Graph of SQLite performance improvements since 2009

The code got three times faster. Because no single micro-optimization can silently break anything, thousands of individually unmeasurable optimizations could be applied since 2009. The benchmark curve is a steady descent in CPU cycles.

He also pushes back on a fashionable framing — that source code and test code are distinct artifacts. In SQLite, 15–20% of the shipped source exists only for testing. He draws the analogy to chip design, where 15–20% of the transistors on your CPU are used only during manufacturing test and never again. That, he suggests, is a reasonable target.

On the related claim that you should just write the tests and let an agent write the code: “Maybe I’m being dense, but I’m not seeing how that’s really going to be that helpful.” His own test corpus is far larger than the source.

Fuzzers, then AIs

The happy bug-free years ran 2009 to 2013. Then coverage-guided fuzzing arrived — AFL, libFuzzer — and, counterintuitively, 100% MC/DC code is unusually vulnerable to fuzzers, because achieving that coverage means pruning or annotating away unreachable branches, and fuzzers are remarkably creative at reaching them anyway.

Slide on the fuzzing era and its effect on bug discovery

The fix was to build their own libFuzzer-based fuzzer with a SQLite-specific mutator that fuzzes the database file and the SQL simultaneously. Once dialed in, external fuzzer bugs stopped — because the team was finding them first. Manuel Rigger later added semantic fuzzing (if a query returns a tuple, using it as a subquery should return the same tuple), which found a fresh class of bugs, and the fuzzer was extended again.

Most recently: an avalanche of AI-found bugs, and they are a genuinely different distribution than either fuzzers or MC/DC finds. Hipp’s example: SQLite’s median() aggregate uses a hand-rolled quicksort (3× faster than qsort because it avoids the comparison callback). An AI constructed a pathological million-entry input that overruns the CPU stack and segfaults. “Correct me if I’m wrong, but I don’t think Zig or Fil-C or Rust or Go is going to help me here.”

They are good at finding these, he says, and bad at fixing them — the AI proposed a depth parameter with a fallback algorithm. Hipp’s actual fix was to recurse only on the smaller partition (guaranteeing log n depth) and loop on the larger one, which also made it faster. He later found the technique in the literature: Sedgewick published it while Hipp was in high school.

Comments as testing, asserts as executable comments

A minor thread with a strong claim: code comments are part of your testing. About one third of SQLite’s source is comments, and Hipp rejects the self-documenting-code position outright. His argument is neurological — writing formal language and writing human language use different pathways, and each catches errors the other misses. Everyone has experienced explaining a bug aloud and solving it mid-sentence.

His rule of thumb for the AI era is neat: your comment should be a sufficient prompt for an AI to reproduce the code.

Asserts get the same treatment — an assert is an executable comment. A comment and its code might disagree; a passing assert you can believe. SQLite has over 7,500, disabled by default because they run the code 4× slower, plus whole invariant-checking subroutines omitted from production builds, plus NEVER()/ALWAYS() macros whose behavior changes across coverage builds, debug builds, and production.

Summary slide of lessons learned

Key takeaways

  1. Design for testability from the start. You cannot take a finished product and suddenly test it — SQLite had to be restructured, and the test hooks ship in production builds.
  2. 100% MC/DC coverage works. It is an enormous amount of work, it never ends (every feature and bug fix updates the harness), and it demonstrably eliminated the bug stream.
  3. Test the delivered object code, not just the source. Compilers have bugs; SQLite has found them in GCC, Clang, and MSVC.
  4. Make failure injectable. Function-pointer tables for OS calls, swappable allocators, and a snapshot-and-corrupt fake filesystem turn “untestable” failure modes into loops.
  5. Don’t fear test code 10× the size of your product, or 15–20% of shipped source existing only for testing. Chip designers accept the same ratio in transistors.
  6. Coverage buys refactoring and speed, not just correctness. Three committers can rewrite subsystems in point releases, and thousands of unmeasurable micro-optimizations tripled throughput since 2009.
  7. High coverage does not immunize you against fuzzers or AIs. Both find classes of bugs MC/DC structurally cannot — build your own fuzzer, and expect the AI-found bug stream to keep growing.
  8. Mutation testing remains unsolved here. Hipp can hit 100% MC/DC reliably; he has not figured out how to make mutation testing a reliable requirement.

Source