dbx

DBX - Opinionated database utilities for Go

Convenience layer on top of sqlx. No ORM — just prepared statements, migrations, and pagination.

Table of contents

• Opening a database
  • Migration files
• Prepared statements
  • Example: CRUD operations
  • Streaming with QueryxIteratorFunc
  • Result type
• Transactions
  • RunTx helper
  • Transaction function types
  • Example: atomic multi-table insert
• Pagination
  • Setup
  • Fetching pages
  • PageRequest / PageResponse
  • Full pagination loop
• Breaking changes in v1.0.0

Opening a database

import "github.com/borud/dbx/v2"

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

// Returns *sqlx.DB
db, err := dbx.OpenSQLX(
    dbx.WithDSN(":memory:"),
    dbx.WithDriver("sqlite"),
    dbx.WithMigrations(migrationsFS, "migrations"),
)

// Returns *sql.DB
db, err := dbx.Open(
    dbx.WithDSN(":memory:"),
    dbx.WithDriver("sqlite"),
    dbx.WithMigrations(migrationsFS, "migrations"),
)

Filesystem migrations (instead of embedded):

dbx.WithMigrations(os.DirFS("migrations"), ".")

Pragmas:

dbx.WithPragmas(
    "PRAGMA foreign_keys = ON",
    "PRAGMA synchronous = NORMAL",
)

Custom migration driver (e.g. MySQL):

import mysql "github.com/golang-migrate/migrate/v4/database/mysql"

dbx.WithMigrationDriver("mysql", "mysql",
    func(db *sql.DB) (database.Driver, error) {
        return mysql.WithInstance(db, &mysql.Config{})
    }),

Migration files

Numbered SQL files in a directory:

