Reportage

SQLite — The Quiet Database Inside Everything

A reportage on the small file living in your phone, your car, your plane, and your browser — and on the three-person team in North Carolina that has been quietly building it, for a quarter of a century, the way medieval masons built cathedrals.

Photo by Pixabay on Pexels

There is, almost certainly, a small file inside the device you are reading this on. It is unremarkable in the way that nails and door hinges are unremarkable. It has no logo. It does not advertise itself. If you knew where to look, you would find several copies of it on your phone, a few more inside your browser, and another tucked into the firmware of the car you drove this morning, if you drove. The file is a database. It is, by a margin so wide that nobody in the industry seriously argues about it, the most-deployed piece of database software in the history of computing. It is called SQLite, and it has been written, for the last twenty-five years, by three people working from a single small company in Charlotte, North Carolina.

This is a reportage about that file, and about the unusually quiet way it came to live inside almost everything. It is also, I should warn you, a reportage that does not contain a fall. The story of most large software things is the story of how they got too big and broke, or how their founders fell out, or how a market shift made them obsolete. This is not that story. SQLite is approximately the same shape today as it was in 2004, runs more or less the same way, is maintained by more or less the same people, and is, by any reasonable measure, the single most successful piece of small-team software ever written. That is the strange thing about it. That is the thing worth looking at.

To understand what SQLite is, and why its particular kind of quiet ubiquity matters, it helps to step out of computing for a few centuries and look at a different kind of long, anonymous, infrastructural craft. So that is where we will start.

Cathedral model versus startup model — two ways of building software, with very different durability profiles

You can hold a SQLite database in your hand, in the sense that the entire format is documented in a single file on the SQLite website, and the entire library — the C code that reads, writes, locks, journals, and queries that file — fits in roughly 150,000 lines of source. That is a small program. The Linux kernel is on the order of 30 million lines. A modern web browser is comfortably over 20 million. SQLite is a hundredth of a percent of either, by line count, and it runs, in some form, inside both.

The number of SQLite installations on Earth is genuinely uncountable. The SQLite documentation, written in characteristically dry language at sqlite.org, claims that SQLite is “likely used more than all other database engines combined.” This is the sort of sentence that would normally invite skepticism. In this case, the arithmetic is unforgiving. Every Android phone ships with multiple SQLite databases, used by the operating system itself for app storage, contact lists, and message history. Every iPhone does the same. Every Mac. Every Windows 10 and Windows 11 install. Every modern Linux distribution. Every Firefox install, every Chrome install, every Safari install. Skype, before its rewrite. Dropbox. Adobe. Bloomberg terminals. The Python standard library ships with it. The PHP standard library ships with it. The macOS frameworks ship with it. There are, depending on whose estimate you trust, somewhere between one trillion and ten trillion deployed SQLite databases at this moment, and the lower bound is conservative.

Hold that number in mind for a second. Then notice that you have probably never heard a single person, professional or otherwise, complain about SQLite at a dinner. Nobody at a dinner has ever complained about SQLite. There are entire genres of database criticism — Oracle-as-vampire, MongoDB-loses-your-data, Redis-changed-its-license, Postgres-vacuum-pain — and SQLite is in none of them. The most-deployed database in the world is also, by some margin, the least-discussed.

This is not because nobody uses it. It is because, almost always, the people using SQLite are not the people who chose it. Apple chose it. Google chose it. Mozilla chose it. The avionics suppliers chose it. The browser engineers chose it. The hundreds of millions of users downstream just have it, the way you have door hinges. It works. They never had to think about it. That is, in fact, the entire engineering accomplishment.

SQLite by the numbers — three engineers, public-domain code, and the most-deployed database in human history

SQLite is not a competitor to MySQL. It is a competitor to fopen.

That sentence — written by SQLite’s own authors on the project’s homepage — is the most useful thing anyone has ever said about it. SQLite is not really a database in the sense that Oracle and Postgres are databases. It is a way of storing structured data on local disk, the way fopen is a way of storing unstructured bytes on local disk. The unit of deployment is a single file. There is no server. There is no daemon. There is no port to open. There is no cluster to manage. The library links into your program, your program reads and writes a file, and the file is a database. That is the entire trick, and the entire trick is what allowed SQLite to seep into everything.

The Navy contract

