Skip to content
~/freshteapot

Writing

When SQLite connection string order is API semantics

How two Go SQLite drivers handle pragma order, and why busy_timeout belongs before journal_mode.

3 min read
#Go#Sqlite
On this page

I was configuring two Go services to share a SQLite database. The connection string included a busy timeout and WAL mode:

?_busy_timeout=10000&_journal_mode=WAL

Then I found a comment in PocketBase: busy_timeout must be set first so that the connection will wait if switching to WAL encounters a busy database.

This is one of those cases where string order can be API semantics, not just presentation. The precise behaviour, however, belongs to the database driver.

Two drivers, two interfaces

PocketBase uses modernc.org/sqlite and supplies repeated generic _pragma parameters:

?_pragma=busy_timeout(10000)&_pragma=journal_mode(WAL)

In modernc.org/sqlite v1.57.0, the driver collects those values, moves busy_timeout first, sorts the rest, and then executes them. The order written by the caller expresses the intention, but this version of the driver also enforces the important part.

github.com/mattn/go-sqlite3 presents dedicated options instead:

?_busy_timeout=10000&_journal_mode=WAL

It parses the query string into named parameters, then applies them in its own fixed order. It sets PRAGMA busy_timeout well before PRAGMA journal_mode. Swapping those two options in the original string does not change their execution order.

The lesson is small but useful: do not infer connection initialisation order from the appearance of a DSN. Read the driver. PocketBase’s comment explained why the ordering matters; the two driver implementations showed where that ordering actually comes from.

A short history

There is a direct trail for the behaviour in modernc.org/sqlite.

In November 2024, a user reported intermittent SQLITE_BUSY errors while opening pooled connections. Their DSN placed busy_timeout after journal_mode(WAL). A new connection could therefore attempt to set WAL before installing its busy handler, and fail immediately if another connection held the required lock.

The reporter compared the behaviour with mattn/go-sqlite3. Mattn’s driver already applied busy_timeout early, regardless of its position in the DSN. In the diagnostic comment, they showed that applying busy_timeout first stopped the errors. They then proposed a consistent pragma order. The maintainer confirmed that the suggestion had been incorporated in the fix titled apply busy_timeout pragma early.

The root cause lies in SQLite itself. busy_timeout installs a busy handler on a connection. Changing journal_mode changes persistent database state and may need a lock. The handler must be ready before that work begins.

The sequence is therefore: SQLite’s locking semantics, Mattn’s established ordering, a reproducible failure in modernc, an upstream fix, and PocketBase’s concise comment preserving the reason.

Searched for

  • busy_timeout sqlite
  • modernc sqlite pragma order SQLITE_BUSY
  • mattn go-sqlite3 busy_timeout journal_mode order

References