SQLite: The Database in Every Pocket

A Database Born From a Navy Contract

In the year two thousand, a software developer named Richard Hipp was working on a project for the United States Navy. The project involved guided missile destroyers and the software systems that ran on them. The specific problem Hipp was trying to solve was that the destroyer software needed a database, but the existing database options were all wrong for the context. The destroyers were sometimes at sea for months. The database had to keep running even when nothing else was available, no server administrator, no network connection, no support team. The database had to be small enough to embed inside the application that used it. The database had to be reliable enough that nobody would ever lose data from a Navy ship because of database corruption.

The existing databases at the time were not built for this. They were server software. They needed administrators. They needed network connections. They required configuration and tuning and monitoring. They were appropriate for a corporate data center, not for a missile destroyer in the Indian Ocean. Hipp decided to write his own.

[calm]

He called it SQLite. The name was a description rather than a brand. It was a small, embeddable engine that spoke the standard SQL query language. The whole database fit inside a single library that an application could link to. The database itself was just a file on disk. There was no server. There was no administrator. There was nothing to configure. The application opened the file, ran queries, wrote results, closed the file. That was the entire interaction.

The original Navy project was completed. The database survived the project. Hipp continued developing it. He released it as open source under the most permissive license possible, the public domain. He explicitly disclaimed any ownership over the code. Anyone could use it, modify it, sell it, embed it in commercial products, without any obligation to him or to anyone else.

Twenty-five years later, SQLite is the most-deployed piece of software in human history. There are estimated to be over one trillion individual SQLite databases in existence. Every iPhone has dozens of SQLite databases inside it. Every Android phone has dozens. Every web browser uses SQLite for its bookmarks, history, and cookies. The Boeing seven-eight-seven aircraft uses SQLite. The Apollo lunar lander did not use it because it predated SQLite, but if there had been a lunar lander developed after two thousand, it probably would have. The Navy contract that started everything was small. The result of the contract reshaped how the world thinks about databases.

Why SQLite Won

To understand SQLite's success, you have to think about what most people actually want from a database. Most applications do not need the kind of database that runs in a data center with administrators and replication and high availability. Most applications just need to store some data and read it back later. The data is not big. The traffic is not high. The reliability requirements are real but modest. The application is one program, running on one machine, with one user.

For this case, which is most cases, a server database is overkill. The server database brings enormous complexity to solve problems the application does not have. It also brings real costs. The server has to run. The administrator has to administer. The network has to be reachable. Each of these is a potential failure mode that the application would not otherwise have.

SQLite eliminates all of this. The application just opens a file. The file is the database. The application reads and writes to it through the SQLite library, which is linked into the application itself. There is no separate process. There is no network. There is nothing to fail except the application itself, which would fail anyway. The database adds no new failure modes.

This sounds like a small thing. It is not. It is the difference between an application that can be deployed anywhere and an application that requires infrastructure. The application that requires infrastructure is harder to use, harder to maintain, and more expensive to run. The application that can be deployed anywhere is none of these. SQLite makes applications easier to write and easier to live with, at no cost to the user.

The same logic applies to journalism. A working reporter does not need a database server. The reporter needs to store some data and query it later. The data might be company records, document metadata, fact-checking notes, contact information, source references. The reporter wants to access this data on their own laptop, sometimes offline, sometimes from a notebook in a coffee shop. The reporter does not want to run a server. The reporter does not want to configure anything. The reporter wants the database to disappear into the workflow.

SQLite does this. The database is just a file in a folder. The reporter can copy it, back it up, version-control it, email it to themselves, store it on an external drive. The file is the database. The database is the file. The portability is total.

What The Query Language Actually Does

SQLite speaks SQL, which is the same query language used by every major database since the nineteen-seventies. The language is genuinely good. It was designed by people who thought carefully about what kinds of questions someone would want to ask of a database, and it has held up across five decades of changes in the underlying technology. The investment of learning SQL is an investment in a skill that will not become obsolete.

The basic structure of SQL is straightforward. You have tables, which are like spreadsheets, with rows and columns. You write queries that say which rows you want, which columns to return, and how to combine information from multiple tables. The query language is declarative, meaning you describe what you want, not how to compute it. The database figures out the most efficient way to produce the answer.

[serious]

For a reporter, this is genuinely useful. Suppose you have a table of companies and a table of permits. You can write a single query that says, give me every company that holds a permit issued in the last six months, sorted by the issue date, with the company's address and the permit type. The database does all the work. The reporter sees only the answer. The same logical question, written without SQL, might require dozens of lines of Python or hours of manual cross-referencing in spreadsheets. The SQL version is one or two lines.

The language has a learning curve. The basic queries are easy. The more advanced features take time to absorb. Most reporters who learn SQL spend a few weeks getting comfortable with it and then never need to relearn it. The investment is real. The payoff is the rest of your career.

The sqlite-utils Toolkit

There is a specific tool worth mentioning that makes SQLite particularly approachable for journalism. It is called sqlite-utils, written by Simon Willison, the same developer who created git-scraping and shot-scraper. The tool is both a Python library and a command-line program. It is built on top of SQLite and is designed to address the specific question of how to get data into SQLite without ceremony.

The classic problem with any database is that the data starts somewhere else. The data might be in a comma-separated values file, an Excel spreadsheet, a JSON file, a web API. Before you can query the data with SQL, you have to load it into the database. This loading step is, traditionally, where most people give up. The data does not fit. The columns are misaligned. The character encoding is wrong. The dates are in a strange format. The loading is harder than the querying.