The story of SQLite begins, as several of the best engineering stories do, with somebody trying to avoid a piece of software they did not want to deal with. In 2000, D. Richard Hipp — Doctor Hipp, a software engineer with a Duke PhD in operations research, working as a freelance consultant out of Charlotte — was on a contract for General Dynamics. The contract was for software that would run on US Navy guided-missile destroyers. The Navy wanted, among other things, a database. The default choice was IBM Informix, which was a real database in the real-database sense — a big server program, a license, a database administrator, a manual, the whole apparatus.

Hipp, by his own telling on the Talking Drupal podcast in 2017, did not want to be the person responsible for keeping IBM Informix running on a destroyer at sea. He had also noticed something about how the application actually used the database. It did not need most of what Informix offered. It needed to store some data, query some data, and not lose any of it when the ship rolled or the power flickered. The complicated middle layer — the network protocol, the user accounts, the multi-client locking — was overhead. Worse than overhead, in a deployed environment with no system administrator and a strict reliability budget, it was risk.

So Hipp wrote a small library that did the data part and skipped the server part. He called it SQLite. He released the first version in August 2000, into the public domain, with no license at all. He kept working on it. Then he kept working on it some more. Then it was fifteen years later, and he was still working on it, and so were two other people, and the small library was sitting inside every smartphone in the world.

This is the part of the story where, in a normal startup narrative, somebody arrives with venture capital. Nobody arrived with venture capital. Hipp incorporated something called Hwaci — Hipp, Wyrick & Company, Incorporated, named for himself and his original co-founder — to handle the consulting work that grew up around SQLite, and the company stayed small. By the late 2010s, Hwaci employed Hipp, Joe Mistachkin, Dan Kennedy, and Richard Hipp’s wife handling the books. The development team — the people who actually wrote SQLite code — was three. It is still three.

SQLite is the one major database project in history that has never had a paid sales team, never raised outside funding, and never had more than a handful of full-time engineers.

That is not a quote from a manifesto. It is just an inventory.

Public domain

There is a small artifact on the SQLite website, on the page titled “Public Domain — No License Whatsoever,” that is worth dwelling on for a moment. Most open-source projects have a license — MIT, BSD, Apache, GPL — that says, in legal language, what you may do with the code. SQLite has no license. It says, in plain language, that the code belongs to nobody and that you may use it for anything. There is a sentence on the page, half-buried in the legal text, that reads: “May you do good and not evil. May you find forgiveness for yourself and forgive others. May you share freely, never taking more than you give.” Hipp, who is openly Christian and writes the way someone might write a parish bulletin, included this as a kind of blessing where most projects would have a copyright notice.

The legal effect of public-domain dedication is unusual. In most jurisdictions, you cannot actually disclaim copyright; the law assigns it to you whether you want it or not. SQLite handles this with a workaround: companies that need a license, for compliance reasons — and Apple, Adobe, Bloomberg, and Mozilla are all such companies — can pay a one-time fee to Hwaci for a contract that essentially states, yes, you may use this code, no, we will not sue you, here is a piece of paper that your lawyers will accept. The fee is small, by enterprise-software standards. The license, once purchased, lasts the company’s lifetime. It is the only revenue model SQLite has, and it is sufficient.

The cathedrals — and this is the part where the cathedrals come in — were built on roughly the same financial principle. Chartres took 26 years to build. Notre-Dame de Paris took 182. The Cologne Cathedral, started in 1248, was completed in 1880. The masons who began these buildings did not see them finished. The masons who finished them did not lay the foundations. There were no architects in the modern sense; there were chief masons, who held the design in their heads and on small parchment sheets, and who passed the work down through their guilds and their apprentices and, often, their families. The buildings were funded by a slow accumulation of small contributions — the cathedral chapter’s endowment, individual bequests, parish collections, the occasional rich pilgrim. There was no business plan. There was, instead, the assumption that the work was worth doing, that the next generation would continue it, and that the building would be standing in five hundred years.

The peculiar thing about cathedral economics is that the building, once finished, becomes a public good. Anyone may walk in. Anyone may use it. The masons’ names are mostly lost. The chief masons of Chartres are anonymous. The men who carved the saints in the western tympanum of Reims are, by and large, unidentifiable. The work outlasts the workers, who outlasted the patrons, who outlasted the original brief. What survives is the artifact. The discipline that produced it is preserved in the artifact itself.

