Skip to content
ella-toPublic

About

a set of tools on top of sqlite golang library

Resources

Stars

1 star

Watchers

0 watching

Forks

Repository files navigation

░██████╗░██████╗░██╗░░░░░██╗████████╗███████╗
██╔════╝██╔═══██╗██║░░░░░██║╚══██╔══╝██╔════╝
╚█████╗░██║██╗██║██║░░░░░██║░░░██║░░░█████╗░░
░╚═══██╗╚██████╔╝██║░░░░░██║░░░██║░░░██╔══╝░░
██████╔╝░╚═██╔═╝░███████╗██║░░░██║░░░███████╗
╚═════╝░░░░╚═╝░░░╚══════╝╚═╝░░░╚═╝░░░╚══════╝

Go Reference Go Report Card License: MIT

sqlite wraps zombiezen.com/go/sqlite with connection pooling, automatic parameter binding, migrations, and geo queries.

Installation

go get ella.to/[email protected]

Opening a Database

db, err := sqlite.New(ctx,
    sqlite.WithFile("app.db"),
    sqlite.WithPoolSize(10),
)
defer db.Close()

Every new connection gets these PRAGMAs applied automatically:

  • foreign_keys = ON
  • journal_mode = WAL
  • synchronous = NORMAL (change with WithSynchronous(sqlite.SynchronousFull) if every commit must survive power loss)
  • cache_size = -16384 (16 MiB per connection, change with WithCacheSize)
  • temp_store = MEMORY
  • mmap_size = 268435456 (256 MiB, change with WithMmapSize, 0 disables)
  • journal_size_limit = 67108864 (the WAL is truncated back to 64 MiB after checkpoints)

Close runs PRAGMA optimize on one connection before closing the pool.

In-Memory Database

db, err := sqlite.New(ctx, sqlite.WithMemory())

Custom Connection String

db, err := sqlite.New(ctx, sqlite.WithStringConn("file:app.db?mode=ro"))

Connection Preparation

Run setup logic on every new connection in the pool (e.g. loading extensions or registering custom functions):

db, err := sqlite.New(ctx,
    sqlite.WithFile("app.db"),
    sqlite.WithConnPrepareFunc(func(conn *sqlite.Conn) error {
        return conn.ExecScript(`PRAGMA busy_timeout = 5000;`)
    }),
    sqlite.WithFunctions(map[string]*sqlite.FunctionImpl{
        "my_upper": {
            NArgs:         1,
            Deterministic: true,
            Scalar: func(ctx *sqlite.Context, args []sqlite.Value) {
                ctx.ResultText(strings.ToUpper(args[0].Text()))
            },
        },
    }),
)

Running Queries

Get a Connection

conn, err := db.Conn(ctx)
defer conn.Done() // always return to pool

Prepare and Execute

Prepare auto-binds parameters by position. It understands Go types and maps them to SQLite types:

stmt, err := conn.Prepare(ctx, "SELECT * FROM users WHERE id = ? AND active = ?", userID, true)
Go Type SQLite Type
string, fmt.Stringer TEXT
int, int64, etc. INTEGER
float32, float64 FLOAT
bool INTEGER (0/1)
[]byte, json.RawMessage BLOB
time.Time INTEGER (Unix timestamp)
nil, nil pointers NULL
maps, slices, structs TEXT (JSON-encoded)

Pointers bind as the value they point to (*string is TEXT, *time.Time is an INTEGER timestamp, *Struct is JSON). Unsigned values above math.MaxInt64 return ErrIntegerOverflow instead of wrapping negative.

Statements from Prepare are cached per connection, keyed by the SQL string. If you stop stepping a statement before it returns false, call stmt.Reset() before conn.Done(); the pool panics on a connection that still has an active statement.

For SQL built at runtime (for example with a varying number of Placeholders), use PrepareTransient so the cache does not grow without bound. You own the statement and must finalize it:

stmt, err := conn.PrepareTransient(ctx, "SELECT * FROM users WHERE id IN ("+sqlite.Placeholders(len(ids))+")", ids...)
if err != nil {
    return err
}
defer stmt.Finalize()

Using the Exec Helper

For one-off operations where you don't need to manage the connection yourself:

err := db.Exec(ctx, func(ctx context.Context, conn *sqlite.Conn) error {
    stmt, err := conn.Prepare(ctx, "INSERT INTO users (name, email) VALUES (?, ?)", name, email)
    if err != nil {
        return err
    }
    _, err = stmt.Step()
    return err
})

Transactions

Save opens a savepoint right away and returns the function that ends it. Defer the returned function for automatic commit/rollback based on error (note the (): the savepoint has to be opened before the statements run):

