Documentation
¶
Overview ¶
Package sqlitex provides ergonomic helpers over a database/sql SQLite database: deferred savepoints, transaction wrappers, multi-statement scripts, a row-callback query runner, single-row and multi-row scalar reads, and an embed.FS schema-migration runner.
The helpers operate on the standard *sql.DB / *sql.Conn / *sql.Tx types and work with any database/sql driver, but assume SQLite semantics (SAVEPOINT, BEGIN IMMEDIATE, multi-statement Exec, PRAGMA user_version). They fill the quality-of-life gap that users of zombiezen.com/go/sqlite and crawshaw.io/sqlite miss when moving to a database/sql driver — this package is named and shaped after their sqlitex helpers.
Deferred savepoints ¶
release, err := sqlitex.Save(ctx, conn)
if err != nil { return err }
defer release(&err) // rolls back on a non-nil *err or panic, else releases
Transactions ¶
err := sqlitex.Transaction(ctx, db, func(tx *sql.Tx) error {
_, err := tx.ExecContext(ctx, "INSERT INTO t VALUES (?)", 1)
return err // non-nil rolls back; nil commits
})
Row-callback queries ¶
Execute runs a query and invokes a callback per row, handling the Query/Next/Scan/Err/Close lifecycle. ResultStrings / ResultInts collect a single column across rows:
err := sqlitex.Execute(ctx, db, "SELECT name FROM users WHERE age > :min",
&sqlitex.ExecOptions{
Named: map[string]any{"min": 18},
ResultFunc: func(rows *sql.Rows) error { /* rows.Scan(...) */ return nil },
})
Migrations ¶
Embed numbered .sql files and apply the pending ones, tracked via PRAGMA user_version:
//go:embed migrations/*.sql var migrations embed.FS sub, _ := fs.Sub(migrations, "migrations") n, err := sqlitex.Migrate(ctx, db, sub) // n = migrations applied
Index ¶
- func ExecScript(ctx context.Context, e Execer, script string) error
- func Execute(ctx context.Context, eq ExecQuerier, query string, opts *ExecOptions) error
- func ImmediateTransaction(ctx context.Context, conn *sql.Conn, fn func(conn *sql.Conn) error) (err error)
- func Migrate(ctx context.Context, db *sql.DB, fsys fs.FS) (applied int, err error)
- func ResultBool(ctx context.Context, q Querier, query string, args ...any) (bool, error)
- func ResultFloat(ctx context.Context, q Querier, query string, args ...any) (float64, error)
- func ResultInt(ctx context.Context, q Querier, query string, args ...any) (int64, error)
- func ResultInts(ctx context.Context, eq ExecQuerier, query string, args ...any) ([]int64, error)
- func ResultStrings(ctx context.Context, eq ExecQuerier, query string, args ...any) ([]string, error)
- func ResultText(ctx context.Context, q Querier, query string, args ...any) (string, error)
- func Save(ctx context.Context, conn *sql.Conn) (release func(errp *error), err error)
- func Transaction(ctx context.Context, db *sql.DB, fn func(tx *sql.Tx) error) (err error)
- type ExecOptions
- type ExecQuerier
- type Execer
- type Migration
- type Querier
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func ExecScript ¶
ExecScript runs a multi-statement SQL script (statements separated by ';') in a single ExecContext on e.
func Execute ¶
func Execute(ctx context.Context, eq ExecQuerier, query string, opts *ExecOptions) error
Execute runs query against eq with opts, handling the row lifecycle so callers don't repeat the Query/Next/Scan/Err/Close dance. It is the ergonomic row-callback helper the crawshaw/zombiezen sqlitex lineage is known for, expressed over database/sql:
err := sqlitex.Execute(ctx, db, "SELECT id, name FROM users WHERE age > ?",
&sqlitex.ExecOptions{
Args: []any{18},
ResultFunc: func(rows *sql.Rows) error {
var id int64
var name string
if err := rows.Scan(&id, &name); err != nil {
return err
}
// … use id, name …
return nil
},
})
With a non-nil ResultFunc it runs as a query; with a nil ResultFunc (or nil opts) it runs as a statement via ExecContext, suitable for DDL/DML.
func ImmediateTransaction ¶
func ImmediateTransaction(ctx context.Context, conn *sql.Conn, fn func(conn *sql.Conn) error) (err error)
ImmediateTransaction runs fn inside a BEGIN IMMEDIATE transaction on conn — SQLite acquires the write lock up front, avoiding a deadlock-prone upgrade-from-read under concurrency. It commits when fn returns nil and rolls back on error or panic. fn must run its statements on the same conn.
func Migrate ¶
Migrate applies every migration in fsys whose version exceeds the database's current PRAGMA user_version, in ascending order, each in its own transaction, bumping user_version as it goes. It is idempotent: re-running applies nothing once the database is up to date. Returns the number of migrations applied.
Migration files are named "NNNN_description.sql" (see LoadMigrations).
func ResultBool ¶
ResultBool runs a single-row, single-column query and returns the value as a bool (non-zero / non-empty is true).
func ResultFloat ¶
ResultFloat runs a single-row, single-column query and returns the value as a float64.
func ResultInt ¶
ResultInt runs query (which must return a single row, single column) and returns the value as an int64. sql.ErrNoRows is returned for no rows.
func ResultInts ¶
ResultInts runs query and collects the first column of every row as int64s — the multi-row companion to ResultInt.
func ResultStrings ¶
func ResultStrings(ctx context.Context, eq ExecQuerier, query string, args ...any) ([]string, error)
ResultStrings runs query and collects the first column of every row as strings — the multi-row companion to ResultText.
func ResultText ¶
ResultText runs a single-row, single-column query and returns the value as a string.
func Save ¶
Save opens a SAVEPOINT on conn and returns a release function for the deferred-cleanup idiom:
release, err := sqlitex.Save(ctx, conn)
if err != nil { return err }
defer release(&err)
release rolls back to the savepoint when *errp is non-nil or a panic is in flight, and always releases it. A nil errp always releases (commit). Nesting is safe: each call uses a distinct savepoint name. Use a *sql.Conn (not a pooled *sql.DB) so every statement lands on the same connection.
Types ¶
type ExecOptions ¶
type ExecOptions struct {
// Args are positional bind parameters for ? placeholders.
Args []any
// Named are named bind parameters (e.g. :id / @id / $id). Each is passed as
// sql.Named and appended after Args. Bound by name, so map order is
// irrelevant.
Named map[string]any
// ResultFunc, if non-nil, is invoked once per result row with the *sql.Rows
// positioned on that row — call rows.Scan to read it. Returning an error
// stops iteration and is returned by Execute. When nil, Execute runs the
// statement without expecting rows (DDL/DML).
ResultFunc func(rows *sql.Rows) error
}
ExecOptions configures Execute.
type ExecQuerier ¶
type ExecQuerier interface {
Execer
QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
}
ExecQuerier is implemented by *sql.DB, *sql.Conn, and *sql.Tx — the two methods Execute needs (the row-returning and no-result paths).
type Execer ¶
type Execer interface {
ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
}
Execer is implemented by *sql.DB, *sql.Conn, and *sql.Tx.
type Migration ¶
type Migration struct {
Version int // numeric prefix of the filename (e.g. 0003_… → 3)
Name string // the description after the prefix (e.g. "add_index")
SQL string // file contents (may contain multiple statements)
}
Migration is one schema-migration step parsed from a `.sql` file.
func LoadMigrations ¶
LoadMigrations reads every `*.sql` file in fsys (recursively), parsing each filename of the form "NNNN_description.sql" into a Migration. Results are sorted ascending by version. It errors on a non-numeric prefix or a duplicate version.
Source Files