How SQLite Became the Most Deployed Database

You save a note on your phone. You close the app. You open it again three hours later, on a plane with no signal, and the note is there. No spinner, no “connecting” message, no error. Something on the device itself held that text and handed it back to you.

That something is almost certainly SQLite.

A database in a single file

SQLite is a C library that implements a small, fast, self-contained SQL database engine. It reads and writes directly to an ordinary disk file, with no server process in between. The entire database, tables, indexes, triggers, and all, lives in one file you can copy, email, or drop on a USB stick.

The project’s own documentation calls it “an in-process library” and notes that it is “the most widely deployed database in the world.” That claim is not marketing. It is an arithmetic result of the places SQLite ends up.

The problem on a destroyer

D. Richard Hipp began SQLite on May 29, 2000, according to the SQLite developers page. He was working for General Dynamics on a contract with the United States Navy, designing software for a damage-control system aboard guided-missile destroyers. The system originally ran on HP-UX with an IBM Informix database back-end.

That back-end was a client/server database. It needed a server process. It needed someone to start it, stop it, configure it, and restart it when it crashed. On a ship, there is no database administrator. There is no data center down the hall. The software had to work without expert human support, or the system failed.

Hipp’s constraint was not performance. It was the absence of a person.

The design that fell out of that constraint

SQLite is serverless. The SQLite project’s “Distinctive Features” page explains it plainly: “With SQLite, the process that wants to access the database reads and writes directly from the database files on disk. There is no intermediary server process.” The application calls functions inside the SQLite library, and the library handles the file I/O.

Under the hood, SQLite compiles SQL text into bytecode, then runs that bytecode on a virtual machine, according to the project’s architecture documentation. That design keeps the library self-contained: no external dependencies, no network protocol, no separate process to manage.

The consequences of that choice cascade through everything the reader notices.

Zero-configuration. There is no installation procedure, no setup, no configuration files, and no administrator to create a database instance or assign permissions. The “Distinctive Features” page states: “SQLite just works.” Any program that can access the disk can use an SQLite database.

Single file. A complete SQLite database with multiple tables, indexes, triggers, and views is contained in one ordinary disk file. That file is cross-platform. The “Long Term Support” page says database files are “bit-for-bit identical on 32-bit, 64-bit, big-endian, and little-endian platforms.” You can copy a database from a Windows laptop to an Android phone to a Linux server without converting anything.

Flexible typing. SQLite is forgiving about datatypes. If you insert the text string ‘wxyz’ into a column declared INTEGER, SQLite does not throw an error. It stores the string. The “Quirks” page says this is intentional: SQLite is “flexibly typed” while other engines are “rigidly typed.” This causes problems when developers prototype with SQLite and then move to PostgreSQL, where the same insert fails. But for an embedded database that has to keep working no matter what, flexibility is a feature.

All of these choices trace back to the destroyer. A database that needs no administrator, no server, and no conversion between platforms is a database that can run on a ship, in a car, or in a browser tab.

How that design ended up everywhere

The SQLite project maintains a page called “Most Widely Deployed SQL Database Engine.” It lists the places SQLite is found:

Airbus confirms that SQLite is used in the flight software for the A350 XWB family of aircraft, according to the “Well-Known Users” page. Bosch uses SQLite in multimedia systems installed in GM, Nissan, and Suzuki automobiles. Adobe uses SQLite as the application file format for Photoshop Lightroom. The United States Library of Congress recognizes SQLite as a recommended storage format for long-term archival.

The arithmetic behind “most widely deployed” comes from phones. The “Most Widely Deployed” page notes there are more than 4.0 billion smartphones in active use. Each holds hundreds of SQLite database files. The project’s estimate is over one trillion (1e12) active SQLite databases.

Some of those databases are obvious: your browser history, your bookmarks, your notes app. Others are invisible. Your phone’s contacts, your text messages, your calendar, your photo metadata, your Wi-Fi passwords. All SQLite files. The database engine is not something you install. It is something that is already there.

The one thing it doesn’t do well

SQLite has a concurrency limit. It supports an unlimited number of simultaneous readers, but only one writer at any instant, as the SQLite forum documentation explains. If two processes try to write to the same database file at the same time, one has to wait.

For a phone, a browser, or a desktop application, this is not a problem. The user is one person, and the writes are quick. For a high-traffic website with many simultaneous writers, a client/server database is the right tool. The SQLite “Appropriate Uses” page says SQLite works well for sites that get fewer than 100,000 hits per day, a conservative estimate. It notes that SQLite has been demonstrated to handle ten times that amount.

The project’s write-ahead logging (WAL) mode improves concurrency. It allows readers and writers to proceed at the same time. But WAL has a limitation: all processes using the database must be on the same host computer. It does not work over a network filesystem. For a database that lives inside a single device, that is not a constraint. For a shared database on a network drive, it is.

What the file format pledge means for you

The SQLite developers pledge to support the file format through the year 2050. The “Long Term Support” page states: “The intent of the developers is to support SQLite through the year 2050. At this writing, 2050 is still 25 years in the future. Nobody knows what will happen in that time, and we cannot absolutely promise that SQLite will be viable or useful that far out. But we can promise this: we plan as if we will be supporting SQLite until 2050.”

That pledge has a practical consequence. A SQLite file you create today will be readable by a version of SQLite released decades from now. The format is documented, stable, and backwards compatible. The Library of Congress recommends it for archival storage for exactly this reason.

If you are choosing a format for data you want to keep, a SQLite file is a safer bet than a proprietary application format. It is one file, it is cross-platform, and the people who maintain it have promised not to break it. That is not a common promise in software.