API Reference

Complete API reference for the github.com/zoobz-io/soy package.

Soy

The main coordinator type. Create one per table.

New

func New[T any](db *sqlx.DB, tableName string, renderer astql.Renderer) (*Soy[T], error)

Creates a new soy instance for the given struct type and table name with the specified SQL renderer. Inspects struct tags, builds schema, and caches metadata.

Available renderers from github.com/zoobz-io/astql/pkg:

• postgres.New() - PostgreSQL
• mariadb.New() - MariaDB
• sqlite.New() - SQLite
• mssql.New() - Microsoft SQL Server

Methods

Select

func (c *Soy[T]) Select() *Select[T]

Returns a builder for single-record SELECT queries.

Query

func (c *Soy[T]) Query() *Query[T]

Returns a builder for multi-record SELECT queries.

Insert

func (c *Soy[T]) Insert() *Create[T]

Returns a builder for INSERT operations.

Modify

func (c *Soy[T]) Modify() *Update[T]

Returns a builder for UPDATE operations.

Remove

func (c *Soy[T]) Remove() *Delete[T]

Returns a builder for DELETE operations.

Count

func (c *Soy[T]) Count() *Aggregate[T]

Returns a builder for COUNT(*) aggregates.

Sum

func (c *Soy[T]) Sum(field string) *Aggregate[T]

Returns a builder for SUM aggregates on the specified field.

Avg

func (c *Soy[T]) Avg(field string) *Aggregate[T]

Returns a builder for AVG aggregates on the specified field.

Min

func (c *Soy[T]) Min(field string) *Aggregate[T]

Returns a builder for MIN aggregates on the specified field.

Max

func (c *Soy[T]) Max(field string) *Aggregate[T]

Returns a builder for MAX aggregates on the specified field.

Lifecycle Callbacks

OnScan

func (c *Soy[T]) OnScan(fn func(ctx context.Context, result *T) error)

Registers a callback that fires after scanning a row into *T. Called in Select, Query, Update, Create, and Compound execution paths. Pass nil to unregister.

OnRecord

func (c *Soy[T]) OnRecord(fn func(ctx context.Context, record *T) error)

Registers a callback that fires before writing a *T. Called in Create execution paths (single insert, batch insert, upsert) before the INSERT is executed. Pass nil to unregister.

Spec Methods

QueryFromSpec

func (c *Soy[T]) QueryFromSpec(spec QuerySpec) *Query[T]

Creates a Query builder from a JSON-serializable spec.

SelectFromSpec

func (c *Soy[T]) SelectFromSpec(spec SelectSpec) *Select[T]

Creates a Select builder from a JSON-serializable spec.

InsertFromSpec

func (c *Soy[T]) InsertFromSpec(spec CreateSpec) *Create[T]

Creates a Create builder from a JSON-serializable spec.

ModifyFromSpec

func (c *Soy[T]) ModifyFromSpec(spec UpdateSpec) *Update[T]

Creates an Update builder from a JSON-serializable spec.

RemoveFromSpec

func (c *Soy[T]) RemoveFromSpec(spec DeleteSpec) *Delete[T]

Creates a Delete builder from a JSON-serializable spec.

CountFromSpec

func (c *Soy[T]) CountFromSpec(spec AggregateSpec) *Aggregate[T]

Creates a COUNT aggregate from a spec.

SumFromSpec

func (c *Soy[T]) SumFromSpec(spec AggregateSpec) *Aggregate[T]

Creates a SUM aggregate from a spec. Uses spec.Field as the column to sum.

AvgFromSpec

func (c *Soy[T]) AvgFromSpec(spec AggregateSpec) *Aggregate[T]

Creates an AVG aggregate from a spec. Uses spec.Field as the column to average.

MinFromSpec

func (c *Soy[T]) MinFromSpec(spec AggregateSpec) *Aggregate[T]

Creates a MIN aggregate from a spec. Uses spec.Field as the column.

MaxFromSpec

func (c *Soy[T]) MaxFromSpec(spec AggregateSpec) *Aggregate[T]

Creates a MAX aggregate from a spec. Uses spec.Field as the column.

CompoundFromSpec

func (c *Soy[T]) CompoundFromSpec(spec CompoundQuerySpec) *Compound[T]

Creates a Compound builder from a JSON-serializable spec.

SelectT

Builder for single-record SELECT queries.

Methods

Fields

func (s *Select[T]) Fields(fields ...string) *Select[T]

Selects specific columns. Returns all columns if not called.

