SQLite in Production, Honestly

Tallowbrook, my invoicing tool, runs on one SQLite file on one small server. It has paying customers and it has been up through a year of deploys. People are surprised by this, usually in the tone you'd use for a friend who has decided to live in a van. I'm not here to talk you into it. I'm here to tell you what it costs.

What you get

No network hop to the database, so a query is a function call. No connection pool to size. Backups are one file: a nightly .backup into a dated copy, restored into a scratch database on the first of the month. The entire local development setup is a path. When something is slow, I can open the file in a shell and ask it why.

I won't pretend those are small advantages for a one-person company. Every service I don't run is a service that can't page me.

The one limit

SQLite lets many connections read and exactly one write at a time. In its write-ahead log mode, which you want for a web app, readers and writers stop blocking each other. The documentation puts it as "readers do not block writers and a writer does not block readers", and it adds the catch in the next breath: there is a single log file, so there can only be one writer at a time. (It also calls the no-blocking promise "mostly true", with a few obscure cases where a query still gets SQLITE_BUSY, so be ready for that error anyway.)

If your writes are small and quick, you may never notice. If every request writes and holds the transaction open across a network call, you'll notice quickly.

See it

This takes twenty seconds. One shell holds a write transaction open for four seconds while others try to use the database:

sqlite3 app.db "PRAGMA journal_mode=WAL; CREATE TABLE t(n INTEGER); INSERT INTO t VALUES(1);"
( printf 'BEGIN IMMEDIATE;\nINSERT INTO t VALUES(2);\n'; sleep 4; printf 'COMMIT;\n' ) | sqlite3 app.db &
sleep 1
sqlite3 app.db 'SELECT count(*) FROM t;'                        # reader
sqlite3 app.db 'INSERT INTO t VALUES(3);'                       # writer, no timeout
time sqlite3 app.db '.timeout 5000' 'INSERT INTO t VALUES(3);'  # writer, waits

On my machine the reader returned 1 immediately: the old snapshot, not blocked, not seeing the uncommitted row. The second writer printed Error: stepping, database is locked (5). The third waited about three seconds (time reported 3.004 real) and then succeeded. The final count was 3.

That second line is the failure you meet in production if you forget the timeout. The fix is one line of configuration: every connection sets a busy timeout, so a writer queues behind another instead of giving up.

My rules

1. WAL mode on, always.

2. A busy timeout on every connection, five seconds to start.

3. Short write transactions. Do the slow work first, then open the transaction, write, commit. Start it with BEGIN IMMEDIATE, so the busy timeout applies at the start and not halfway through.

4. One process owns writes. Background jobs go through the same code path as requests.

5. Test the restore, not just the backup.

When I'd leave

When I need a second server writing at the same time, or when the write rate is a real fraction of the limit, I'll move. I know the signal: the busy timeout starts showing up in latency graphs. Until then, one file is the least stressful part of my week.