dba

package module
v0.15.1 Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Oct 1, 2026 License: MIT Imports: 16 Imported by: 9

README

dba

Go Reference CI codecov Go Report Card

A SQL-respecting query builder for Go.

dba does not translate SQL into method calls, nor hide it behind an object model — you write the SQL, dba handles the rest of the plumbing: dynamic condition assembly, parameter numbering and dialect placeholders, fragment reuse, struct/map to INSERT/UPDATE mapping, and high-frequency collaboration patterns like pagination. It sits in the gap between sqlx (scan enhancement) and squirrel (AST-style builder), philosophically close to MyBatis dynamic SQL, but with an immutable chainable API instead of XML.

db, _ := dba.Open("pgx", dsn)

users, err := db.Select("users", "status = #{1}", "active").
    AddIf(name != "", "AND name LIKE #{1}", "%"+name+"%").
    AddIf(len(ids) > 0, "AND id IN (#{1|expand})", ids).
    Add("ORDER BY created_at DESC").
    FetchList[User]()

One sentence for the core property: the builder is immutable. Every method returns a new instance, so half-built queries can be safely passed across functions, cached, and forked — this is the foundation of pagination, count reuse, and similar patterns.

Install

go get github.com/kran/dba

Depends on jmoiron/sqlx. Requires Go 1.27+ (generic methods). Create via Open(driver, dsn) or NewFromSqlx(*sqlx.DB); placeholder format is chosen automatically by driver ($n for the Postgres family, ? otherwise), identifier quoting too (MySQL backticks, ANSI double quotes elsewhere), and the row-limiting clause dialect (SQL:2008 standard by default, LIMIT/OFFSET for mysql/sqlite) — each overridable with Formatter/Quoter/Pager.

Template language

dba's template has two core symbols, one per layer:

Syntax Layer Meaning
#{key} value resolve an argument and bind it as a placeholder
#{key|pipe} value resolve an argument, hand it to the named pipe
${var} structure expand a named variable (template recursion)
${var:default} structure expand default text when the variable is undefined
Value layer: #{} and pipes

Argument keys come in two forms. Positional: #{1}, #{2} address the n-th argument of the current fragment — numbering is per-fragment, each Add counts from 1, and fragments never interfere, so fragments can be freely composed without renumbering. Named: #{name} resolves from the last argument (struct or map) of the fragment; structs map by db tag, sharing the same rules as row scanning.

db.Add("WHERE age > #{1} AND city = #{2}", 18, "SH")       // positional
db.Add("WHERE name = #{Name} AND age > #{Age}", user)       // named: struct
db.Add("WHERE name = #{name}", dba.H{"name": "bob"})        // named: map (H is a map[string]any alias)
db.Add("WHERE a = #{1} AND b = #{key}", 1, dba.H{"key": 2}) // mixed: named source is the last arg

Pipes decide how an argument value enters the SQL. Five built-in:

// bind (default): bind as a placeholder
db.Add("WHERE id = #{1}", 42)                        // → WHERE id = $1

// expand: expand a slice into comma-separated placeholders
db.Add("WHERE id IN (#{1|expand})", []int{1, 2, 3})  // → IN ($1, $2, $3)

// raw: inject the argument value as SQL text (caller attests safety, see "Safety boundaries")
db.Add("WHERE created > #{1|raw}", "NOW()")           // → WHERE created > NOW()

// quote: treat the argument value as an identifier, quoted per dialect (dynamic columns/sorting)
db.Add("ORDER BY #{1|quote}", sortCol)                // → ORDER BY "sort_col"

// literalquote: the macro content itself is the identifier (consumes no argument)
db.Add("SELECT * FROM #{users|literalquote}")         // → SELECT * FROM "users"

Two shorthand macros: !{1} ≡ #{1|raw}, @{users} ≡ #{users|literalquote}. An empty slice through expand yields IN (), which the database rejects with a syntax error — dba does not intercept; pair with AddIf(len(ids) > 0, ...) for dynamic conditions.

Structure layer: ${} variables

Variables are named SQL fragments (template + their own arguments), expanded recursively at render time, with isolated argument scopes:

q := db.Add("SELECT * FROM t WHERE ${cond} ${order:ORDER BY id}").
    Var("cond", "status = #{1} AND ${scope}", "active").
    Var("scope", "org_id = #{1}", orgID)
// ${order:...} falls back to the default ORDER BY id when undefined

Variables are late-bound: referencing with Add before defining with Var is legal; lookup happens at build time. This is the basis of the slot protocol (see F/I/O below). Rendering recursion has a depth limit (64); self-referential cycles produce a clear error instead of a stack overflow.

Node: arguments as subtrees

Expr(sql, args...) returns a Node — a SQL fragment together with its own arguments. Wherever a Node appears in an argument position it is inlined instead of bound as a placeholder; this is the library-wide invariant:

// Add argument
db.Add("WHERE updated < #{1}", dba.Expr("NOW() - INTERVAL #{1} DAY", 7))
// → WHERE updated < NOW() - INTERVAL $1 DAY

// Insert/Update field values
db.Update("counters", dba.H{
    "views": dba.Expr("views + #{1}", 1),  // inlined: views = views + $1
    "name":  "n",                           // plain value: placeholder
}, "id = #{1}", 5)

// expand slice elements (subqueries mixed into an IN list)
keys := []any{"alice", dba.Expr("lower(#{1})", input), "bob"}
db.Add("WHERE username IN (#{1|expand})", keys)
// → IN ($1, lower($2), $3)

// struct fields (a Node-typed field collapses to a single column)
type Event struct {
    Name    string   `db:"name"`
    Created dba.Node `db:"created"`
}
db.Insert("events", Event{Name: "e", Created: dba.Expr("NOW()")})

*Node is supported as well; a nil pointer binds as SQL NULL.

CRUD generators

db.Select("users", "age > #{1}", 18)          // SELECT ${F:*} FROM "users" WHERE age > $1
db.Insert("users", user)                       // struct or map
db.Update("users", changes, "id = #{1}", id)   // changes is a struct or map
db.Delete("users", "id = #{1}", id)
db.BatchInsert("users", entities)              // bulk, all columns (see omitempty)
db.Add("INSERT INTO t (a, b) VALUES").Batch(rows) // manual bulk value groups

Generators are just wrappers around Add; the resulting builder chains on:

db.Insert("users", u).Add("ON CONFLICT (email) DO NOTHING").Exec()
Struct mapping and omitempty

Column naming shares one mapper with scanning and named arguments: db tag wins (used verbatim), no tag falls back to the lowercased field name, db:"-" excludes. Nested/anonymous structs expand recursively into flat columns; driver.Valuer implementers, time.Time and its aliases, and Node collapse to atomic single columns.

Zero-value semantics on write are per-field and visible at the declaration site (deliberately unlike GORM's implicit global zero-value skipping):

value field, no tag       → always written (zero values too)
value field + omitempty   → zero values skipped (auto-increment id / DB-defaulted columns)
pointer field + omitempty → nil skipped; non-nil kept (even pointing at zero) — the escape hatch for writing zeros
Node field                → single-column inline; zero Node + omitempty skipped
map                       → fully manual: write exactly what you pass

Pointer escape hatch example: Views *int tagged omitempty — nil means "unset, skip this column", &zero means "I explicitly want 0". sql.NullString{Valid: false} is the zero value, so with omitempty it is skipped — for an explicit NULL use a pointer or a map.

BatchInsert forces all columns (ignores omitempty): a batch requires every row to share the same column set — per-row omission would drift the set.

Slot protocol: F / I / O

Generators and utilities cooperate through three conventional variable names, all built on ${var:default} late binding:

F — column list slot. Select generates ${F:*}, FetchPage forks the same builder into a count query via Var(F, "COUNT(1)"). To feed hand-written SQL to FetchPage, embed ${F:*} on the main chain:

q := db.Add(`SELECT ${F:*} FROM orders o JOIN users u ON o.uid = u.id WHERE o.status = #{1}`, st)
items, total, err := q.FetchPage[Order](1, 20)

FetchPage's count is a plain substitution — not for GROUP BY / DISTINCT queries; write your own count for those. The row-limiting clause defaults to the SQL:2008 standard OFFSET m ROWS FETCH NEXT n ROWS ONLY, with mysql/sqlite automatically switching to LIMIT/OFFSET; override with Pager(). Note that SQL Server / Oracle / DB2 require ORDER BY — the query must carry an ${order:...} slot or a bare ORDER BY.

O — sort slot (strongly recommended). Write ORDER BY as ${order:ORDER BY id DESC} and FetchPage's count query clears it. This is more than saving a pointless sort: on PostgreSQL / SQL Server an ORDER BY over a non-aggregated source column in an aggregate query is a hard error, and SQL Server's OFFSET...FETCH additionally requires ORDER BY on the data query — only the ${order} slot satisfies both at once. A bare ORDER BY happens to work on lenient dialects like SQLite, but errors on PG / SQL Server.

I — INSERT modifier slot. Insert generates INSERT ${I:} INTO ..., empty by default. Chained Add can only append to the tail (RETURNING / ON CONFLICT reachable), leaving INSERT-to-INTO a dead corner — this slot is the only vent:

db.Var(dba.I, "IGNORE").Insert("users", u)   // INSERT IGNORE INTO ...

Execution and scanning

Every result-taking method shares the Fetch verb (one prefix, full family visible via autocomplete):

// one row: strict 0..1 (0 rows → (nil, nil); more than one → error;
// write LIMIT 1 to express "any row"; T is the value type, the return is
// naturally a pointer: FetchOne[User]() → *User)
u, err := q.FetchOne[User]()
if err != nil { return err }
if u == nil { /* not found */ }
// many rows (struct/basic types; single-column queries work too)
items, err := q.FetchList[User]()
// single value (a scalar is a degenerate one-row case)
v, err := q.FetchOne[int64]()
// page + count (requires the ${F} slot; standard clause by default,
// LIMIT for mysql/sqlite, Pager() overridable)
items, total, err := q.FetchPage[User](1, 20)
// keyed: query-level IndexBy / GroupBy (duplicate keys error)
m, err := q.FetchIndexed[int](func(u User) int { return u.ID })
g, err := q.FetchGrouped[int](func(u User) int { return u.OrgID })
// dynamic columns (unknown at compile time; no row → (nil, nil))
m, err := q.FetchOneMap()
ms, err := q.FetchMaps()
// streaming: lazy iterator (break-safe; check err per row)
for u, err := range q.Iter[User]() {
    if err != nil { return err }
    // ...
}
// eager raw cursor (executes immediately)
rows, err := q.FetchRows()
// not a fetch
result, err := q.Exec()
sql, args, err := q.ToSQL()

Fetch* methods are generic (Go 1.27+): they return values instead of taking a dest pointer.

Builder errors (invalid generator input, mismatched Batch widths, ...) accumulate along the chain; subsequent operations no-op and the error is returned at execution or ToSQL time. You can also check q.Error() at any point.

Transactions
err := db.Transaction(func(tx *dba.SQL) error {
    if _, err := tx.Insert("orders", order).Exec(); err != nil {
        return err
    }
    _, err := tx.Update("stock", dba.H{"n": dba.Expr("n - #{1}", 1)}, "sku = #{1}", sku).Exec()
    return err // returning an error (or panicking) rolls back
})

Inside an existing transaction, Transaction runs the function body directly — no nesting. Manual Begin/Commit/Rollback also available.

Logging
db = db.SetLogger(func(ctx context.Context, begin time.Time, query string, args []any, err error) {
    slog.Info("sql", "cost", time.Since(begin), "query", query, "err", err)
})

The callback fires after every execution; it never alters the execution flow.

Dao: generic single-table helper

Dao[T] collects "the usual operations for one table" in one place; table name and primary key are maintained at a single point:

type User struct {
    ID      int64  `db:"id,omitempty"`
    Email   string `db:"email"`
    Name    string `db:"name"`
    Deleted int    `db:"deleted"`
}

// Optional hook: implementing it enables it
func (u *User) BeforeCreate() error {
    if u.Email == "" { return errors.New("email required") }
    return nil
}

userDao := dba.NewDao[User](db, "users")          // default pk id, override with .PK("uid")

id, err := userDao.Create(&u)                      // returns auto-increment/RETURNING pk
u, err  := userDao.FetchByPK(42)                  // not found → (nil, nil); check nil first
u, err  := userDao.FetchOne("email = #{1}", email) // strict 0..1, errors on multiple rows
list, err := userDao.FetchList("deleted = 0")
n, err  := userDao.Update(dba.H{"name": "x"}, "id = #{1}", 42)
n, err  := userDao.Delete("id = #{1}", 42)
ok, err := userDao.Exists("email = #{1}", email)
items, total, err := userDao.FetchPage(1, 20, "deleted = 0")
n, err  := userDao.Batch(users)                    // bulk insert, returns affected rows

Design points:

Not found is not an error. FetchOne/FetchByPK return (nil, nil) when nothing matches — not sql.ErrNoRows. "No row" is a normal business outcome; callers check nil, no errors.Is everywhere. FetchOne is strict 0..1 (multiple rows error, so primary-key lookups self-check data integrity); use FetchList when the condition doesn't guarantee uniqueness.

Raw series keeps full chaining. RawCreate/RawSelect/RawBatch return a builder instead of executing, so complex needs (ON CONFLICT, RETURNING, extra conditions) keep building on top of the Dao:

pk, err := userDao.RawCreate(&u).
    Add("ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name").
    Add("RETURNING " + "id").
    FetchOne[int64]()

Transaction propagation. dao.WithTx(tx) returns a Dao copy bound to the transaction; the original Dao is untouched:

db.Transaction(func(tx *dba.SQL) error {
    d := userDao.WithTx(tx)
    // all operations on d go through the transaction
    return nil
})

Alias variables for cross-table JOINs. dao.Vars(alias) produces three reference variables for the table declaration/alias/primary key — table name and pk changes touch only the Dao:

q := db.Add(`SELECT ${u}.*, ${o}.total
             FROM ${u.as} JOIN ${o.as} ON ${o}.uid = ${u.pk}
             WHERE ${u}.status = #{1}`, "active").
    Vars(userDao.Vars("u")).
    Vars(orderDao.Vars("o"))
// ${u.as} → "users" AS "u"    ${u} → "u"    ${u.pk} → "u"."id"

Column references are out of Vars' scope (${u}.email written bare) — the column set is not part of the Dao's maintenance duty.

Safety boundaries

Treat this section as a prerequisite, not a footnote.

Arguments through #{} are safe — every bind/expand path ends in a driver-level placeholder, no concatenation. Risk concentrates in three explicit "text injection" entry points; they exist for flexibility, and the safety responsibility is the caller's.

First, templates themselves must never concatenate untrusted input. Add("WHERE name = '" + userInput + "'") is injection in any library; in dba the problem starts earlier — #{/${ inside userInput would be parsed as macros. Rule: template strings are literals in code or from trusted sources; user data always goes through the argument position.

Second, the raw pipe (!{}) is the only arbitrary-text injection point. Use it only for code-controlled SQL expressions (NOW(), DEFAULT), never user input. Auditing is a global search for |raw and !{ — that enumerates the whole injection surface.

Third, quote/literalquote escape per dialect but do not whitelist. #{1|quote} prevents identifier escaping, yet the user can still name any column (an information-leak surface). For dynamic sort columns, pass through a whitelist before the pipe.

Extending

Custom pipes register via RegisterPipe (instance-scoped, copy-on-write, no global state):

db = db.RegisterPipe("upper", func(ctx dba.RenderCtx, content string) error {
    v, err := ctx.Resolve(content)   // resolve the argument
    if err != nil { return err }
    return ctx.Bind(strings.ToUpper(fmt.Sprint(v))) // Bind: Node inlines, plain values placeholder
})
db.Add("WHERE name = #{1|upper}", "bob")   // binds "BOB"

Inside a pipe, always route argument values through ctx.Bind — the Node inline semantics are inherited automatically.

Non-goals

dba deliberately does not do the following; read this section before filing a feature request in these directions:

No relations and preloading — you write the JOIN; dba offers Vars(alias) to maintain the references, nothing more. No database migrations. No cross-dialect SQL translation — the dialect SQL you write goes out verbatim; dba handles only two dialect differences: placeholders and identifier quoting. No query caching. No interception of empty IN () — that is the database's syntax error; dba does not guess your intent. No nested transactions / savepoints. No model validation — the BeforeCreate/ BeforeUpdate hooks are the seam left for you to do it.

Appendix: macro prefix registration (compatibility)

RegisterMacro(prefix, pipe) registers a custom macro prefix (e.g. ^{1} ≡ #{1|upper}). Kept for existing projects; new code should use #{key|pipe} directly — fully equivalent and no table lookup for readers. # and $ are reserved prefixes; the built-in @/! are implemented through this mechanism too.

db = db.RegisterMacro('^', "upper")   // ^{1} ≡ #{1|upper}

Development

go test -race -coverpkg=./... -covermode=atomic -coverprofile=coverage.out ./...
go tool cover -func=coverage.out | tail -1   # total coverage

-coverpkg=./... matters: the external test package in ./tests exercises the root package, so per-package coverage alone under-reports it. Coverage is published to Codecov by CI.

Documentation

Index

Constants

View Source
const F = "F"

F is the slot variable name for the column set, used by Select and Page.

Contract:

  • Select renders "${F:*}" (default *); FetchPage swaps it for Var(F, "COUNT(1)") to build the count query.
  • To feed hand-written SQL into FetchPage, the main chain must contain either ${F:...} or a bare ${F} (see its probe).
  • The F slot belongs on the main chain (mainNodes); a ${F} hidden inside a varNode is not recognised by FetchPage.
View Source
const I = "I"

I is the slot variable name for an INSERT modifier (INSERT ${I:} INTO ...), empty by default.

Rationale: once Insert has generated a statement, chained Add calls can only append at the end — which is where ON DUPLICATE KEY / RETURNING / ON CONFLICT naturally belong. The gap between INSERT and INTO is the one dead spot, and this slot is the only vent for it:

db.Var(I, "IGNORE").Insert("users", u)  // INSERT IGNORE INTO ...
View Source
const O = "order"

O is the slot variable name for ORDER BY. FetchPage issues two queries whose ORDER BY requirements are exact opposites:

data query:  keep it (SQL Server's OFFSET...FETCH requires ORDER BY)
count query: clear it (an aggregate carrying the ORDER BY source column
             is a hard error on PG/mssql)

Queries meant for FetchPage should therefore write ORDER BY as ${order:...} rather than bare text (variable names are matched literally, so the slot name "order" equals the const value):

q.Add("SELECT ... WHERE x ${order:ORDER BY id DESC}")

Variables

This section is empty.

Functions

func AnsiQuoter

func AnsiQuoter(s string) string

AnsiQuoter wraps identifiers in double quotes.

func ColumnsAndValues added in v0.5.2

func ColumnsAndValues(model any, omitempty bool) ([]string, []any, error)

ColumnsAndValues converts a struct or map into column names and bind values.

Struct strategy: fieldList (derived from the reflectx TypeMap — the same mapper used by #{name} resolution and row scanning) builds the field list, with dba's atomicity decision (isAtomicColumn) applied per field:

  • atomic types (driver.Valuer implementers / time.Time and convertible aliases / Node) collapse to a single column;
  • structs (embedded or plain, value or pointer) expand recursively;
  • other basic types / []byte are single columns.

Collected field values pass through normalizeBindValue before entering vals. The map branch does no normalization (same level as Add arguments: bind exactly what the caller passed).

func DollarFormat

func DollarFormat(idx int) string

DollarFormat returns "$1", "$2", ...

func GroupBy

func GroupBy[T any, K comparable](slice []T, fn func(T) K) map[K][]T

GroupBy groups slice elements by fn(element): 1 key → N values.

func IndexBy

func IndexBy[T any, K comparable](slice []T, fn func(T) K) (map[K]T, error)

IndexBy converts a slice into a map keyed by fn(element). Returns an error if duplicate keys are found.

func IsOk

func IsOk(v any) bool

IsOk returns true if v is non-nil, non-blank string, or non-empty slice/array/map.

func LimitOffsetPager added in v0.14.0

func LimitOffsetPager(limit, offset string) string

LimitOffsetPager generates "LIMIT n OFFSET m" (the mysql/sqlite family; NewFromSqlx selects it automatically for those drivers).

func Map

func Map[T any, R any](slice []T, fn func(T) R) []R

Map transforms each element of a slice using fn and returns a new slice.

func MySQLQuoter

func MySQLQuoter(s string) string

MySQLQuoter wraps identifiers in backticks.

func OffsetFetchPager added in v0.14.0

func OffsetFetchPager(limit, offset string) string

OffsetFetchPager generates "OFFSET m ROWS FETCH NEXT n ROWS ONLY" (SQL:2008 standard; the default, supported by PostgreSQL / SQL Server 2012+ / Oracle 12c+ / DB2 11.1+).

Note: SQL Server/Oracle/DB2 require ORDER BY — the query must carry the ${order:...} slot or a bare ORDER BY.

func QmarkFormat

func QmarkFormat(_ int) string

QmarkFormat returns "?" for every index.

Types

type BeforeCreate

type BeforeCreate interface {
	BeforeCreate() error
}

BeforeCreate is implemented by structs that need a hook before INSERT.

type BeforeUpdate

type BeforeUpdate interface {
	BeforeUpdate() error
}

BeforeUpdate is implemented by structs that need a hook before UPDATE.

type Dao

type Dao[T any] struct {
	// contains filtered or unexported fields
}

Dao is a generic single-table CRUD helper.

func NewDao

func NewDao[T any](q *SQL, table string) *Dao[T]

NewDao creates a Dao bound to the given table. Default primary key is "id".

func (*Dao[T]) Batch

func (d *Dao[T]) Batch(entities []T) (int64, error)

Batch bulk-inserts multiple records and returns affected rows.

func (*Dao[T]) Count

func (d *Dao[T]) Count(where string, args ...any) (int64, error)

Count returns the number of matching records.

func (*Dao[T]) CountAll

func (d *Dao[T]) CountAll() (int64, error)

CountAll returns the total number of records in the table.

func (*Dao[T]) Create

func (d *Dao[T]) Create(data any) (int64, error)

Create inserts a single record and returns the generated primary key. On PostgreSQL, uses RETURNING. On other drivers, uses LastInsertId.

func (*Dao[T]) Delete

func (d *Dao[T]) Delete(where string, args ...any) (int64, error)

Delete deletes records matching the given condition.

func (*Dao[T]) Exists

func (d *Dao[T]) Exists(where string, args ...any) (bool, error)

Exists returns true if at least one matching record exists.

func (*Dao[T]) FetchAll added in v0.14.0

func (d *Dao[T]) FetchAll() ([]T, error)

FetchAll fetches all records from the table.

func (*Dao[T]) FetchByPK added in v0.15.0

func (d *Dao[T]) FetchByPK(id any) (*T, error)

FetchByPK fetches a single record by primary key. The primary key is unique, so this goes through the strict FetchOne (more than one row means corrupt data and is reported as an error). Returns (nil, nil) when nothing is found — "no result" is a normal business outcome rather than an error (sql.ErrNoRows is not returned); callers must check for nil before dereferencing.

func (*Dao[T]) FetchList added in v0.14.0

func (d *Dao[T]) FetchList(where string, args ...any) ([]T, error)

FetchList fetches multiple records by condition.

func (*Dao[T]) FetchOne added in v0.14.0

func (d *Dao[T]) FetchOne(where string, args ...any) (*T, error)

FetchOne fetches a single record by condition (strictly 0..1: more than one row is an error). Returns (nil, nil) when nothing is found — "no result" is a normal business outcome rather than an error (sql.ErrNoRows is not returned); callers must check for nil before dereferencing. Use FetchList when the condition is not guaranteed to be unique.

func (*Dao[T]) FetchPage added in v0.14.0

func (d *Dao[T]) FetchPage(page, size int, where string, args ...any) ([]T, int64, error)

func (*Dao[T]) PK

func (d *Dao[T]) PK(pk string) *Dao[T]

PK returns a new Dao with the given primary key column.

func (*Dao[T]) RawBatch

func (d *Dao[T]) RawBatch(entities []T) *SQL

RawBatch bulk-inserts multiple records and returns a SQL builder for chaining.

func (*Dao[T]) RawCreate

func (d *Dao[T]) RawCreate(data any) *SQL

RawCreate inserts a single record and returns the SQL builder for chaining (e.g. ON CONFLICT, RETURNING).

func (*Dao[T]) RawSelect

func (d *Dao[T]) RawSelect(where string, args ...any) *SQL

func (*Dao[T]) SQL

func (d *Dao[T]) SQL() *SQL

SQL returns the underlying SQL builder for custom queries.

func (*Dao[T]) Table

func (d *Dao[T]) Table(table string) *Dao[T]

Table returns a new Dao with the given table name.

func (*Dao[T]) Update

func (d *Dao[T]) Update(data any, where string, args ...any) (int64, error)

Update updates records matching the given condition.

func (*Dao[T]) Vars

func (d *Dao[T]) Vars(alias string) map[string]Node

Vars generates the table alias reference variables (table name and primary key are maintained in one place, the Dao):

${u.as}  → `users` AS `u`   (FROM/JOIN table declaration)
${u}     → `u`              (alias reference: ${u}.name / ${u}.*)
${u.pk}  → `u`.`id`         (primary key reference, kept by Dao.PK)

Column references are not generated (write them bare: ${u}.email) — the column set is not part of the Dao's maintenance duty. Without an alias (single-table case) it returns nil; just write the table name bare.

func (*Dao[T]) WithCtx

func (d *Dao[T]) WithCtx(ctx context.Context) *Dao[T]

func (*Dao[T]) WithTx

func (d *Dao[T]) WithTx(tx *SQL) *Dao[T]

WithTx returns a Dao backed by the given transaction.

type Formatter added in v0.12.0

type Formatter func(idx int) string

Formatter generates a placeholder for the n-th parameter.

type H

type H = map[string]any

H is a shorthand for map[string]any.

type LogFunc added in v0.11.0

type LogFunc func(ctx context.Context, begin time.Time, query string, args []any, err error)

LogFunc is the SQL execution log callback: it runs once after every execution. begin is the start time, so observers compute the duration or open a span themselves. It does not change the execution flow, and a panic raised inside it does not affect the result.

func NewLogger added in v0.11.0

func NewLogger(logger *slog.Logger, slowThreshold time.Duration, doClean bool) LogFunc

NewLogger is the default execution log callback, based on slog: slow queries are logged at Warn, errors at Error, and everything else at Debug. Set doClean to true to fold whitespace for single-line display. SQL syntax (comments/dialect) is preserved as-is — dba does not interpret SQL.

type Node

type Node struct {
	Text string
	Args []any
}

Node is a SQL fragment together with its own positional arguments, as returned by Expr. It makes "arguments as subtrees" possible: a Node passed in an argument position is inlined into the SQL instead of being bound as a placeholder, and a Node stored in a struct field collapses to a single column value.

Argument numbering inside Text restarts at #{1}, independently of the fragment the Node is inlined into.

func Expr

func Expr(sql string, args ...any) Node

Expr creates a Node fragment; passed as an argument it is inlined into the SQL ("arguments as subtrees") instead of bound as a placeholder.

type Pager added in v0.14.0

type Pager func(limit, offset string) string

Pager generates the row-limiting clause appended by FetchPage.

limit and offset are two placeholder tokens (e.g. "#{1}", "#{2}") whose positions in the clause the pager decides. FetchPage always passes arguments in the semantic order (limit=size, offset), so a pager is free to swap the two tokens to match its dialect without affecting the caller.

type Pipe added in v0.8.0

type Pipe func(ctx RenderCtx, content string) error

Pipe is a pipe: the interpreter of macro content.

A pipe receives the literal macro content (e.g. "1" for #{1|pipe}, "users" for @{users}) and decides what to do with it: use the literal as-is (ident), or turn it into an argument via ctx.Resolve (bind/raw/expand). User pipes are free to choose — that freedom is the whole point. Built-ins: bind/expand/raw/quote/literalquote; register your own with RegisterPipe.

type Quoter

type Quoter func(string) string

Quoter quotes an identifier for the target dialect.

type RenderCtx added in v0.8.0

type RenderCtx interface {
	// Bind binds a value: a Node is rendered inline (arguments as subtrees),
	// any other value writes a placeholder and collects the argument.
	// This is the single implementation point of the "arguments as subtrees"
	// invariant — pipes always route argument values through Bind.
	Bind(v any) error
	// Render renders a Node sub-fragment inline, recursively, using that
	// Node's own Args.
	Render(n Node) error
	// WriteString writes raw SQL text.
	WriteString(s string)
	// QuoteIdent writes a dialect-quoted identifier.
	QuoteIdent(s string)
	// Resolve resolves macro content as an argument key (positional or named);
	// pipes call it as needed.
	Resolve(key string) (any, error)
}

RenderCtx is the rendering context: the output port for pipes and macros.

type SQL

type SQL struct {
	// contains filtered or unexported fields
}

SQL is an immutable, chainable query builder backed by sqlx.

func NewFromSqlx

func NewFromSqlx(db *sqlx.DB) *SQL

NewFromSqlx creates a SQL builder from a sqlx.DB. Auto-detects driver for placeholder formatter, identifier quoting, and FetchPage's row-limiting clause (SQL:2008 OFFSET...FETCH by default; LIMIT/OFFSET for mysql/sqlite).

func Open

func Open(driver, dsn string) (*SQL, error)

Open connects to a database and returns a SQL builder. It is a convenience over sqlx.Connect + NewFromSqlx.

func (*SQL) Add

func (d *SQL) Add(query string, args ...any) *SQL

Add appends a SQL fragment and returns a new builder.

func (*SQL) AddIf

func (d *SQL) AddIf(cond bool, query string, args ...any) *SQL

AddIf conditionally appends a SQL fragment.

func (*SQL) Batch

func (d *SQL) Batch(rows [][]any) *SQL

Batch generates parenthesized value groups for bulk INSERT.

func (*SQL) BatchInsert

func (d *SQL) BatchInsert(table string, entities []any) *SQL

BatchInsert builds a complete INSERT from a slice of entities. All entities must have the same column structure.

func (*SQL) Begin

func (d *SQL) Begin() (*SQL, error)

Begin starts a transaction and returns a new builder backed by the Tx.

func (*SQL) Close

func (d *SQL) Close() error

Close closes the underlying connection pool. Builders created with NewFromSqlx share the caller's *sqlx.DB, so closing them also closes that shared pool.

func (*SQL) Commit

func (d *SQL) Commit() error

Commit commits the active transaction.

func (*SQL) Delete

func (d *SQL) Delete(table string, where string, args ...any) *SQL

Delete generates and appends a DELETE FROM statement.

func (*SQL) Error

func (d *SQL) Error() error

Error returns the accumulated error on this builder.

func (*SQL) Exec

func (d *SQL) Exec() (sql.Result, error)

Exec builds and executes a non-query statement.

func (*SQL) FetchGrouped added in v0.14.0

func (d *SQL) FetchGrouped[K comparable, V any](key func(V) K) (map[K][]V, error)

FetchGrouped fetches all rows and groups them into a map keyed by key(v) — the query-side GroupBy. Each key maps to a slice of values, in query order.

func (*SQL) FetchIndexed added in v0.14.0

func (d *SQL) FetchIndexed[K comparable, V any](key func(V) K) (map[K]V, error)

FetchIndexed fetches all rows and indexes them into a map keyed by key(v) — the query-side IndexBy. Duplicate keys are an error (same as IndexBy).

func (*SQL) FetchList added in v0.14.0

func (d *SQL) FetchList[T any]() ([]T, error)

FetchList scans multiple rows and returns them as a slice (struct/basic types, sqlx mapping). For dynamic queries with unknown columns use FetchMaps.

func (*SQL) FetchMaps added in v0.14.0

func (d *SQL) FetchMaps() ([]map[string]any, error)

FetchMaps scans multiple rows into a slice of maps — for queries whose columns are not known at compile time.

func (*SQL) FetchOne added in v0.14.0

func (d *SQL) FetchOne[T any]() (*T, error)

FetchOne scans a single row and returns a pointer to it. Strictly 0..1: no row → (nil, nil); more than one row → an error (it fetches one extra row, catching a missing LIMIT 1). When "any row will do", write LIMIT 1 explicitly in the SQL.

T is meant to be a value type (FetchOne[User]() returns *User; the pointer comes from the return value). Passing a pointer type yields **T, so do not write FetchOne[*User]().

func (*SQL) FetchOneMap added in v0.14.0

func (d *SQL) FetchOneMap() (map[string]any, error)

FetchOneMap scans a single row into a map — for queries whose columns are not known at compile time. Same contract as FetchOne: strictly 0..1, more than one row is an error; no row → (nil, nil).

func (*SQL) FetchPage added in v0.14.0

func (d *SQL) FetchPage[T any](page, size int) ([]T, int64, error)

FetchPage fetches a page of rows and the total count.

Contract: the query must contain the ${F:...} slot (or bare ${F}) on the main chain — the F slot is substituted with COUNT(1) for the count query. Constraints: no GROUP BY / DISTINCT in the F slot content (the count query reuses the same template).

ORDER BY must live in the ${order:...} slot: the count query clears it with Var(O, ""). Under strict dialects (PG/mssql) an aggregate query carrying the ORDER BY source column is a hard error, while SQL Server's OFFSET...FETCH forces the data query to carry ORDER BY — only the ${order} slot satisfies both at once. Example:

q.Add("SELECT ... WHERE x ${order:ORDER BY id DESC}") // then q.FetchPage[User](1, 20)

The row-limiting clause defaults to the SQL:2008 standard (OFFSET...FETCH); the mysql/sqlite family switches to LIMIT/OFFSET automatically, and Pager() overrides either. The SQL Server/Oracle/DB2 family requires ORDER BY. page/size must be >= 1, otherwise an error is returned (no silent clamping — the caller's parameter bug surfaces early). The total==0 case skips the data query entirely. The total==0 case skips the data query entirely.

func (*SQL) FetchRows added in v0.14.0

func (d *SQL) FetchRows() (*sqlx.Rows, error)

FetchRows returns a raw *sqlx.Rows cursor (the query executes immediately, which suits streaming scenarios; for lazy streaming use Iter).

func (*SQL) Formatter added in v0.12.0

func (d *SQL) Formatter(formatter Formatter) *SQL

Formatter returns a new builder with the given placeholder formatter.

func (*SQL) Insert

func (d *SQL) Insert(table string, data any) *SQL

Insert generates and appends an INSERT INTO statement.

func (*SQL) Iter added in v0.14.0

func (d *SQL) Iter[T any]() iter.Seq2[T, error]

Iter returns a lazy iterator over the rows (struct/basic types, sqlx mapping). Lazy: the query runs when the for-range starts, and an early break still releases the connection. Each row needs an err check (inherent to iter.Seq2[T, error]).

for u, err := range q.Iter[User]() {
    if err != nil {
        return err
    }
    // ...
}

func (*SQL) Pager added in v0.14.0

func (d *SQL) Pager(pager Pager) *SQL

Pager returns a new builder using the given renderer for the row-limiting clause appended by FetchPage, overriding the driverName auto-detection.

func (*SQL) Pool

func (d *SQL) Pool() *sqlx.DB

Pool returns the *sqlx.DB this builder was created from.

func (*SQL) Quoter

func (d *SQL) Quoter(quoter Quoter) *SQL

Quoter returns a new builder with the given identifier quoter.

func (*SQL) RegisterMacro added in v0.8.0

func (d *SQL) RegisterMacro(prefix byte, pipe string) *SQL

RegisterMacro registers a macro alias (prefix → pipe name) and returns a new builder:

q := db.RegisterMacro('^', "upper")
q.Add("WHERE name = ^{1}", "bob") → equivalent to #{1|upper}

'#' and '$' are reserved prefixes and cannot be registered; the pipe name may point at a pipe that is not registered yet (the error surfaces at render time).

func (*SQL) RegisterPipe added in v0.8.0

func (d *SQL) RegisterPipe(name string, fn Pipe) *SQL

RegisterPipe registers a custom pipe and returns a new builder:

q := db.RegisterPipe("upper", func(ctx RenderCtx, content string) error {
	v, err := ctx.Resolve(content)
	if err != nil {
		return err
	}
	return ctx.Bind(strings.ToUpper(fmt.Sprint(v))) // Bind: Node inlines, plain values become placeholders
})
q.Add("WHERE name = #{1|upper}", "bob") → $1 = "BOB"

func (*SQL) Rollback

func (d *SQL) Rollback() error

Rollback rolls back the active transaction.

func (*SQL) Select

func (d *SQL) Select(table string, where string, args ...any) *SQL

Select generates a SELECT statement with ${F:*} for field expansion.

func (*SQL) SetLogger added in v0.11.0

func (d *SQL) SetLogger(fn LogFunc) *SQL

SetLogger sets the execution log callback (nil disables logging). Copy-on-write: the receiver is not modified.

func (*SQL) ToSQL

func (d *SQL) ToSQL() (string, []any, error)

ToSQL returns the built SQL and arguments without executing.

func (*SQL) Transaction

func (d *SQL) Transaction(fn func(*SQL) error) error

Transaction executes fn inside a transaction. If fn returns an error or panics, the transaction is rolled back. If already in a transaction, fn is executed directly without nesting.

func (*SQL) Unsafe

func (d *SQL) Unsafe() *SQL

Unsafe returns a new builder that ignores unmapped columns.

func (*SQL) Update

func (d *SQL) Update(table string, data any, where string, args ...any) *SQL

Update generates and appends an UPDATE ... SET statement.

func (*SQL) Var

func (d *SQL) Var(key string, query string, args ...any) *SQL

Var registers a named variable for ${key} expansion.

func (*SQL) Vars

func (d *SQL) Vars(vars map[string]Node) *SQL

Vars registers several named variables at once for ${key} expansion, typically the map returned by Dao.Vars. Variables are merged into the existing set rather than replacing it, and the receiver is not modified.

func (*SQL) WithCtx

func (d *SQL) WithCtx(ctx context.Context) *SQL

WithCtx returns a new builder with the given context.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL