database

package
v1.0.0 Latest Latest
Warning

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

 Go to latest Published: Jul 3, 2026 License: MIT Imports: 15 Imported by: 0

Documentation

Overview

Package database provides a high-performance query builder, struct scanner, lifecycle hooks, and explicit eager relationship loading for OniWorks.

Design principles:

  • Query builder is lazy — nothing executes until a terminal method is called
  • Reflection cache: struct field mapping is computed once per type, never per-request
  • No lazy loading: relationships are never populated automatically (no hidden queries)
  • Batch eager loading: db.Load(&users, "Posts") fires WHERE user_id IN (...) — no N+1
  • Context-everywhere: all queries accept context.Context

Index

Constants

This section is empty.

Variables

View Source 
var ErrNotFound = fmt.Errorf("database: record not found")

ErrNotFound is returned by First when no matching row is found.

Functions

func SetDefault 

func SetDefault(db *DB)

SetDefault sets the package-level default DB used by top-level Table() / Raw() calls.

Types

type Builder 

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

Builder is a lazy, fluent SQL query builder. Nothing is executed until a terminal method (First, All, Count, Insert, Update, etc.) is called.

func Raw 

func Raw(query string, args ...any) *Builder

Raw is a package-level shorthand: database.Raw("SELECT ...", args...)

func Table 

func Table(table string) *Builder

Table is a package-level shorthand: database.Table("users").Where(...)

func (*Builder) All 

func (b *Builder) All(dest any) error

All executes the query and scans all rows into dest (must be a pointer to a slice).

func (*Builder) Avg 

func (b *Builder) Avg(col string) (float64, error)

Avg executes AVG(col) over the current query and returns the result. Returns 0 when no rows match (SQL NULL). col is validated and quoted.

func (*Builder) Chunk 

func (b *Builder) Chunk(size int, fn func(batch []map[string]any) error) error

Chunk iterates the query in LIMIT/OFFSET batches of size rows, invoking fn for each batch until the result set is exhausted or fn returns an error (which stops iteration and is returned). An ORDER BY is required — without a deterministic order, OFFSET pagination can skip or repeat rows.

Rows written while chunking runs can still shift offsets between batches; prefer ChunkByID when the table has a numeric id column.

func (*Builder) ChunkByID 

func (b *Builder) ChunkByID(size int, fn func(batch []map[string]any) error) error

ChunkByID iterates the query in keyset batches — WHERE id > last ORDER BY id LIMIT size — which stays correct and fast even when rows are inserted or deleted mid-iteration. The table must have a column named "id" with a sortable, unique value; any ORDER BY set on the builder is replaced by "id ASC".

func (*Builder) Count 

func (b *Builder) Count() (int64, error)

Count executes a COUNT(*) query and returns the result.

func (*Builder) Ctx 

func (b *Builder) Ctx(ctx context.Context) *Builder

Ctx sets the context for this query.

func (*Builder) CursorPaginate 

func (b *Builder) CursorPaginate(col string, after any, perPage int, dest any) (any, error)

CursorPaginate performs single-column keyset pagination: WHERE col > after ORDER BY col ASC LIMIT perPage+1. dest must be a pointer to a slice (of structs or map[string]any) and receives at most perPage rows. When more rows exist, the col value of the last returned row is returned as nextCursor — pass it as after on the next call. nextCursor is nil on the final page. Pass after == nil for the first page.

col must be unique (or effectively unique) for stable pagination, and is validated and quoted like every other identifier. 

next, err := db.Table("posts").CursorPaginate("id", nil, 20, &posts)

func (*Builder) Decrement 

func (b *Builder) Decrement(col string, by int) error

Decrement atomically subtracts by from col for all rows matching the WHERE clause. It is Increment with a negated amount, so the generated SQL is "SET col = col + ?" with a negative bind value.

func (*Builder) Delete 

func (b *Builder) Delete() error

