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.

1 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.

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.

2 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:

$ 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.

3 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.

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

Run those once per connection at startup. WAL mode persists with the database file, but the others are per-connection. synchronous = NORMAL trades a sliver of crash durability for a large speed gain and is the right default for most things. And foreign_keys = ON is, frustratingly, off by default for historical compatibility, so you must opt in to the referential integrity you almost certainly want.

4 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.

5 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. The whole database is one file you can cp to back up.

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