Pure Go SQLite With modernc and When to Choose It Over cgo

modernc.org/sqlite is SQLite for Go with no C in the build. The comparison with the cgo driver covers why you would choose it. This article is about using it: how to configure it, which settings matter for a service, and one failure you will meet in the first hour.

The DSN is the configuration

SQLite settings are PRAGMA statements, and most of them apply per connection. A database/sql pool opens connections on demand, so running a pragma once after Open configures one connection and leaves the others at defaults. The modernc driver solves this with _pragma parameters in the DSN, which it applies to every connection it opens:

file:app.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)&_pragma=synchronous(NORMAL)

The file: prefix makes SQLite parse the part after ? as URI parameters. The four pragmas are the usual starting set for a service.

The failure and the fix

package main

import (
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"sync"
	"sync/atomic"

	_ "modernc.org/sqlite"
)

func writers(dsn string, n int) (ok, busy int64, firstErr string) {
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		panic(err)
	}
	defer db.Close()
	db.SetMaxOpenConns(n) // several connections, so writes really contend
	_, _ = db.Exec(`create table if not exists w (id integer primary key autoincrement, v int)`)
	var wg sync.WaitGroup
	var o, b atomic.Int64
	var once sync.Once
	for i := range n {
		wg.Add(1)
		go func() {
			defer wg.Done()
			for j := range 50 {
				if _, err := db.Exec(`insert into w (v) values (?)`, i*100+j); err != nil {
					b.Add(1)
					once.Do(func() { firstErr = err.Error() })
					continue
				}
				o.Add(1)
			}
		}()
	}
	wg.Wait()
	return o.Load(), b.Load(), firstErr
}

func main() {
	dir, _ := os.MkdirTemp("", "sq579")
	defer os.RemoveAll(dir)

	// 1. Defaults: a rollback journal and no busy timeout.
	p1 := filepath.Join(dir, "default.db")
	ok, busy, first := writers("file:"+p1, 8)
	fmt.Printf("defaults:                 ok=%d busy>0=%v\n", ok, busy > 0)
	fmt.Printf("  first error: %.60s\n", first)

	// 2. WAL plus a busy timeout, set per connection through the DSN.
	p2 := filepath.Join(dir, "tuned.db")
	dsn := "file:" + p2 + "?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)&_pragma=synchronous(NORMAL)"
	ok, busy, _ = writers(dsn, 8)
	fmt.Printf("WAL + busy_timeout(5000): ok=%d busy=%d\n", ok, busy)

	// 3. Confirm the pragmas applied.
	db, _ := sql.Open("sqlite", dsn)
	defer db.Close()
	var mode string
	var fk, bt int
	_ = db.QueryRow(`pragma journal_mode`).Scan(&mode)
	_ = db.QueryRow(`pragma foreign_keys`).Scan(&fk)
	_ = db.QueryRow(`pragma busy_timeout`).Scan(&bt)
	fmt.Printf("pragmas read back: journal_mode=%s foreign_keys=%d busy_timeout=%d\n", mode, fk, bt)

	// 4. Single writer connection, many readers: the other usual layout.
	rw, _ := sql.Open("sqlite", dsn)
	rw.SetMaxOpenConns(1)
	defer rw.Close()
	_, err := rw.Exec(`insert into w (v) values (1)`)
	fmt.Println("single writer pool insert:", err)
}

go run . 2>&1

defaults:                 ok=11 busy>0=true
  first error: database is locked (5) (SQLITE_BUSY)
WAL + busy_timeout(5000): ok=400 busy=0
pragmas read back: journal_mode=wal foreign_keys=1 busy_timeout=5000
single writer pool insert: <nil>

The program runs the same workload twice: eight goroutines, each on its own connection, inserting 50 rows each into a file database.

With defaults, the database uses a rollback journal and a zero busy timeout. A writer that finds the database locked by another connection gives up immediately. The output shows busy>0=true and the first error, database is locked (5) (SQLITE_BUSY). Only 11 of the 400 inserts succeeded in the recorded run. This is the first thing most people see with SQLite from Go, usually in a service that has two requests writing at once, and it looks like a bug in the driver. It is SQLite's locking model working as designed, with a configuration that gives writers no patience.

