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:
