753a13df

Author: Michael Lynch <git@mtlynch.io>

Committer: Michael Lynch <mtlynch@noreply.codeberg.org>

Apply SQLite pragmas to every pooled connection (#326)

An *sql.DB is a connection pool, not a connection. SQLite scopes most
pragmas to the connection that set them, so the single db.Exec that
configured the store only ever configured whichever pooled connection
happened to serve that one call. Every additional connection that
database/sql opened afterwards ran on SQLite's defaults.

Measured against a real on-disk database with five simultaneous
connections, the old wiring left wal_autocheckpoint at SQLite's default
of 1000 on four of the five, and likewise left synchronous at FULL and
temp_store unset.

wal_autocheckpoint is the one that matters for durability. Litestream
takes ownership of checkpointing and asks applications to disable
SQLite's automatic checkpoints (https://litestream.io/tips/) so that
SQLite cannot recycle WAL frames that Litestream has not replicated yet.
Leaking that setting on most connections meant the app was checkpointing
behind Litestream's back on nearly every write.

Pass a per-connection init callback to driver.Open instead. The ncruces
driver invokes it on every new connection, so the settings now apply
uniformly.

Two details worth recording:

  - journal_mode stays a one-time Exec. It is persisted in the database
    file header rather than scoped to a connection, so running it per
    connection would be wasted work and could return SQLITE_BUSY once
    other connections are live. Running it immediately after Open also
    forces the first connection open, which surfaces any error from the
    init callback that database/sql would otherwise defer.

  - busy_timeout = 5000 is dropped rather than moved. It was a
    regression: this driver already applies a 60s busy timeout to every
    connection, so the old code was lowering the timeout to 5s on the
    single connection it touched. Litestream's tip assumes SQLite's bare
    default of 0, which does not apply here.

foreign_keys is kept in the callback for uniformity, but note it was not
actually broken before. This driver's embedded SQLite is compiled with
foreign keys on by default, verified at 1 on a connection with no pragmas
applied at all.

store/sqlite/litestream.go is deleted; its two remaining pragmas move
into the callback behind the same OptimizeForLitestream flag. The flag is
retained because wal_autocheckpoint = 0 is unsafe without Litestream --
nothing else would checkpoint, so the -wal file would grow without bound.
Two callers still depend on that: the TinyBeans importer and the test
store.

Co-Authored-By: Claude <noreply@anthropic.com>

Reviewed-on: https://codeberg.org/mtlynch/little-moments/pulls/326

Timeline