sqlkit

package module
v0.0.0-...-6f40dae Latest Latest
Warning

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

 Go to latest Published: Jun 22, 2026 License: MIT Imports: 17 Imported by: 0

README

sqlkit

Go Reference

A SQL toolkit for Go — a Swiss-army knife for the whole life of a SQL statement. Build it as typed Go, hold it as an AST, compile it to text, parse text back into that same AST, then analyze, redact, route, migrate, trace, and test around it. One representation runs through the middle, so the blades compose.

Two features sit at the center, and everything else builds on them:

  • SQL DSL — a typed query builder. Typed columns carry the predicate vocabulary, the ON clause and the projections are typed columns too, and rows scan into your own struct, so the compiler checks the SQL as Go. One notation covers both queries (DML) and schema changes (DDL).
  • SQL AST — the node tree the DSL builds and the compiler renders to text. It is untyped, and it is the representation the rest of the kit speaks: the parser produces it from raw SQL, and the analysis and redaction passes read and rewrite it. Modeled on go/ast, with a Walk/Visitor traversal.

Everything else is opt-in, and much of it works without the typed builder at all. querytrace is standalone database/sql tracing — it observes hand-written SQL, an ORM, or any other builder just as well as sqlkit's, and the sqltest query spy is built on it — while sqlparse, analysis, and redaction operate on any SQL once it is in the AST, whoever wrote it. Schema-as-code and codegen, migrations, and routing are the parts that lean on the builder and the declared schema.

Architecture

The DSL and the parser are two front ends that produce the same AST; the compiler and the AST passes are the back ends that consume it. One representation in the middle is what lets the blades compose.

+----------------------------------------------------------------------+
| Your application -- typed queries, schema as Go                      |
+----------------------------------------------------------------------+
   |
   v
+----------------------------------------------------------------------+
| Front ends  ->  build the AST                                        |
|   - Typed DSL   query builder + generated bindings                   |
|                 (sqlkit, decl, codegen)                              |
|   - Parser      raw SQL text -> AST                                  |
|                 (sqlparse: Postgres / MySQL)                         |
+----------------------------------------------------------------------+
   |
   v
+----------------------------------------------------------------------+
| SQL AST + compiler   (sql)                                           |
|   builders -> AST -> SQL text + args   +   Walk / Visitor            |
+----------------------------------------------------------------------+
   |
   v
+----------------------------------------------------------------------+
| AST passes  ->  read / rewrite the AST                               |
|   analysis (structural facts)   +   redaction (mask PII)             |
+----------------------------------------------------------------------+
   |
   v
+----------------------------------------------------------------------+
| Runtime  ->  execute                                                 |
|   db, session, tx, hooks, routing, retry, struct scan                |
+----------------------------------------------------------------------+

Alongside (no builder needed): migrations (migrate), tracing (querytrace),
testing (sqltest). querytrace wraps any database/sql driver, so it -- and
the sqltest spy built on it -- trace SQL whether sqlkit emitted it or not.

Overview

The narrative guides live in docs/; the per-package API reference is on pkg.go.dev.

  • Getting started — schema → bindings → typed queries, end to end.
  • Query builder — the typed DSL in full: joins, subqueries, CTEs, window functions, upserts, RETURNING, preload, transactions, and the standalone ToSQL builder.
  • Schema as code & code generation — declare the schema with decl, generate bindings and DDL, customize the type mapping, bootstrap from a live database.
  • Migrations — DAG-ordered up/down migrations, a CLI, and Alembic-style autogeneration.
  • Parser — sqlparse turns raw SQL text back into the AST (the inverse of build-then-compile), with PostgreSQL and MySQL frontends, so the analysis and redaction passes work on hand-written SQL too.
  • Observability — hooks, querytrace tracing, and the analysis AST framework.
  • Redaction — mask PII literals before logging a statement.
  • Query routing — read/write splitting, sharding, pinned sessions.
  • Testing — the sqltest seeding helpers, query spy, and mock database.
  • Limitations — dialect support and engine-specific gaps.

A taste of the DSL — a join with a correlated subquery, scanning into a local struct:

// Typed columns carry the predicate vocabulary; the ON clause and the
// projections are typed columns too, and rows scan into your own struct.
var rows []struct {
    Title  string
    Author string
}
err := db.Select(appdb.Posts.Title, sql.As(appdb.Users.Name, "author")).
    From(appdb.Posts).
    Join(appdb.Users, appdb.Posts.UserID.EqExpr(appdb.Users.ID)).
    Where(
        appdb.Posts.Published.Eq(true),
        sqlkit.Like(appdb.Users.Email, "%@example.com"),
        sqlkit.Exists(
            sqlkit.Select(appdb.Comments.ID).From(appdb.Comments).
                Where(appdb.Comments.PostID.EqExpr(appdb.Posts.ID)),
        ),
    ).
    OrderBy(appdb.Posts.CreatedAt.Desc()).
    Limit(20).
    All(ctx).Scan(&rows)

Multiple databases

The same query builder, schema, and migrations target both PostgreSQL and MySQL; PostgreSQL is the default and the most exercised dialect. sql.Dialect is the single point where engine differences are resolved — identifier quoting, placeholders, capability flags, and DDL rendering. Engine-specific gaps surface at compile time (e.g. MySQL has no RETURNING); see Limitations for the full matrix.

License

Released under the MIT License.

Documentation

Overview

Package sqlkit provides a typed SQL toolkit for Go: a query builder whose column and value types are checked at compile time, with results scanned into caller-defined structs.

Table bindings are normally generated from a live PostgreSQL schema with the sqlkit command; the generated code implements the Table interface and exposes one Column[T] per database column. Queries are built from those bindings and materialized into a destination of your choosing: 

var users []MyUserDTO
err := db.Select().From(appdb.Users).Where(appdb.Users.ID.Gt(10)).
	All(ctx, db).Scan(&users)

Index

Examples

Constants

This section is empty.

Variables

View Source 
var ErrMissingPrimaryKey = errors.New("sqlkit: row struct has no field for a primary-key column")

ErrMissingPrimaryKey is returned when a row struct has no field mapping to a primary-key column, so its key cannot be read.

View Source 
var ErrNoPrimaryKey = errors.New("sqlkit: table has no primary key")

ErrNoPrimaryKey is returned when a primary-key operation is attempted on a table whose binding declares no primary key.

View Source 
var ErrNoRows = stdsql.ErrNoRows

ErrNoRows is returned when a single-row Scan finds no row.

View Source 
var ErrRouteConflict = errors.New(
	"sqlkit: WithRoute conflicts with the session's pinned backend; a transaction cannot span backends")

ErrRouteConflict is returned when a statement run inside a session carries a WithRoute hint naming a backend other than the one the session is pinned to. A transaction lives on a single backend, so it cannot be relocated mid-flight; the conflicting hint is a programming error rather than something to silently ignore. Test for it with errors.Is.

View Source 
var ErrTooManyRows = errors.New("sqlkit: query returned more than one row")

ErrTooManyRows is returned when a One query yields more than one row.

View Source 
var ErrVersionConflict = errors.New("sqlkit: optimistic lock conflict: row was modified concurrently")

ErrVersionConflict is returned by an optimistic-locking UPDATE (UpdateQuery.CheckVersion) whose Exec matches no row, meaning another writer advanced the version column first or the row no longer exists. Test for it with errors.Is to reload and retry.

Functions

func ColumnValues 

func ColumnValues(table Table, row any) (names []string, values []any, err error)

ColumnValues maps a table's columns to the values found in row, in Columns declaration order, skipping any column the struct provides no field for and any field tagged read-only (`sqlkit:",readonly"`), which is never written. It uses the same column-to-field mapping as Scan and Insert. row may be a struct or a pointer to one. It backs the uow package's UPDATE assignment building.

func ExponentialBackoff 

func ExponentialBackoff(base, max time.Duration) func(attempt int) time.Duration

ExponentialBackoff returns a backoff that doubles from base each attempt and is capped at max.

func InBatches 

func InBatches[V any](ctx context.Context, q Querier, values []V, build func(chunk []V) Rows, dest any, chunkSize ...int) error

InBatches splits values into chunks small enough that build's per-chunk query stays within q's configured bind-parameter limit, runs each chunk, and concatenates the scanned rows into dest (a pointer to a slice). It is the manual counterpart to the automatic IN-list chunking the typed builders apply to a column's In: reach for it when the query the builder cannot split on its own — a hand-built IN, or one combined with ORDER BY/LIMIT — must still run a large value list. build receives each chunk and should return the Rows for it (typically q.Select()...Where(col.In(chunk...)).All(ctx)).

