sqlitekit

Overview ¶

Package sqlitekit provides a wrapper around SQLite connection pools and the embedded SQL migrations used by the application.

Overview ¶

The package provides a single read-write pool for mutations, a read-only pool for concurrent readers, transactional helpers, and an embedded migration runner.

The DB type manages two pools:

• RW pool — always size 1, so writes are serialised automatically.
• RO pool — defaults to size 10; used for concurrent read-only queries.

Connections are obtained and returned via the DB.WithRW / DB.WithRO helpers. DB also co-ordinates shutdown so that DB.Close waits for borrowed connections to be returned before tearing down the pools. Callers should supply contexts with deadlines or timeouts to bound how long DB.Close will wait.

Connection rules ¶

*sqlite.Conn instances are borrowed from internal pools and must only be used inside the DB.WithRW / DB.WithRO callback where they were provided. Do NOT retain, reuse, or pass a borrowed *sqlite.Conn to other goroutines. The connection is not safe for concurrent use and doing so may cause data races or corruption-like behaviour.

Constructing a DB ¶

db, err := sqlitekit.New(ctx,
    sqlitekit.WithURI("file::memory:?mode=memory&cache=shared"),
    sqlitekit.WithMigrationsFS(migrationsFS),
)

New accepts zero or more DBOption values. If no URI is provided via WithURI, the value of the SQLITEKIT_DB environment variable is used; if that is also unset the default path "/var/lib/sqlitekit/sqlitekit.db" is used.

Useful options:

• WithURI — sqlite connection string (default: env SQLITEKIT_DB or "/var/lib/sqlitekit/sqlitekit.db").
• WithROPoolSize — read-only pool size (default: 10).
• WithMigrationsFS — filesystem of *.up.sql migration files (see below).
• WithSlowOpThreshold — log a warning when WithRW/WithRO takes longer than this (default: 500ms; 0 disables).
• WithLogger — slog.Logger for diagnostics (default: slog.Default()).
• WithRWFlags / WithROFlags — override sqlite open flags.

Nested calls ¶

DB.WithRW and DB.WithRO can be called from within an active callback. The active connection is stored in the context; nested calls detect it and reuse the same connection rather than borrowing a new one.

• Nested DB.WithRW inside DB.WithRW: a SQLite SAVEPOINT is used instead of a new transaction. Returning nil from the inner callback releases the savepoint (folding the inner changes into the outer transaction). Returning an error rolls back only to that savepoint; the outer transaction remains open and usable.

  If rolling back or releasing that savepoint itself fails, the failure is recorded and the outer transaction is rolled back instead of committed, even when the outer callback returned nil. The nested call returns an error joining the callback and cleanup failures, and the outer call returns one joining the cleanup failure with any outer callback error. A transaction whose savepoint cleanup failed is never committed.

• Nested DB.WithRO inside DB.WithRO, or DB.WithRO inside DB.WithRW: the same connection is reused with no additional transaction wrapping.

• Nested DB.WithRW inside DB.WithRO: not allowed — returns ErrRWInsideRO. A read-only connection cannot be promoted to read-write.

• A nested call naming a DB other than the one owning the active transaction returns ErrWrongDB without invoking the callback, since the active connection belongs to the outer database. This is checked before ErrRWInsideRO.

This means nested calls never deadlock on the size-1 RW pool and never create conflicting transactions.

The context passed to a callback carries that transaction's mutable state. The context itself is safe to hold and pass around, but a nested DB.WithRW or DB.WithRO call made with it runs against that transaction's single connection and shares its cleanup state, so such calls must not be made from multiple goroutines at once: a connection, like the transaction it belongs to, is single-threaded. The context must not be retained after the callback returns either — the connection has gone back to its pool, so a late call would run on a pooled connection without borrowing one, bypassing ErrDBAlreadyClosed and the pool's bookkeeping.

Shutdown ¶

DB.Close always marks the DB closed immediately (preventing new borrows) and then waits for outstanding connections to be returned. If the context expires before all connections drain, DB.Close returns context.Err and schedules pool teardown in a background goroutine that runs once the last connection is returned.