migrations/0001_init.up.sql
migrations/0002_add_foo_field.up.sql
migrations/0003_add_bar_table.up.sql
```text
migrations/0001_init.up.sql
migrations/0002_add_foo_field.up.sql
migrations/0003_add_bar_table.up.sql

Only up migrations are supported.

Prepared statements

Function types that wrap prepared statements with generics:

Note: NewPaginatedSelectFunc constructs SQL from its table, orderCol, and baseWhere arguments. The table and column names are interpolated directly into queries — they are validated as safe SQL identifiers (ASCII letters, digits, underscores only) and the constructor panics on invalid input. The baseWhere clause is passed through as-is, so use ? placeholders for any user-supplied values.

Example: CRUD operations

type Record struct {
    Name string `db:"name"`
    TS   int64  `db:"ts"`
}

// Define your storage interface as function fields
type Storage struct {
    Add    dbx.NamedExecFunc[Record]
    AddRet dbx.EntityQueryRowxFunc[Record]
    Get    dbx.QueryRowxFunc[Record]
    Update dbx.NamedExecFunc[Record]
    Delete dbx.ExecFunc
    List   dbx.SelectFunc[Record]
}

// Wire up
ops := Storage{
    Add:    dbx.NewNamedExecFunc[Record](db, "INSERT INTO foo (name,ts) VALUES (:name,:ts)"),
    AddRet: dbx.NewEntityQueryRowxFunc[Record](db, "INSERT INTO foo (name,ts) VALUES (:name,:ts) RETURNING *"),
    Get:    dbx.NewQueryRowxFunc[Record](db, "SELECT * FROM foo WHERE name = ?"),
    Update: dbx.NewNamedExecFunc[Record](db, "UPDATE foo SET ts = :ts WHERE name = :name"),
    Delete: dbx.NewExecFunc(db, "DELETE FROM foo WHERE name = ?"),
    List:   dbx.NewSelectFunc[Record](db, "SELECT * FROM foo ORDER BY ts ASC"),
}

// Insert
res, err := ops.Add(ctx, Record{Name: "alice", TS: 1})
lastID, _ := res.LastInsertID()

// Insert with RETURNING
rec, err := ops.AddRet(ctx, Record{Name: "bob", TS: 2})

// Get
rec, err := ops.Get(ctx, "alice")

// Update
res, err := ops.Update(ctx, Record{Name: "alice", TS: 99})

// Delete
err := ops.Delete(ctx, "alice")

// List all
records, err := ops.List(ctx)

Streaming with QueryxIteratorFunc

For large result sets that shouldn't be loaded into memory at once:

listIter := dbx.NewQueryxIteratorFunc[Record](db, "SELECT * FROM foo ORDER BY ts ASC")

ctx, cancel := context.WithTimeout(ctx, 10*time.Second)
defer cancel()

for rec, err := range listIter(ctx) {
    // context cancellation terminates the loop with an error
    if err != nil {
        break
    }
    fmt.Println(rec)
}

Result type

NamedExecFunc returns dbx.Result which wraps sql.Result:

res, err := ops.Add(ctx, record)
if id, ok := res.LastInsertID(); ok { ... }
if n, ok := res.RowsAffected(); ok { ... }

Transactions

The prepared-statement functions above operate on *sqlx.DB directly. When you need multiple operations to succeed or fail atomically, use the transaction variants.

RunTx helper

RunTx handles begin, commit, and rollback. If the callback returns an error (or panics), the transaction is rolled back; otherwise it is committed.

err := dbx.RunTx(ctx, db, func(tx *sqlx.Tx) error {
    // all operations here run in a single transaction
    return nil
})

Transaction function types

Each prepared-statement type has a Tx counterpart that runs inside a *sqlx.Tx. The statement is prepared on the *sqlx.DB at construction time and bound to the transaction on each call, so it is parsed once. Construct these outside any transaction: on a database with a single connection (such as SQLite :memory:), preparing while a transaction holds the connection would wait forever.

Batch inserts

NewTxBatchExecFunc inserts a slice of structs with multi-row statements, up to maxRows rows each, and returns one Result per statement. Statements for 1 to maxRows rows are prepared up front.

insert := dbx.NewTxBatchExecFunc[Event](db, "INSERT INTO events (time, path) VALUES ", "(:time, :path)", 8)

err := dbx.RunTx(ctx, db, func(tx *sqlx.Tx) error {
    _, err := insert(ctx, tx, events)
    return err
})

Batching matters: each statement has a fixed cost in the driver (modernc.org/sqlite starts a goroutine per statement when the context can be cancelled), so inserting rows one statement at a time is several times slower. Keep maxRows small, since some drivers bind parameters in time quadratic in their number.

Example: atomic multi-table insert

A common pattern is inserting related rows across tables atomically. The Tx function types can be wired up alongside their non-transactional counterparts:

type DeviceStore struct {
    Add    dbx.NamedExecFunc[Device]        // normal, non-tx insert
    AddTx  dbx.TxNamedExecFunc[Device]      // same SQL, but for use in transactions
    Get    dbx.QueryRowxFunc[Device]
    GetTx  dbx.TxQueryRowxFunc[Device]
}

type PSKStore struct {
    AddTx dbx.TxNamedExecFunc[PSK]
}

// Wire up (typically at init time)
devices := DeviceStore{
    Add:   dbx.NewNamedExecFunc[Device](db, "INSERT INTO devices (id,name) VALUES(:id,:name)"),
    AddTx: dbx.NewTxNamedExecFunc[Device](db, "INSERT INTO devices (id,name) VALUES(:id,:name)"),
    Get:   dbx.NewQueryRowxFunc[Device](db, "SELECT * FROM devices WHERE id = ?"),
    GetTx: dbx.NewTxQueryRowxFunc[Device](db, "SELECT * FROM devices WHERE id = ?"),
}

psks := PSKStore{
    AddTx: dbx.NewTxNamedExecFunc[PSK](db, "INSERT INTO psk (identity,device_id,key) VALUES(:identity,:device_id,:key)"),
}

// Atomic insert: device + initial PSK
err := dbx.RunTx(ctx, db, func(tx *sqlx.Tx) error {
    if _, err := devices.AddTx(ctx, tx, device); err != nil {
        return err
    }
    _, err := psks.AddTx(ctx, tx, psk)
    return err
})

Pagination

Cursor-based pagination via PaginatedSelectFunc[T].

Setup

list := dbx.NewPaginatedSelectFunc[Record](
    db,
    "foo",                                              // table name
    "ts",                                               // ORDER BY column (used as cursor)
    "",                                                 // base WHERE clause ("" for none)
    func(r Record) string { return strconv.FormatInt(r.TS, 10) }, // cursor extractor
)

With a WHERE clause:

list := dbx.NewPaginatedSelectFunc[Record](
    db, "foo", "ts", "WHERE name = ?",
    func(r Record) string { return strconv.FormatInt(r.TS, 10) },
)

// Pass WHERE args after PageRequest
rows, page, err := list(ctx, dbx.PageRequest{PageSize: 10}, "alice")

Fetching pages

// All rows (no pagination)
rows, page, err := list(ctx, dbx.PageRequest{})

// First page
rows, page, err := list(ctx, dbx.PageRequest{PageSize: 10})

// Next page (forward)
rows, page, err := list(ctx, dbx.PageRequest{PageSize: 10, After: page.LastCursor})

// Previous page (backward)
rows, page, err := list(ctx, dbx.PageRequest{PageSize: 10, Before: page.FirstCursor})

PageRequest / PageResponse

type PageRequest struct {
    PageSize int    // 0 = unlimited, clamped to MaxPageSize (1000)
    After    string // forward cursor (mutually exclusive with Before)
    Before   string // backward cursor (mutually exclusive with After)
}

type PageResponse struct {
    HasMore     bool   // true if more pages exist
    FirstCursor string // cursor of first item in page
    LastCursor  string // cursor of last item in page
}

• After and Before are mutually exclusive (setting both returns an error)
• PageSize > MaxPageSize (1000) is silently clamped
• Backward pagination (Before) returns results in ascending order
• Table and column names are validated as safe SQL identifiers (panics at init on invalid input)

Full pagination loop

var allRows []Record
req := dbx.PageRequest{PageSize: 20}

for {
    rows, page, err := list(ctx, req)
    if err != nil { return err }

    allRows = append(allRows, rows...)

    if !page.HasMore {
        break
    }
    req.After = page.LastCursor
}

Breaking changes in v1.0.0

WithPragmas now takes variadic arguments

The signature changed from WithPragmas([]string{...}) to WithPragmas(...).

Before:

dbx.WithPragmas([]string{
    "PRAGMA foreign_keys = ON",
    "PRAGMA synchronous = NORMAL",
})

After:

dbx.WithPragmas(
    "PRAGMA foreign_keys = ON",
    "PRAGMA synchronous = NORMAL",
)

ValidSQLIdentifier rejects identifiers starting with a digit

ValidSQLIdentifier("123table") now returns false. This aligns with standard SQL identifier rules. Table and column names passed to NewPaginatedSelectFunc must not start with a digit.

NewNamedExecFunc no longer returns sql.ErrNoRows on zero affected rows

Previously, NewNamedExecFunc returned both a valid Result and sql.ErrNoRows when RowsAffected == 0. This was misleading because the operation succeeded — zero rows affected is a valid outcome for many statements. The Result is still returned and callers should check result.RowsAffected() to detect zero affected rows when needed.

Note: NewExecFunc retains the sql.ErrNoRows behavior since it does not return a Result.