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)
	  }
}()

Articles from blogs I follow around the net

Variations of the Range kata

In the languages I usually employ. The Range kata is succinct, bordering on the spartan in both description and requirements. To be honest, it's hardly the most inspiring kata available, and yet it may help showcase a few in…

via ploeh blog January 1, 2024

Some notes on NixOS

Hello! Over the holidays I decided it might be fun to run NixOS on one of my servers, as part of my continuing experiments with Nix. My motivation for this was that previously I was using Ansible to provision the server, but then I’d ad hoc installed a bunch…

via Julia Evans January 1, 2024

I found some of my first code! Annotating and reflecting on robotics code from 2009.

In high school, one of my teachers shattered my plans for my life, in the most beautiful way. Most of my life, I'd intended to become a math professional of some sort: a math teacher, when that was all I saw math for; an actuary, when I started to lear…

via ntietz.com blog January 1, 2024

Generated by openring