dba

package module
v0.14.0 Latest Latest
Warning

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

Go to latest
Published: Aug 20, 2026 License: MIT Imports: 16 Imported by: 9

README

dba

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 → (zero, false, nil); more than one → error;
// write LIMIT 1 to express "any row")
u, found, err := q.FetchOne[User]()
// 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, found, 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)
m, found, 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, ok, err  := userDao.GetByID(42)                 // not found → (zero, false, nil); check ok first
u, ok, 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/GetByID return (zero, false, nil) when nothing matches — not sql.ErrNoRows. "No row" is a normal business outcome; callers check ok, 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:

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}

Documentation

Index

Constants

View Source
const F = "F"

F is the default field variable name used by Select and Page. F 是 Select/Page 的列集槽位变量名。

契约:

  • Select 生成 "${F:*}" (默认 *), Page 用 Var(F, "COUNT(1)") 换计数。
  • 手写 SQL 接入 Page: 主链必须含 ${F:...} 或裸 ${F} (见 Page 的探测)。
  • 约定 F 槽在主链 (mainNodes); 藏在 varNode 里的 ${F} 不被 Page 识别。
View Source
const I = "I"

I 是 INSERT 修饰词槽位 (INSERT ${I:} INTO ...), 默认为空。 存在理由: Insert 生成后, 链式 Add 只能追加尾部 (ON DUPLICATE KEY / RETURNING / ON CONFLICT 天然可达), 唯独 INSERT 与 INTO 之间是死角, 此槽是唯一通气孔:

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

O 是排序槽位变量名。FetchPage 发两条查询, 对 ORDER BY 的要求恰好相反:

data 查询: 保留 (SQL Server 的 OFFSET...FETCH 强制 ORDER BY)
count 查询: 清空 (聚合查询带 ORDER BY 源列在 PG/mssql 是硬错误)

因此接入 FetchPage 的查询应把 ORDER BY 写成 ${order:...} 而非裸文本 (变量名是字面匹配, 槽名 "order" 与 const 值一致):

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" (mysql/sqlite 系; NewFromSqlx 对这两族 driver 自动选用)。

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 标准, 默认: PostgreSQL / SQL Server 2012+ / Oracle 12c+ / DB2 11.1+ 均支持)。 注意: SQL Server/Oracle/DB2 强制要求 ORDER BY —— 查询必须带 ${order:...} 槽 或裸 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]) 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, bool, error)

FetchOne fetches a single record by condition (严格 0..1: 多于一行报错)。 未找到时返回 (零值, false, nil) —— “无结果”是正常业务结果而非错误 (不返回 sql.ErrNoRows); 调用方必须先判 ok 再使用返回的实体。 条件不保证唯一时请用 FetchList。

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]) GetByID

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

GetByID fetches a single record by primary key. 主键唯一, 内部走严格 FetchOne (>1 行即数据异常, 会报错)。 未找到时返回 (零值, false, nil) —— “无结果”是正常业务结果而非错误 (不返回 sql.ErrNoRows); 调用方必须先判 ok 再使用返回的实体。

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 生成表别名引用变量 (表名/主键在 DAO 一处维护):

${u.as}  → ` + "`users` AS `u`" + `   (FROM/JOIN 表声明)
${u}     → ` + "`u`" + `              (别名引用: ${u}.name / ${u}.*)
${u.pk}  → ` + "`u`.`id`" + `          (主键引用: 主键列名 DAO.PK 维护)

列引用不生成 (列名裸写: `${u}.email`), 列集不属于 DAO 的维护职责。 无 alias (单表场景) 返回 nil, 表名裸写即可。

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 SQL 执行日志回调: 每次执行后调用一次 (begin 为执行开始时间, 观察者自行计算耗时/开 span; 不改变执行流, panic 不影响执行结果)。

func NewLogger added in v0.11.0

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

