dba

package module
v0.10.0 Latest Latest
Warning

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

 Go to latest Published: Aug 2, 2026 License: MIT Imports: 15 Imported by: 0

README

dba

dba is an immutable, chainable SQL builder for sqlx. No ORM, no code generation — you write SQL, it handles the plumbing.

Quick Example

q := dba.NewFromSqlx(db)

// One base query, three uses — none of them mutate the original
base := q.Add("SELECT ${F:*} FROM users ${where:} ${order:ORDER BY id}")

// Use 1: filtered list with pagination
filter := base.
    Var("where", "WHERE status = #{1} AND age >= #{2}", "active", 18).
    Var("order", "ORDER BY created_at DESC")

// Use 2: count total for the same filter — Var overrides F to COUNT(1)
total, _, _ := dba.Scalar[int64](filter.Var(dba.F, "COUNT(1)"))

// Use 3: get just one column for a dropdown
ids := []int64{}
filter.Var(dba.F, "DISTINCT id").List(&ids)

What just happened:

  • ${F:*} and ${where:} are named slots — fill them with Var, leave them empty to use the inline default (* and nothing)
  • dba.Scalar and dba.Page reuse the same query for count + data without string manipulation
  • filter is still untouched after all three calls — immutable, safe to pass around

Why Not Just sqlx?

// sqlx — string wrangling, manual IN expansion, error-prone
query := "SELECT * FROM users WHERE status = ?"
args := []any{"active"}
if name != "" {
    query += " AND name = ?"
    args = append(args, name)
}
query, args, _ := sqlx.In("SELECT * FROM users WHERE id IN (?)", ids)
// → "SELECT * FROM users WHERE id IN (?,?,?)" with 3 args

// dba — same thing, cleaner
q.Add("SELECT * FROM users WHERE status = #{1}", "active").
    AddIf(name != "", "AND name = #{1}", name).
    Add("AND id IN (#{1|expand})", ids) // explicit slice expansion
Pain point Raw sqlx dba
IN (?) expansion sqlx.In() in a separate call `#{1
Conditional clauses String concat + manual arg spread AddIf(cond, ...)
Count + data pagination Two manually maintained SQL strings Page[T](base, page, size)
Identifier quoting Manual per dialect @{1} auto-quotes for PG/MySQL/SQLite
Struct hooks (timestamps) Manual before every insert BeforeCreate() error interface

Core Builder

Macro syntax
Syntax What it does Example
#{1} Positional parameter — single value, never expands "WHERE id = #{1}" → "WHERE id = $1"
#{name} Named parameter from map/struct "WHERE id = #{id}" + map["id"]
`#{1 pipe}` Pipe — custom value rendering
@{1} / !{1} Macro aliases: @→ident, !→raw "SELECT @{1}" → SELECT "name"
${key:default} Fillable slot with fallback "${order:ORDER BY id}" → overridable
XX{...} Double prefix escapes to literal (any registered macro) "##{1}" → literal "#{1}"

Parameters are single values by default — rendering is explicit via pipes:

  • #{1} binds one value as-is: []byte works as a single binary param (BLOB/BINARY columns), and passing a non-byte slice is left to the driver to reject (unsupported type []int).
  • #{1|expand} expands a slice/array into separate params — use it for IN lists: IN (#{1|expand}) + []int{1,2,3} → $1, $2, $3. No more sqlx.In. An empty slice expands to zero params (IN (), rejected by the database).
  • #{1|raw} injects raw text; #{1|ident} quotes an identifier. (NULL: pass nil — #{1} binds it directly)
  • @{1} and !{1} are macro aliases for ident / raw — the template-side shortcuts.
  • Whitespace is tolerated around keys and pipes: #{1| expand } ≡ #{1|expand}.
Custom pipes & macros

Pipes are the single extension point for value rendering — register once, use in any template:

q := db.RegisterPipe("upper", func(ctx dba.RenderCtx, v any) error {
    ctx.AddParam(strings.ToUpper(v.(string)))
    return nil
})
q.Add("WHERE name = #{1|upper}", "bob") // → $1 = "BOB"

// Macro aliases: any prefix → pipe (local dialect, shared semantics)
q = q.RegisterMacro('^', "upper")
q.Add("WHERE name = ^{1}", "bob") // same as #{1|upper}

Both are instance-scoped and copy-on-write — no global state, no concurrency issues. # and $ are reserved prefixes; @/! are built-in macros (ident/raw).