Where

func (s *Select[T]) Where(field, operator, param string) *Select[T]

Adds a WHERE condition. Multiple calls produce AND.

WhereAnd

func (s *Select[T]) WhereAnd(conditions ...Condition) *Select[T]

Adds grouped AND conditions.

WhereOr

func (s *Select[T]) WhereOr(conditions ...Condition) *Select[T]

Adds grouped OR conditions.

WhereNull

func (s *Select[T]) WhereNull(field string) *Select[T]

Adds IS NULL condition.

WhereNotNull

func (s *Select[T]) WhereNotNull(field string) *Select[T]

Adds IS NOT NULL condition.

WhereBetween

func (s *Select[T]) WhereBetween(field, lowParam, highParam string) *Select[T]

Adds a WHERE field BETWEEN low AND high condition.

WhereNotBetween

func (s *Select[T]) WhereNotBetween(field, lowParam, highParam string) *Select[T]

Adds a WHERE field NOT BETWEEN low AND high condition.

WhereFields

func (s *Select[T]) WhereFields(leftField, operator, rightField string) *Select[T]

Adds a WHERE condition comparing two fields (e.g., WHERE "created_at" < "updated_at").

OrderBy

func (s *Select[T]) OrderBy(field, direction string) *Select[T]

Adds ORDER BY clause. Direction: "asc" or "desc".

OrderByNulls

func (s *Select[T]) OrderByNulls(field, direction, nulls string) *Select[T]

Adds ORDER BY with NULLS FIRST or NULLS LAST. The nulls parameter must be "first" or "last" (case insensitive).

OrderByExpr

func (s *Select[T]) OrderByExpr(field, operator, param, direction string) *Select[T]

Adds ORDER BY with expression (e.g., for pgvector).

Distinct

func (s *Select[T]) Distinct() *Select[T]

Adds DISTINCT clause.

DistinctOn

func (s *Select[T]) DistinctOn(fields ...string) *Select[T]

Adds DISTINCT ON clause (PostgreSQL).

GroupBy

func (s *Select[T]) GroupBy(fields ...string) *Select[T]

Adds GROUP BY clause.

Having

func (s *Select[T]) Having(field, operator, param string) *Select[T]

Adds HAVING condition.

HavingAgg

func (s *Select[T]) HavingAgg(function, field, operator, param string) *Select[T]

Adds HAVING with aggregate function.

ForUpdate

func (s *Select[T]) ForUpdate() *Select[T]

Adds FOR UPDATE lock.

ForNoKeyUpdate

func (s *Select[T]) ForNoKeyUpdate() *Select[T]

Adds FOR NO KEY UPDATE lock.

ForShare

func (s *Select[T]) ForShare() *Select[T]

Adds FOR SHARE lock.

ForKeyShare

func (s *Select[T]) ForKeyShare() *Select[T]

Adds FOR KEY SHARE lock.

Exec

func (s *Select[T]) Exec(ctx context.Context, params map[string]any) (*T, error)

Executes the query and returns a single result. Returns an error if no rows found.

ExecTx

func (s *Select[T]) ExecTx(ctx context.Context, tx *sqlx.Tx, params map[string]any) (*T, error)

Executes within a transaction.

ExecAtom

func (s *Select[T]) ExecAtom(ctx context.Context, params map[string]any) (*atom.Atom, error)

Executes the query and scans the result directly into an *atom.Atom. Returns an error if no rows found.

ExecTxAtom

func (s *Select[T]) ExecTxAtom(ctx context.Context, tx *sqlx.Tx, params map[string]any) (*atom.Atom, error)

Executes within a transaction and returns an *atom.Atom.

Render

func (s *Select[T]) Render() (*astql.QueryResult, error)

Returns the generated SQL as a QueryResult without executing. The QueryResult contains the SQL string and parameter information.

MustRender

func (s *Select[T]) MustRender() *astql.QueryResult

Like Render but panics on error.

QueryT

Builder for multi-record SELECT queries. Inherits all Select methods plus:

Additional Methods

Limit

func (q *Query[T]) Limit(n int) *Query[T]

Sets the maximum number of rows to return (static value).

LimitParam

func (q *Query[T]) LimitParam(param string) *Query[T]

Sets the LIMIT clause to a parameterized value. Useful for API pagination where limit comes from request parameters.

Offset

func (q *Query[T]) Offset(n int) *Query[T]

Sets the number of rows to skip (static value).

OffsetParam

