SQLite Connection Configuration

These notes are basically cribbed from Ben Johnson’s excellent “Building Production Applications Using Go & SQLite” GopherCon talk, specifically the “Configuring SQLite” section.

Generally speaking, you’ll want to set three PRAGMAs on each SQLite connection:

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;

In turn,

PRAGMA journal_mode = WAL; sets the database into WAL journaling mode. Unlike the others, this doesn’t necessarily need to be set on each connection; once a database is in WAL mode it’ll stay in that mode across database connections. WAL journaling mode is recommended for most SQLite server applications, because it makes writers not block readers.

PRAGMA busy_timeout = 5000; sets the busy_timeout to 5000 milliseconds (5 seconds). Without this setting, if a write transaction is running, and another write transaction starts, the second write transaction will fail immediately.

PRAGMA foreign_keys = ON; turns on SQLite’s foreign key support. Foreign Keys aren’t enabled by default in SQLite for historical reasons, but foreign keys are great for maintaining data integrity. So – use them!