NewLogger 默认执行日志回调 (基于 slog): 慢查询 Warn / 错误 Error / 常规 Debug。 Set cleanSpec to true to fold whitespace for single-line display. SQL 语法 (注释/方言) 原样保留 —— dba 不解释 SQL。

type Node

type Node struct {
	Text string
	Args []any
}

func Expr

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

Expr creates a Node fragment; passed as an argument it is inlined into the SQL (参数即子树) 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.

入参是两个占位符 token (如 "#{1}", "#{2}"), pager 决定它们在子句中的位置; FetchPage 始终按 (limit=size, offset) 的语义顺序传参, 因此 pager 可以自由 调换两个 token 的位置 (方言差异), 而不影响调用方。

type Pipe added in v0.8.0

type Pipe func(ctx RenderCtx, content string) error

Pipe 管道: 宏内容的解读者。

管道收到宏内容的字面量 (如 #{1|pipe} 的 "1"、@{users} 的 "users"), 由管道自行决定: 字面量直接用 (ident), 或经 ctx.Resolve 取参数 (bind/raw/expand)。 用户管道可自由选择 — 这是管道的灵活性所在。 内置: bind/expand/raw/quote/literalquote; 用户通过 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 绑定一个值: Node 内联渲染 (参数即子树), 其他值写占位符并收集参数。
	// "参数即子树"不变式的唯一实现点 —— 管道一律经 Bind 处理参数值。
	Bind(v any) error
	// Render 内联渲染一个 Node 子片段 (递归, 用该 Node 自己的 Args)。
	Render(n Node) error
	// WriteString 写入原始 SQL 文本。
	WriteString(s string)
	// QuoteIdent 写入方言 quoting 的标识符。
	QuoteIdent(s string)
	// Resolve 把宏内容作为参数 key 解析 (位置/命名); 管道按需调用。
	Resolve(key string) (any, error)
}

RenderCtx 渲染上下文: 管道与宏的渲染出口。

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

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) — 查询版 GroupBy。每个键对应一个值切片 (保持查询顺序)。

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) — 查询版 IndexBy。重复键报错 (与 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, bool, error)

FetchOne scans a single row and returns it by value. 严格 0..1: 无行 → (零值, false, nil); 多于一行 → 报错 (多取一行检测, 抓住漏写 LIMIT 1 的 bug)。需要“随便取一行”时在 SQL 里显式写 LIMIT 1。

func (*SQL) FetchOneMap added in v0.14.0

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

FetchOneMap scans a single row into a map — for queries whose columns are not known at compile time. 与 FetchOne 同契约: 严格 0..1, 多于一行报错。 found=false (nil map) when no row matches.

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, "") — 严格方言 (PG/mssql) 下聚合查询带 ORDER BY 源列是硬错误, 且 SQL Server 的 OFFSET...FETCH 强制数据查询带 ORDER BY, 两者只能靠 ${order} 槽同时满足。Example:

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

分页子句默认 SQL:2008 标准 (OFFSET...FETCH), mysql/sqlite 系自动切 LIMIT/OFFSET, 可用 Pager() 覆盖; SQL Server/Oracle/DB2 系强制 ORDER BY。 page/size 必须 >= 1, 否则报错 (不静默钳制, 尽早暴露调用方的参数 bug)。 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 (查询立即执行, 适合流式场景; 惰性流式请用 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). 惰性: 查询在 for-range 开始时才执行; break 提前退出会正确释放 连接。每行需判 err (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 with the given row-limiting clause renderer (FetchPage 追加的分页子句模板, 覆盖 driverName 自动探测)。

func (*SQL) Pool

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

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 注册宏别名 (前缀 → 管道名), 返回新 builder:

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

'#'/'$' 为保留前缀不可注册; 管道名允许指向尚未注册的管道 (渲染时报错)。

func (*SQL) RegisterPipe added in v0.8.0

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

RegisterPipe 注册自定义管道, 返回新 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 内联, 普通值占位符
})
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 设置执行日志回调 (nil 关闭)。copy-on-write。

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

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