func (q *Query[T]) OffsetParam(param string) *Query[T]

Sets the OFFSET clause to a parameterized value. Useful for API pagination where offset comes from request parameters.

Union

func (q *Query[T]) Union(other *Query[T]) *Compound[T]

Combines with another query using UNION.

UnionAll

func (q *Query[T]) UnionAll(other *Query[T]) *Compound[T]

Combines using UNION ALL.

Intersect

func (q *Query[T]) Intersect(other *Query[T]) *Compound[T]

Combines using INTERSECT.

IntersectAll

func (q *Query[T]) IntersectAll(other *Query[T]) *Compound[T]

Combines using INTERSECT ALL.

Except

func (q *Query[T]) Except(other *Query[T]) *Compound[T]

Combines using EXCEPT.

ExceptAll

func (q *Query[T]) ExceptAll(other *Query[T]) *Compound[T]

Combines using EXCEPT ALL.

Exec

func (q *Query[T]) Exec(ctx context.Context, params map[string]any) ([]*T, error)

Executes and returns all matching rows.

ExecTx

func (q *Query[T]) ExecTx(ctx context.Context, tx *sqlx.Tx, params map[string]any) ([]*T, error)

Executes within a transaction.

ExecAtom

func (q *Query[T]) ExecAtom(ctx context.Context, params map[string]any) ([]*atom.Atom, error)

Executes the query and scans all results directly into []*atom.Atom. Returns nil for empty results.

ExecTxAtom

func (q *Query[T]) ExecTxAtom(ctx context.Context, tx *sqlx.Tx, params map[string]any) ([]*atom.Atom, error)

Executes within a transaction and returns []*atom.Atom.

CompoundT

Builder for compound queries with set operations.

Methods

Inherits ordering and pagination methods from Query, plus:

Union, UnionAll, Intersect, IntersectAll, Except, ExceptAll

func (c *Compound[T]) Union(other *Query[T]) *Compound[T]

Chains additional set operations.

OrderBy, Limit, Offset

Applied to the final combined result.

Exec

func (c *Compound[T]) Exec(ctx context.Context, params map[string]any) ([]*T, error)

CreateT

Builder for INSERT operations.

Methods

OnConflict

func (c *Create[T]) OnConflict(columns ...string) *Create[T]

Specifies conflict columns for ON CONFLICT handling.

DoNothing

func (c *Create[T]) DoNothing() *Create[T]

Sets ON CONFLICT DO NOTHING.

DoUpdate

func (c *Create[T]) DoUpdate() *Create[T]

Sets ON CONFLICT DO UPDATE.

Set

func (c *Create[T]) Set(field, param string) *Create[T]

Adds a SET clause for DO UPDATE.

Build

func (c *Create[T]) Build() *Create[T]

Finalises the builder.

Exec

func (c *Create[T]) Exec(ctx context.Context, record *T) (*T, error)

Inserts a single record and returns the result with RETURNING.

ExecTx

func (c *Create[T]) ExecTx(ctx context.Context, tx *sqlx.Tx, record *T) (*T, error)

Inserts within a transaction.

ExecAtom

func (c *Create[T]) ExecAtom(ctx context.Context, params map[string]any) (*atom.Atom, error)

Inserts a record using a parameter map and returns the result as *atom.Atom. Requires all non-primary-key columns to be provided in params.

ExecTxAtom

func (c *Create[T]) ExecTxAtom(ctx context.Context, tx *sqlx.Tx, params map[string]any) (*atom.Atom, error)

Inserts within a transaction and returns an *atom.Atom.

ExecBatch

func (c *Create[T]) ExecBatch(ctx context.Context, records []*T) (int64, error)

Inserts multiple records and returns the count of records inserted.

UpdateT

Builder for UPDATE operations.

Methods

Set

func (u *Update[T]) Set(field, param string) *Update[T]

Adds a column to update.

SetExpr

func (u *Update[T]) SetExpr(field, operator, param string) *Update[T]

Adds a computed assignment using a binary expression: field = field <op> param. Use for atomic increments, decrements, and similar operations. Supported operators: +, -, *, /, %.

Where, WhereAnd, WhereOr, WhereNull, WhereNotNull

Same as Select. At least one WHERE is required.

Exec

func (u *Update[T]) Exec(ctx context.Context, params map[string]any) (*T, error)

Executes and returns the updated record. Returns error if no WHERE clause.

ExecTx

func (u *Update[T]) ExecTx(ctx context.Context, tx *sqlx.Tx, params map[string]any) (*T, error)

