sqlpro

sqlpro

sqlpro is a small, reflection-based convenience layer over Go's database/sql. It maps Go structs to rows, rewrites placeholders, expands IN clauses, runs transactions with lifecycle hooks, and provides bulk insert/update helpers — while staying out of your way: you always write the SQL.

It supports PostgreSQL (via pgx) and SQLite (via modernc.org/sqlite).

import "github.com/programmfabrik/sqlpro"

db, err := sqlpro.Open("postgres", "host=localhost dbname=app sslmode=disable")
// or: sqlpro.Open("sqlite", "/path/to/app.db?_pragma=journal_mode(WAL)")

type Author struct {
    ID   int64  `db:"id,pk,omitempty"`
    Name string `db:"name"`
}

a := &Author{Name: "Ada"}
db.Insert("author", a)          // INSERT; a.ID is filled in

var authors []*Author
db.Query(&authors, "SELECT * FROM author WHERE name LIKE ?", "A%")

Contents

• Install
• Connecting
• Mapping structs to rows
• Reading
• Writing
• NULL, JSON and custom column types
• Placeholders & escaping
• Transactions
• Introspection
• Errors
• Examples
• Testing & benchmarks

Install

go get github.com/programmfabrik/sqlpro

Import a driver somewhere in your program (sqlpro selects it by name):

import (
    _ "modernc.org/sqlite"       // for "sqlite"
    // pgx is pulled in by sqlpro itself for "postgres"
)

Connecting

Open(driver, dsn) returns a DB. driver is "postgres", "sqlite" or "sqlite3".

db, err := sqlpro.Open("sqlite", "/tmp/app.db")
defer db.Close()

Open pings the connection and applies driver-appropriate defaults: PostgreSQL uses $1 placeholders and RETURNING for generated keys; SQLite uses ? placeholders and the RFC3339 time format. Everything below works the same on both drivers.

db.DB() returns the underlying *sql.DB if you need something sqlpro does not wrap.

Mapping structs to rows

Columns are matched to struct fields by the db tag. Only exported fields with a db tag participate. Embedded structs are flattened, so their columns are promoted as if declared inline.

type Row struct {
    ID   int64  `db:"id,pk,omitempty"`
    Name string `db:"name"`
}

Tag options (comma-separated after the column name):

db:"name" with no options just maps the column.

Reading

Query / QueryContext pick how to scan from the type of the target:

var n int64
db.Query(&n, "SELECT count(*) FROM author")

var a Author
db.Query(&a, "SELECT * FROM author WHERE id = ?", 1)

var all []*Author
db.Query(&all, "SELECT * FROM author ORDER BY name")

var names []string
db.Query(&names, "SELECT name FROM author")

A single-row target that matches no row returns ErrQueryReturnedZeroRows; a slice target simply comes back empty.

Writing

db.Insert("author", &a)                  // pk written back into a
db.InsertContext(ctx, "author", &a)

db.Update("author", &a)                  // by pk
db.Save("author", &a)                    // upsert: insert if pk==0, else update

db.Exec("DELETE FROM author WHERE id = ?", 1)
affected, lastID, err := db.ExecContextRowsAffected(ctx, "UPDATE author SET name = ?", "x")

Bulk helpers operate on a slice of structs in one round-trip. On PostgreSQL, InsertBulk uses COPY FROM for large batches; on SQLite it builds multi-row INSERTs (chunked under the placeholder limit):

db.InsertBulk("author", []*Author{{Name: "a"}, {Name: "b"}})
db.InsertBulkOnConflictDoNothingContext(ctx, "author", rows, "name") // skip conflicts on "name"
db.UpdateBulkContext(ctx, "author", rows)                            // update many by pk

Bulk inserts do not read generated keys back into each struct; SELECT the rows afterwards if you need their ids.

NULL, JSON and custom column types

• NULL: use a pointer field. nil ⇄ SQL NULL.
• JSON: tag the field db:"col,json"; it is stored as JSON text.
• json.RawMessage: stored/loaded verbatim.
• Custom types: implement driver.Valuer (write) and sql.Scanner (read).

type Place struct {
    Name   *string         `db:"name"`        // nullable
    Config Settings        `db:"config,json"` // JSON column
    Raw    json.RawMessage `db:"raw"`         // stored as-is
    At     Geo             `db:"at"`          // Valuer + Scanner
}

A custom column type implements both interfaces:

func (g Geo) Value() (driver.Value, error) { return fmt.Sprintf("%g,%g", g.Lat, g.Lng), nil }
func (g *Geo) Scan(v any) error            { /* parse v into *g */ }

The helper scanners NullTime, NullJson and NullRawMessage are used internally and are exported for direct use with *sql.Rows.Scan.

Placeholders & escaping

Write portable ? placeholders; sqlpro rewrites them to $1, $2, … on PostgreSQL. Special placeholders:

• IN ? — pass a slice and it expands to the right number of placeholders:

  db.Query(&names, "SELECT name FROM author WHERE id IN ?", []int64{1, 2, 3})
  

• @ — the next argument is quoted as a SQL identifier (table/column):

  db.Query(&n, "SELECT count(*) FROM @", "author")
  

Escaping helpers for the rare cases a value can't be a bound argument:

db.EscValue("O'Hara")                 // 'O''Hara'
sqlpro.IlikeSql(db.Driver(), "berg")  // driver-correct case-insensitive LIKE snippet

Transactions

ExecTX is the recommended entry point. It opens a dedicated connection, hands a transaction-carrying context to your job, commits on success and rolls back on error or panic. Inside the job, get the transaction with CtxTX(ctx) and use it exactly like a DB:

err := db.ExecTX(ctx, func(ctx context.Context) error {
    tx := sqlpro.CtxTX(ctx)
    if err := tx.Insert("account", &acc); err != nil {
        return err // -> rollback
    }
    return tx.Exec("UPDATE ledger SET balance = balance - ? WHERE id = ?", amt, id)
}, nil) // *sql.TxOptions, or nil

Transactions cannot be nested (ExecTX inside ExecTX errors). Lifecycle hooks can be registered on the transaction:

Use BeforeCommit when a side effect must be atomic with the transaction (e.g. bumping a cache version); use the After* hooks for non-transactional effects (logging, cache invalidation).

For explicit control there are also Begin(), BeginRead() (read-only) and BeginContext(), each returning a TX you Commit() / Rollback() yourself.

Introspection

v, _ := db.Version()  // e.g. "Sqlite 3.45.0" / PostgreSQL version string
n, _ := db.Name()     // current database name / sqlite file
db.Log()              // returns a copy with debug logging enabled

Errors

• ErrQueryReturnedZeroRows — a single-row Query found nothing. Test with errors.Is.
• ErrMismatchedRowsAffected — an operation affected an unexpected number of rows.

Examples

A runnable, end-to-end tour lives in examples/. It exercises every feature against a throwaway SQLite database:

go run ./examples

Testing & benchmarks

Most tests run against SQLite and need no setup:

go test ./...                 # everything except the PostgreSQL test
go test -run TestCopyFrom .   # needs a local PostgreSQL "apitest" database

feature_test.go is a from-scratch, feature-by-feature suite covering the full public surface. The scan path has a benchmark:

go test -run='^$' -bench=BenchmarkScanRows -benchmem .

The slice-of-struct read path is optimized to build its column plan, scan buffer and null-scanners once per query and reuse them across rows, so only the row struct itself is allocated per row.