err := db.Exec(ctx, func(ctx context.Context, conn *sqlite.Conn) (err error) {
    defer conn.Save()(&err)

    stmt, _ := conn.Prepare(ctx, "INSERT INTO orders (user_id, total) VALUES (?, ?)", userID, total)
    stmt.Step()

    stmt, _ = conn.Prepare(ctx, "UPDATE users SET order_count = order_count + 1 WHERE id = ?", userID)
    stmt.Step()

    return nil // commits on nil, rolls back on error
})

For transactions that write, prefer Immediate, which takes the write lock up front (BEGIN IMMEDIATE) so the transaction cannot fail with SQLITE_BUSY_SNAPSHOT after it has started reading:

err := db.Exec(ctx, func(ctx context.Context, conn *sqlite.Conn) (err error) {
    end, err := conn.Immediate()
    if err != nil {
        return err
    }
    defer end(&err)
    // ...
    return nil
})

Write and Read

SQLite allows one writer at a time. Write runs a function on a dedicated writer connection inside BEGIN IMMEDIATE, so concurrent writers queue up in Go instead of failing with SQLITE_BUSY, and read-modify-write logic cannot fail with SQLITE_BUSY_SNAPSHOT. It commits when the function returns nil and rolls back on an error or panic:

err := db.Write(ctx, func(ctx context.Context, conn *sqlite.Conn) error {
    stmt, err := conn.Prepare(ctx, "UPDATE users SET order_count = order_count + 1 WHERE id = ?", userID)
    if err != nil {
        return err
    }
    _, err = stmt.Step()
    return err
})

Read runs a function on a pooled connection inside a deferred transaction, so all of its statements see one consistent snapshot. Reads run in parallel with each other and with the writer:

err := db.Read(ctx, func(ctx context.Context, conn *sqlite.Conn) error {
    // SELECT ...
    return nil
})

Conn and Exec keep working as before; Write is the safer choice whenever several goroutines modify the database.

Running SQL Scripts

// Execute a multi-statement string
err := sqlite.RunScript(ctx, db, `
    CREATE TABLE IF NOT EXISTS users (id TEXT PRIMARY KEY, name TEXT);
    CREATE INDEX IF NOT EXISTS idx_users_name ON users(name);
`)

// Execute all .sql files from a directory (sorted alphabetically)
err := sqlite.RunScriptFiles(ctx, db, "./sql/setup/")

Migrations

Apply SQL migration files from an embedded filesystem. Already-applied migrations are tracked in a migrations_sqlite table, so only new files run on each call.

//go:embed migrations/*.sql
var migrationsFS embed.FS

err := sqlite.Migration(ctx, db, migrationsFS, "migrations")

Migration files are applied in sorted order. Name them with a numeric prefix to control ordering:

migrations/
  001_create_users.sql
  002_add_email_index.sql
  003_create_orders.sql

Reading Columns

Helper functions for extracting typed values from query results:

// Unix timestamp -> time.Time (UTC)
created := sqlite.LoadTime(stmt, "created_at")

// Integer -> bool
active := sqlite.LoadBool(stmt, "is_active")

// JSON column -> Go types
attrs, err := sqlite.LoadJsonMap[string](stmt, "attributes")
tags, err := sqlite.LoadJsonArray[string](stmt, "tags")
profile, err := sqlite.LoadJsonStruct[UserProfile](stmt, "profile")

Placeholders

Generate placeholder strings for building dynamic queries:

// Single row: "?, ?, ?"
sqlite.Placeholders(3)

// Multiple rows: "(?, ?), (?, ?), (?, ?)"
sqlite.GroupPlaceholders(3, 2)

Geo Queries

Built-in support for bounding-box distance queries using the haversine formula. Useful for "find things near this point" queries.

// WHERE clause that filters to a bounding box around the point
where := sqlite.CreateCondSQL(lat, lon, distanceInKm)

// SELECT expression that computes squared distance from the point
dist := sqlite.CreateDistanceSQL(lat, lon)

query := fmt.Sprintf(
    "SELECT *, %s AS dist FROM locations WHERE %s ORDER BY dist LIMIT 20",
    dist, where,
)

The bounding box uses a 1.1x safety margin, so you get slightly more results than the exact radius — good for a first pass before doing precise distance filtering in your application.

Debugging

Format a query with its parameters for logging:

sql := sqlite.ShowSql("SELECT * FROM users WHERE name = ? AND age > ?", "Alice", 30)
// SELECT * FROM users WHERE name = 'Alice' AND age > 30

Error Sentinels

sqlite.ErrNotFound    // row not found
sqlite.ErrPrepareSQL  // failed to prepare statement
sqlite.ErrExecSQL     // failed to execute statement
sqlite.ErrUnknownType // unsupported Go type for parameter binding
sqlite.ErrIntegerOverflow // unsigned value does not fit in a signed 64-bit INTEGER

License

MIT — see LICENSE.md for details.

About

a set of tools on top of sqlite golang library

Resources

Stars

1 star

Watchers

0 watching

Forks

Releases

Packages

Used by

Contributors

Languages