Delete removes rows matching the WHERE clause. 

db.Table("users").Where("id = ?", id).Delete()

func (*Builder) Exec 

func (b *Builder) Exec() error

Exec executes a raw write query (INSERT, UPDATE, DELETE) that returns no rows. It requires a Raw() builder — calling Exec on a Table() builder returns an error rather than guessing at a statement (previously it fell back to an unguarded DELETE).

func (*Builder) Exists 

func (b *Builder) Exists() (bool, error)

Exists reports whether any row matches the query. It issues SELECT EXISTS(SELECT 1 FROM ... WHERE ...) — on both Postgres and MySQL the engine can stop at the first matching row, which is much cheaper than COUNT(*) on large tables.

func (*Builder) First 

func (b *Builder) First(dest any) error

First executes the query and scans a single row into dest. Returns ErrNotFound if no row matches.

func (*Builder) FirstOrCreateMap 

func (b *Builder) FirstOrCreateMap(match Map, extra Map, dest any) error

FirstOrCreateMap finds the first row matching all match columns and scans it into dest. If no row matches, it inserts match merged with extra (extra wins on key collisions, timestamps stamped like InsertMap) and re-selects the row into dest so database defaults and generated ids are populated.

NOT atomic: a concurrent insert between the SELECT and the INSERT can create a duplicate (or make the INSERT fail on a unique index). For an atomic guarantee, add a unique index over the match columns and use UpsertMap instead.

func (*Builder) ForceDelete 

func (b *Builder) ForceDelete() error

ForceDelete permanently removes rows even on a soft-delete builder. 

db.Table("posts").SoftDelete().Where("id = ?", id).ForceDelete()

func (*Builder) GroupBy 

func (b *Builder) GroupBy(cols ...string) *Builder

GroupBy adds a GROUP BY clause. Each argument must be a "[table.]column" reference and is validated and quoted to prevent SQL injection. Use GroupByRaw for trusted raw expressions.

func (*Builder) GroupByRaw 

func (b *Builder) GroupByRaw(expr string) *Builder

GroupByRaw adds a raw, unescaped GROUP BY expression. The caller is responsible for safety — never pass user input directly.

func (*Builder) Having 

func (b *Builder) Having(clause string, args ...any) *Builder

Having adds a HAVING clause. Repeated calls accumulate and are AND-joined, like Where. The clause is raw, unescaped SQL — the caller is responsible for ensuring it is safe. Never pass user input directly; use ? placeholders for values. 

db.Table("orders").GroupBy("user_id").Having("COUNT(*) > ?", 5)

func (*Builder) Increment 

func (b *Builder) Increment(col string, by int) error

Increment atomically adds by to col for all rows matching the WHERE clause: 

UPDATE t SET col = col + ? WHERE ...

The soft-delete scope is honored like Update. Like Delete/Update, it refuses to run without a WHERE clause — use WhereRaw("1=1") to hit all rows. 

db.Table("posts").Where("id = ?", id).Increment("views", 1)

func (*Builder) Insert 

func (b *Builder) Insert(dest any) error

Insert inserts dest (a model pointer) and calls BeforeCreate / AfterCreate hooks. dest must be a struct pointer; for map[string]any use InsertMap instead.

func (*Builder) InsertMany 

func (b *Builder) InsertMany(src any) error

InsertMany inserts a slice of structs (or struct pointers) in a single multi-row VALUES statement, reusing the same field metadata as Insert. Auto-increment primary keys are skipped like Insert, and created_at / updated_at time.Time fields are stamped on each element.

Unlike Insert, lifecycle hooks are NOT invoked and auto-increment IDs are NOT backfilled onto the structs (drivers only report the first inserted id) — use Insert per model when you need either.

func (*Builder) InsertMap 

func (b *Builder) InsertMap(data map[string]any) error

InsertMap inserts a row from a plain map[string]any. Column order is sorted for determinism. Hooks are not called. "created_at" and "updated_at" are injected as UTC time.Time values when not already present in the map, so each driver formats the timestamp natively (MySQL DATETIME rejects RFC3339 strings in strict mode).

func (*Builder) InsertMaps 

func (b *Builder) InsertMaps(rows []map[string]any) error

InsertMaps inserts multiple rows in a single multi-row VALUES statement. All rows must have exactly the same keys — a mismatched row is an error rather than a guess (a union of keys would silently insert NULL/zero values for the missing columns). Column order is sorted for determinism. "created_at"/"updated_at" are stamped on every row like InsertMap when not already present. Hooks are not called. Inserting an empty slice is a no-op.

func (*Builder) Join 

func (b *Builder) Join(clause string) *Builder

Join adds an INNER JOIN. The clause is raw, unescaped SQL — the caller is responsible for ensuring it is safe. Never pass user input directly. 

db.Table("posts").Join("users ON users.id = posts.user_id")

func (*Builder) LeftJoin 

func (b *Builder) LeftJoin(clause string) *Builder

LeftJoin adds a LEFT JOIN. The clause is raw, unescaped SQL — the caller is responsible for ensuring it is safe. Never pass user input directly.

func (*Builder) Limit 

func (b *Builder) Limit(n int) *Builder

Limit sets the maximum number of results.

func (*Builder) Max 

func (b *Builder) Max(col string) (float64, error)

Max executes MAX(col) over the current query and returns the result. The column must be numeric — MAX over text or date columns cannot be represented as float64 and returns a conversion error. Returns 0 when no rows match (SQL NULL).

func (*Builder) Min 

func (b *Builder) Min(col string) (float64, error)

Min executes MIN(col) over the current query and returns the result. The column must be numeric — MIN over text or date columns cannot be represented as float64 and returns a conversion error. Returns 0 when no rows match (SQL NULL).

func (*Builder) Offset 

func (b *Builder) Offset(n int) *Builder

Offset sets the number of results to skip.

func (*Builder) OnlyTrashed 

func (b *Builder) OnlyTrashed() *Builder

OnlyTrashed scopes the query to soft-deleted rows only (deleted_at IS NOT NULL). It implies SoftDelete() and takes precedence over WithTrashed(). 

db.Table("posts").OnlyTrashed().All(&trashed)

func (*Builder) OrWhere 

func (b *Builder) OrWhere(clause string, args ...any) *Builder

OrWhere adds an OR WHERE condition.

func (*Builder) OrWhereGroup 

func (b *Builder) OrWhereGroup(fn func(*Builder)) *Builder

OrWhereGroup adds a parenthesized group of conditions joined to the previous clauses with OR. See WhereGroup.

func (*Builder) OrderBy 

func (b *Builder) OrderBy(clause string) *Builder

OrderBy adds an ORDER BY clause. The clause is a comma-separated list of "[table.]column [ASC|DESC] [NULLS FIRST|NULLS LAST]" terms; column identifiers are validated and quoted to prevent SQL injection, and the direction/NULLS keywords are checked against an allow-list. Anything outside that grammar (function calls, arithmetic, etc.) is rejected — use OrderByRaw for trusted raw expressions. 

db.Table("users").OrderBy("created_at DESC").OrderBy("name ASC")
db.Table("messages").OrderBy("last_message_at DESC NULLS LAST")

func (*Builder) OrderByRaw 

func (b *Builder) OrderByRaw(clause string) *Builder

OrderByRaw adds a raw, unescaped ORDER BY expression. The caller is responsible for safety — never pass user input directly.

func (*Builder) Paginate 

func (b *Builder) Paginate(page, perPage int, dest any) (*Page[any], error)

Paginate executes a COUNT and a SELECT with LIMIT/OFFSET and returns a Page. page is 1-based.

func (*Builder) Pluck 

func (b *Builder) Pluck(col string, dest any) error

Pluck retrieves a single column as a []T slice. 

var emails []string
db.Table("users").Pluck("email", &emails)

func (*Builder) Restore 

func (b *Builder) Restore() error

Restore un-deletes soft-deleted rows matching the WHERE clause by setting deleted_at back to NULL. The statement is automatically scoped to trashed rows (deleted_at IS NOT NULL), so live rows are never touched. Like Delete/Update it refuses to run without a WHERE clause — use WhereRaw("1=1") to restore all trashed rows. 

db.Table("posts").Where("id = ?", id).Restore()

func (*Builder) Save 

func (b *Builder) Save(dest any) error

Save updates all fields of dest by primary key. Save always takes the update path, so it fires BeforeSave → BeforeUpdate before the UPDATE and AfterUpdate → AfterSave after it.

func (*Builder) Scan 

func (b *Builder) Scan(dest any) error

Scan executes a raw query and scans a single scalar value.

func (*Builder) Select 

func (b *Builder) Select(cols ...string) *Builder

Select specifies which columns to retrieve. Each argument must be a column reference — "col", "table.col", "col AS alias", "*", or "table.*" — and is validated and quoted to prevent SQL injection. For aggregate or computed expressions (e.g. "COUNT(*) AS n"), use SelectRaw instead. 

db.Table("users").Select("id", "email").All(&users)

func (*Builder) SelectRaw 

func (b *Builder) SelectRaw(expr string) *Builder

SelectRaw adds a raw, unescaped SELECT expression. The caller is responsible for ensuring the expression is safe — never pass user input directly. 

db.Table("posts").SelectRaw("COUNT(*) AS post_count")

func (*Builder) SoftDelete 

func (b *Builder) SoftDelete() *Builder

SoftDelete tells the builder this table uses soft deletes (deleted_at column). Automatically adds "deleted_at IS NULL" to WHERE unless WithTrashed() is called.

func (*Builder) Sum 

func (b *Builder) Sum(col string) (float64, error)

Sum executes SUM(col) over the current query and returns the result. Returns 0 when no rows match (SQL NULL). col is validated and quoted.

func (*Builder) ToSQL 

func (b *Builder) ToSQL() (string, []any, error)

ToSQL returns the SELECT statement and bind arguments the builder would execute, with dialect-native placeholders, WITHOUT executing it and WITHOUT releasing the builder — it can be called mid-chain for debugging/logging and then followed by a terminal method. 

q, args, _ := db.Table("users").Where("active = ?", true).ToSQL()

func (*Builder) Update 

func (b *Builder) Update(data Map) error

Update updates specific columns for rows matching the WHERE clause. 

db.Table("users").Where("id = ?", 1).Update(database.Map{"name": "Alice"})

On a SoftDelete() builder, soft-deleted rows are excluded from the UPDATE unless WithTrashed() is set.

func (*Builder) UpdateOrCreateMap 

func (b *Builder) UpdateOrCreateMap(match Map, values Map) error

UpdateOrCreateMap updates values on the first row matching all match columns, or inserts match merged with values when no row matches. The UPDATE stamps updated_at (when not supplied) and the INSERT stamps both timestamps, mirroring InsertMap.

NOT atomic: a concurrent writer between the existence check and the write can race. For an atomic guarantee, add a unique index over the match columns and use UpsertMap instead.

func (*Builder) UpsertMap 

func (b *Builder) UpsertMap(data map[string]any, conflictCols []string, updateCols []string) error

UpsertMap inserts data, updating updateCols instead when a row already exists. Dialect handling:

  • Postgres: INSERT ... ON CONFLICT (conflictCols) DO UPDATE SET col = EXCLUDED.col. conflictCols must name a unique index/constraint.
  • MySQL: INSERT ... ON DUPLICATE KEY UPDATE col = VALUES(col). conflictCols are IGNORED — MySQL always resolves against whatever unique key the row collides with.

Every identifier (data keys, conflictCols, updateCols) is validated and quoted; updateCols must be a subset of data's keys since the update references the inserted values. created_at/updated_at are stamped like InsertMap when absent. 

db.Table("settings").UpsertMap(
	database.Map{"key": "theme", "value": "dark"},
	[]string{"key"}, []string{"value", "updated_at"})

func (*Builder) Where 

func (b *Builder) Where(clause string, args ...any) *Builder

Where adds a WHERE condition (AND-joined). 

db.Table("users").Where("active = ?", true).Where("role = ?", "admin")

func (*Builder) WhereGroup 

func (b *Builder) WhereGroup(fn func(*Builder)) *Builder

WhereGroup adds a parenthesized group of conditions joined to the previous clauses with AND. fn receives a fresh builder whose Where/OrWhere/WhereIn/ etc. calls are collected and rendered inside the parentheses. 

db.Table("users").
	Where("active = ?", true).
	WhereGroup(func(g *database.Builder) {
		g.Where("role = ?", "admin").OrWhere("role = ?", "owner")
	})
// WHERE (active = ?) AND ((role = ?) OR (role = ?))

func (*Builder) WhereIn 

func (b *Builder) WhereIn(col string, values ...any) *Builder

WhereIn adds a WHERE column IN (...) clause.

func (*Builder) WhereInSub 

func (b *Builder) WhereInSub(col string, sub *Builder) *Builder

WhereInSub adds "col IN (SELECT ...)" using sub as the sub-query.

WhereInSub CONSUMES sub: the sub-builder is rendered immediately and released back to the pool, so it must not be used (or released) again after this call. Placeholders from the sub-query are merged into the outer query and normalized together at execution time. 

sub := db.Table("orders").Select("user_id").Where("total > ?", 100)
db.Table("users").WhereInSub("id", sub).All(&users)

func (*Builder) WhereNotIn 

func (b *Builder) WhereNotIn(col string, values ...any) *Builder

WhereNotIn adds a WHERE column NOT IN (...) clause.

func (*Builder) WhereNotNull 

func (b *Builder) WhereNotNull(col string) *Builder

WhereNotNull adds WHERE column IS NOT NULL.

func (*Builder) WhereNull 

func (b *Builder) WhereNull(col string) *Builder

WhereNull adds WHERE column IS NULL.

func (*Builder) WhereRaw 

func (b *Builder) WhereRaw(clause string, args ...any) *Builder

WhereRaw adds a raw WHERE clause without any quoting or escaping. Use this for complex conditions or when you need full control over the SQL. 

db.Table("users").WhereRaw("1=1").Delete() // delete all rows

func (*Builder) With 

func (b *Builder) With(relations ...string) *Builder

With eager-loads the named relationships after the main query executes.

Note: relations are loaded WITHOUT soft-delete scoping — soft-deleted related rows are included, because the related model's soft-delete configuration is not known at load time. 

db.Table("users").With("Posts", "Role").All(&users)

func (*Builder) WithTrashed 

func (b *Builder) WithTrashed() *Builder

WithTrashed includes soft-deleted rows (does not add "deleted_at IS NULL").

type Config 

type Config struct {
	Driver   Driver
	Host     string
	Port     int
	Name     string
	User     string
	Password string
	SSLMode  string

	// Pool settings
	MaxOpen     int
	MaxIdle     int
	MaxLifetime time.Duration

	// PostgreSQL-specific: use pgxpool directly for best performance
	PgxConfig *pgxpool.Config
}

Config holds all database connection parameters.

func DefaultConfig 

func DefaultConfig() Config

DefaultConfig returns a localhost PostgreSQL config suitable for development.

type DB 

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

DB is the central database handle. It wraps *sql.DB and provides the query builder API. Create one DB per database connection and share it.

func Default 

func Default() *DB

Default returns the package-level default DB, or panics if not set.

func MustOpen 

func MustOpen(cfg Config) *DB