A subsequent DB.Close call with a fresh context will block until that background teardown completes, giving callers a reliable "fully closed" guarantee before process exit:

// Attempt graceful drain; accept timeout.
_ = db.Close(shortCtx)
// Wait for background teardown to finish.
_ = db.Close(context.Background())

Running work ¶

err = db.WithRW(ctx, func(ctx context.Context, c *sqlite.Conn) error {
    // executed inside a transaction; return nil to commit, any error to roll back
    return nil
})

err = db.WithRO(ctx, func(ctx context.Context, c *sqlite.Conn) error {
    // read-only; transaction is always rolled back
    return nil
})

Slow-operation logging ¶

When WithSlowOpThreshold is set (default: 500 ms), any DB.WithRW or DB.WithRO call that takes longer than the threshold emits a WARN log entry (via the configured logger) followed by an INFO completion entry. Each pair shares a monotonic sequence number for correlation. Set the threshold to 0 to disable slow-op logging entirely.

Migrations ¶

Migration files must be placed at the root of the provided filesystem and named with a zero-padded numeric prefix, a descriptive name, and the ".up.sql" suffix:

000001_create_users_table.up.sql
000002_add_indexes.up.sql

Files are parsed for their numeric prefix and applied in numeric order. Those at or below the current database version are already applied and are not run again.

Before anything is applied, the runner compares the database's recorded version against the highest migration this binary carries. A database written by a NEWER binary — one that knew about migrations this binary does not have — is refused with ErrMigrationDatabaseAhead, because migrations run forward only and continuing would run this binary's queries against a schema it does not know. The failure would otherwise surface later, per request, as an unexplained SQL error.

The runner validates the pending migrations for contiguity before applying any of them. Each pending migration's version must be exactly one greater than the one before it (the first must be currentVersion+1). If a gap is found the runner returns ErrMigrationUnexpectedVersionError and no migration is applied.

Migration state is maintained in a single-row table:

CREATE TABLE schema_migration (
    id      INTEGER PRIMARY KEY CHECK(id = 1),
    version INTEGER NOT NULL
);

The CHECK constraint guarantees only one row ever exists. The version is incremented by one after each migration script executes successfully.

To apply embedded migrations at startup, pass WithMigrationsFS to New:

db, err := sqlitekit.New(ctx, sqlitekit.WithMigrationsFS(migrations.FS))

Migration duration is bounded by the context passed to New. Most migrations are DDL and complete in milliseconds, but if you need an explicit timeout wrap the context before calling New:

ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
defer cancel()
db, err := sqlitekit.New(ctx, sqlitekit.WithMigrationsFS(migrations.FS))

Use WithMigrationsFS(nil) to skip migrations in tests.

Migration design: forward-only ¶

This package intentionally supports only "up" migrations (*.up.sql). There are no rollback/down scripts. Schema changes are always forward-only: once a migration is applied it is permanent. This avoids the class of bugs where a down-migration is out of sync with the data already written by the up-migration.

Error sentinels ¶

Connection and transaction lifecycle:

ErrDBAlreadyClosed // the DB was closed; returned by WithRW and WithRO
ErrRWInsideRO      // WithRW called inside a WithRO callback
ErrWrongDB         // a nested call targets a different DB than the active transaction

The last two are caller bugs rather than transient failures; they identify the enclosing database by pointer equality and do not invoke the callback.

Migrations:

ErrMigration                       // base sentinel for all migration errors
ErrMigrationScriptError            // a migration SQL script failed; joined with ErrMigration
ErrMigrationUnexpectedVersionError // version gap or bad filename; joined with ErrMigration

All three migration sentinels are joined using errors.Join, so callers can test with:

errors.Is(err, sqlitekit.ErrMigration)

In-memory databases ¶

When opening multiple pools against the same in-memory database (e.g. for tests), use a shared-cache URI so both pools see the same data:

file::memory:?mode=memory&cache=shared

Using plain ":memory:" gives each connection a separate in-memory database.

Migration runner and helpers for embedded SQL migrations.