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!