MustOpen is like Open but panics on error.

func Open 

func Open(cfg Config) (*DB, error)

Open connects to the database using cfg and returns a *DB.

func (*DB) Close 

func (db *DB) Close() error

Close closes the underlying connection pool.

func (*DB) Driver 

func (db *DB) Driver() Driver

Driver returns the database driver type.

func (*DB) Load 

func (db *DB) Load(dest any, relations ...string) error

Load populates the named relationship(s) on a slice of model pointers. It fires exactly one batch query per relationship — never N individual queries. 

db.Load(&users, "Posts")         // SELECT * FROM posts WHERE user_id IN (1,2,3)
db.Load(&users, "Posts", "Role") // two batch queries

func (*DB) LoadContext 

func (db *DB) LoadContext(ctx context.Context, dest any, relations ...string) error

LoadContext is like Load but with context.

func (*DB) Ping 

func (db *DB) Ping(ctx context.Context) error

Ping verifies the connection is alive.

func (*DB) QuoteIdentifier 

func (db *DB) QuoteIdentifier(s string) string

QuoteIdentifier quotes a SQL identifier (table or column) for the active dialect, escaping any embedded quote characters. Use it when building a raw clause fragment that must include a caller-supplied identifier safely.

func (*DB) Raw 

func (db *DB) Raw(sql string, args ...any) *Builder

Raw returns a Builder for a raw SQL query. Terminal methods Scan, All, and Count all work on Raw builders. 

db.Raw("SELECT COUNT(*) FROM users WHERE active = ?", true).Scan(&count)
db.Raw("SELECT * FROM users WHERE role = ?", "admin").All(&users)

func (*DB) SQLDB 

func (db *DB) SQLDB() *sql.DB

SQLDB exposes the underlying *sql.DB for advanced use (migrations, raw exec, etc.).

func (*DB) SetLogLevel 

func (db *DB) SetLogLevel(level slog.Level)

SetLogLevel sets the minimum log level for query logging (default: slog.LevelDebug).

func (*DB) Table 

func (db *DB) Table(table string) *Builder

Table returns a new Builder for the given table name. 

db.Table("users").Where("active = ?", true).All(&users)

func (*DB) Transaction 

func (db *DB) Transaction(fn func(tx *DB) error) error

Transaction executes fn inside a database transaction. If fn returns nil, the transaction is committed; otherwise it is rolled back. Nested calls create savepoints so inner failures can be rolled back independently.

func (*DB) TransactionContext 

func (db *DB) TransactionContext(ctx context.Context, fn func(tx *DB) error) error

TransactionContext is like Transaction but accepts a context.

func (*DB) WithLogger 

func (db *DB) WithLogger(l *slog.Logger) *DB

WithLogger sets a custom logger.

type Driver 

type Driver string

Driver identifies the database backend. 

const (
	DriverPostgres Driver = "postgres"
	DriverMySQL    Driver = "mysql"
)

type Grammar 

type Grammar interface {
	// Placeholder returns the n-th positional bind placeholder ($1, $2 for
	// Postgres; ? for MySQL).
	Placeholder(n int) string
	// QuoteIdent quotes an identifier, doubling embedded quote characters.
	QuoteIdent(s string) string
}

Grammar generates database-dialect-specific SQL fragments.

DDL generation (CREATE TABLE, ALTER TABLE, indexes) lives in the framework/migrations package; this interface only covers the fragments the query builder needs at runtime.

type Map 

type Map = map[string]any

Map is a convenience alias used in Insert/Update calls.

type Page 

type Page[T any] struct {
	Items       []T   `json:"items"`
	Total       int64 `json:"total"`
	PerPage     int   `json:"per_page"`
	CurrentPage int   `json:"current_page"`
	LastPage    int   `json:"last_page"`
	From        int64 `json:"from"`
	To          int64 `json:"to"`
}

Page holds paginated query results.

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.