I am aware, as I write this, that comparing a database engine to a Gothic cathedral is the sort of thing that sounds, on first hearing, like trade-press hyperbole. I would not have written it if the structural analogy had not insisted on itself. SQLite is, like a cathedral, an infrastructural artifact built by a small anonymous team over many decades, funded by the slow drip of small license fees from organizations that need it, given freely to anyone who walks in, and explicitly designed to outlast the people who are working on it. The file format, by formal commitment, will be readable through the year 2050. Hipp has said, on multiple occasions, that he expects SQLite databases written this year to be readable a century from now. That is not a marketing claim. It is the architectural mandate of the project.

Turning point: the airplanes

There is a moment in the SQLite story, not famous, that I think of as the moment the cathedral got its certification. In the early 2010s, the avionics industry — the part of aviation that builds the software running flight management computers, navigation displays, and cockpit instruments — began adopting SQLite for on-board databases. This was not a casual decision. Software that runs on aircraft must be certified to standards like DO-178B (later DO-178C), which is the international avionics software certification standard. DO-178B Level A — the highest assurance level, used for software whose failure could cause catastrophic loss — requires a particular kind of test coverage called Modified Condition/Decision Coverage, abbreviated MC/DC. Without going into the formal definition, MC/DC requires that the test suite demonstrate, for every Boolean condition in the code, that each individual sub-condition can independently affect the outcome. It is one of the most stringent test-coverage standards in any software domain.

To put it plainly: avionics software has to be tested in a way that ordinary software is not.

In 2009, in response to a customer in the avionics industry, the SQLite team developed a proprietary test harness called TH3, which is internal to Hwaci and is used for the express purpose of demonstrating, on every release, 100 percent MC/DC coverage of SQLite’s source code. According to the SQLite project’s own testing documentation at sqlite.org/testing.html, TH3 contains, alongside the project’s other test suites, somewhere on the order of 92 million lines of test code, exercising about 150,000 lines of production code. That ratio — roughly 700 lines of test for every line of code shipped — is, by any reasonable standard, deranged. Most professional codebases consider a 1:1 test-to-code ratio ambitious. Famously well-tested commercial software lives in the 2:1 to 5:1 range. SQLite’s ratio is two orders of magnitude beyond that.

The SQLite team writes more test code in a year than most companies write production code in a decade.

I have spent a lot of my life reading other people’s tests. I have also spent a lot of my life writing tests for code that I knew, even as I wrote them, were tests written to satisfy a process rather than tests that would actually catch the bugs they were nominally designed to catch. SQLite’s tests are not that kind of test. They are, by every account I have read and by every example available on the public website, the other kind. They exist because the team has decided that this particular small file, sitting in this particular small library, must not lose data, must not crash, must not corrupt, on any of the platforms or workloads or pathological inputs the world will throw at it. The tests are a moral document.

The avionics adoption is the part of the story that turned this discipline from a private virtue into a publicly verifiable fact. SQLite is, today, used in aviation systems certified to DO-178B Level A. The Federal Aviation Administration’s Operational Performance Assessment process uses SQLite databases. Airbus uses SQLite in flight software. Several of the major avionics suppliers use it. The civil-aviation database for navigation data — the package that tells your aircraft where the runway ends and where the terrain begins — is, in many implementations, a SQLite file.

I find the aviation moment significant for a reason that goes past the engineering. To put SQLite into a cockpit is to take a position. It is to say, on behalf of an organization with thousands of engineers and a regulator looking over its shoulder, that this small library written by three people in North Carolina is the most reliable way to store structured data the organization can find. It is to bet lives on it. The avionics industry, which is paid to be paranoid, made that bet years ago and has been renewing it ever since. Whatever else SQLite is, it is software that the people who write the software for airplanes trust more than the alternatives.

Timeline: SQLite from 2000 destroyer contract through avionics certification, 2007 Apple adoption, 2014 Android adoption, 2050 file format commitment

The development team, as of the most recent public accounts at sqlite.org and the project’s own Hwaci pages, is Richard Hipp, Dan Kennedy, and Joe Mistachkin. Hipp is the original author and the architect. Kennedy joined in 2002. Mistachkin joined in 2007. The three of them have written, between them, virtually every line of SQLite code that has ever shipped. Outside contributions exist — there are extensions, ports, language bindings — but the core engine, the part that actually reads and writes the file format, is overwhelmingly the work of these three engineers.

This is, I want to be clear, statistically extraordinary. Most large open-source projects of comparable importance have hundreds or thousands of contributors. The Linux kernel has over twenty thousand. Postgres has hundreds of active developers. Even small infrastructure projects — Redis, NGINX, the Go standard library — typically have at least a few dozen people with commit access. SQLite has three.

