SQLite: The Database You Already Have and Probably Underuse

There is a reflex, the moment a project needs to store anything, to stand up Postgres. A container, a connection string, a user, a password, a port, a backup strategy, a thing to keep running and patched. And for an enormous number of projects, all of that ceremony is in service of a workload a single file on disk could handle without breaking a sweat. That file is SQLite, it is almost certainly already installed on whatever you are reading this on, and it is one of the most underused tools in the business.

I’ve watched myself make this mistake more than once — spinning up a database server for a side project that had, on a busy day, a few hundred writes and a few thousand reads, then spending an evening on backups and connection pooling for a workload a laptop could serve in its sleep. The Postgres reflex is so strong that “which server database” gets decided before anyone asks whether a server is needed at all. This post is the argument for asking that question first.

What it is, and what it isn’t

SQLite is a complete SQL database engine that lives inside your application as a library. There is no server process. There is no network. Your program calls into SQLite directly, and SQLite reads and writes a single ordinary file containing the entire database — tables, indexes, the lot. You can copy that file, email it, commit it, or delete it like any other file, because that is all it is.

That “no network” point deserves emphasis, because it’s where most of the speed comes from. Every query against a client-server database is a round-trip: your process serialises a request, sends it over a socket, waits, and deserialises a response. SQLite is a function call into a library already loaded in your process. For the read-heavy access pattern most applications actually have, eliminating that round-trip is a larger performance win than any query tuning you’d do on the server side.

It is not trying to be Postgres. There is no concurrent-writer cluster, no fine-grained user permissions, no replication built in. SQLite has exactly one writer at a time per database, though it serves any number of simultaneous readers happily. The mistake people make is assuming this disqualifies it from real work. It does not. It disqualifies it from a specific shape of real work — many machines hammering writes at one shared store — that far fewer applications actually have than the reflex assumes.

It’s also worth killing a myth: SQLite is not a toy. It is arguably the most rigorously tested software on the planet, with a test suite that exercises millions of lines of test code against a comparatively tiny engine, including deliberate power-loss and out-of-memory simulations. It’s the database inside your browser, your phone, your car’s infotainment, and countless embedded devices precisely because it’s boring and reliable in a way flashier engines aren’t.

Getting started is genuinely trivial

There is nothing to install on most systems; the sqlite3 command-line tool ships with macOS, most Linux distributions, and every mainstream language’s standard library or near-universal package set. Here is the entire setup:

1
2
3
4
5
6
7
8
9

$ sqlite3 app.db
sqlite> CREATE TABLE notes (
   ...>   id      INTEGER PRIMARY KEY,
   ...>   body    TEXT NOT NULL,
   ...>   created TEXT DEFAULT (datetime('now'))
   ...> );
sqlite> INSERT INTO notes (body) VALUES ('first note');
sqlite> SELECT * FROM notes;
1|first note|2023-11-14 09:14:02

That is a real, indexed, queryable database, created in four lines, with no daemon and no configuration. From Python it is just as direct — the sqlite3 module is in the standard library, so import sqlite3 and you have a database. No dependency to add, no service to manage. Compare that to the docker-compose.yml, the connection pooler, and the migration tooling a server database drags in before it stores a single row; the whole single-file-stack ergonomic I like about Compose is one that SQLite simply doesn’t need for the data layer, because there’s no data-layer service to compose.

The one knob that matters

If you take a single piece of tuning advice away, make it this: turn on WAL mode. By default SQLite uses a rollback journal that makes writers and readers block each other more than they need to. Write-Ahead Logging changes that, letting reads proceed while a write is in flight, and it dramatically improves concurrency for the read-heavy workloads most applications actually have.

1
2
3
4

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;

Run those at startup. WAL mode persists with the database file, but the others are per-connection, so set them on every connection. synchronous = NORMAL trades a sliver of crash durability for a large speed gain and is the right default under WAL for most things. foreign_keys = ON is, frustratingly, off by default for historical compatibility, so you must opt in to the referential integrity you almost certainly want. And busy_timeout is the one people forget: without it, the instant a second writer collides with the single-writer lock you get an immediate SQLITE_BUSY error rather than a short, polite wait. Setting it to a few seconds turns most write contention from a crash into a brief pause.

Where it genuinely shines

The obvious case is application storage where the application and its data live on one machine: desktop apps, mobile apps, command-line tools that need to remember things between runs. SQLite is the most deployed database engine in the world precisely because it is sitting inside your browser, your phone, and countless devices, quietly doing exactly this.

