sqldb

Overview ¶

Package sqldb provides Postgres helpers on top of jmoiron/sqlx and the pgx driver: connection setup, named query/exec wrappers with query logging, transactions, and bulk insert/upsert.

Open builds a *sqlx.DB from a Config; StatusCheck waits for it to become reachable. The Named* helpers run named queries/execs, log the expanded SQL at debug level, and translate well-known Postgres errors into the package sentinels (ErrDBNotFound, ErrDBDuplicatedEntry, ErrUndefinedTable) so callers match them with errors.Is instead of inspecting driver codes. WithinTran runs a function inside a transaction, committing on success and rolling back on error or panic. Bulk* batch large multi-row writes within Postgres's parameter limit.

Usage ¶

cfg := sqldb.Config{
    User: "postgres", Password: "postgres",
    Host: "localhost:5432", Name: "app",
    Schema: "public", MaxIdleConns: 2, MaxOpenConns: 5,
    DisableTLS: true,
}
db, err := sqldb.Open(cfg)
if err != nil {
    return err
}
if err := sqldb.StatusCheck(ctx, db); err != nil {
    return err
}

// Named query into a slice.
var widgets []Widget
const q = `SELECT id, name FROM widgets WHERE name = :name`
arg := struct {
    Name string `db:"name"`
}{Name: "gadget"}
if err := sqldb.NamedQuerySlice(ctx, log, db, q, arg, &widgets); err != nil {
    return err
}

// Write inside a transaction.
err = sqldb.WithinTran(ctx, log, db, func(tx *sqlx.Tx) error {
    const ins = `INSERT INTO widgets (id, name) VALUES (:id, :name)`
    return sqldb.NamedExecContext(ctx, log, tx, ins, w)
})
if errors.Is(err, sqldb.ErrDBDuplicatedEntry) {
    // unique violation
}

Querying ¶

• ExecContext / NamedExecContext run statements; the RowsAffected variant returns the affected-row count.
• QueryStruct / NamedQueryStruct scan exactly one row (ErrDBNotFound when none).
• QuerySlice / NamedQuerySlice scan every row into a *[]T.

The plain (non-Named) variants take no parameters; the Named variants bind from a struct via its `db` tags.

Bulk writes ¶

BulkInsert writes len(values)/len(columns) rows, splitting into batches that stay under Postgres's bind-parameter cap, with values laid out row-major. An optional conflictAction (e.g. "ON CONFLICT DO NOTHING") is appended verbatim. BulkUpsert builds the ON CONFLICT … DO UPDATE clause for you from the conflict columns.

Transactions ¶

WithinTran is the common path. For stores that must run against either a pool or an outer transaction, depend on the Beginner / CommitRollbacker seam: NewBeginner adapts a *sqlx.DB, and ExtContext extracts the query surface from a started transaction.

Config ¶

Config fields: User, Password, Host, Name, Schema (sets search_path), MaxIdleConns, MaxOpenConns, DisableTLS (DisableTLS=true selects sslmode=disable, otherwise sslmode=require). Open always sets timezone=utc.