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

Simpler encapsulation with immutability

A worked example. I've noticed that many software organizations struggle with encapsulation with 'bigger' problems. It may be understandable and easily applicable to define a NaturalNumber type or ensure that a minim…

via ploeh blog June 12, 2024

Update on our infrastructure plans

Now that Drew has provided an update on the general state of SourceHut, I would like to follow up with one focusing on our infrastructure. Much has happened under the hood, a lot has not happened, and plans come and go as reality changes its mind every now a…

via Blogs on Sourcehut June 12, 2024

OpenBSD extreme privacy setup

# Introduction This blog post explains how to configure an OpenBSD workstation with extreme privacy in mind. This is an attempt to turn OpenBSD into a Whonix or Tails alternative, although if you really need that level of privacy, use a system from this…

via Solene'% June 10, 2024

Generated by openring