ExecBatch

func (u *Update[T]) ExecBatch(ctx context.Context, paramsList []map[string]any) (int64, error)

Updates multiple records with different parameter sets.

DeleteT

Builder for DELETE operations.

Methods

Where, WhereAnd, WhereOr, WhereNull, WhereNotNull

Same as Select. At least one WHERE is required.

Exec

func (d *Delete[T]) Exec(ctx context.Context, params map[string]any) (int64, error)

Executes and returns rows affected. Returns error if no WHERE clause.

ExecTx

func (d *Delete[T]) ExecTx(ctx context.Context, tx *sqlx.Tx, params map[string]any) (int64, error)

ExecBatch

func (d *Delete[T]) ExecBatch(ctx context.Context, paramsList []map[string]any) (int64, error)

AggregateT

Builder for aggregate queries.

Methods

Where, WhereAnd, WhereOr, WhereNull, WhereNotNull

Same as Select.

Exec

func (a *Aggregate[T]) Exec(ctx context.Context, params map[string]any) (float64, error)

Returns the aggregate result. NULL results return 0.0.

ExecTx

func (a *Aggregate[T]) ExecTx(ctx context.Context, tx *sqlx.Tx, params map[string]any) (float64, error)

Condition Helpers

C

func C(field, operator, param string) Condition

Creates a simple condition.

Null

func Null(field string) Condition

Creates an IS NULL condition.

NotNull

func NotNull(field string) Condition

Creates an IS NOT NULL condition.

Specs

JSON-serializable query definitions.

QuerySpec

type QuerySpec struct {
    Fields     []string        `json:"fields,omitempty"`
    Where      []ConditionSpec `json:"where,omitempty"`
    OrderBy    []OrderBySpec   `json:"order_by,omitempty"`
    GroupBy    []string        `json:"group_by,omitempty"`
    Having     []ConditionSpec `json:"having,omitempty"`
    HavingAgg  []HavingAggSpec `json:"having_agg,omitempty"`
    Limit      *int            `json:"limit,omitempty"`
    Offset     *int            `json:"offset,omitempty"`
    Distinct   bool            `json:"distinct,omitempty"`
    DistinctOn []string        `json:"distinct_on,omitempty"`
    ForLocking string          `json:"for_locking,omitempty"` // "update", "no_key_update", "share", "key_share"
}

SelectSpec

type SelectSpec struct {
    Fields     []string        `json:"fields,omitempty"`
    Where      []ConditionSpec `json:"where,omitempty"`
    OrderBy    []OrderBySpec   `json:"order_by,omitempty"`
    GroupBy    []string        `json:"group_by,omitempty"`
    Having     []ConditionSpec `json:"having,omitempty"`
    HavingAgg  []HavingAggSpec `json:"having_agg,omitempty"`
    Limit      *int            `json:"limit,omitempty"`
    Offset     *int            `json:"offset,omitempty"`
    Distinct   bool            `json:"distinct,omitempty"`
    DistinctOn []string        `json:"distinct_on,omitempty"`
    ForLocking string          `json:"for_locking,omitempty"` // "update", "no_key_update", "share", "key_share"
}

ConditionSpec

type ConditionSpec struct {
    // Simple condition fields
    Field    string `json:"field,omitempty"`
    Operator string `json:"operator,omitempty"`
    Param    string `json:"param,omitempty"`
    IsNull   bool   `json:"is_null,omitempty"`

    // Condition group fields (for AND/OR grouping)
    Logic string          `json:"logic,omitempty"` // "AND" or "OR"
    Group []ConditionSpec `json:"group,omitempty"` // Nested conditions
}

OrderBySpec

type OrderBySpec struct {
    Field     string `json:"field"`
    Direction string `json:"direction"`           // "asc" or "desc"
    Nulls     string `json:"nulls,omitempty"`     // "first" or "last" for NULLS FIRST/LAST
    Operator  string `json:"operator,omitempty"`  // For vector ops: "<->", "<#>", "<=>", "<+>"
    Param     string `json:"param,omitempty"`     // Parameter for expression-based ordering
}

HavingAggSpec

type HavingAggSpec struct {
    Func     string `json:"func"`               // "count", "sum", "avg", "min", "max", "count_distinct"
    Field    string `json:"field,omitempty"`    // Field to aggregate (empty for COUNT(*))
    Operator string `json:"operator"`           // Comparison operator
    Param    string `json:"param"`              // Parameter name for comparison value
}

CreateSpec