sqlite-utils solves this with a simple command. You point it at a file. It reads the file. It guesses the structure. It creates the appropriate table in your SQLite database. It loads the data. The whole operation takes one line and produces a queryable database. The tool handles all the common edge cases automatically. The data shows up. You start querying.

For working journalism, this matters enormously. The friction between data and analysis collapses. The reporter does not need to spend a day cleaning the data before they can start asking questions. The data is in the database within minutes. The questions can start being asked immediately. The pattern accelerates the work of investigation in a way that is hard to overstate.

The same tool also includes features for transforming data inside the database. You can rename columns, extract substrings, convert types, join tables, all from the command line or from a short Python script. The investigation grows as the data grows. The database is not a snapshot but a living workspace where the data and the questions and the answers all coexist.

The Personal Database Pattern

The pattern that emerges from sqlite-utils and SQLite together is what some journalists have called the personal database pattern. The idea is that every working reporter should maintain a long-running personal database of facts relevant to their beat. Every company they have written about. Every person they have interviewed. Every document they have referenced. Every event they have covered. All of it goes into one SQLite database file. The file lives on their laptop. The file is backed up to their preferred backup system. The file grows over time.

The benefit of this practice is that the reporter accumulates institutional knowledge in a queryable form. After five years on a beat, the reporter has thousands of records. The records are searchable. The records are joinable. The reporter can ask questions like, have I ever written about this company before, or, who is the chairman of every mining company I have records on, or, what was the date of the last permit issued to a company in this district. The answers come back in seconds, from a database the reporter has built one record at a time.

[calm]

This is different from how most journalism is practiced. Most journalists rely on memory, on notes, on filed-away articles, on web searches against their own previous work. Each of these is fragile and incomplete. The personal database is none of these things. The database is comprehensive in the sense that everything the reporter has chosen to record is there. The database is precise in the sense that the answers come from structured records, not from imperfect recall.

For a reporter starting this practice, the initial investment is real. The first few weeks of building the database feel slow. There is nothing in it yet. The queries return empty results. The benefit is invisible. By the third month, the database starts to be useful. By the second year, it is essential. By the fifth year, the reporter cannot imagine working without it.

The Public Domain Decision

There is one specific detail about SQLite worth mentioning, which is its license. The software is in the public domain. This is unusual. Most open-source software has some kind of license attached, even very permissive ones. SQLite has, intentionally, no license at all. The code belongs to nobody.

Hipp's reasoning was that the database was useful enough that he wanted nothing to stand between any potential user and the software. Even a permissive license like the Apache or BSD license requires the user to keep a copyright notice attached. The public domain release requires nothing. The user can do anything they want.

The consequence has been that SQLite is embedded in software that other licenses would have prevented. Companies that are nervous about open-source licensing have no such concerns about SQLite. The lack of license is not a bug. It is the most permissive possible position, and it has contributed materially to the trillion deployments. The software has spread because there was nothing to stop it.

This is a model that does not work for every project. The maintainers of SQLite have funded their work through paid consulting and a commercial extension, the SQLite Encryption Extension. The main database remains free. The encryption extension and the support contracts are how Hipp pays his rent. The model has worked. The company that develops SQLite is small, sustainable, and seemingly immortal.

What This Has To Do With Working Journalists

The practical use of SQLite for a working reporter is to have a database file for each significant investigation, plus a long-running personal database for everything that crosses the reporter's desk. The investigation databases are scoped to specific projects. The personal database is comprehensive. Both are SQLite files. Both are queryable with SQL. Both grow with the reporter's work.

[serious]

The skills are transferable. Learning SQLite is learning SQL. SQL applies everywhere. The next database the reporter encounters, whether PostgreSQL or MySQL or DuckDB, speaks roughly the same language. The investment compounds across the rest of the reporter's career.

The tools make this approachable. sqlite-utils handles the data import. Datasette, also by Simon Willison, provides a web interface for browsing and querying. DB Browser for SQLite provides a graphical interface for editing data by hand. The whole ecosystem around SQLite is unusually mature, unusually free, and unusually useful for journalism.

The Larger Pattern

The thing worth saying about SQLite, as a closing observation, is that it represents a specific philosophy about what good software does. The philosophy is that good software gets out of the way. It solves the problem it is supposed to solve. It does not add new problems. It does not require attention. It does not have opinions about how the user should structure their life. It just works, quietly, for decades, while the user gets on with whatever the actual work is.

[calm]

This is rarer than it should be. Much software is built to demand attention, to require maintenance, to push the user into a specific workflow that benefits the software's makers. SQLite is the opposite. It demands nothing. It requires no maintenance from the user. It accommodates whatever workflow the user wants. The software is essentially invisible.

For a working journalist, this invisibility is exactly right. The journalism is the work. The tools should disappear into the work. SQLite disappears. The database is just a file. The queries are just questions. The answers are just results. The reporter writes the article. The article gets published. The database is still sitting there, quietly, ready for the next question, ready for the next investigation, ready for the next year. The Navy contract from two thousand has produced, by accident, the most durable piece of journalism infrastructure available. The fact that it was an accident is part of what makes it work. The reporter who learns SQLite is investing in a piece of software that will outlast all of us, and probably most of what comes after.