dba

package module
v0.5.3 Latest Latest
Warning

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

Go to latest
Published: Aug 2, 2026 License: MIT Imports: 14 Imported by: 9

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})", ids) // auto-expands slices
Pain point Raw sqlx dba
IN (?) expansion sqlx.In() in a separate call #{1} auto-detects slices
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 "WHERE id = #{1}" → "WHERE id = $1"
#{name} Named parameter from map/struct "WHERE id = #{id}" + map["id"]
${key:default} Fillable slot with fallback "${order:ORDER BY id}" → overridable
@{1} / @{name} Dialect-aware quoting "SELECT @{1}" → "SELECT \"name\""
!{1} / !{name} Raw text injection (use sparingly) "ORDER BY !{1}" → "ORDER BY id DESC"
##{text} Escape — output #{text} literally "##{1}" → literal "#{1}"

Slices passed to #{} auto-expand: []int{1,2,3} → $1, $2, $3. No more sqlx.In.

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

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 strip comments and normalize whitespace.

type Node

type Node struct {
	RawSQL string
	Args   []any
}

type Quoter

type Quoter func(string) string

Quoter quotes an identifier for the target dialect.

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

type TableDef

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

TableDef builds a set of Node variables for a table, enabling Add("select ${u.*} from ${u}"). Usage:

Vars(Table("user", "u").Fields("id", "name").Build())

func Table

func Table(table, alias string) *TableDef

Table creates a table alias definition builder.

func (*TableDef) Alias

func (t *TableDef) Alias(alias string) *TableDef

func (*TableDef) Build

func (t *TableDef) Build() map[string]Node

Build returns a map[string]Node suitable for SQL.Vars(). 每条规则注册一个 ${key}, key 可以是表名、别名、带限定符的字段:

Table("users", "u").Fields("id", "name").Build()

  "u"        → Node{RawSQL: "@{1} AS @{2}",  Args: []any{"users", "u"}}    → `users` AS `u`
  "u.id"     → Node{RawSQL: "@{1}.@{2}",      Args: []any{"u", "id"}}       → `u`.`id`
  "u.name"   → Node{RawSQL: "@{1}.@{2}",      Args: []any{"u", "name"}}     → `u`.`name`
  "u.*"      → Node{RawSQL: "@{1}.*",          Args: []any{"u"}}             → `u`.*
  "users"    → Node{RawSQL: "@{1}",            Args: []any{"users"}}         → `users`
  "users.id" → Node{RawSQL: "@{1}.@{2}",      Args: []any{"users", "id"}}   → `users`.`id`
  "users.*"  → Node{RawSQL: "@{1}.*",          Args: []any{"users"}}         → `users`.*

用法:

Vars(Table("users", "u").Build())
Add("SELECT ${u.*} FROM ${u} LEFT JOIN @{profiles} p ON ${u.id} = p.user_id")

func (*TableDef) Fields

func (t *TableDef) Fields(fields ...string) *TableDef

Fields registers the columns included in the alias.* expansion.

func (*TableDef) Struct

func (t *TableDef) Struct(model any) *TableDef

Struct extracts column names from the model's executor tags, replacing any previously set fields. Embed structs (like Timestamped) are expanded.

Jump to

Keyboard shortcuts

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