q is the *Database or *Session the per-chunk queries run against. chunkSize defaults to its bind-parameter limit; pass a positive override to cap it lower (e.g. to leave room for the query's other parameters). With no limit available and no override, every value goes in a single chunk.

func IsRetryable 

func IsRetryable(err error) bool

IsRetryable is WithRetry's default predicate: it reports whether err is a serialization failure or deadlock (IsSerializationFailure) or was explicitly marked with Retryable anywhere in its wrap chain.

func IsSerializationFailure 

func IsSerializationFailure(err error) bool

IsSerializationFailure reports whether err is a transaction serialization failure or a detected deadlock — the transient conflicts that are resolved by replaying the transaction. It recognizes the SQLSTATE codes 40001 (serialization_failure) and 40P01 (deadlock_detected) on any error exposing SQLState() string (pgx). MySQL's driver does not expose a SQLSTATE method, so detect its deadlock (1213) / lock-wait-timeout (1205) errors with a custom RetryIf predicate.

func PrimaryKeyValues 

func PrimaryKeyValues(table Table, row any) ([]any, error)

PrimaryKeyValues extracts a row's primary-key column values, in the table's PrimaryKey declaration order, using the same column-to-field mapping as Scan and Insert (sqlkit tag, exact name, snake_case, embedded structs flattened). row may be a struct or a pointer to one. It is the bridge the uow package uses to identify a row from its binding. It returns ErrNoPrimaryKey when the table has no key and ErrMissingPrimaryKey when the struct omits a key column.

func RegisterDriverDialect 

func RegisterDriverDialect(driverName string, d Dialect)

RegisterDriverDialect maps a database/sql driver name to a dialect so Open and DialectForDriver resolve it. Use it for a driver sqlkit does not seed by default, or to point a custom/aliased driver name at an existing dialect (e.g. RegisterDriverDialect("mysql-custom", MySQLDialect{})). It overwrites any existing mapping for the name.

func Retryable 

func Retryable(err error) error

Retryable wraps err so the default retry predicate (IsRetryable) treats it as retryable, even when it is not a recognized serialization failure. Return it from a WithSession callback — directly or wrapped further with %w — to signal that a failure is safe to replay: 

if conflicted {
	return sqlkit.Retryable(err)
}

Retryable(nil) returns nil. The mark survives additional wrapping, since IsRetryable finds it anywhere in the error chain.

func ScanRows 

func ScanRows(rows *stdsql.Rows, dest any) error

ScanRows copies an externally produced *sql.Rows result into dest, using the same column-to-field mapping rules as Rows.Scan (`sqlkit` tag, exact name, snake_case, embedded structs flattened, sql.Scanner fields). It makes sqlkit's scanner available when the query is executed by another library — plain database/sql, sqlx, a pgx stdlib pool — typically over SQL produced by the unbound builders with ToSQL: 

sqlText, args, _ := sqlkit.Select().From(Users).Where(...).ToSQL(dialect)
rows, _ := otherDB.QueryContext(ctx, sqlText, args...)
defer rows.Close()
err := sqlkit.ScanRows(rows, &users)

dest may be a pointer to a slice of structs, struct pointers, or scalars (every remaining row), or a pointer to a struct or scalar (the next row, or ErrNoRows when none is left). The caller keeps ownership of rows and remains responsible for closing them.

func SetColumns 

func SetColumns(rowPtr any, names []string, values []any) error

SetColumns assigns values to the fields of *rowPtr that map to the named columns, using the same column-to-field mapping as Scan and Insert. rowPtr must be a non-nil pointer to a struct so the fields are settable; columns the struct has no field for are skipped. It is the write-back counterpart of ColumnValues — the uow package uses it to store a database-generated primary key recovered from Result.LastInsertId back into the caller's row.

func Star 

func Star(table Table) sql.Selection

Star is the "table.*" select-list item — every column of table, qualified by its name (or alias when the binding is aliased), like Column.Reference. Use it to project one table whole alongside individual columns of others, which a plain Select cannot express on its own: 

db.Select(sqlkit.Star(Users), Profiles.Bio).
	From(Users).JoinRel(Users.Rels.Profile).
	All(ctx).Scan(&dst)

compiles to SELECT "users".*, "profiles"."bio" FROM ... . For an unqualified "*" use sql.Star; selecting from a single table with no columns already emits every column without it (db.Select().From(Users)).

Types

type Access 

type Access int

Access is a statement's read/write nature for routing: a definite read, a definite write, or undetermined. It is the three-way form of IsWrite, exposing the case IsWrite folds away — a statement whose nature could not be inferred (an opaque raw string), which IsWrite reports as a write to stay on the safe side. A Router that wants to handle the undetermined case differently (its own default, or a warning) branches on Access instead. 

const (
	// AccessRead is a definite read: a non-locking SELECT, set operation, or a
	// raw string beginning with SELECT (without a locking clause).
	AccessRead Access = iota
	// AccessWrite is a definite write: INSERT/UPDATE/DELETE/MERGE/DDL, or a
	// locking read (SELECT ... FOR UPDATE/SHARE), which must run on the primary.
	AccessWrite
	// AccessUnknown is an undetermined statement: a raw string whose read/write
	// nature could not be inferred from its text. IsWrite treats it as a write.
	AccessUnknown
)

func (Access) String 

func (a Access) String() string

String renders the Access for logs and errors.

type AnyColumn 

type AnyColumn interface {
	sql.Expression
	sql.Selection
	// ColumnName returns the bare database column name.
	ColumnName() string
	// Reference returns the qualified SQL column reference.
	Reference() string
}

AnyColumn is the type-erased view of a Column[T], used for table metadata.

type Backend 

type Backend interface {
	TxExecutor
}

Backend is one routable execution target — typically a connection pool wrapped with DB. It is a TxExecutor so the router can both run auto-commit statements and begin transactions on it for pinned sessions.

func DB 

func DB(db *stdsql.DB) Backend

DB adapts a database/sql handle to a Backend, the routing counterpart to WrapDB. Register the handles behind a Router with NewRouter: 

router := sqlkit.NewRouter(map[string]sqlkit.Backend{
	"default": sqlkit.DB(primary),
	"replica": sqlkit.DB(replica),
}, myRouter)
db := sqlkit.WrapExecutor(router)

type Balancer 

type Balancer interface {
	// Pick returns one of replicas, which is non-empty.
	Pick(replicas []string) string
}

Balancer chooses one replica alias from the configured candidates for a read. RoundRobin and Random are provided; implement it for a custom policy (weights, least-connections, ...).

func Random 

func Random() Balancer

Random returns a Balancer that picks a replica uniformly at random.

func RoundRobin 

func RoundRobin() Balancer

RoundRobin returns a Balancer that cycles through the replicas in order. Each call to ReadWriteRouter needs its own, so RoundRobin returns a fresh one.

type Cardinality 

type Cardinality int

Cardinality classifies a relation's target multiplicity: whether a source row relates to at most one target row (ToOne) or to many (ToMany). Preload uses it to decide whether a relation field is a single value or a slice. 

const (
	// ToOne is a belongs-to or has-one relation (the target yields at most one
	// row per source row).
	ToOne Cardinality = iota
	// ToMany is a has-many relation.
	ToMany
)

func (Cardinality) String 

func (c Cardinality) String() string

String renders the cardinality for diagnostics.

type ChunkOption 

type ChunkOption func(*chunkConfig)

ChunkOption configures a query's automatic statement splitting; pass it to a builder's AutoChunk.

func ChunkOff 

func ChunkOff() ChunkOption

ChunkOff forces the statement to run as a single statement, disabling automatic splitting for this query even when it is enabled on the executor.

func ChunkSize 

func ChunkSize(n int) ChunkOption

ChunkSize caps each split statement at n items — rows for a bulk INSERT, IN-list values for a SELECT/UPDATE/DELETE. It only lowers the chunk below the dialect's bind-parameter limit; it never raises it past the limit.

type Column 

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

Column is a typed reference to a database column. T is the Go type rows scan into, so predicates and assignments built from the column are type-checked at compile time.

A Column carries only what query building needs — the table identity, the column name, and the static type T. Schema metadata such as SQL type, length, precision, and constraints lives in the schema model the generator consumes, not on the runtime binding.

func NewColumn 

func NewColumn[T any](table TableInfo, name string) Column[T]

NewColumn creates a typed column bound to a table. It is intended for use by generated code, but is also a usable constructor for hand-written bindings.

func (Column[T]) Add 

func (c Column[T]) Add(value T) TypedSelection[T]

Add creates a "column + value" expression, typed as T (e.g. for Set(n.SetExpr(n.Add(1)))). Subtraction, multiplication, and division follow.

func (Column[T]) Asc 

func (c Column[T]) Asc() sql.OrderTerm

Asc creates an ascending ORDER BY term.

func (Column[T]) Between 

func (c Column[T]) Between(low, high T) Predicate

Between creates a "column BETWEEN low AND high" predicate (inclusive).

func (Column[T]) ColumnName 

func (c Column[T]) ColumnName() string

ColumnName returns the bare database column name.

func (Column[T]) Desc 

func (c Column[T]) Desc() sql.OrderTerm

Desc creates a descending ORDER BY term.

func (Column[T]) Div 

func (c Column[T]) Div(value T) TypedSelection[T]

Div creates a "column / value" expression.

func (Column[T]) Eq 

func (c Column[T]) Eq(value T) Predicate

Eq creates a "column = value" predicate.

func (Column[T]) EqAny 

func (c Column[T]) EqAny(values ...T) Predicate

EqAny creates a "column = ANY($1)" predicate (PostgreSQL), the array-valued form of In: it binds the whole list as a single array parameter instead of one placeholder per value, so it never approaches the bind-parameter limit and needs no auto-chunking. Unlike In it is PostgreSQL-only. The values are bound as one []T array.

func (Column[T]) EqExpr 

func (c Column[T]) EqExpr(other TypedExpr[T]) Predicate

EqExpr creates a "column = expression" predicate, e.g. for join conditions.

func (Column[T]) Excluded 

func (c Column[T]) Excluded() TypedExpr[T]

Excluded refers to this column's proposed value inside an OnConflict DoUpdate assignment (PostgreSQL excluded.col, MySQL VALUES(col)), e.g. Users.Name.SetExpr(Users.Name.Excluded()) to overwrite with the new row.

func (Column[T]) Ge 

func (c Column[T]) Ge(value T) Predicate

Ge creates a "column >= value" predicate.

func (Column[T]) GeAll 

func (c Column[T]) GeAll(subquery Query) Predicate

GeAll creates a "column >= ALL (subquery)" predicate.

func (Column[T]) GeAny 

func (c Column[T]) GeAny(subquery Query) Predicate

GeAny creates a "column >= ANY (subquery)" predicate.

func (Column[T]) GeExpr 

func (c Column[T]) GeExpr(other TypedExpr[T]) Predicate

GeExpr creates a "column >= expression" predicate.

func (Column[T]) Gt 

func (c Column[T]) Gt(value T) Predicate

Gt creates a "column > value" predicate.

func (Column[T]) GtAll 

func (c Column[T]) GtAll(subquery Query) Predicate

GtAll creates a "column > ALL (subquery)" predicate.

func (Column[T]) GtAny 

func (c Column[T]) GtAny(subquery Query) Predicate

GtAny creates a "column > ANY (subquery)" predicate.

func (Column[T]) GtExpr 

func (c Column[T]) GtExpr(other TypedExpr[T]) Predicate

GtExpr creates a "column > expression" predicate.

func (Column[T]) In 

func (c Column[T]) In(values ...T) Predicate

In creates a "column IN (values...)" predicate. When it is the only predicate of a Where call and the executor has auto-chunk enabled (off by default, see WithAutoChunk), a value list large enough to exceed the limit is split across several statements automatically; see SelectQuery.All. The chunks are disjoint, so the combined rows are exactly those of the full list.

func (Column[T]) InQuery 

func (c Column[T]) InQuery(subquery Query) Predicate

InQuery creates a "column IN (subquery)" predicate. The subquery must project a single column; its type is not statically checked against T.

func (Column[T]) IsDistinctFrom 

func (c Column[T]) IsDistinctFrom(value T) Predicate

IsDistinctFrom creates a "column IS DISTINCT FROM value" predicate, the null-safe counterpart of Ne: it is true when the values differ and treats two NULLs as not distinct, so unlike <> it never yields NULL. It renders as MySQL's null-safe <=> operator there.

func (Column[T]) IsDistinctFromExpr 

func (c Column[T]) IsDistinctFromExpr(other TypedExpr[T]) Predicate

IsDistinctFromExpr creates a "column IS DISTINCT FROM expression" predicate.

func (Column[T]) IsNotDistinctFrom 

func (c Column[T]) IsNotDistinctFrom(value T) Predicate

IsNotDistinctFrom creates a "column IS NOT DISTINCT FROM value" predicate, the null-safe counterpart of Eq: it is true when the values match and treats two NULLs as equal. It renders as MySQL's null-safe <=> operator there.

func (Column[T]) IsNotDistinctFromExpr 

func (c Column[T]) IsNotDistinctFromExpr(other TypedExpr[T]) Predicate

IsNotDistinctFromExpr creates a "column IS NOT DISTINCT FROM expression" predicate.

func (Column[T]) IsNotNull 

func (c Column[T]) IsNotNull() Predicate

IsNotNull creates a "column IS NOT NULL" predicate.

func (Column[T]) IsNull 

func (c Column[T]) IsNull() Predicate

IsNull creates a "column IS NULL" predicate.

func (Column[T]) Le 

func (c Column[T]) Le(value T) Predicate

Le creates a "column <= value" predicate.

func (Column[T]) LeAll 

func (c Column[T]) LeAll(subquery Query) Predicate

LeAll creates a "column <= ALL (subquery)" predicate.

func (Column[T]) LeAny 

func (c Column[T]) LeAny(subquery Query) Predicate

LeAny creates a "column <= ANY (subquery)" predicate.

func (Column[T]) LeExpr 

func (c Column[T]) LeExpr(other TypedExpr[T]) Predicate

LeExpr creates a "column <= expression" predicate.

func (Column[T]) Lt 

func (c Column[T]) Lt(value T) Predicate

Lt creates a "column < value" predicate.

func (Column[T]) LtAll 

func (c Column[T]) LtAll(subquery Query) Predicate

LtAll creates a "column < ALL (subquery)" predicate.

func (Column[T]) LtAny 

func (c Column[T]) LtAny(subquery Query) Predicate

LtAny creates a "column < ANY (subquery)" predicate.

func (Column[T]) LtExpr 

func (c Column[T]) LtExpr(other TypedExpr[T]) Predicate

LtExpr creates a "column < expression" predicate, e.g. comparing against another column or a scalar subquery built with SubExpr.

func (Column[T]) Mul 

func (c Column[T]) Mul(value T) TypedSelection[T]

Mul creates a "column * value" expression.

func (Column[T]) Ne 

func (c Column[T]) Ne(value T) Predicate

Ne creates a "column <> value" predicate.

func (Column[T]) NeAll 

func (c Column[T]) NeAll(values ...T) Predicate

NeAll creates a "column <> ALL($1)" predicate (PostgreSQL), the array-valued form of NotIn: true when the column equals none of the array's elements. Like EqAny it binds the list as a single array parameter.

func (Column[T]) NeExpr 

func (c Column[T]) NeExpr(other TypedExpr[T]) Predicate

NeExpr creates a "column <> expression" predicate.

func (Column[T]) NotBetween 

func (c Column[T]) NotBetween(low, high T) Predicate

NotBetween creates a "column NOT BETWEEN low AND high" predicate.

func (Column[T]) NotIn 

func (c Column[T]) NotIn(values ...T) Predicate

NotIn creates a "column NOT IN (values...)" predicate. Unlike In it is not split automatically: NOT IN cannot be partitioned across statements (a row is excluded only when it appears in no chunk), so a very large NOT IN list must fit within the bind-parameter limit. Express the complement as an anti-join or a NOT EXISTS subquery when it would not.

func (Column[T]) NotInQuery 

func (c Column[T]) NotInQuery(subquery Query) Predicate

NotInQuery creates a "column NOT IN (subquery)" predicate.

func (Column[T]) Reference 

func (c Column[T]) Reference() string

Reference returns the qualified SQL column reference, qualified by the table's alias when it has one, else by its real qualified name.

func (Column[T]) SQLExpression 

func (Column[T]) SQLExpression()

func (Column[T]) SQLSelection 

func (Column[T]) SQLSelection()

func (Column[T]) Set 

func (c Column[T]) Set(value T) sql.Assignment

Set creates a typed SET assignment for UPDATE statements.

func (Column[T]) SetExpr 

func (c Column[T]) SetExpr(value TypedExpr[T]) sql.Assignment

SetExpr creates a SET assignment from another typed expression.

func (Column[T]) Sub 

func (c Column[T]) Sub(value T) TypedSelection[T]

Sub creates a "column - value" expression.

type Cursor 

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

Cursor is an opaque position in a paginated result. It is produced by the Paginator — Start for the first page, and Page.Next / Page.Prev for the neighbours of a fetched page — never built by hand. Its direction is baked in, so it can only move the way it was meant to.

To cross an HTTP request, render it with String and rebuild it with ParseCursor; treat the string as an opaque token, not a value to parse.

func ParseCursor 

func ParseCursor(token string) Cursor

ParseCursor rebuilds a Cursor from the token String produced earlier, e.g. one received as an HTTP query parameter. An empty token yields the first-page cursor. The token is validated when it is used (Paginate), matching how the other builders defer errors to their terminal.

func Start 

func Start() Cursor

Start is the cursor for the first page.

func (Cursor) String 

func (c Cursor) String() string

String returns the opaque token to transport (URL, JSON). It is empty for the first-page cursor and for the absent neighbour of an edge page.

type CursorCodec 

type CursorCodec interface {
	Encode(raw []byte) (string, error)
	Decode(token string) ([]byte, error)
}

CursorCodec turns a cursor's raw bytes into the opaque token string and back. Implement it to sign (HMAC), encrypt, or use a non-base64 format; the default is URL-safe base64.

type Database 

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

Database is the entry point of sqlkit: it wraps a database/sql handle, runs statements in auto-commit mode, and opens transactional sessions.

func Open 

func Open(driverName, dsn string, opts ...DatabaseOption) (*Database, error)

Open opens a database/sql handle from a driver name and DSN string. The dialect is inferred from the driver name (see DialectForDriver); pass WithDialect to override.

func WrapDB 

func WrapDB(db *stdsql.DB, opts ...DatabaseOption) *Database

WrapDB creates a sqlkit database handle from an existing database/sql handle.

func WrapExecutor 

func WrapExecutor(exec Executor, opts ...DatabaseOption) *Database

WrapExecutor creates a database that runs every statement through exec instead of a database/sql handle — the counterpart to WrapDB for a custom terminal (statement router, in-memory or remote backend, test double). exec is the terminal that runs each statement after its Before hooks, and sessions opened from the database inherit it.

Sessions opened from the database are transactional when exec also implements TxExecutor: their statements run in a transaction it begins, and Commit, Rollback, and savepoints take effect. When exec implements only Executor, a session has no managed transaction — its statements run directly through exec and Commit/Rollback are no-ops, so exec owns any transactional semantics.

func (*Database) AutoChunkEnabled 

func (d *Database) AutoChunkEnabled() bool

AutoChunkEnabled reports whether automatic, limit-driven statement splitting is enabled for this database (WithAutoChunk).

func (*Database) Delete 

func (d *Database) Delete(table Table) DeleteQuery

Delete starts a DELETE builder for a table, mirroring Select.

func (*Database) Dialect 

func (d *Database) Dialect() sql.Dialect

Dialect returns the configured SQL dialect.

func (*Database) Except 

func (d *Database) Except(first, second SelectQuery, rest ...SelectQuery) UnionQuery

Except combines two or more SELECT queries with EXCEPT, executed against this database.

func (*Database) Exec 

func (d *Database) Exec(ctx context.Context, query sql.Query) (stdsql.Result, error)

Exec compiles and runs a statement outside a session (auto-commit). It accepts any sql.Query, including the DDL statements built with the sql package (sql.CreateTable, sql.AlterTable, ...), since those are Query values too. A statement that renders empty for the dialect (e.g. a standalone comment on a dialect that comments inline) is a no-op and returns a nil result.

func (*Database) Identities 

func (d *Database) Identities() *meta.IdentityMap

Identities returns the catalog identity map compiled from the metadata bound with WithMetadata, or nil if none was bound. A uow.Scope opened over this database reads it to validate table bindings automatically.

func (*Database) Insert 

func (d *Database) Insert(table Table) InsertQuery

Insert starts an INSERT builder for a table, mirroring Select. Feed it rows with Values, or a query with FromSelect.

func (*Database) Intersect 

func (d *Database) Intersect(first, second SelectQuery, rest ...SelectQuery) UnionQuery

Intersect combines two or more SELECT queries with INTERSECT, executed against this database.

func (*Database) Merge 

func (d *Database) Merge(target Table) MergeQuery

Merge starts a MERGE builder for a target table, mirroring Select. Set the source with Using, the join with On, and the actions with When.

func (*Database) NewSession 

func (d *Database) NewSession(opts ...SessionOption) *Session

NewSession creates a transaction-scoped session. No transaction is opened yet — it begins lazily when the first statement runs through the session — so creating one is free and there is no error to handle here.

func (*Database) RawSQL 

func (d *Database) RawSQL(ctx context.Context, query any, args ...any) Rows

RawSQL prepares a hand-written SQL string (with args) or a built sql.Query for execution against this database; materialize it with Scan.

func (*Database) SQL 

func (d *Database) SQL() *stdsql.DB

SQL returns the wrapped database/sql handle.

func (*Database) Select 

func (d *Database) Select(selections ...sql.Selection) SelectQuery

Select starts a typed SELECT builder executed against this database. With no arguments it selects every column of the From source.

func (*Database) Union 

func (d *Database) Union(first, second SelectQuery, rest ...SelectQuery) UnionQuery

Union combines two or more SELECT queries with UNION, executed against this database. It mirrors the package-level Union but binds the result to this database regardless of the operands' own executors.

func (*Database) UnionAll 

func (d *Database) UnionAll(first, second SelectQuery, rest ...SelectQuery) UnionQuery

UnionAll is Union with UNION ALL, keeping duplicate rows.

func (*Database) Update 

func (d *Database) Update(table Table) UpdateQuery

Update starts an UPDATE builder for a table, mirroring Select.

func (*Database) WithSession 

func (d *Database) WithSession(ctx context.Context, fn func(s *Session) error, opts ...SessionOption) error

WithSession runs fn inside a session and commits when fn returns nil; on error or panic it rolls back and the error (or panic) propagates. Thanks to lazy transaction begin, a callback that runs no statements costs nothing. 

err := db.WithSession(ctx, func(s *sqlkit.Session) error {
	if _, err := s.Insert(Users).Values(u).Exec(ctx); err != nil {
		return err
	}
	_, err := s.Update(Posts).Set(...).Where(...).Exec(ctx)
	return err
})

Pass WithRetry to replay the whole callback in a fresh transaction on a retryable failure (a serialization failure or deadlock by default); ctx then also bounds the retry loop, cutting the backoff short and returning the last error once it is cancelled.

type DatabaseOption 

type DatabaseOption func(*Database)

DatabaseOption configures a Database.

func WithAutoChunk 

func WithAutoChunk(enabled bool) DatabaseOption

WithAutoChunk enables or disables automatic statement splitting. It is off by default; pass true so bulk INSERTs, large IN lists, RETURNING writes, and uow flushes are split to stay within the dialect's bind-parameter limit (the BindParamLimiter capability, 65535 for PostgreSQL/MySQL). With it off, explicit chunking via AutoChunk and InBatches still works. Sessions inherit the setting and can override it with WithSessionAutoChunk.

func WithBatchReadSnapshot 

func WithBatchReadSnapshot(enabled bool) DatabaseOption

WithBatchReadSnapshot controls whether a split SELECT (a large IN list chunked to stay within the bind-parameter limit) runs its chunk queries inside one REPEATABLE READ transaction, so they observe a single consistent snapshot just as the unsplit statement would. It is on by default whenever splitting is enabled; pass false to run the chunks as independent reads instead, trading the snapshot for not holding a connection across the chunks. It has no effect when splitting is disabled, or on chunks already running inside a Session (which use that session's transaction). It does not change the atomic transaction wrapping split INSERT/UPDATE/DELETE writes.

func WithBatchWriteSnapshot 

func WithBatchWriteSnapshot(enabled bool) DatabaseOption

WithBatchWriteSnapshot raises the isolation of the transaction wrapping a split INSERT/UPDATE/DELETE to REPEATABLE READ, so any data those statements read is consistent across the chunks — the case that matters for a reference UPDATE, whose SET subquery or FROM source would otherwise be re-read per chunk and could drift between them. It is off by default because REPEATABLE READ can surface serialization failures (PostgreSQL 40001) the caller must retry; the split is always atomic regardless, so enable this only when the chunks read data that must not change mid-batch. It has no effect on chunks already running inside a Session, which use that session's transaction.

func WithDialect 

func WithDialect(dialect sql.Dialect) DatabaseOption

WithDialect overrides the SQL dialect (PostgreSQL by default).

func WithHooks 

func WithHooks(hooks ...Hooks) DatabaseOption

WithHooks registers lifecycle hooks run around every statement executed through the database (and through sessions it opens): a Before hook just before a statement reaches the database, and an AfterQuery/AfterExec hook once its result is produced — see Hooks. Hooks run in registration order; multiple calls accumulate.

func WithMetadata 

func WithMetadata(m meta.Metadata) DatabaseOption

WithMetadata binds a catalog's Metadata to the database. The metadata is compiled into an identity map (see meta.Metadata.Identities) that every uow.Scope opened over this database — or a session derived from it — validates its table bindings against automatically, without an explicit uow.WithIdentities. A conflict in the metadata (meta.ErrTableConflict) is surfaced by Open; WithMetadata is otherwise opt-in, and a database without it runs uow scopes that skip catalog validation.

func WithPreloadBatchSize 

func WithPreloadBatchSize(n int) DatabaseOption

WithPreloadBatchSize sets the maximum number of parent keys per follow-up IN query a Preload issues, so an eager-load over many parents runs several smaller statements (like SQLAlchemy's selectinload) instead of one large IN. It applies in addition to the dialect's bind-parameter limit and only when auto-split is enabled. The default is 1000; pass 0 to chunk only at that hard limit. Sessions inherit the setting.

type DeleteQuery 

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

DeleteQuery is a DELETE statement.

func Delete 

func Delete(table Table) DeleteQuery

Delete creates an unbound DELETE builder, for ToSQL or tests. Executing it returns an error; start from a Database or Session to run it.

Example 
q := sqlkit.Delete(Posts).Where(Posts.UserID.Eq(7))

text, args, _ := q.ToSQL(sql.PostgreSQLDialect{})
fmt.Println(text)
fmt.Println(args)

Output:
DELETE FROM "public"."posts" WHERE ("public"."posts"."user_id" = $1)
[7]

func (DeleteQuery) AutoChunk 

func (q DeleteQuery) AutoChunk(opts ...ChunkOption) DeleteQuery

AutoChunk configures how a lone WHERE column IN whose value list exceeds the executor's bind-parameter limit is split across statements: ChunkSize(n) caps each statement at n values, ChunkOff() runs it as one statement. The chunks run atomically and their RowsAffected sum; ToSQL is unaffected.

func (DeleteQuery) Exec 

func (q DeleteQuery) Exec(ctx context.Context, opts ...ExecOption) (stdsql.Result, error)

Exec runs the DELETE statement and returns the database/sql result. When a lone column IN in the WHERE carries more values than the executor's bind-parameter limit allows, the value list is split across several statements run atomically (one transaction through a Database, the session's transaction through a Session); the returned result then sums their RowsAffected.

func (DeleteQuery) Filter 

func (q DeleteQuery) Filter(filter any) DeleteQuery

Filter narrows the DELETE with a reusable filter bundle applied against the target table's columns: either a Predicate, or a struct whose set fields map to columns by name and hold FilterTerm values (sqlkit.Le(20), …) or predicates. It is the DELETE counterpart of SelectQuery.Filter, so one filter bundle narrows a read or a write alike. A mapping error is deferred to the terminal.

func (DeleteQuery) Join 

func (q DeleteQuery) Join(source sql.Source, on Predicate) DeleteQuery

Join joins source on the predicate. It renders as an inline join on MySQL and as a correlated USING with the predicate folded into WHERE on PostgreSQL.

func (DeleteQuery) LeftJoin 

func (q DeleteQuery) LeftJoin(source sql.Source, on Predicate) DeleteQuery

LeftJoin is Join with a LEFT JOIN (MySQL inline form only).

func (DeleteQuery) Returning 

func (q DeleteQuery) Returning(selections ...sql.Selection) ReturningQuery

Returning adds an explicit RETURNING clause and makes the statement yield rows: end the chain with All(...).Scan to read them. With no arguments it returns all table columns.

func (DeleteQuery) ToSQL 

func (q DeleteQuery) ToSQL(dialect sql.Dialect) (string, []any, error)

ToSQL compiles the DELETE statement. A deferred IN is folded into a single statement carrying the full value list, independent of any configured bind-parameter limit (which only affects Exec).

func (DeleteQuery) Using 

func (q DeleteQuery) Using(sources ...sql.Source) DeleteQuery

Using adds correlated sources to the DELETE (PostgreSQL DELETE ... USING / MySQL multi-table DELETE); correlate them with Where.

func (DeleteQuery) UsingRel 

func (q DeleteQuery) UsingRel(rel Relation) DeleteQuery

UsingRel correlates the DELETE to a relation's target on its foreign-key condition (PostgreSQL DELETE ... USING / MySQL multi-table DELETE), so callers never spell out the ON clause.

func (DeleteQuery) Where 

func (q DeleteQuery) Where(predicates ...Predicate) DeleteQuery

Where adds predicates, combined with AND. A lone column IN whose value list is large enough to exceed the executor's bind-parameter limit is held back and split across statements by Exec; see WithAutoChunk.

func (DeleteQuery) WithCTE 

func (q DeleteQuery) WithCTE(ctes ...sql.CTE) DeleteQuery

WithCTE prepends common table expressions to the DELETE, emitting a leading WITH clause. Reference them from a WHERE subquery.

type Dialect 

type Dialect = querysql.Dialect

Dialect captures SQL syntax details that vary by database engine.

func DialectForDriver 

func DialectForDriver(driverName string) Dialect

DialectForDriver maps a database/sql driver name to a dialect. Known drivers (registered by default or via RegisterDriverDialect) resolve to their dialect; any other name defaults to PostgreSQL. Open uses it to infer the dialect; override with WithDialect.

type ExecOption 

type ExecOption interface {
	// contains filtered or unexported methods
}

ExecOption configures a single execution — a terminal such as All, First, One, Page, Exec, or RawSQL. WithRoute is one.

type Execable 

type Execable interface {
	Query
	Exec(ctx context.Context, opts ...ExecOption) (stdsql.Result, error)
}

Execable is a statement run for its effect rather than its rows — an INSERT, UPDATE, or DELETE without RETURNING — executed with Exec(ctx). Hold one to run a mutation chosen at runtime: 

var stmt Execable
if remove {
	stmt = db.Delete(Users).Where(Users.ID.Eq(id))
} else {
	stmt = db.Update(Users).Set(Users.Active.Set(false)).Where(Users.ID.Eq(id))
}
_, err := stmt.Exec(ctx)

type Executor 

type Executor interface {
	// Query runs a read statement and returns its rows.
	Query(ctx context.Context, stmt Statement) (*stdsql.Rows, error)
	// Exec runs a write statement and returns its result.
	Exec(ctx context.Context, stmt Statement) (stdsql.Result, error)
}

Executor runs a single compiled Statement against the database. It is the terminal that runs a statement after its Before hooks have shaped it. Supply one with WrapExecutor to route, instrument, or fake statement execution; the default runs against the wrapped database/sql handle (or a Session's transaction).

type FilterTerm 

type FilterTerm interface {
	// contains filtered or unexported methods
}

FilterTerm is one deferred predicate in a filter bundle. The constructors below (Eq, Le, …) produce column-bound terms: SelectQuery.Filter resolves the column from the struct field's name and applies the term to it. A Predicate is also accepted as a filter field value — it is already complete and ignores the field's column.

func Eq 

func Eq(value any) FilterTerm

Eq builds a "column = value" filter term.

func FilterIsNull 

func FilterIsNull() FilterTerm

FilterIsNull builds a "column IS NULL" filter term.

func FilterLike 

func FilterLike(pattern string) FilterTerm

FilterLike builds a "column LIKE pattern" filter term.

func Ge 

func Ge(value any) FilterTerm

Ge builds a "column >= value" filter term.

func Gt 

func Gt(value any) FilterTerm

Gt builds a "column > value" filter term.

func In 

func In(values ...any) FilterTerm

In builds a "column IN (values…)" filter term.

func Le 

func Le(value any) FilterTerm

Le builds a "column <= value" filter term.

func Lt 

func Lt(value any) FilterTerm

Lt builds a "column < value" filter term.

func Ne 

func Ne(value any) FilterTerm

Ne builds a "column <> value" filter term.

type Hooks 

type Hooks struct {
	// Before runs just before a statement executes, for both reads and writes
	// (stmt.Op distinguishes them). It may rewrite *stmt — its SQL and Args, or
	// its Query followed by Recompile — or return an error to abort the
	// statement before it reaches the database. The returned context flows into
	// execution and the matching After hook, so a tracer can start a span here
	// and end it there. A nil Before is skipped.
	Before func(ctx context.Context, stmt *Statement) (context.Context, error)

	// AfterQuery runs after a read's result is scanned into dest, the caller's
	// own destination (a pointer to a slice for every row, or to a single struct
	// or scalar for one row). It runs on the execution or scan error too, with
	// err non-nil and dest not valid, so spans close and failures are observed;
	// observers that only care about success return early when err != nil. dest
	// is the materialized result, so a hook may inspect or transform it in place.
	// Returning an error fails the call; returning nil leaves any execution error
	// in place — a hook reports its own error, it does not swallow the query's. A
	// nil AfterQuery is skipped.
	AfterQuery func(ctx context.Context, stmt Statement, dest any, err error) error

	// AfterExec runs after a write, with the sql.Result it produced (nil on
	// error). Its error contract matches AfterQuery: it runs on failure too, and
	// returning an error fails the call without masking an execution error. A nil
	// AfterExec is skipped.
	AfterExec func(ctx context.Context, stmt Statement, result stdsql.Result, err error) error
}

Hooks observes — and may rewrite — statements around execution. It is the lifecycle counterpart to a value-level database/sql.Scanner: a Before hook runs just before a statement reaches the database, and an After hook runs once its result has been produced, at the boundary where the result is a concrete Go value rather than the opaque *sql.Rows cursor the execution path works with. That boundary is the point of the design: a read result cannot be transformed mid-execution (consuming *sql.Rows would starve the scanner), but after the scan dest is the caller's own materialized value, free to inspect or rewrite.

Register Hooks with WithHooks; nil fields are skipped, so a hook fills in only the lifecycle points it needs. Multiple Hooks run in registration order, and a Session inherits its database's Hooks. Transaction-control statements (a Session's savepoints) bypass hooks.

Hooks deliberately offer no retry or short-circuit: statement-level retry is unsafe across a transaction boundary (a failed statement may have aborted the surrounding transaction, so the whole unit of work must replay), and belongs outside the Database around the unit of work. For per-value transformation, implement database/sql.Scanner on the column type instead.

type InsertQuery 

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

InsertQuery is an INSERT statement, fed either by Values (row structs, mapped to columns by field name the same way Scan maps results) or by FromSelect (INSERT ... SELECT).

func Insert 

func Insert(table Table) InsertQuery

Insert creates an unbound INSERT builder, for ToSQL or tests. Executing it returns an error; start from a Database or Session to run it: 

db.Insert(Users).Values([]User{{...}}).Exec(ctx)
db.Insert(Archive).Columns(...).FromSelect(sqlkit.Select(...).From(Posts)).Exec(ctx)
Example 
// insertDTO maps only the email column, so the others are omitted and the
// database fills the serial id; RETURNING reads the generated key back.
q := sqlkit.Insert(Users).
	Values(insertDTO{Mail: "a@example.com"}).
	Returning(Users.ID)

text, args, _ := q.ToSQL(sql.PostgreSQLDialect{})
fmt.Println(text)
fmt.Println(args)

Output:
INSERT INTO "public"."users" ("email") VALUES ($1) RETURNING "public"."users"."id"
[a@example.com]
Example (MysqlReturning)

MySQL rejects RETURNING at compile time, so the engine gap is caught before any SQL reaches the database. 

_, _, err := sqlkit.Insert(Users).
	Values(insertDTO{Mail: "a@example.com"}).
	Returning(Users.ID).
	ToSQL(sql.MySQLDialect{})

fmt.Println(err != nil)

Output:
true

func (InsertQuery) AutoChunk 

func (q InsertQuery) AutoChunk(opts ...ChunkOption) InsertQuery

AutoChunk configures how a Values-based INSERT is split into several statements when executed: ChunkSize(n) caps each statement at n rows, ChunkOff() runs it as one statement. With no ChunkSize, the dialect's bind-parameter limit still sizes each statement to floor(limit / columns) so it never exceeds the limit; an explicit ChunkSize can only make statements smaller.

It applies to Exec/Returning only: ToSQL always compiles the full statement, and it has no effect on FromSelect. Split statements run atomically — one transaction through a Database, the session's transaction through a Session.

func (InsertQuery) BatchSize deprecated 

func (q InsertQuery) BatchSize(n int) InsertQuery

BatchSize splits a Values-based INSERT into statements of at most n rows.

Deprecated: use AutoChunk(ChunkSize(n)).

func (InsertQuery) Columns 

func (q InsertQuery) Columns(columns ...AnyColumn) InsertQuery

Columns restricts the inserted columns for FromSelect; the order must match the SELECT list. Without it the database assigns the query's columns in table order.

func (InsertQuery) DoNothing 

func (q InsertQuery) DoNothing() InsertQuery

DoNothing makes the upsert skip conflicting rows (DO NOTHING). MySQL has no DO NOTHING form.

func (InsertQuery) DoUpdate 

func (q InsertQuery) DoUpdate(assignments ...sql.Assignment) InsertQuery

DoUpdate makes the upsert update conflicting rows with the given assignments (DO UPDATE SET ...). Build them with Column.Set / SetExpr, referencing the proposed values with Column.Excluded. Refine it with Where.

func (InsertQuery) Exec 

func (q InsertQuery) Exec(ctx context.Context, opts ...ExecOption) (stdsql.Result, error)

Exec runs the INSERT statement and returns the database/sql result. The rows are split into several statements when BatchSize is set or the dialect's bind-parameter limit would otherwise be exceeded; the returned result then aggregates the batches: RowsAffected sums every batch and LastInsertId reports the final batch. Split statements run atomically — in one transaction through a Database, joining the session's transaction through a Session.

func (InsertQuery) FromSelect 

func (q InsertQuery) FromSelect(query sql.Query) InsertQuery

FromSelect makes this an INSERT ... SELECT, inserting the rows produced by the query. It is mutually exclusive with Values.

func (InsertQuery) OnConflict 

func (q InsertQuery) OnConflict(columns ...AnyColumn) InsertQuery

OnConflict starts an upsert clause targeting a column list — PostgreSQL's ON CONFLICT (cols) — and is followed by an action, DoNothing or DoUpdate: 

db.Insert(Users).Values(u).
	OnConflict(Users.ID).
	DoUpdate(Users.Name.SetExpr(Users.Name.Excluded())).
	Exec(ctx)

Call it with no columns for MySQL's ON DUPLICATE KEY UPDATE. Without a following action the clause is DO NOTHING.

func (InsertQuery) OnConflictConstraint 

func (q InsertQuery) OnConflictConstraint(name string) InsertQuery

OnConflictConstraint starts an upsert clause targeting a named constraint (PostgreSQL ON CONFLICT ON CONSTRAINT). PostgreSQL only.

func (InsertQuery) OnConflictExpr 

func (q InsertQuery) OnConflictExpr(exprs ...sql.Expression) InsertQuery

OnConflictExpr starts an upsert clause targeting an expression unique index (PostgreSQL ON CONFLICT ((lower(email)))).

func (InsertQuery) Returning 

func (q InsertQuery) Returning(selections ...sql.Selection) ReturningQuery

Returning adds an explicit RETURNING clause and makes the statement yield rows: end the chain with All(...).Scan to read them (e.g. database-generated keys). With no arguments it returns all table columns; otherwise the given columns or expressions.

func (InsertQuery) ToSQL 

func (q InsertQuery) ToSQL(dialect sql.Dialect) (string, []any, error)

ToSQL compiles the INSERT statement.

func (InsertQuery) Values 

func (q InsertQuery) Values(rows ...any) InsertQuery

Values appends rows to insert. Each argument may be a struct, a pointer to a struct, or a slice of either. Struct fields map to columns by `sqlkit` tag, exact name, or snake_case — the same rules Scan uses — so both generated row structs and caller-defined DTOs work. Columns with no matching field are omitted, as are defaulted columns that are zero-valued in every row and fields tagged read-only (`sqlkit:",readonly"`). A field tagged `sqlkit:",json"` is written as its JSON encoding.

func (InsertQuery) Where 

func (q InsertQuery) Where(predicates ...Predicate) InsertQuery

Where adds predicates to the upsert's DO UPDATE action (PostgreSQL ON CONFLICT ... DO UPDATE ... WHERE). Repeated calls AND together. It is ignored by DO NOTHING and unsupported on MySQL.

type LockOption 

type LockOption = sql.LockOption

LockOption refines a SELECT row-locking clause (ForUpdate / ForShare / …); use SkipLocked, NoWait, and LockOf.

func LockOf 

func LockOf(tables ...string) LockOption

LockOf restricts the lock to rows from the named tables/aliases (FOR UPDATE OF t).

func NoWait 

func NoWait() LockOption

NoWait adds NOWAIT, erroring immediately instead of waiting on a locked row.

func SkipLocked 

func SkipLocked() LockOption

SkipLocked adds SKIP LOCKED, skipping rows another transaction has already locked rather than waiting (the queue-worker pattern).

type MergeQuery 

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

MergeQuery is a typed MERGE builder (SQL:2003, PostgreSQL 15+): it matches rows of a target table against a USING data source on a join condition and applies a list of WHEN clauses. Build it from a Database or Session, set the source with Using and the join with On, then add WHEN clauses with When: 

db.Merge(Users).
	Using(staging).
	On(Cond(sql.Eq(Users.ID.Reference(), staging.Col("id")))).
	When(sqlkit.WhenMatched().ThenUpdate(Users.Name.Set("x"))).
	When(sqlkit.WhenNotMatched().ThenInsert([]string{"id", "name"}, 1, "x")).
	Exec(ctx)

MERGE is PostgreSQL/standard-SQL only; other dialects error at compile time.

func Merge 

func Merge(target Table) MergeQuery

Merge creates an unbound MERGE builder, for ToSQL or tests. Executing it returns an error; start from a Database or Session to run it.

func (MergeQuery) As 

func (q MergeQuery) As(alias string) MergeQuery

As sets an alias for the target table (MERGE INTO target AS alias).

func (MergeQuery) Exec 

func (q MergeQuery) Exec(ctx context.Context, opts ...ExecOption) (stdsql.Result, error)

Exec runs the MERGE statement and returns the database/sql result.

func (MergeQuery) On 

func (q MergeQuery) On(condition Predicate) MergeQuery

On sets the join condition that pairs source rows with target rows.

func (MergeQuery) Returning 

func (q MergeQuery) Returning(selections ...sql.Selection) ReturningQuery

Returning adds an explicit RETURNING clause (PostgreSQL 17+) and makes the statement yield rows: end the chain with All(...).Scan to read them.

func (MergeQuery) ToSQL 

func (q MergeQuery) ToSQL(dialect sql.Dialect) (string, []any, error)

ToSQL compiles the MERGE statement.

func (MergeQuery) Using 

func (q MergeQuery) Using(source sql.Source) MergeQuery

Using sets the data source matched against the target — a table or subquery, optionally aliased with sql.SourceAlias/sql.Alias.

func (MergeQuery) When 

func (q MergeQuery) When(whens ...MergeWhen) MergeQuery

When appends WHEN clauses, evaluated in order for each row.

func (MergeQuery) WithCTE 

func (q MergeQuery) WithCTE(ctes ...sql.CTE) MergeQuery

WithCTE prepends common table expressions to the MERGE, emitting a leading WITH clause.

type MergeWhen 

type MergeWhen = sql.MergeWhen

MergeWhen is one WHEN clause of a MERGE — a match kind, an optional AND condition, and the action to perform. It is an alias for sql.MergeWhen; build it with WhenMatched, WhenNotMatched, or WhenNotMatchedBySource.

func WhenMatched 

func WhenMatched() MergeWhen

WhenMatched starts a WHEN MATCHED clause, applied to rows present in both the target and the source. Its action is ThenUpdate, ThenDelete, or ThenDoNothing.

func WhenNotMatched 

func WhenNotMatched() MergeWhen

WhenNotMatched starts a WHEN NOT MATCHED [BY TARGET] clause, applied to source rows with no target match. Its action is ThenInsert or ThenDoNothing.

func WhenNotMatchedBySource 

func WhenNotMatchedBySource() MergeWhen

WhenNotMatchedBySource starts a WHEN NOT MATCHED BY SOURCE clause, applied to target rows with no source match. Its action is ThenUpdate, ThenDelete, or ThenDoNothing.

type MySQLDialect 

type MySQLDialect = querysql.MySQLDialect

MySQLDialect implements MySQL identifier quoting and placeholders; see sql.MySQLDialect for its RETURNING and codegen caveats.

type Op 

type Op int

Op is the kind of statement a hook sees: a row-returning read or a result-returning write. 

const (
	// OpQuery is a read that yields rows (SELECT, a RETURNING read, RawSQL).
	OpQuery Op = iota
	// OpExec is a write that yields a sql.Result (INSERT, UPDATE, DELETE, and
	// the DDL statements run through Exec).
	OpExec
)

func (Op) String 

func (o Op) String() string

String renders the Op for logs and errors.

type Page 

type Page struct {
	HasNext bool
	HasPrev bool
	// contains filtered or unexported fields
}

Page is the metadata of a fetched page. The rows themselves were scanned into the destination passed to Paginate; Page reports whether neighbours exist and hands out the cursors to reach them.

func (Page) Next 

func (p Page) Next() Cursor

Next returns the cursor for the following page (pass it back to Paginate). It is meaningful only when HasNext is true.

func (Page) Prev 

func (p Page) Prev() Cursor

Prev returns the cursor for the preceding page. It is meaningful only when HasPrev is true.

type PageKey 

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

PageKey is one sort key of a Paginator: a column and its direction. It also remembers the column's Go type so the cursor's value for it decodes back to the right type (an int64 key stays an int64 with no float widening, a time.Time stays a time.Time). Build keys with KeyAsc and KeyDesc.

func KeyAsc 

func KeyAsc[T any](col Column[T]) PageKey

KeyAsc builds an ascending sort key for col.

func KeyDesc 

func KeyDesc[T any](col Column[T]) PageKey

KeyDesc builds a descending sort key for col.

type Paginator 

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

Paginator performs keyset (cursor) pagination over a SelectQuery. Unlike LIMIT/OFFSET it walks the result by comparing against the last row already seen, so a page costs the same no matter how deep it is and rows inserted or deleted between requests do not shift the window.

Configure it once with the sort keys and a page size, then call Paginate with a Cursor. The first page uses Start; each returned Page yields the Cursor for the following (Next) and preceding (Prev) pages, which you pass straight back: 

pager := sqlkit.NewPaginator(
	sqlkit.KeyDesc(Users.CreatedAt),
	sqlkit.KeyAsc(Users.ID), // a unique tie-breaker, so the order is total
).Limit(20)

var users []User
page, err := pager.Paginate(ctx, db.Select().From(Users), &users, sqlkit.Start())
if page.HasNext {
	page, err = pager.Paginate(ctx, db.Select().From(Users), &users, page.Next())
}

End the keys with a unique column (typically the primary key) so the order is total and no row is skipped or repeated at a page boundary.

func NewPaginator 

func NewPaginator(keys ...PageKey) *Paginator

NewPaginator builds a Paginator that sorts by keys, in order. Provide at least one key, the last of which should be unique across rows. Set the page size with Limit before paginating.

func (*Paginator) Limit 

func (pg *Paginator) Limit(n int) *Paginator

Limit sets the page size and returns the Paginator for chaining.

func (*Paginator) Paginate 

func (pg *Paginator) Paginate(ctx context.Context, q SelectQuery, dst any, cur Cursor) (Page, error)

Paginate fetches one page of q at cur and scans it into dst, a pointer to a slice of structs (or struct pointers). It builds the ORDER BY from the Paginator's keys, adds the seek predicate carried by cur, and fetches one row past the page size to detect whether more rows follow. The returned Page reports the neighbours and hands out their cursors.

The cursor encodes a row's values for the key columns, so q must project them (the default SELECT of every column does). Do not also set q's OrderBy: Paginate orders by the Paginator's keys.

func (*Paginator) WithCodec 

func (pg *Paginator) WithCodec(codec CursorCodec) *Paginator

WithCodec replaces how a cursor's bytes are turned into the opaque token string (and back), for signing, encryption, or a non-base64 form. The default is URL-safe base64. The codec only wraps the final envelope; the typed (de)serialization of the key values stays inside the Paginator, so cursor values keep their Go types regardless of the codec.

type PostgreSQLDialect 

type PostgreSQLDialect = querysql.PostgreSQLDialect

PostgreSQLDialect implements PostgreSQL identifier quoting and placeholders.

type Predicate 

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

Predicate is a boolean SQL expression usable in WHERE, HAVING, and JOIN ON clauses. Predicates are built from typed columns, so a type mismatch is a compile error rather than a runtime SQL error.

func And 

func And(predicates ...Predicate) Predicate

And combines predicates with AND.

func Cond 

func Cond(expr sql.Expression) Predicate

Cond wraps a raw SQL expression as a predicate. It is the escape hatch for conditions the typed layer does not cover, such as CASE expressions or window function comparisons built with package sql.

func Exists 

func Exists(subquery Query) Predicate

Exists creates an "EXISTS (subquery)" predicate, true when the subquery returns at least one row — the typed way to express a semi-join: 

db.Select().From(Users).Where(sqlkit.Exists(
	sqlkit.Select(Orders.ID).From(Orders).Where(Orders.UserID.EqExpr(Users.ID)),
))

func ILike 

func ILike(expr TypedExpr[string], pattern string) Predicate

ILike creates a case-insensitive LIKE predicate for string expressions. It renders as PostgreSQL's native ILIKE and as LOWER(x) LIKE LOWER(pattern) on MySQL, so it is case-insensitive on both regardless of column collation.

func InValues 

func InValues(col sql.Expression, values ...any) Predicate

InValues builds a chunkable "col IN (values)" predicate from an untyped column expression — the escape-hatch counterpart to Column[T].In for cases the typed layer does not cover (such as a primary-key column resolved at runtime). Unlike Cond(sql.In(...)), the result carries the metadata needed to auto-split a large value list across statements; see WithAutoChunk.

func Like 

func Like(expr TypedExpr[string], pattern string) Predicate

Like creates a LIKE predicate for string expressions.

func Match 

func Match(query string, columns ...TypedExpr[string]) Predicate

Match creates a portable full-text search predicate over one or more string columns, interpreting query as natural-language text. It renders as PostgreSQL's to_tsvector(...) @@ plainto_tsquery(...) and MySQL's MATCH(...) AGAINST(... IN NATURAL LANGUAGE MODE) — on MySQL the columns must be covered by a FULLTEXT index — and is rejected on SQLite and the standard target. For boolean-mode queries or a PostgreSQL text-search configuration, drop to the sql package: Cond(sql.Match(cols...).Against(q).Boolean()).

func Not 

func Not(predicate Predicate) Predicate

Not negates a predicate.

func NotExists 

func NotExists(subquery Query) Predicate

NotExists creates a "NOT EXISTS (subquery)" predicate.

func NotLike 

func NotLike(expr TypedExpr[string], pattern string) Predicate

NotLike creates a NOT LIKE predicate for string expressions.

func NotSimilarTo 

func NotSimilarTo(expr TypedExpr[string], pattern string) Predicate

NotSimilarTo creates a negated SQL-standard NOT SIMILAR TO predicate.

func Or 

func Or(predicates ...Predicate) Predicate

Or combines predicates with OR.

func SimilarTo 

func SimilarTo(expr TypedExpr[string], pattern string) Predicate

SimilarTo creates a SQL-standard SIMILAR TO predicate for string expressions, whose pattern extends LIKE's % and _ with regex metacharacters. It is supported on PostgreSQL and the standard target; MySQL rejects it.

func (Predicate) And 

func (p Predicate) And(others ...Predicate) Predicate

And combines this predicate with others using AND.

func (Predicate) Expression 

func (p Predicate) Expression() sql.Expression

Expression returns the underlying untyped SQL expression.

func (Predicate) Or 

func (p Predicate) Or(others ...Predicate) Predicate

Or combines this predicate with others using OR.

type Querier 

type Querier interface {
	// Select starts a typed SELECT bound to this querier. With no arguments
	// it selects every column of the From source.
	Select(selections ...sql.Selection) SelectQuery

	// Insert starts an INSERT bound to this querier. Feed it rows with
	// Values, or a query with FromSelect.
	Insert(table Table) InsertQuery

	// Update starts an UPDATE bound to this querier.
	Update(table Table) UpdateQuery

	// Delete starts a DELETE bound to this querier.
	Delete(table Table) DeleteQuery

	// Merge starts a MERGE bound to this querier. Set the source with Using,
	// the join with On, and the actions with When.
	Merge(target Table) MergeQuery

	// RawSQL prepares a hand-written SQL string (with args) or a built
	// sql.Query; materialize it with Scan.
	RawSQL(ctx context.Context, query any, args ...any) Rows

	// Dialect returns the SQL dialect the querier compiles against. Both
	// *Database and *Session expose it, so helpers bound to a Querier can
	// branch on dialect capabilities (see Dialect.SupportsReturning).
	Dialect() sql.Dialect
}

Querier starts typed query chains. Both *Database (auto-commit) and *Session (a single transaction) implement it. Chains are bound to the querier they start from, so terminals take only a context: 

db.Select().From(Users).All(ctx).Scan(&users)
session.Update(Users).Set(...).Exec(ctx)

type Query 

type Query = sql.Query

Query is any SQL statement this package compiles to text — a SELECT, a set operation, or an INSERT/UPDATE/DELETE, with or without RETURNING. Every builder satisfies it through ToSQL, so it is the umbrella to hold a statement whose only shared capability is being compiled. It is an alias for sql.Query. To also run the statement, use the row-reading Scannable / Selectable or the mutating Execable below.

type ReadWriteOption 

type ReadWriteOption func(*readWriteRouter)

ReadWriteOption configures ReadWriteRouter.

func WithBalancer 

func WithBalancer(b Balancer) ReadWriteOption

WithBalancer sets how reads are spread across replicas. The default is RoundRobin; pass Random or your own Balancer.

func WithReplicas 

func WithReplicas(aliases ...string) ReadWriteOption

WithReplicas names the replica backends reads are balanced across. With none, reads go to the primary ("default").

type Relation 

type Relation interface {
	// Target is the related table, used as the JOIN/SELECT source.
	Target() Table
	// JoinOn is the ON predicate equating the foreign-key column pairs, e.g.
	// profiles.user_id = users.id.
	JoinOn() Predicate
	// Cardinality reports whether the relation is to-one or to-many.
	Cardinality() Cardinality
	// Name is the relation's generated field name (for diagnostics and as the
	// default Preload destination field).
	Name() string
	// Keys returns the join column pairs by bare SQL name, in order: for each i,
	// the relation matches target.target[i] = base.base[i]. Preload uses them to
	// batch-load the target with target.target[i] IN (base key values) and to
	// group the results back onto the base rows.
	Keys() (target, base []string)
}

Relation is generated join metadata: a typed path from a base table to a related table, derived from a foreign key. Generated relation structs embed the target columns binding — so callers reach the target's typed columns directly (appdb.Users.Rels.Profile.Public) — and implement this interface so SelectQuery.JoinRel can build the JOIN, and Preload the batched fetch, from the relation alone.

type RelationSource 

type RelationSource interface {
	// Relations lists the table's foreign-key relations.
	Relations() []Relation
}

RelationSource is implemented by table bindings that expose their generated relations, so Preload(targetTable) can resolve which relation reaches the target. Generated bindings implement it when emitted with relations.

type RetryOption 

type RetryOption func(*retryConfig)

RetryOption configures WithRetry.

func MaxAttempts 

func MaxAttempts(n int) RetryOption

MaxAttempts caps the total number of tries (the first attempt included). A value below 1 is treated as 1 (no retry).

func RetryBackoff 

func RetryBackoff(fn func(attempt int) time.Duration) RetryOption

RetryBackoff sets the wait before the attempt+1'th try, given the just-failed attempt number (1-based). Return 0 (or pass nil) for no wait.

func RetryIf 

func RetryIf(pred func(error) bool) RetryOption

RetryIf replaces the predicate that decides whether a failed attempt is worth retrying. Use it to retry application-level conflicts (for an idempotent callback that reloads its inputs), e.g. RetryIf(func(err error) bool { return errors.Is(err, sqlkit.ErrVersionConflict) }), or to recognize a driver's deadlock errors that IsSerializationFailure does not.

type ReturningQuery 

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

ReturningQuery is an INSERT, UPDATE, or DELETE statement whose RETURNING clause was set explicitly with Returning(...); only then does the statement yield rows, ended with All(ctx).Scan(&dst). Statements without Returning end with Exec.

func (ReturningQuery) All 

func (q ReturningQuery) All(ctx context.Context, opts ...ExecOption) Rows

All prepares the statement for execution against the executor the chain was built from; Scan runs it and reads the returned rows. When the underlying write would exceed the executor's bind-parameter limit, newRows splits it (via the builder's splitStatements) into several chunks — each carrying the RETURNING clause — run atomically as writes, and their rows are concatenated, mirroring the split SELECT read path.

func (ReturningQuery) ToSQL 

func (q ReturningQuery) ToSQL(dialect sql.Dialect) (string, []any, error)

ToSQL compiles the statement.

type RouteFlag 

type RouteFlag int

RouteFlag tunes a WithRoute hint. The only flag is NoPropagate. 

const (

	// NoPropagate confines a per-statement WithRoute to the statement itself, so
	// its Preload follow-up queries are routed independently (by their own
	// table) rather than inheriting the hint. It is safe for read/write
	// splitting but dangerous for sharding, where a child must hit the same
	// shard as its parent; use it only when you mean it.
	NoPropagate RouteFlag
)

type RouteOption 

type RouteOption interface {
	ExecOption
	SessionOption
}

RouteOption is the option returned by WithRoute, accepted by both terminals and session constructors.

func WithRoute 

func WithRoute(alias string, flags ...RouteFlag) RouteOption

WithRoute supplies an advisory routing hint. Passed to a terminal it sets the statement's RouteHint, which the Router reads from Route; passed to NewSession or WithSession it routes the whole session to that backend at Begin. It is a hint, not a command: the Router decides whether to honor it (the conventional idiom honors it). Add NoPropagate to keep a per-statement hint off Preload follow-ups. 

db.Select().From(Users).All(ctx, sqlkit.WithRoute("replica"))
db.NewSession(sqlkit.WithRoute("replica"))

type Router 

type Router interface {
	Route(ctx context.Context, stmt Statement) string
}

Router decides which backend alias a statement runs against. It is the single routing decision point: both auto-commit statements and sessions (at Begin) consult it. Return "" to defer to the "default" backend.

Route receives the fully built Statement, so it can branch on stmt.IsWrite() (correct even for RETURNING writes and locking reads), stmt.Tables(), stmt.Kind(), or stmt.RouteHint (the advisory alias a caller set with WithRoute). For the three-way read/write/undetermined distinction — to route a statement whose nature could not be inferred (an opaque raw string) on its own path instead of the safe write default — branch on stmt.Access() and handle AccessUnknown explicitly. Honoring the hint is the Router's choice; the recommended idiom is to return it when set: 

func (r MyRouter) Route(ctx context.Context, stmt sqlkit.Statement) string {
	if stmt.RouteHint != "" {
		return stmt.RouteHint
	}
	if stmt.IsWrite() {
		return "" // -> default (primary)
	}
	return "replica"
}

func ReadWriteRouter 

func ReadWriteRouter(opts ...ReadWriteOption) Router

ReadWriteRouter returns the canonical read/write-splitting Router: it sends writes — and undetermined statements (AccessUnknown), to stay on the safe side — to the "default" backend (the primary), and balances reads across the replica backends named with WithReplicas. Locking reads (SELECT ... FOR UPDATE) count as writes and go to the primary.

A WithRoute hint is honored only for reads (pin a read to a specific replica, or to the primary for read-after-write); on a write it is ignored, so a stray hint can never route a write to a read replica. 

db := sqlkit.WrapExecutor(sqlkit.NewRouter(map[string]sqlkit.Backend{
	"default": sqlkit.DB(primary),
	"replica": sqlkit.DB(replica),
}, sqlkit.ReadWriteRouter(sqlkit.WithReplicas("replica"))))

With no replicas — a single-database setup, or one where replicas are configured only in some environments — reads also go to "default", so the same wiring works whether or not replicas are present.

type Routing 

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

Routing is the terminal Executor that routes each statement to a named Backend chosen by a Router. Pass it to WrapExecutor. It implements TxExecutor, so a session opened over it runs in a real transaction pinned to the single backend the Router selects for that session.

Routing itself emits no logs: a Router is your own code, so log routing decisions there if you want them, and observe execution through Hooks.

func NewRouter 

func NewRouter(backends map[string]Backend, router Router) *Routing

NewRouter builds a Routing over the named backends, choosing among them with router. The "default" backend is the fallback used whenever the Router returns "" (or for a statement the Router has no opinion on), so a typical setup registers the primary as "default".

func (*Routing) Begin 

func (r *Routing) Begin(ctx context.Context, opts *stdsql.TxOptions) (Tx, error)

Begin satisfies TxExecutor for the plain (unrouted) case, pinning the transaction to the backend the Router picks for a non-read-only, hint-free session. Sessions use beginRouted to convey their route hint and read-only intent.

func (*Routing) Exec 

func (r *Routing) Exec(ctx context.Context, stmt Statement) (stdsql.Result, error)

Exec routes and runs a write statement.

func (*Routing) Query 

func (r *Routing) Query(ctx context.Context, stmt Statement) (*stdsql.Rows, error)

Query routes and runs a read statement.

type Rows 

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

Rows is a pending query result. It is produced by All, First, One, and RawSQL, and materialized with Scan. Execution is deferred until Scan, so build and query errors surface there.

func ErrRows 

func ErrRows(err error) Rows

ErrRows returns a Rows that fails with err when scanned. It lets the builders defer a build-time error (e.g. SelectQuery.Filter's mapping error) to the Scan call, matching how the typed builders surface compilation errors.

func (Rows) Scan 

func (r Rows) Scan(dest any) error

Scan runs the query and copies the result into dest. Columns are matched to struct fields by `sqlkit` tag, exact field name, or snake_case field name, with embedded structs flattened; fields implementing sql.Scanner (e.g. sql.NullString) receive the column value directly. dest may be:

  • a pointer to a slice of structs, struct pointers, or scalars — every row;
  • a pointer to a struct or scalar — the first row, or ErrNoRows if none.

type Scannable 

type Scannable interface {
	Query
	All(ctx context.Context, opts ...ExecOption) Rows
}

Scannable is a statement that produces rows to scan, read with All(ctx).Scan: a SELECT, a set operation (UNION and friends), or an INSERT/UPDATE/DELETE with a RETURNING clause. Hold one when a branch may build different row-producing statements that are all consumed the same way: 

var q Scannable
if includeArchived {
	q = db.Select().From(Users).UnionAll(db.Select().From(Archived))
} else {
	q = db.Select().From(Users)
}
err := q.All(ctx).Scan(&users)

type SelectQuery 

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

SelectQuery is a typed SELECT builder. Columns and predicates are typed via Column[T], so type mismatches are compile errors. Results are materialized into a caller-defined destination with All(ctx).Scan(&dst).

Build it from a Database or a Session — the chain is bound to that executor — then set the source with From before adding clauses: 

db.Select().From(Users).Where(Users.ID.Gt(10)).All(ctx).Scan(&dst)

func Select 

func Select(selections ...sql.Selection) SelectQuery

Select starts an unbound SELECT builder for use as a subquery (FromSelect, Union) or with ToSQL. With no arguments it selects every column of the From source; otherwise it selects the given columns or expressions (use sql.As to alias projections). Executing an unbound chain returns an error; start from a Database or Session to run it.

Example 
q := sqlkit.Select(Users.ID, Users.Email).From(Users).
	Where(Users.ID.Gt(10), Users.Name.IsNotNull()).
	OrderBy(Users.Email.Asc()).
	Limit(5)

text, args, _ := q.ToSQL(sql.PostgreSQLDialect{})
fmt.Println(text)
fmt.Println(args)

Output:
SELECT "public"."users"."id", "public"."users"."email" FROM "public"."users" WHERE (("public"."users"."id" > $1) AND ("public"."users"."name" IS NOT NULL)) ORDER BY "public"."users"."email" LIMIT 5
[10]
Example (Join) 
q := sqlkit.Select(Posts.ID, Posts.Title).From(Posts).
	Join(Users, Posts.UserID.EqExpr(Users.ID)).
	Where(Users.Email.Eq("alice@example.com"))

text, args, _ := q.ToSQL(sql.PostgreSQLDialect{})
fmt.Println(text)
fmt.Println(args)

Output:
SELECT "public"."posts"."id", "public"."posts"."title" FROM "public"."posts" JOIN "public"."users" ON ("public"."posts"."user_id" = "public"."users"."id") WHERE ("public"."users"."email" = $1)
[alice@example.com]

func (SelectQuery) All 

func (q SelectQuery) All(ctx context.Context, opts ...ExecOption) Rows

All prepares the query for execution against the executor the chain was built from. The query runs when Scan is called; build and execution errors surface there.

When a lone column IN carries more values than the executor's bind-parameter limit allows in one statement, the value list is split across several statements and Scan concatenates their rows. The split is skipped, running a single statement, when the query also carries ORDER BY, GROUP BY, DISTINCT, HAVING, or LIMIT/OFFSET, whose semantics concatenation cannot preserve.

func (SelectQuery) As 

func (q SelectQuery) As(alias string) sql.DerivedTable

As wraps this SELECT as an aliased subquery (derived table), for use as a FROM/JOIN source and column handle. Reference its output columns with the returned handle's Col, which qualifies them by the alias: 

sub := sqlkit.Select(Users.ID).From(Users).Where(Users.Active.Eq(true)).As("sub")
db.Select(sub.Col("id")).From(sub).All(ctx).Scan(&ids)

func (SelectQuery) AutoChunk 

func (q SelectQuery) AutoChunk(opts ...ChunkOption) SelectQuery

AutoChunk configures how a lone WHERE column IN whose value list exceeds the executor's bind-parameter limit is split across statements by All: ChunkSize(n) caps each statement at n values, ChunkOff() runs it as one statement. The chunk rows are concatenated; ToSQL and the single-row terminals are unaffected.

func (SelectQuery) CTE 

func (q SelectQuery) CTE(name string, opts ...sql.CTEOption) sql.CTE

CTE wraps this SELECT as a common table expression named name, for use as a FROM source and column handle, and attached to a statement with WithCTE. Pass sql.Recursive() / sql.Columns(...) options to configure it.

func (SelectQuery) Count 

func (q SelectQuery) Count(ctx context.Context, opts ...ExecOption) (int64, error)

Count runs SELECT COUNT(*) over this query (as a subquery, so joins, grouping, and predicates apply) and returns the number of matching rows.

func (SelectQuery) CrossJoin 

func (q SelectQuery) CrossJoin(source sql.Source) SelectQuery

CrossJoin adds a CROSS JOIN, the Cartesian product of the inputs. It takes no ON condition.

func (SelectQuery) Distinct 

func (q SelectQuery) Distinct() SelectQuery

Distinct marks the query as SELECT DISTINCT.

func (SelectQuery) DistinctOn 

func (q SelectQuery) DistinctOn(exprs ...sql.Expression) SelectQuery

DistinctOn marks the query as SELECT DISTINCT ON (exprs...), keeping the first row of each group equal on the given expressions (PostgreSQL only). Pair it with an ORDER BY beginning with the same expressions to make "first" deterministic.

func (SelectQuery) Except 

func (q SelectQuery) Except(other SelectQuery) UnionQuery

Except combines this query with another using EXCEPT.

func (SelectQuery) ExceptAll 

func (q SelectQuery) ExceptAll(other SelectQuery) UnionQuery

ExceptAll combines this query with another using EXCEPT ALL, keeping duplicate rows.

func (SelectQuery) Filter 

func (q SelectQuery) Filter(filter any) SelectQuery

Filter applies a reusable filter bundle against the From table's columns: either a Predicate, or a struct whose set fields map to columns by name and hold FilterTerm values (sqlkit.Le(20), …) or predicates. A mapping error is deferred to the terminal. It requires a From table (set Filter after From).

func (SelectQuery) First 

func (q SelectQuery) First(ctx context.Context, opts ...ExecOption) Rows

First is All limited to the first row: LIMIT 1 is pushed into the query, and Scan into a struct or scalar yields that row, or ErrNoRows when the result is empty.

func (SelectQuery) ForKeyShare 

func (q SelectQuery) ForKeyShare(opts ...LockOption) SelectQuery

ForKeyShare adds a FOR KEY SHARE clause (PostgreSQL only).

func (SelectQuery) ForNoKeyUpdate 

func (q SelectQuery) ForNoKeyUpdate(opts ...LockOption) SelectQuery

ForNoKeyUpdate adds a FOR NO KEY UPDATE clause (PostgreSQL only).

func (SelectQuery) ForShare 

func (q SelectQuery) ForShare(opts ...LockOption) SelectQuery

ForShare adds a FOR SHARE row-locking clause (a shared lock).

func (SelectQuery) ForUpdate 

func (q SelectQuery) ForUpdate(opts ...LockOption) SelectQuery

ForUpdate adds a FOR UPDATE row-locking clause, refined with the SkipLocked / NoWait / LockOf options. Row locking is PostgreSQL/MySQL (8.0+) only.

func (SelectQuery) From 

func (q SelectQuery) From(source sql.Source) SelectQuery

From sets the table or subquery to select from. It must be called before the other clause methods. When Select was given no columns and the source can list its columns, they are selected explicitly; otherwise SELECT *.

func (SelectQuery) FullJoin 

func (q SelectQuery) FullJoin(source sql.Source, on Predicate) SelectQuery

FullJoin adds a FULL OUTER JOIN.

func (SelectQuery) FullJoinRel 

func (q SelectQuery) FullJoinRel(rel Relation) SelectQuery

FullJoinRel adds a FULL OUTER JOIN to a relation's target on its foreign-key condition.

func (SelectQuery) Get 

func (q SelectQuery) Get(ctx context.Context, pk ...any) Rows

Get fetches the row matching the From table's primary key and returns the first result. Pass one value per primary-key column, in declaration order. It requires a From table (set it before Get).

func (SelectQuery) GroupBy 

func (q SelectQuery) GroupBy(exprs ...sql.Expression) SelectQuery

GroupBy adds GROUP BY expressions (columns or sql expressions).

func (SelectQuery) Having 

func (q SelectQuery) Having(predicate Predicate) SelectQuery

Having adds a HAVING predicate.

func (SelectQuery) Intersect 

func (q SelectQuery) Intersect(other SelectQuery) UnionQuery

Intersect combines this query with another using INTERSECT.

func (SelectQuery) IntersectAll 

func (q SelectQuery) IntersectAll(other SelectQuery) UnionQuery

IntersectAll combines this query with another using INTERSECT ALL, keeping duplicate rows.

func (SelectQuery) Join 

func (q SelectQuery) Join(source sql.Source, on Predicate) SelectQuery

Join adds an INNER JOIN.

func (SelectQuery) JoinRel 

func (q SelectQuery) JoinRel(rel Relation) SelectQuery

JoinRel adds an INNER JOIN to a relation's target on its foreign-key condition, so callers never spell out the ON clause: 

db.Select().From(Users).JoinRel(Users.Rels.Posts)

func (SelectQuery) JoinUsing 

func (q SelectQuery) JoinUsing(source sql.Source, columns ...string) SelectQuery

JoinUsing adds an INNER JOIN ... USING (columns) on the equally named columns.

func (SelectQuery) LeftJoin 

func (q SelectQuery) LeftJoin(source sql.Source, on Predicate) SelectQuery

LeftJoin adds a LEFT JOIN.

func (SelectQuery) LeftJoinRel 

func (q SelectQuery) LeftJoinRel(rel Relation) SelectQuery

LeftJoinRel adds a LEFT JOIN to a relation's target on its foreign-key condition.

func (SelectQuery) LeftJoinUsing 

func (q SelectQuery) LeftJoinUsing(source sql.Source, columns ...string) SelectQuery

LeftJoinUsing adds a LEFT JOIN ... USING (columns).

func (SelectQuery) Limit 

func (q SelectQuery) Limit(n int) SelectQuery

Limit sets the LIMIT clause.

func (SelectQuery) NaturalJoin 

func (q SelectQuery) NaturalJoin(source sql.Source) SelectQuery

NaturalJoin adds a NATURAL JOIN, an inner join on every column the sources share by name. It takes no condition.

func (SelectQuery) NaturalLeftJoin 

func (q SelectQuery) NaturalLeftJoin(source sql.Source) SelectQuery

NaturalLeftJoin adds a NATURAL LEFT JOIN.

func (SelectQuery) Offset 

func (q SelectQuery) Offset(n int) SelectQuery

Offset sets the OFFSET clause.

func (SelectQuery) One 

func (q SelectQuery) One(ctx context.Context, opts ...ExecOption) Rows

One is All restricted to exactly one row: Scan yields the row when the result has exactly one, ErrNoRows when it is empty, and ErrTooManyRows when there are more. LIMIT 2 is pushed into the query, so detecting the excess row never reads past the second.

func (SelectQuery) OrderBy 

func (q SelectQuery) OrderBy(terms ...sql.OrderTerm) SelectQuery

OrderBy adds ORDER BY terms.

func (SelectQuery) Page 

func (q SelectQuery) Page(ctx context.Context, limit, offset int, opts ...ExecOption) Rows

Page is All over a LIMIT/OFFSET window: it pushes LIMIT limit OFFSET offset into the query and prepares it for execution, for paginated fetches without spelling out the clauses: 

db.Select().From(Users).OrderBy(Users.ID.Asc()).Page(ctx, 20, 40).Scan(&users)

func (SelectQuery) Preload 

func (q SelectQuery) Preload(target Table, conditions ...Predicate) SelectQuery

Preload registers a related table to eager-load, optionally narrowed by conditions. The target is resolved to the From table's relation reaching it, so callers pass the target binding rather than the relation: Preload(Posts), not Preload(Users.Rels.Posts). The load is not a join; when a terminal's Scan runs, the relation is fetched in one batched follow-up query and assigned onto the scanned destination by reflection (matched to a field by the relation name or a `preload:"…"` tag), avoiding the N+1: 

db.Select().From(Users).Preload(Posts).All(ctx).Scan(&users)

Conditions are ANDed into the follow-up SELECT, so only matching children load: 

db.Select().From(Users).Preload(Posts, Posts.Published.Eq(true))

For a nested eager-load, pass a RelPath as the target — Preload then builds each hop, creating any intermediate level not already registered, and the conditions apply to the final hop (Users→Posts→Comments): 

db.Select().From(Users).
    Preload(sqlkit.RelPath(Posts, Comments), Comments.Approved.Eq(true)).
    All(ctx).Scan(&users)

Resolution errors (no From table, a table on the path exposes no relations, or no/ambiguous relation between two hops) are deferred to the terminal. When more than one relation reaches the same table (e.g. a self-reference's Manager and Reports), pick one explicitly with PreloadRel.

func (SelectQuery) PreloadRel 

func (q SelectQuery) PreloadRel(rel Relation, conditions ...Predicate) SelectQuery

PreloadRel registers a relation to eager-load by value, for when Preload's target-table lookup is ambiguous (several relations reach the same table) or a hand-built relation is used. It takes the same conditions, but its target is not resolved to a RelationSource, so it cannot be a hop on a RelPath.

func (SelectQuery) RightJoin 

func (q SelectQuery) RightJoin(source sql.Source, on Predicate) SelectQuery

RightJoin adds a RIGHT JOIN.

func (SelectQuery) RightJoinRel 

func (q SelectQuery) RightJoinRel(rel Relation) SelectQuery

RightJoinRel adds a RIGHT JOIN to a relation's target on its foreign-key condition.

func (SelectQuery) SQLSource 

func (q SelectQuery) SQLSource() SourceExpression

SQLSource marks the query as a subquery source.

func (SelectQuery) ToSQL 

func (q SelectQuery) ToSQL(dialect sql.Dialect) (string, []any, error)

ToSQL compiles the query. A deferred IN is folded into a single statement, so the compiled SQL always carries the full value list regardless of any configured bind-parameter limit (which only affects All's execution).

func (SelectQuery) Union 

func (q SelectQuery) Union(other SelectQuery) UnionQuery

Union combines this query with another using UNION. The result stays bound to this chain's executor.

func (SelectQuery) UnionAll 

func (q SelectQuery) UnionAll(other SelectQuery) UnionQuery

UnionAll combines this query with another using UNION ALL.

func (SelectQuery) Where 

func (q SelectQuery) Where(predicates ...Predicate) SelectQuery

Where adds predicates, combined with AND. A lone column IN whose value list is large enough to exceed the executor's bind-parameter limit is held back and split across statements by All; see WithAutoChunk.

func (SelectQuery) Window 

func (q SelectQuery) Window(defs ...sql.NamedWindow) SelectQuery

Window adds named window definitions to the SELECT's WINDOW clause, referenced from an OVER clause with sql.Over(fn).Named(name). Build a definition with sql.WindowDef(name).PartitionBy(...).OrderBy(...).

func (SelectQuery) WithCTE 

func (q SelectQuery) WithCTE(ctes ...sql.CTE) SelectQuery

WithCTE prepends common table expressions to the SELECT, emitting a leading WITH clause. Like the other clause methods it must be called after From.

type Selectable 

type Selectable interface {
	Scannable
	First(ctx context.Context, opts ...ExecOption) Rows
	One(ctx context.Context, opts ...ExecOption) Rows
	Page(ctx context.Context, limit, offset int, opts ...ExecOption) Rows
	Count(ctx context.Context, opts ...ExecOption) (int64, error)
}

Selectable is a SELECT-shaped query — a SELECT or a set operation — that also supports the single-row, paginated, and counting reads, not just All. It is the interface to reach for when a branch assigns either a SelectQuery or a UnionQuery and the caller still wants First/One/Page/Count: 

var q Selectable = base
if onlyOne {
	err = q.One(ctx).Scan(&u)
} else {
	n, err = q.Count(ctx)
}

ReturningQuery is deliberately only Scannable, not Selectable: a RETURNING statement yields its rows once through All, with no LIMIT/OFFSET or count of its own.

type Session 

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

Session is a transaction-scoped database handle. The transaction begins lazily: nothing is opened until the first statement runs through the session, so a session that executes nothing costs nothing and its Commit and Close are no-ops. Once a statement has run, finish with Commit or Rollback (Close rolls back if neither happened).

Session implements Querier, so query chains start from it and run inside its transaction: session.Select().From(t).All(ctx).Scan(&dst).

The transaction comes from the database's terminal Executor: when it implements TxExecutor (the default database/sql handle, or a WrapExecutor terminal that opts in) the session runs inside a real transaction; otherwise statements run directly on the terminal and Commit/Rollback are no-ops.

func (*Session) AutoChunkEnabled 

func (s *Session) AutoChunkEnabled() bool

AutoChunkEnabled reports whether automatic, limit-driven statement splitting is enabled for this session (WithAutoChunk / WithSessionAutoChunk).

func (*Session) Close 

func (s *Session) Close() error

Close rolls back the transaction if the session has not been committed. It is safe to defer unconditionally; on an untouched or committed session it does nothing.

func (*Session) Commit 

func (s *Session) Commit() error

Commit commits the transaction. If no transaction was begun (no statement ran, or the terminal cannot transact), Commit just closes the session.

func (*Session) Delete 

func (s *Session) Delete(table Table) DeleteQuery

Delete starts a DELETE bound to this session's transaction.

func (*Session) Dialect 

func (s *Session) Dialect() sql.Dialect

Dialect returns the session's SQL dialect. It mirrors Database.Dialect and lets layers above sqlkit (such as the uow package) branch on dialect capabilities like RETURNING support without reaching into the executor.

func (*Session) Except 

func (s *Session) Except(first, second SelectQuery, rest ...SelectQuery) UnionQuery

Except combines two or more SELECT queries with EXCEPT, bound to this session's transaction.

func (*Session) Exec 

func (s *Session) Exec(ctx context.Context, query sql.Query) (stdsql.Result, error)

Exec compiles and runs a statement through the session's transaction. It accepts any sql.Query, including the DDL statements built with the sql package (sql.CreateTable, sql.AlterTable, ...). A statement that renders empty for the dialect is a no-op and returns a nil result.

func (*Session) Identities 

func (s *Session) Identities() *meta.IdentityMap

Identities returns the catalog identity map inherited from the database that opened this session (see WithMetadata), or nil if none was bound. A uow.Scope opened over this session reads it to validate table bindings automatically.

func (*Session) Insert 

func (s *Session) Insert(table Table) InsertQuery

Insert starts an INSERT bound to this session's transaction. Feed it rows with Values, or a query with FromSelect.

func (*Session) Intersect 

func (s *Session) Intersect(first, second SelectQuery, rest ...SelectQuery) UnionQuery

Intersect combines two or more SELECT queries with INTERSECT, bound to this session's transaction.

func (*Session) Merge 

func (s *Session) Merge(target Table) MergeQuery

Merge starts a MERGE bound to this session's transaction.

func (*Session) RawSQL 

func (s *Session) RawSQL(ctx context.Context, query any, args ...any) Rows

RawSQL prepares a hand-written SQL string (with args) or a built sql.Query for execution in this session's transaction; materialize it with Scan.

func (*Session) Release 

func (s *Session) Release(ctx context.Context, name string) error

Release discards the named savepoint, merging its work into the enclosing transaction or savepoint.

func (*Session) Rollback 

func (s *Session) Rollback() error

Rollback rolls back the transaction. If no transaction was begun, it just closes the session.

func (*Session) RollbackTo 

func (s *Session) RollbackTo(ctx context.Context, name string) error

RollbackTo rolls the transaction back to the named savepoint, undoing every statement since it was opened while leaving the savepoint (and the outer transaction) in place.

func (*Session) Savepoint 

func (s *Session) Savepoint(ctx context.Context) (string, error)

Savepoint opens a savepoint in the session's transaction and returns its generated name. The transaction begins on first use, so a savepoint may be the first statement. Undo work back to it with RollbackTo, or discard the savepoint with Release; the outer Commit releases any that remain.

func (*Session) Select 

func (s *Session) Select(selections ...sql.Selection) SelectQuery

Select starts a typed SELECT bound to this session's transaction. With no arguments it selects every column of the From source.

func (*Session) Union 

func (s *Session) Union(first, second SelectQuery, rest ...SelectQuery) UnionQuery

Union combines two or more SELECT queries with UNION, bound to this session's transaction. It mirrors the package-level Union but binds the result to this session regardless of the operands' own executors.

func (*Session) UnionAll 

func (s *Session) UnionAll(first, second SelectQuery, rest ...SelectQuery) UnionQuery

UnionAll is Union with UNION ALL, keeping duplicate rows.

func (*Session) Update 

func (s *Session) Update(table Table) UpdateQuery

Update starts an UPDATE bound to this session's transaction.

type SessionOption 

type SessionOption interface {
	// contains filtered or unexported methods
}

SessionOption configures a new session. WithTxOptions, WithSessionAutoChunk, and WithRoute return one.

func WithRetry 

func WithRetry(opts ...RetryOption) SessionOption

WithRetry makes WithSession replay its whole callback in a fresh transaction when it fails with a retryable error — the safe granularity for a retry, since a failed statement may have aborted the surrounding transaction, so the entire unit of work must run again. It applies only to WithSession (which owns the callback); a manually managed NewSession is unaffected.

By default it makes up to 3 attempts and retries an error that is a serialization failure or deadlock, or one wrapped with Retryable (IsRetryable), waiting an exponential backoff between tries. Tune it with MaxAttempts, RetryIf, and RetryBackoff: 

err := db.WithSession(ctx, func(s *sqlkit.Session) error {
	// ... read, decide, write — all replayed on a serialization failure
	return nil
}, sqlkit.WithRetry(sqlkit.MaxAttempts(5)))

The callback must be idempotent across attempts: it runs again from the top, against a new transaction, each time it is retried.

func WithSessionAutoChunk 

func WithSessionAutoChunk(enabled bool) SessionOption

WithSessionAutoChunk overrides, for this session, whether automatic limit-driven statement splitting is enabled. Without it a session inherits the database's WithAutoChunk setting; pass true or false to force it on or off for statements run through the session (and uow scopes opened over it).

func WithTxOptions 

func WithTxOptions(options TxOptions) SessionOption

WithTxOptions sets transaction options for the session.

type SourceExpression 

type SourceExpression = sql.SourceExpression

SourceExpression aliases sql.SourceExpression so generated code only needs to import package sqlkit.

type StandardSQLDialect 

type StandardSQLDialect = querysql.StandardSQLDialect

StandardSQLDialect renders portable ANSI/SQL-standard text; see sql.StandardSQLDialect. It has no bundled driver and is reached via WithDialect (or DialectForDriver after RegisterDriverDialect).

type Statement 

type Statement struct {
	// Op is OpQuery for reads and OpExec for writes.
	Op Op
	// Query is the SQL DSL the statement was compiled from, or nil for a
	// raw-string statement (RawSQL with a string).
	Query sql.Query
	// SQL is the compiled SQL text that will run.
	SQL string
	// Args are the bind arguments that will run.
	Args []any
	// Dialect is the dialect SQL was compiled for; Recompile uses it.
	Dialect sql.Dialect
	// RouteHint is an advisory backend alias set by WithRoute. A Router reads it
	// from Route to honor (or override) a caller's routing preference; it is
	// empty when no hint was given. It is purely a hint — the Router decides what
	// to do with it.
	RouteHint string
}

Statement is the query a hook inspects and may rewrite before it runs, and the value a Router routes on.

SQL and Args are the source of truth for execution: the executor runs exactly what they hold, so a Before hook that rewrites them changes what reaches the database. Query carries the SQL DSL (the sql.Query AST) the statement was compiled from, for hooks and routers that want to branch on its structure — the target table, the kind of statement, and so on. It is nil when there is no AST, as with a RawSQL string. To rewrite at the AST level, build a new query, assign it to Query, and call Recompile to refresh SQL and Args from it.

func (Statement) Access 

func (s Statement) Access() Access

Access reports the statement's read/write nature, including the undetermined case. It derives from Kind, with a locking read (SELECT ... FOR UPDATE/SHARE) counted as a write because it takes row locks. A raw string that could not be classified is AccessUnknown — distinct from a definite read — so a Router can decide how to route it rather than having the choice made for it.

func (Statement) IsWrite 

func (s Statement) IsWrite() bool

IsWrite reports whether the statement must run on the primary/writer: a definite write, a locking read (SELECT ... FOR UPDATE/SHARE), or an undetermined statement (AccessUnknown), treated as a write to stay on the safe side. It is the boolean convenience over Access; call Access for the three-way read/write/unknown distinction.

func (Statement) Kind 

func (s Statement) Kind() StatementKind

Kind classifies the statement from its AST, independent of Op, so a write carried over the row-returning path (a RETURNING statement) is still reported as a write. A raw-string statement (no AST) is classified from its leading keyword: a SELECT is KindRawRead, anything else KindRawUnknown.

func (*Statement) Recompile 

func (s *Statement) Recompile() error

Recompile recompiles Query for the statement's Dialect and replaces SQL and Args with the result. It is the AST-level rewrite path: set Query to a new builder result, then call Recompile. It returns an error if Query is nil or compilation fails.

func (Statement) Tables 

func (s Statement) Tables() []string

Tables returns the tables the statement targets, best effort, with the primary target first. It reads the AST: the target table of an INSERT/UPDATE/DELETE/ MERGE, or the From table of a SELECT when its source is a table. It returns nil when no table can be determined (a subquery source, a raw string, or an AST shape that does not expose one), so a Router that shards by table should treat an empty result as "no opinion".

type StatementKind 

type StatementKind int

StatementKind is the normalized kind of a statement, derived from its AST (Statement.Query) rather than its Op. It exists because Op alone cannot tell a write from a read: an INSERT/UPDATE/DELETE ... RETURNING runs through the row-returning path with Op == OpQuery, yet is a write. A Router branches on Kind (via IsWrite) to route reads and writes correctly. 

const (
	// KindSelect is a row-returning read: SELECT or a set operation (UNION, ...).
	// A SELECT ... FOR UPDATE/SHARE is still KindSelect, but IsWrite reports it as
	// a write because it takes row locks.
	KindSelect StatementKind = iota
	// KindInsert is an INSERT (with or without RETURNING).
	KindInsert
	// KindUpdate is an UPDATE (with or without RETURNING).
	KindUpdate
	// KindDelete is a DELETE (with or without RETURNING).
	KindDelete
	// KindMerge is a MERGE.
	KindMerge
	// KindDDL is a schema statement (CREATE/ALTER/DROP, ...) run through Exec.
	KindDDL
	// KindRawRead is a raw-string statement (RawSQL) that begins with SELECT.
	KindRawRead
	// KindRawUnknown is a raw-string statement whose read/write nature could not
	// be determined from its text; it is treated as a write (routed to the
	// write/default backend) to stay on the safe side.
	KindRawUnknown
)

func (StatementKind) String 

func (k StatementKind) String() string

String renders the StatementKind for logs and errors.

type Table 

type Table interface {
	sql.Source

	// Info returns the table identity.
	Info() TableInfo

	// Columns returns all column bindings in declaration order.
	Columns() []AnyColumn

	// PrimaryKey returns the primary key column bindings.
	PrimaryKey() []AnyColumn

	// Defaulted returns the columns that have a database-side default.
	// INSERT omits them when the row value is the Go zero value, letting the
	// database fill them in.
	Defaulted() []AnyColumn
}

Table is implemented by generated table bindings: the table identity and column metadata the builders use to generate statements. Row data flows in through Insert(...).Values (struct fields mapped to columns by name) and out through Scan, so bindings carry no row-type parameter.

func RelPath 

func RelPath(tables ...Table) Table

RelPath builds a nested preload target from a chain of related tables, each related to the one before it (the first to the From table). Pass it to Preload to eager-load along the whole path, creating any intermediate level not already registered; the Preload conditions then apply to the final table: 

db.Select().From(Users).
    Preload(sqlkit.RelPath(Posts, Comments), Comments.Approved.Eq(true)).
    All(ctx).Scan(&users)
// Users → Posts → Comments, in three batched queries.

type TableInfo 

type TableInfo struct {
	Schema string
	Name   string
	// Alias, when set, is the name an aliased view of the table is referenced
	// by. It affects only column qualification and the FROM/JOIN source render
	// ("<table> AS <alias>"); statement targets and FK rendering keep using the
	// real QualifiedName.
	Alias string
	// Checksum and Generator carry the binding's identity (see the type doc).
	Checksum  string
	Generator string
}

TableInfo identifies a database table. Schema and Name locate it; Checksum is a hex digest of the table's structure (columns, primary key, indexes, foreign keys, checks) carried over from the meta model the binding was generated from. Two bindings of the same table — even generated by independent packages — share a Checksum, which lets the uow package reconcile them; a binding generated from a drifted schema differs.

Generator fingerprints the code generator that produced the binding: the sqlkit version, a hash of the generation templates, and the format version. Bindings emitted by different sqlkit versions carry different Generators, so a Unit of Work refuses to reconcile generated code that drifted across packages even when the structure happens to match.

Checksum and Generator are empty for hand-written bindings, which is treated as "unidentified".

func (TableInfo) QualifiedName 

func (t TableInfo) QualifiedName() string

QualifiedName returns the real table name with an optional schema prefix. It is the statement target (INSERT/UPDATE/DELETE) and FK reference identity, so it ignores Alias.

func (TableInfo) SQLSource 

func (t TableInfo) SQLSource() SourceExpression

SQLSource marks TableInfo as a sql.Source, rendering "<qualified> AS <alias>" when aliased.

func (TableInfo) WithAlias 

func (t TableInfo) WithAlias(alias string) TableInfo

WithAlias returns a copy of the table identity aliased as alias.

type Tx 

type Tx interface {
	Executor
	// Commit commits the transaction.
	Commit() error
	// Rollback aborts the transaction, discarding its work.
	Rollback() error
}

Tx is a transaction-scoped Executor: its Query and Exec run inside the transaction, and Commit or Rollback ends it. It is what TxExecutor.Begin returns.

type TxExecutor 

type TxExecutor interface {
	Executor
	// Begin starts a transaction with the given options (nil for the driver
	// default) and returns it as a Tx.
	Begin(ctx context.Context, opts *stdsql.TxOptions) (Tx, error)
}

TxExecutor is an Executor that can begin a transaction. A Session opened over a terminal that implements it runs inside a real transaction — Commit, Rollback, and savepoints take effect; over a terminal that does not, the session runs statements directly with no managed transaction. The default database/sql handle implements it (so WrapDB and Open sessions are transactional); a WrapExecutor terminal may implement it to give its own sessions transactions.

type TxOptions 

type TxOptions struct {
	Isolation sql.IsolationLevel
	ReadOnly  bool
}

TxOptions describes transaction behavior requested by generated operations.

func (TxOptions) ToSQL 

func (o TxOptions) ToSQL() *sql.TxOptions

ToSQL converts sqlkit transaction options to database/sql options.

type TypedExpr 

type TypedExpr[T any] interface {
	sql.Expression
	// contains filtered or unexported methods
}

TypedExpr is a SQL expression whose static Go type is T. Only sqlkit types (columns, typed values) implement it, which lets the builders reject type-mismatched comparisons at compile time.

func V 

func V[T any](value T) TypedExpr[T]

V creates a typed bind value expression.

type TypedSelection 

type TypedSelection[T any] interface {
	TypedExpr[T]
	sql.Selection
}

TypedSelection is a TypedExpr[T] that can also stand as a SELECT-list item, so it works both in typed comparisons and as a projection (via sql.As). It is the static type returned by SubExpr.

func Cast 

func Cast[T any](expr sql.Expression, sqlType string) TypedSelection[T]

Cast converts an expression to sqlType, keeping the result typed as T so it stays usable in typed comparisons and projections. The target type spelling is the caller's (e.g. "integer", "text", "numeric(10, 2)"); it is not checked against T.

func SubExpr 

func SubExpr[T any](subquery Query) TypedSelection[T]

SubExpr wraps a single-column, single-row subquery as a typed scalar expression of type T. It is the typed handle for a correlated scalar subquery, usable in comparisons (Users.Age.GtExpr(sqlkit.SubExpr[int64](avg))) and as a projection (sql.As(sqlkit.SubExpr[int64](cnt), "order_count")). The subquery may reference the enclosing query's columns; its projected type is not statically checked against T.

type UnionQuery 

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

UnionQuery is a set operation over SELECT queries, materialized the same way with All(ctx).Scan(&dst).

func Except 

func Except(first, second SelectQuery, rest ...SelectQuery) UnionQuery

Except combines two or more SELECT queries with EXCEPT, keeping rows of the first operand absent from the rest. See Union for operand and executor rules.

EXCEPT is left-associative: with operands a, b, c the result is (a EXCEPT b) EXCEPT c.

func ExceptAll 

func ExceptAll(first, second SelectQuery, rest ...SelectQuery) UnionQuery

ExceptAll is Except with EXCEPT ALL, the multiset difference that drops a left row once per matching right row rather than entirely. See Union for operand and executor rules.

func Intersect 

func Intersect(first, second SelectQuery, rest ...SelectQuery) UnionQuery

Intersect combines two or more SELECT queries with INTERSECT, keeping rows present in every operand. See Union for operand and executor rules.

func IntersectAll 

func IntersectAll(first, second SelectQuery, rest ...SelectQuery) UnionQuery

IntersectAll is Intersect with INTERSECT ALL, the multiset intersection that keeps duplicate rows. See Union for operand and executor rules.

func Union 

func Union(first, second SelectQuery, rest ...SelectQuery) UnionQuery

Union combines two or more SELECT queries with UNION, flattening what would otherwise be a left-folded chain of SelectQuery.Union calls: 

sqlkit.Union(
	db.Select(Users.ID).From(Users),
	db.Select(Admins.ID).From(Admins),
	db.Select(Guests.ID).From(Guests),
).OrderBy(sql.Asc(sql.Col("id"))).All(ctx).Scan(&ids)

The result is bound to the first operand's executor, so start the list with a query built from a Database or Session to run it. At least two operands are required, enforced by the signature.

func UnionAll 

func UnionAll(first, second SelectQuery, rest ...SelectQuery) UnionQuery

UnionAll is Union with UNION ALL, keeping duplicate rows.

func (UnionQuery) All 

func (q UnionQuery) All(ctx context.Context, opts ...ExecOption) Rows

All prepares the set operation for execution against the executor the chain was built from.

func (UnionQuery) As 

func (q UnionQuery) As(alias string) sql.DerivedTable

As wraps this set-operation query as an aliased subquery (derived table), for use as a FROM/JOIN source and column handle; see SelectQuery.As.

func (UnionQuery) CTE 

func (q UnionQuery) CTE(name string, opts ...sql.CTEOption) sql.CTE

CTE wraps this set-operation query as a common table expression named name, for use as a FROM source and column handle (typically a recursive CTE). Pass sql.Recursive() / sql.Columns(...) options to configure it.

func (UnionQuery) Count 

func (q UnionQuery) Count(ctx context.Context, opts ...ExecOption) (int64, error)

Count runs SELECT COUNT(*) over the combined result (wrapped as a subquery, so the set operation applies) and returns the number of rows; see SelectQuery.Count.

func (UnionQuery) Except 

func (q UnionQuery) Except(other SelectQuery) UnionQuery

Except appends another EXCEPT operand.

func (UnionQuery) ExceptAll 

func (q UnionQuery) ExceptAll(other SelectQuery) UnionQuery

ExceptAll appends another EXCEPT ALL operand.

func (UnionQuery) First 

func (q UnionQuery) First(ctx context.Context, opts ...ExecOption) Rows

First is All limited to the first row of the combined result; see SelectQuery.First.

func (UnionQuery) Intersect 

func (q UnionQuery) Intersect(other SelectQuery) UnionQuery

Intersect appends another INTERSECT operand.

func (UnionQuery) IntersectAll 

func (q UnionQuery) IntersectAll(other SelectQuery) UnionQuery

IntersectAll appends another INTERSECT ALL operand.

func (UnionQuery) Limit 

func (q UnionQuery) Limit(n int) UnionQuery

Limit sets the LIMIT clause of the combined result.

func (UnionQuery) Offset 

func (q UnionQuery) Offset(n int) UnionQuery

Offset sets the OFFSET clause of the combined result.

func (UnionQuery) One 

func (q UnionQuery) One(ctx context.Context, opts ...ExecOption) Rows

One is All restricted to exactly one row of the combined result; see SelectQuery.One.

func (UnionQuery) OrderBy 

func (q UnionQuery) OrderBy(terms ...sql.OrderTerm) UnionQuery

OrderBy adds ORDER BY terms applied to the combined result. In a set operation, ORDER BY must reference the result's output columns by name or position (e.g. sql.Asc(sql.Col("email"))), not table-qualified columns.

func (UnionQuery) Page 

func (q UnionQuery) Page(ctx context.Context, limit, offset int, opts ...ExecOption) Rows

Page is All over a LIMIT/OFFSET window of the combined result; see SelectQuery.Page.

func (UnionQuery) SQLSource 

func (q UnionQuery) SQLSource() SourceExpression

SQLSource marks the query as a subquery source.

func (UnionQuery) ToSQL 

func (q UnionQuery) ToSQL(dialect sql.Dialect) (string, []any, error)

ToSQL compiles the query.

func (UnionQuery) Union 

func (q UnionQuery) Union(other SelectQuery) UnionQuery

Union appends another UNION operand.

func (UnionQuery) UnionAll 

func (q UnionQuery) UnionAll(other SelectQuery) UnionQuery

UnionAll appends another UNION ALL operand.

type UpdateQuery 

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

UpdateQuery is an UPDATE statement. Assignments come from typed columns, so it does not need to be generic over the row type.

func Update 

func Update(table Table) UpdateQuery

Update creates an unbound UPDATE builder, for ToSQL or tests. Assignments are created with Column.Set, so value types are checked at compile time. Executing it returns an error; start from a Database or Session to run it.

Example 
name := "Alice"
q := sqlkit.Update(Users).
	Set(Users.Name.Set(&name)).
	Where(Users.ID.Eq(7))

// Only the SQL text is printed: the bound arg is a *string, whose textual
// form is a non-deterministic pointer address.
text, _, _ := q.ToSQL(sql.PostgreSQLDialect{})
fmt.Println(text)

Output:
UPDATE "public"."users" SET "name" = $1 WHERE ("public"."users"."id" = $2)

func (UpdateQuery) AutoChunk 

func (q UpdateQuery) AutoChunk(opts ...ChunkOption) UpdateQuery

AutoChunk configures how a lone WHERE column IN whose value list exceeds the executor's bind-parameter limit is split across statements: ChunkSize(n) caps each statement at n values, ChunkOff() runs it as one statement. The chunks run atomically and their RowsAffected sum; ToSQL is unaffected.

func (UpdateQuery) CheckVersion 

func (q UpdateQuery) CheckVersion(col AnyColumn, current any) UpdateQuery

CheckVersion enables optimistic locking on the UPDATE using col as the version column. It guards the statement with WHERE col = current and bumps SET col = col + 1, so a row another transaction already advanced past current matches nothing. Exec then returns ErrVersionConflict when the statement affects no row — the row was modified concurrently (or no longer exists) — letting the caller reload and retry: 

res, err := db.Update(Users).SetStruct(u, Users.Name).
    Where(Users.ID.Eq(u.ID)).CheckVersion(Users.Version, u.Version).Exec(ctx)
if errors.Is(err, sqlkit.ErrVersionConflict) { /* reload and retry */ }

Pair it with Where to identify the row (typically by primary key); the version guard is ANDed onto that predicate. The version column is referenced unqualified, so CheckVersion targets a single-table UPDATE.

func (UpdateQuery) Exec 

func (q UpdateQuery) Exec(ctx context.Context, opts ...ExecOption) (stdsql.Result, error)

Exec runs the UPDATE statement and returns the database/sql result. When a lone column IN in the WHERE carries more values than the executor's bind-parameter limit allows, the value list is split across several statements run atomically (one transaction through a Database, the session's transaction through a Session); the returned result then sums their RowsAffected.

func (UpdateQuery) Filter 

func (q UpdateQuery) Filter(filter any) UpdateQuery

Filter narrows the UPDATE with a reusable filter bundle applied against the target table's columns: either a Predicate, or a struct whose set fields map to columns by name and hold FilterTerm values (sqlkit.Le(20), …) or predicates. It is the UPDATE counterpart of SelectQuery.Filter, so one filter bundle narrows a read or a write alike. A mapping error is deferred to the terminal.

func (UpdateQuery) From 

func (q UpdateQuery) From(sources ...sql.Source) UpdateQuery

From adds correlated sources to the UPDATE (PostgreSQL UPDATE ... FROM / MySQL multi-table UPDATE); correlate them with Where.

func (UpdateQuery) Join 

func (q UpdateQuery) Join(source sql.Source, on Predicate) UpdateQuery

Join joins source on the predicate. It renders as an inline join on MySQL and as a correlated FROM with the predicate folded into WHERE on PostgreSQL.

func (UpdateQuery) JoinRel 

func (q UpdateQuery) JoinRel(rel Relation) UpdateQuery

JoinRel correlates the UPDATE to a relation's target on its foreign-key condition (PostgreSQL UPDATE ... FROM / MySQL inline JOIN), so callers never spell out the ON clause.

func (UpdateQuery) LeftJoin 

func (q UpdateQuery) LeftJoin(source sql.Source, on Predicate) UpdateQuery

LeftJoin is Join with a LEFT JOIN (MySQL inline form only).

func (UpdateQuery) Returning 

func (q UpdateQuery) Returning(selections ...sql.Selection) ReturningQuery

Returning adds an explicit RETURNING clause and makes the statement yield rows: end the chain with All(...).Scan to read them. With no arguments it returns all table columns.

func (UpdateQuery) Set 

func (q UpdateQuery) Set(assignments ...sql.Assignment) UpdateQuery

Set appends SET assignments.

func (UpdateQuery) SetStruct 

func (q UpdateQuery) SetStruct(row any, columns ...AnyColumn) UpdateQuery

SetStruct appends SET assignments taken from a struct's fields, mapping fields to columns by name the same way Insert().Values and Scan do (sqlkit tag, exact name, or snake_case). It is the UPDATE counterpart of Insert().Values for a single row, and composes with Set: assignments accumulate.

With no columns, every column the struct maps to is assigned except the primary key, which identifies the row and belongs in Where rather than SET, and except read-only fields (`sqlkit:",readonly"`, for database-generated columns). Pass columns to restrict the update to just those — a partial update that leaves every other column untouched; a primary key or read-only column is included when named explicitly. Unlike a non-zero "patch", zero-valued fields are written, so a column can be cleared to its zero value.

A field tagged `sqlkit:",version"` enables optimistic locking on an implicit update: its column is bumped (col = col + 1) and the row is guarded on the field's current value, so Exec returns ErrVersionConflict when another writer advanced it first — the tag-driven equivalent of CheckVersion. A field tagged `sqlkit:",json"` is written as its JSON encoding.

SetStruct only builds SET assignments (plus the version guard above); pair it with Where to choose the rows: 

db.Update(Users).SetStruct(u, Users.Name, Users.Email).Where(Users.ID.Eq(u.ID)).Exec(ctx)

row may be a struct or a pointer to one. A field-mapping failure (not a struct, a nil pointer, or an explicitly named column with no matching field) is deferred and returned by ToSQL and Exec.

func (UpdateQuery) ToSQL 

func (q UpdateQuery) ToSQL(dialect sql.Dialect) (string, []any, error)

ToSQL compiles the UPDATE statement. A deferred IN is folded into a single statement carrying the full value list, independent of any configured bind-parameter limit (which only affects Exec).

func (UpdateQuery) Where 

func (q UpdateQuery) Where(predicates ...Predicate) UpdateQuery

Where adds predicates, combined with AND. A lone column IN whose value list is large enough to exceed the executor's bind-parameter limit is held back and split across statements by Exec; see WithAutoChunk.

func (UpdateQuery) WithCTE 

func (q UpdateQuery) WithCTE(ctes ...sql.CTE) UpdateQuery

WithCTE prepends common table expressions to the UPDATE, emitting a leading WITH clause. Reference them from a WHERE subquery.

Source Files

View all Source files

Directories

Path Synopsis
Package analyze is a small, read-only analysis framework over the sqlkit SQL AST.
 Package analyze is a small, read-only analysis framework over the sqlkit SQL AST.
Package codegen generates Go source from a database schema model (meta.Metadata): the typed table bindings that the sqlkit query builder is driven from, and optionally a row struct (DTO) per table.
 Package codegen generates Go source from a database schema model (meta.Metadata): the typed table bindings that the sqlkit query builder is driven from, and optionally a row struct (DTO) per table.
Package decl declares database schemas as plain Go values that build directly into the meta model.
 Package decl declares database schemas as plain Go values that build directly into the meta model.
internal
reflectmap
Package reflectmap maps struct fields to database columns, the single definition of the name-mapping rules (sqlkit tag, exact name, snake_case, flattened embeds) shared by result scanning, INSERT value extraction, and the ORM preloader.
 Package reflectmap maps struct fields to database columns, the single definition of the name-mapping rules (sqlkit tag, exact name, snake_case, flattened embeds) shared by result scanning, INSERT value extraction, and the ORM preloader.
testutil
Package testutil provides a scriptable database/sql driver for testing sqlkit without a live database.
 Package testutil provides a scriptable database/sql driver for testing sqlkit without a live database.
Package introspect reads database schema metadata into the schema model.
 Package introspect reads database schema metadata into the schema model.
Package meta is the database schema metadata model: the flat, value-only abstraction of tables and columns that sqlkit's SQL generators consume.
 Package meta is the database schema metadata model: the flat, value-only abstraction of tables and columns that sqlkit's SQL generators consume.
Package migrate runs database schema migrations written with callbacks that receive an Operator.
 Package migrate runs database schema migrations written with callbacks that receive an Operator.
autogen
Package autogen generates a migration by diffing a source schema against a target schema.
 Package autogen generates a migration by diffing a source schema against a target schema.
Package querytrace is an application-level SQL tracing middleware for database/sql.
 Package querytrace is an application-level SQL tracing middleware for database/sql.
driver
Package driver wraps a database/sql/driver so that statements run through it are recorded on the querytrace.Trace carried by their context.
 Package driver wraps a database/sql/driver so that statements run through it are recorded on the querytrace.Trace carried by their context.
Package redact masks the sensitive string literals embedded in a parsed SQL statement.
 Package redact masks the sensitive string literals embedded in a parsed SQL statement.
Package sql contains SQL query builders, expression nodes, and the compiler that turns them into SQL text plus bind arguments.
 Package sql contains SQL query builders, expression nodes, and the compiler that turns them into SQL text plus bind arguments.
Package sqlparse turns raw SQL text into the query and expression nodes of package github.com/aita/sqlkit/sql — the inverse of sqlkit's build-then- compile flow.
 Package sqlparse turns raw SQL text into the query and expression nodes of package github.com/aita/sqlkit/sql — the inverse of sqlkit's build-then- compile flow.
postgres module
Package sqltest is sqlkit's toolkit for testing SQL code.
 Package sqltest is sqlkit's toolkit for testing SQL code.
Package uow provides a Unit of Work over sqlkit table bindings.
 Package uow provides a Unit of Work over sqlkit table bindings.
x
querytrace module
sqlparse module
sqlparse/postgres module

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.