Methods
q.Add("SELECT * FROM users WHERE status = #{1}", "active")          // positional
q.AddIf(minAge > 0, "AND age >= #{1}", minAge)                      // conditional
q.Add("WHERE (name, dept) = (#{1}, #{2})", "alice", "engineering") // named from map
Var — declarative slots

Slots let you delay decisions about what to SELECT or filter on. They're the mechanism behind Page and Expr.

// Basic: override the inline default
q.Add("SELECT ${F:*} FROM users").Var(dba.F, "id, name").ToSQL()
// → SELECT id, name FROM users

// Multiple optional clauses
q.Add("SELECT * FROM users ${where:} ${order:ORDER BY id} ${limit:}").
    Var("where", "WHERE status = #{1}", "active").
    Var("limit", "LIMIT #{1}", 20)
// → SELECT * FROM users WHERE status = $1 ORDER BY id LIMIT $2

// Immutable — each Var returns a new copy
base   := q.Add("SELECT ${F:*} FROM users")
count  := base.Var(dba.F, "COUNT(1)")   // for counting
data   := base.Add("LIMIT 10")           // for listing
// base, count, data are three independent queries

${key:default} — uses default when Var not set. ${key} with no Var and no default outputs nothing.

DML Helpers

Columns are sorted lexicographically for stable SQL. omitempty zero values are excluded.

type User struct {
    ID        int    `db:"id,omitempty"`
    Name      string `db:"name"`
    CreatedAt string `db:"created_at"`
}

q.Insert("users", User{Name: "alice"})
// INSERT INTO "users" ("name") VALUES ($1)  — ID and CreatedAt omitted

q.Update("users", map[string]any{"name": "bob"}, "id = #{1}", 42)
// UPDATE "users" SET "name"=$1 WHERE id = $2

q.Delete("users", "id = #{1}", 42)
// DELETE FROM "users" WHERE id = $1

${I} is an optional slot between INSERT and INTO — use Var(dba.I, "OR IGNORE") to generate INSERT OR IGNORE INTO.

Expr — raw SQL in values
q.Update("stats", map[string]any{
    "views": dba.Expr("views + 1"),
    "score": dba.Expr("score + #{1}", 10),
}, "id = #{1}", 1)
// UPDATE "stats" SET "score"=score + $1, "views"=views + 1 WHERE id = $2
BatchInsert — struct slices to bulk INSERT
users := []User{
    {Name: "alice"},
    {Name: "bob"},
    {Name: "carol"},
}
q.BatchInsert("users", anySlice(users)).Exec()
// INSERT INTO "users" ("name") VALUES ($1), ($2), ($3)
sql.Null* types

NullString/NullInt64 are treated as atomic columns. With omitempty, only the zero value (Valid=false + zero inner value) is omitted:

type Profile struct {
    Bio sql.NullString `db:"bio,omitempty"`
}
// NullString{Valid: false} → omitted (zero value)
// NullString{Valid: true, String: ""} → kept (user set empty string)
// NullString{Valid: true, String: "hello"} → kept

Pagination

q := dba.NewFromSqlx(db).Add("SELECT ${F:*} FROM users ${where:}").
    AddIf(status != "", "WHERE status = #{1}", status).
    Add("ORDER BY id DESC")

items, total, err := dba.Page[User](q, page, size)
// Internally: Var(F, "COUNT(1)") for total, then Add("LIMIT ? OFFSET ?") for data

When total == 0, the data query is skipped entirely.

Generic DAO

type User struct {
    ID        int       `db:"id,omitempty"`
    Name      string    `db:"name"`
    CreatedAt time.Time `db:"created_at"`
}
func (u *User) BeforeCreate() error {
    u.CreatedAt = time.Now()
    return nil
}

dao := dba.NewDao[User](q, "users")

id, _ := dao.Create(User{Name: "alice"})          // hook sets created_at
user, _ := dao.GetByID(id)                         // *User, nil when not found
affected, _ := dao.Update(data, "id = #{1}", id)  // map skips hooks
items, _ := dao.List("age > #{1}", 18)            // []User
count, _ := dao.Count("age > #{1}", 18)           // int64
Cross-DAO transactions
q.Transaction(func(tx *dba.SQL) error {
    uid, err := userDao.WithTx(tx).Create(User{Name: "alice"})
    if err != nil { return err }
    _, err = orderDao.WithTx(tx).Create(Order{UserID: int(uid), Product: "widget"})
    return err // nil → commit, error → rollback
})

Middleware

Onion model — all queries go through the chain. Attach logging, metrics, tracing.

q = q.Use(dba.LogMiddleware(slog.Default(), 200*time.Millisecond))

Custom middleware:

q = q.Use(func(next dba.ExecFunc) dba.ExecFunc {
    return func(ctx context.Context, query string, args []any) (any, error) {
        start := time.Now()
        result, err := next(ctx, query, args)
        log.Printf("[%s] %s", time.Since(start), query)
        return result, err
    }
})

Terminal Methods

  • Get(dest) (bool, error) — single row, (false, nil) when not found
  • List(dest) error — slice pointer
  • Exec() (sql.Result, error) — INSERT/UPDATE/DELETE
  • Rows() (*sqlx.Rows, error) — streaming large result sets
  • ToSQL() (string, []any, error) — debug without execution

Utilities

count, found, _ := dba.Scalar[int64](q.Add("SELECT COUNT(1) FROM users"))

ok := dba.IsOk(value) // true for non-nil, non-empty string, non-empty slice/map

Documentation

Index

Constants

View Source 
const (
	F = "F"
	I = "I"
)

F is the default field variable name used by Select and Page.

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 把 struct 或 map 转换为列名与参数值列表。

struct 策略: 自建递归遍历 (fieldList) 生成字段清单, 遍历时即时判定:

  • 原子类型 (driver.Valuer 实现者 / time.Time 及其可转换别名) 作为单列收束;
  • struct (匿名嵌入或普通字段, 值或指针) 递归展开子字段;
  • 其余基本类型/[]byte 直接作为单列。

time.Time 不实现 Valuer (database/sql 原生参数类型), 由 isAtomicColumn 显式特判。

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 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 Page 

func Page[T any](q *SQL, page, size int) ([]T, int64, error)

Page the query must contain ${F:...} so that Page can swap the field list for COUNT(1).

func QmarkFormat 

func QmarkFormat(_ int) string

QmarkFormat returns "?" for every index.

func Scalar 

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

Scalar returns a single scalar value from a query.

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

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

All fetches all records from the table.

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

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

Get fetches a single record by condition. 未找到时返回 (nil, nil) —— “无结果”是正常业务结果而非错误 (不返回 sql.ErrNoRows); 调用方必须先判 nil 再解引用。

func (*Dao[T]) GetByID 

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

GetByID fetches a single record by primary key. 未找到时返回 (nil, nil) —— “无结果”是正常业务结果而非错误 (不返回 sql.ErrNoRows); 调用方必须先判 nil 再解引用。

func (*Dao[T]) List 

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

List fetches multiple records by condition.

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

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

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 ExecFunc 

type ExecFunc func(ctx context.Context, query string, args []any) (any, error)

ExecFunc is the execution function passed through the middleware chain.

type Formater 

type Formater func(idx int) string

Formater generates a placeholder for the n-th parameter.

type H 

type H = map[string]any

H is a shorthand for map[string]any.

type Hook 

type Hook func(next ExecFunc) ExecFunc

Hook wraps an ExecFunc in onion-style middleware.

func LogHook 

func LogHook(logger *slog.Logger, slowThreshold time.Duration, cleanSpec bool) Hook

LogHook returns a middleware that logs every SQL execution with duration, query, and arguments. Queries exceeding slowThreshold are logged at Warn level. Set cleanSpec to true to fold whitespace for single-line display. SQL 语法 (注释/方言) 原样保留 —— dba 不解释 SQL。

type Node 

type Node struct {
	RawSQL string
	Args   []any
}

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/ident; 用户通过 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 {
	// AddParam 写入一个占位符并收集绑定参数 (内部维护序号与方言格式)。
	AddParam(v any)
	// 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 formater and identifier quoting.

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) Formater 

func (d *SQL) Formater(formatter Formater) *SQL

Formater returns a new builder with the given placeholder formater.

func (*SQL) Get 

func (d *SQL) Get(dest any) (found bool, err error)

Get scans a single row. Returns (false, nil) when no row is found.

func (*SQL) Insert 

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

Insert generates and appends an INSERT INTO statement.

func (*SQL) List 

func (d *SQL) List(dest interface{}) error

List scans multiple rows into a slice pointer.

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, v any) error {
	ctx.AddParam(strings.ToUpper(fmt.Sprint(v)))
	return nil
})
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) Rows 

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

Rows returns a raw *sqlx.Rows cursor for streaming.

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) 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) Use 

func (d *SQL) Use(mw ...Hook) *SQL

Use returns a new builder with the given hooks appended.

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.

type SQLExpr 

type SQLExpr struct {
	Sql  string
	Args []any
}

SQLExpr wraps a raw SQL expression for Insert/Update values.

func Expr 

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

Expr creates a SQLExpr with optional bind arguments.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.