With WAL and busy_timeout(5000), all 400 inserts succeeded and none returned busy. WAL lets readers proceed during a write and makes lock hand-off cheaper, and the busy timeout makes a blocked writer retry for up to five seconds. The writers still run one at a time, as SQLite requires; they wait in line instead of failing.

The pragmas read back correctly from a new connection: journal_mode=wal foreign_keys=1 busy_timeout=5000. That is the check to run when you suspect a setting is not applying. Read it from a connection the pool opened later, not the first one.

A pool shaped for SQLite

The last line of the program opens the same DSN with SetMaxOpenConns(1) and inserts successfully. With one connection, database/sql serialises access in Go: callers wait for the connection and never see a busy error from SQLite. It is the simplest way to be safe, and what many small services do. Its limit is that reads queue behind writes. The two-pool layout fixes that:

• a writer pool with SetMaxOpenConns(1);
• a reader pool with several connections and the DSN parameter mode=ro or _pragma=query_only(1).

In WAL mode the readers proceed concurrently with the writer. Route queries by intent: a small wrapper type with Read() and Write() methods keeps this tidy. Start transactions that will write with BEGIN IMMEDIATE (through a driver parameter for the transaction lock mode or by executing it) so the write lock is taken up front and a later upgrade cannot fail with a busy error in the middle.

Other driver specifics

Driver name and time. The name is sqlite. Times stored from time.Time are written as text in a format the driver chooses, and reading them back into time.Time works for columns declared DATETIME or TIMESTAMP; UTC is the safe choice.

Errors. The driver exposes *sqlite.Error with a Code() method that returns the SQLite result code, such as SQLITE_CONSTRAINT_UNIQUE. Classify errors with errors.As and compare the code, not the text.

No loadable extensions. The translated library cannot load C extension files. If you need FTS tokenizers or vector extensions beyond what is built in, the cgo driver is the route.

Versions. v1.50.0 bundles SQLite 3.53.0. Pin it, and upgrade deliberately; later releases require newer Go versions.

Why the default behaves this way

A rollback-journal database takes a lock on the whole file when a connection writes. A second connection that tries to write while the first holds the lock either waits, if a busy handler is installed, or returns SQLITE_BUSY at once. The default busy handler does nothing. In WAL mode a writer appends to a separate log file and readers see a consistent snapshot, so the lock is only needed between writers and is held for a short time. The busy timeout turns the remaining contention into a short wait.

There is one case the timeout cannot fix, and it matters for correctness. If a transaction first reads and then writes, and another connection commits a write in between, SQLite cannot upgrade the first transaction's read lock and returns SQLITE_BUSY immediately, regardless of the timeout, because waiting would deadlock. The answer is to start such transactions as write transactions from the beginning (BEGIN IMMEDIATE), or to retry the whole transaction on a busy error, as the retry article does for PostgreSQL's serialization failures. The structure is the same: classify the error, roll back and run the unit of work again.

Measuring before you tune

The lab's numbers are about contention, not about speed. If you wonder whether WAL or synchronous(NORMAL) is worth it for your workload, measure your own writes with a benchmark and compare runs with a statistics tool; see benchmarks. synchronous(NORMAL) removes an fsync per transaction in WAL mode, which is a large effect on a laptop disk and a smaller one on server storage, and the trade-off is durability on power loss. Decide it as you would for any database: how much recent data can you afford to lose?

Checklist

1. DSN with file: and the four pragmas.
2. One writer connection, or a busy timeout, never neither.
3. WAL for any service with concurrent access.
4. A read the pragmas back test at start-up.
5. Backups with VACUUM INTO, not by copying a live file.

The SQLite pages on write-ahead logging and busy handling explain the model behind these results, and the driver's own documentation lists the DSN parameters. For the pool settings in the standard library, see the database/sql explainer.