SQLite Production Configuration

Notes taken from Ben Johnson’s talk Building Production Applications Using Go & SQLite.

The complete list of pragmas can be found on SQLite’s official website.

Journal Mode

Journal mode configures how SQLite writes transactions. You almost always want it as WAL (write ahead log).

PRAGMA journal_mode = WAL;

Busy Timeout

Busy timeout sets how long write transactions will wait to start. If unset, writes will fail immediately if another write is running. Five seconds might be enough... most of the times.

PRAGMA busy_timeout = 5000;

Foreign Keys

Believe it or not, for historical reasons foreign keys are not enforced by default.

PRAGMA foreign_keys = ON;

Usage in Go

This is a Go example showing how to use the options above with the github.com/mattn/go-sqlite3 driver.

db, err := sql.Open(
    "sqlite3",
    "file.db?_busy_timeout=5000&_journal_mode=WAL&_foreign_keys=on",
)
if err != nil {
    log.Fatalln("could not open database: %w", err)
}
defer func() {
    if err := db.Close(); err != nil {
        log.Fatalln("could not close database: %w", err)
	  }
}()