It is also extraordinary in another way, which is harder to quantify but may be more important. The three engineers have been, by any external measure, remarkably stable. There has been no public falling-out. There has been no fork. There has been no governance crisis. Hipp and his colleagues have given interviews — Hipp, in particular, is a frequent and gracious podcast guest, and is unusually candid about the project’s economics and culture — and the picture that emerges from those interviews is of three people who have settled into a working rhythm and have continued, year after year, to do the work.

Hipp, in a 2017 interview on the Corecursive podcast, was asked about the team’s size and replied, in essence, that more engineers would not make SQLite better. The bottleneck, he said, was not coding speed; it was design coherence. SQLite has to fit together. Every line of code interacts with every other line, in the sense that the file format and the query planner and the locking discipline and the transaction journal all have to maintain invariants that no individual change can be allowed to break. A larger team, Hipp suggested, would actually make this harder, because the cost of keeping everyone aligned would exceed the benefit of more hands. He used the phrase “cathedral, not bazaar” — a deliberate inversion of Eric Raymond’s famous 1999 essay, in which Raymond argued that open source worked better as a noisy bazaar than as a quiet cathedral. Hipp’s view is that, for a project with SQLite’s properties, the cathedral wins. The cathedral, after all, has been standing for eight hundred years. The bazaars come and go.

This is, again, where the medieval comparison stops being a flourish. Cathedral construction, when it worked, worked because a small core of master masons held the design in their heads and propagated it slowly, by apprenticeship, to the next generation. The buildings rose at a pace that allowed the design to remain coherent. The pace was, by modern standards, glacial. The result was, by any standard, magnificent. Software that has to be both small and reliable — software that has to fit on a smartwatch and not lose data on a destroyer — turns out to benefit, structurally, from the same kind of pace. SQLite is not slow because the team is unambitious. It is slow because the discipline of keeping it coherent over twenty-five years requires that it be slow.

The file format and the year 2050

There is a public document, on the SQLite site, titled “The SQLite File Format” — a description of every byte of the format, every header field, every B-tree page layout. The document is dry. It is also extraordinary as a category of artifact, because it is a public commitment, signed in effect by Hipp on behalf of the project, that databases written in this format will remain readable by future versions of SQLite through 2050. That is a 25-year compatibility window.

Backward compatibility is not novel in software. Microsoft Office can still open files from the early 1990s. The Java Virtual Machine has run bytecode written decades ago. Linux preserves user-space ABI compatibility across most kernel versions. What is unusual about SQLite’s commitment is its formality. The 2050 date is not aspirational. It is part of the project’s stated specification. If you write a SQLite database today, on the project’s promise, the database will be readable by the SQLite that ships in 2049. Children not yet born will write code that reads files written before they existed.

I think about this commitment often, and the cathedral analogy keeps reasserting itself. The masons of Chartres did not know which of their nephews’ grandsons would worship in the building. They built, anyway, with a notion of permanence that we have, for some reason, mostly stopped expecting from software. The default in modern software is that a file written five years ago may already be unreadable, that an API written three years ago has been deprecated, that a database written ten years ago has been migrated to a successor that no longer maintains the old format. SQLite is a counterexample in the strict sense: a piece of widely deployed software that explicitly refuses the default. The format is the format. The format will remain the format. Build on it.

Software that promises to last is rare enough that, when you find it, you should probably build on it.

This is not the kind of sentence that gets said in vendor pitches, because it does not generate quarterly revenue. It is, however, the operating principle behind some of the most consequential software infrastructure on Earth. The TCP protocol still works. The IPv4 packet still works. The POSIX file API still works. UTF-8 still works. SQLite, by its own design and by its team’s deliberate practice, has placed itself into this short list of foundational artifacts that humans have decided, for now, to treat as permanent.

The customers who do not know

The thing I keep coming back to, when I think about SQLite, is the asymmetry between how widely it is used and how rarely it is named. There are billions of people who interact, every day, with software whose entire local-data layer is a SQLite database, and who have never heard the word. They tap a contact in their phone — SQLite. They check a message — SQLite. They open a browser tab and the browser remembers the tab — SQLite. They start their car and the entertainment system remembers the last station — almost certainly SQLite. They board an airplane whose flight management computer reads its navigation database from disk — SQLite, in a substantial and growing fraction of cases. The infrastructure does its work, and the people who depend on it never know, because there is nothing for them to know.

