SQLite is a remarkable piece of software. It is also a library that stores a b-tree in a file and negotiates with the operating system for the right to write to it. Calling that a database is like calling a filing cabinet a records management system. The cabinet is not the system. The system is the thing you have to build around the cabinet.

One writer, one queue

SQLite allows exactly one writer at a time. Not one writer per table. Not one writer per database. One writer, globally, for the whole file. Readers can proceed under WAL, but the moment two transactions want to write, one of them waits or gets SQLITE_BUSY.

This is fine when the writer is a cron job and the readers are a blog. It is not fine when the writer is a web request, and the web request has friends.

The write pathacquirewaitappendeventuallybusy timeoutretry loopWriter AWrite LockWriter BWALCheckpointApplication
The write pathEvery write is a queue with one server and no manager.

Diagram: The write path. Writer A connects to Write Lock (acquire). Writer B connects to Write Lock (wait). Write Lock connects to WAL (append). WAL connects to Checkpoint (eventually). Checkpoint connects to Writer B and fails (busy timeout). Writer B connects to Application asynchronously (retry loop).

WAL is not a scaling strategy

Write-ahead logging lets readers avoid the writer. It does not let two writers coexist. The WAL also has to be checkpointed back into the main database file, and checkpointing is a stop-the-world-ish operation that competes with your writes. A busy WAL is a queue of future latency.

The checkpoint stallappendgrowsthresholdcopy pagesblock brieflyp99 spikeWritesWALWAL SizeCheckpointMain FileWritersLatency
The checkpoint stallThe database is fast until it remembers it has to write the database.

Diagram: The checkpoint stall. Writes connects to WAL (append). WAL connects to WAL Size (grows). WAL Size connects to Checkpoint (threshold). Checkpoint connects to Main File (copy pages). Checkpoint connects to Writers and fails (block briefly). Writers connects to Latency asynchronously (p99 spike).

Types are a polite suggestion

SQLite has type affinity, not types. A column declared VARCHAR(10) will accept a forty-character string, a blob, or the number twelve. INTEGER is a preference. Strict tables exist now, which is a nice way of saying the default mode spent two decades accepting whatever arrived.

This is not a small detail. Every application above SQLite has to reimplement the type system in its own language and hope the two agree.

ALTER TABLE is a rumour

You can add a column, rename a column and drop a column. Anything else, including changing a type or adding a constraint, is a create-copy-drop dance. On a small database this is fine. On a large one it is a maintenance window with a progress bar you wrote yourself.

The migration everyone calls ALTER TABLECREATECOPY rowsrebuildDROPno undoOld TableNew TableIndex RebuildRollback
The migration everyone calls ALTER TABLECreate a new table, copy everything, drop the old one, hope nobody notices.

Diagram: The migration everyone calls ALTER TABLE. Old Table connects to New Table (CREATE). Old Table connects to New Table (COPY rows). New Table connects to Index Rebuild (rebuild). Index Rebuild connects to Old Table (DROP). Old Table connects to Rollback and fails (no undo).

SQLite is the right answer for embedded storage and single-process applications. It is the wrong answer for the thing the internet keeps telling you it is: a drop-in replacement for a database server, minus the server.