Less obvious, and where I keep reaching for it, is server-side. A modest website or internal service running on a single box can serve a remarkable amount of traffic from SQLite, because reads are cheap and local and there is no network round-trip to a database server at all. It is also superb for analysis: point it at a CSV, run SQL over it, and you have a more powerful query tool than most spreadsheets, with none of the setup.

The one workload where WAL’s single-writer limit genuinely bites is sustained concurrent writes — a busy multi-tenant SaaS taking constant inserts from many sources at once. If that’s you, you’ll feel the serialised writer, and Postgres is the honest answer. But a great many “web apps” are 95% reads and a trickle of writes, and for those the limit is theoretical.

There’s a whole category people forget: SQLite as an application file format. Because the database is a single, portable, self-describing file with a stable format guaranteed to be readable for decades, it’s an excellent container for structured documents — think of a design tool saving a project, or a note app storing a notebook. You get transactions, queries, and partial reads for free, instead of inventing a bespoke binary blob or a pile of loose files. The SQLite authors argue, persuasively, that it often beats a custom fopen() format precisely because you inherit atomic writes and crash resistance you’d otherwise have to build by hand.

Backups: the honest gap, and how to fill it

Here’s where you have to be a grown-up about the trade-off. “The whole database is one file you can cp” is true, but naively copying a live SQLite file while it’s being written to can capture a torn, inconsistent snapshot. There are two correct ways to back it up, and neither is hard.

For a point-in-time copy, use SQLite’s own online backup, which reads a consistent snapshot even under concurrent writes:

1
2

# consistent snapshot of a live database
sqlite3 app.db ".backup /backups/app-$(date +%F).db"

For continuous, near-real-time protection, Litestream streams the WAL to object storage (S3, or any S3-compatible bucket) as writes happen, so you can restore to within seconds of a crash. It runs as a small sidecar process and, for a single-node service, gets you most of what people spin up a whole replicated database cluster to achieve. It’s actively maintained; just pin a known-good version rather than tracking bleeding-edge releases blindly, as replication tools occasionally ship subtle regressions.

Either way, back it up on a schedule and test the restore — a backup you’ve never restored is a hypothesis, not a safeguard. This is the same discipline I bang on about in why every side project should have a backup plan: the single-file simplicity is a feature only if you’ve actually verified you can get the file back.

Troubleshooting: the errors you’ll actually meet

SQLITE_BUSY / “database is locked” is the classic. It means a second connection wanted the write lock while another held it. Nine times out of ten the fix is the busy_timeout pragma above, plus making sure you’re not holding a write transaction open longer than necessary. If it persists under WAL, check that all connections are on the same filesystem — WAL does not work correctly over network filesystems like NFS, because it relies on shared-memory coordination between processes on the same host.

The -wal and -shm files won’t go away after you stop the app. That’s normal; they’re WAL bookkeeping. A clean shutdown or a PRAGMA wal_checkpoint(TRUNCATE) folds the WAL back into the main file. Don’t delete them by hand while the database is open.

A database file grows and never shrinks even after you delete rows. SQLite marks freed pages as reusable but doesn’t return them to the OS until you VACUUM. Run VACUUM occasionally, or enable PRAGMA auto_vacuum before the database is populated if you expect a lot of churn.

“Disk I/O error” on a mounted volume almost always means a filesystem that lies about fsync — some network mounts and a few container storage drivers do. SQLite’s durability guarantees are only as good as the storage honouring its flushes; keep the file on real local disk.

Is it worth it?

If you genuinely need many servers writing concurrently to shared state, or row-level security, or built-in replication, then no — reach for Postgres and accept its operational cost as the price of those features. Be honest with yourself about whether you have that problem, though, because the reflex to assume you do is strong and usually wrong.

For everything else — single-machine apps, read-heavy services, local tooling, throwaway analysis, prototypes that quietly become production — SQLite is very often not the cheap compromise but the correct answer. It removes an entire moving part from your stack: no server to run, secure, back up, or wake you at three in the morning. That reduction in moving parts is exactly the kind of hidden operating cost I weigh in self-hosting is not free — every daemon you don’t run is time you don’t spend maintaining it.

Reach for it first. Graduate to a server database when you can name the specific limit SQLite hit — a real concurrency wall, a genuine need for replication — not before. You will be surprised how rarely that day comes.