This is, I think, the highest compliment that infrastructure can earn. Not that it is famous. Not that its founders are celebrated. Not that it raises rounds. That it disappears into the background and continues to function. The water in the city does this. The grid does this, when it is well-maintained. The road system does this. The TCP/IP stack does this. SQLite has joined that small club, and the membership criteria for that club — by the time you notice me, I have failed — are unforgiving.

There is also, as a quiet side note that I do not want to overstate, an ethical dimension to all this. Public-domain licensing is not a marketing decision. It is a commitment about what kind of relationship the software has with the world. SQLite has been used, over its lifetime, by companies whose values and business models you would not write home about. It has also been used by small open-source projects, by hobbyists, by school children learning to program, by humanitarian non-governmental organizations whose data has to live offline in places without internet, by scientists running field equipment in regions where licensing fees are prohibitive. The decision to put the code into the public domain, in 2000, made that downstream universality possible. A more ordinary licensing decision would not have. The cathedrals were, at the time of their building, a similar proposition: a vast public good, given freely to anyone who entered, whose construction was financed by people who would never own it.

Inheritance

The question that hovers, when you look at a project like SQLite, is what happens next. Hipp is in his sixties. Kennedy and Mistachkin are not much younger. The development team is three. The project has, by Hipp’s own statements, no formal succession plan and no public bench of designated heirs. This is, by the standards of software risk management, alarming. It is also, by the standards of medieval cathedral construction, business as usual.

Cathedrals worked because the discipline survived the masons. The chief mason of one generation taught his apprentices, who in turn taught theirs, and the design — the proportions, the ratios, the structural intuitions — propagated forward through bodies and hands rather than through any single document. When a chief mason died, somebody else picked up the work, because the work was bigger than any one person and the people who had been around it for years had absorbed enough of its shape to continue.

SQLite has, in this sense, written down more of itself than any cathedral ever did. The source code is public. The test suites are extensive. The file format is documented down to the byte. The architectural principles are written up in long, thoughtful essays at sqlite.org. Anyone who wants to understand how SQLite is built can, with patience, do so. The discipline is not stored exclusively in three heads. It is stored, redundantly, in artifacts.

But the discipline, in practice, does live in the heads of the three engineers. The judgment about when not to add a feature, the instinct about which optimization is safe and which is not, the experience of having seen, ten years ago, the bug that the current proposed change would re-introduce — that is harder to write down. It is the part of a craft that is genuinely transmitted by apprenticeship. SQLite has, so far, transmitted it once: from Hipp to Kennedy and Mistachkin. The next transmission, if it is going to happen, has not yet been publicly named.

There is a non-zero chance that the project ends, in some quiet way, with the retirement of its founders. There is a higher chance that the work continues, because somebody, somewhere, has been reading the source code carefully for years, and will pick up the trowel when the time comes. There is also a chance that the public-domain dedication, the long testing tradition, and the formal file-format commitment have together made SQLite robust enough to survive even an interruption. The file format will still be there. The test suite will still be there. Someone competent could, in principle, resume the work. Cathedrals have, on rare occasions, been finished after a hundred-year construction pause.

I do not know which of these will happen. I do not think anyone does, including the team. What I find moving — and I have noticed, writing this, that I have used the word moving about a database engine, which is not how I usually write — is that the team has set the project up so that any of these endings is survivable. They have made the discipline transmissible. They have made the artifact durable. They have asked nothing of their successors that their successors cannot, in principle, learn. That is an unusual gift to leave behind.

Coda

I keep coming back to the small file. There is, almost certainly, one in the device you are holding right now. Open the right folder on a phone or a laptop and you will find a name ending in .db or .sqlite. It will be small — usually under a megabyte. You can copy it, attach it to an email, open it on another machine in twenty years, and read every byte. The library that knows how to read it has been written, very slowly and very carefully, by three people in North Carolina, over the span of a quarter-century, with no investors, no marketing department, no governance committee, and no plans to scale. It is one of the most quietly successful pieces of engineering in the history of the discipline.

The masons of Chartres carved their saints into the western tympanum and went home, mostly without signing their names. The cathedral is still standing. The saints are still there. People walk past them every day without looking up, which is roughly the highest praise an infrastructural artifact can receive.

The small file in your phone is doing the same work, on the same terms. Somebody is going to be reading it long after you and I are gone. That, I think, is enough.

[embed]List: Reportage | Curated by Anto Semeraro | Medium Reportage · 7 stories on Mediummedium.com