SQLite in production: WAL mode and knowing when it's enough

SQLite is the most widely deployed database in the world, and it's no longer just for phones and desktop apps. With the right settings it can comfortably back a production web application — as long as you understand its concurrency model.

Rollback journal vs. WAL

By default SQLite uses a rollback journal: before changing a page, it copies the original page to a journal file. While a write is committing, readers are blocked.

Write-Ahead Logging (WAL) flips this around. Changes are appended to a separate -wal file, and readers keep reading the original database pages plus any committed WAL frames they need. The result:

• Readers don't block writers, and writers don't block readers.
• There is still only one writer at a time.
• Writes are usually faster, because appending to the WAL is sequential.

Enable it once — the setting is persistent for the database file:

PRAGMA journal_mode = WAL;

The PRAGMAs worth setting

Run these on every new connection (except journal_mode, which persists):

PRAGMA busy_timeout = 5000;     -- wait up to 5 s for a lock instead of failing immediately
PRAGMA synchronous = NORMAL;    -- safe in WAL mode; much faster than FULL
PRAGMA foreign_keys = ON;       -- off by default for backwards compatibility!

A note on synchronous = NORMAL in WAL mode: the database stays consistent even after a crash, but the most recent transactions may be rolled back after a power loss or OS crash. If you need every committed transaction to survive power loss, use FULL.

Handling "database is locked"

Because there's one writer, concurrent write transactions queue up. Two habits keep this painless:

1. Set busy_timeout, so writers wait instead of erroring.
2. Start write transactions with BEGIN IMMEDIATE, which takes the write lock up front. This avoids a deferred transaction that started as a read failing when it later tries to upgrade to a write.

BEGIN IMMEDIATE;
UPDATE inventory SET qty = qty - 1 WHERE sku = 'DB-101';
INSERT INTO orders (sku, qty) VALUES ('DB-101', 1);
COMMIT;

Keep write transactions short — the single write lock is shared by everyone.

Checkpoints

The WAL file grows until a checkpoint copies its pages back into the main database. SQLite runs automatic checkpoints (by default when the WAL reaches about 1,000 pages), but a checkpoint can't complete past the point a long-running reader still needs. Avoid holding read transactions open for a long time.

Backups

Don't just copy the database file while the app is running — you may miss data still in the WAL. Use the online backup API or:

VACUUM INTO '/backups/app-2026-08-05.db';

When SQLite is enough

SQLite is a great fit when:

• the application runs on a single server (or a single writer process),
• write volume is moderate and transactions are short,
• you value zero-ops simplicity and fast local reads.

Reach for a client-server database like PostgreSQL or SQL Server when you need many concurrent writers, multiple application servers writing to the same data, fine-grained access control, or built-in replication. Also note that WAL mode requires shared memory, so the database must not live on a network file system.

Key takeaways

1. Turn on WAL mode: readers and the writer stop blocking each other.
2. Set busy_timeout, synchronous = NORMAL and foreign_keys = ON.
3. Use BEGIN IMMEDIATE for writes and keep transactions short.
4. Back up with the backup API or VACUUM INTO, and keep the file on local disk.