type CreateSpec struct {
    OnConflict     []string          `json:"on_conflict,omitempty"`     // Conflict columns
    ConflictAction string            `json:"conflict_action,omitempty"` // "nothing" or "update"
    ConflictSet    map[string]string `json:"conflict_set,omitempty"`    // Fields to update on conflict
}

UpdateSpec

type UpdateSpec struct {
    Set   map[string]string `json:"set"`
    Where []ConditionSpec   `json:"where"`
}

DeleteSpec

type DeleteSpec struct {
    Where []ConditionSpec `json:"where"`
}

AggregateSpec

type AggregateSpec struct {
    Field string          `json:"field,omitempty"` // Required for SUM/AVG/MIN/MAX, not used for COUNT
    Where []ConditionSpec `json:"where,omitempty"`
}

The aggregate function is determined by which method you call (CountFromSpec, SumFromSpec, etc.), not by a field in the spec.

CompoundQuerySpec

type CompoundQuerySpec struct {
    Base     QuerySpec        `json:"base"`
    Operands []SetOperandSpec `json:"operands"`
    OrderBy  []OrderBySpec    `json:"order_by,omitempty"`
    Limit    *int             `json:"limit,omitempty"`
    Offset   *int             `json:"offset,omitempty"`
}

SetOperandSpec

type SetOperandSpec struct {
    Operation string    `json:"operation"`
    Query     QuerySpec `json:"query"`
}

Operations: "union", "union_all", "intersect", "intersect_all", "except", "except_all"

Struct Tags

Operators

Comparison

Pattern

Set

Regex (PostgreSQL)

Array

Vector (pgvector)

Arithmetic

Used with SetExpr for computed UPDATE assignments.

Expression Methods

Select and Query builders support expression methods for adding computed columns to SELECT clauses.

String Functions

Math Functions

Date/Time Functions

Null Handling

Type Casting

func (s *Select[T]) SelectCast(field, castType, alias string) *Select[T]

Cast types: text, integer, bigint, smallint, numeric, real, double precision, boolean, date, time, timestamp, timestamptz, interval, uuid, json, jsonb, bytea.

Aggregate Functions

FILTER Aggregates (PostgreSQL)

Aggregate with FILTER clause for conditional aggregation:

CASE Expressions

Build SQL CASE expressions with a fluent API:

result, err := soy.Select().
    Fields("id", "name").
    SelectCase().
        When(soy.C("status", "=", "active"), "active_label").
        When(soy.C("status", "=", "pending"), "pending_label").
        Else("other_label").
        As("status_text").
        End().
    Render()
// SELECT "id", "name", CASE WHEN "status" = :active THEN :active_label ... END AS "status_text" FROM "table"

SelectCase Methods

Window Functions

Build SQL window functions with a fluent API:

result, err := soy.Query().
    Fields("id", "name", "department").
    SelectRowNumber().
        PartitionBy("department").
        OrderBy("salary", "DESC").
        As("rank").
        End().
    Render()
// SELECT "id", "name", "department", ROW_NUMBER() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rank"

Window Function Starters

Window Builder Methods

Frame bounds: "UNBOUNDED PRECEDING", "CURRENT ROW", "UNBOUNDED FOLLOWING"

Condition Helpers

Between

func Between(field, lowParam, highParam string) Condition

Creates a BETWEEN condition for use with WhereAnd/WhereOr.

NotBetween

func NotBetween(field, lowParam, highParam string) Condition

Creates a NOT BETWEEN condition for use with WhereAnd/WhereOr.

Errors

Soy uses sentinel errors that support errors.Is() and errors.As() for precise error handling.

Simple Sentinels

Validation Errors

Query Errors

Builder Errors

Usage with errors.Is

record, err := soy.Select().Where("id", "=", "id").Exec(ctx, params)
if errors.Is(err, soy.ErrNotFound) {
    // Handle missing record
}
if errors.Is(err, soy.ErrInvalidField) {
    // Handle invalid field name
}

Usage with errors.As

Extract details from validation errors:

_, err := soy.Select().Where("bad_field", "=", "id").Exec(ctx, params)

var valErr *soy.ValidationError
if errors.As(err, &valErr) {
    fmt.Printf("Invalid %s: %s\n", valErr.Kind, valErr.Name)
}

Extract details from query errors:

var qErr *soy.QueryError
if errors.As(err, &qErr) {
    fmt.Printf("Operation %s failed at %s phase\n", qErr.Operation, qErr.Phase)
}