1.api.md

title	API Reference
description	Complete API reference for the soy package
author	zoobzio
published	2025-12-15
updated	2025-12-19
tags	Reference API Functions Types

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.

Select[T]

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.

Query[T]

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.

Compound[T]

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)

Create[T]

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.

Update[T]

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.

Delete[T]

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)

Aggregate[T]

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

Tag	Purpose	Example
db	Column name	db:"email"
type	SQL column type	type:"text", type:"serial", type:"vector(1536)"
constraints	Column constraints	constraints:"primary key", constraints:"not null unique"
default	Default value	default:"now()", default:"0"
check	Check constraint	check:"age >= 0"
index	Create index	index:"true"
references	Foreign key	references:"users(id)"

Operators

Comparison

Operator	Description
=	Equals
!=	Not equals
>	Greater than
>=	Greater than or equal
<	Less than
<=	Less than or equal

Pattern

Operator	Description
LIKE	Case-sensitive pattern
NOT LIKE	Negated LIKE
ILIKE	Case-insensitive pattern
NOT ILIKE	Negated ILIKE

Set

Operator	Description
IN	Value in list
NOT IN	Value not in list

Regex (PostgreSQL)

Operator	Description
~	Case-sensitive regex
~*	Case-insensitive regex
!~	Negated regex
!~*	Negated case-insensitive regex

Array

Operator	Description
@>	Contains
<@	Contained by
&&	Overlap

Vector (pgvector)

Operator	Description
<->	L2 (Euclidean) distance
<#>	Inner product distance
<=>	Cosine distance
<+>	L1 (Manhattan) distance

Arithmetic

Operator	Description
+	Addition
-	Subtraction
*	Multiplication
/	Division
%	Modulo

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

Method	SQL Output
SelectUpper(field, alias)	UPPER("field") AS "alias"
SelectLower(field, alias)	LOWER("field") AS "alias"
SelectLength(field, alias)	LENGTH("field") AS "alias"
SelectTrim(field, alias)	TRIM("field") AS "alias"
SelectLTrim(field, alias)	LTRIM("field") AS "alias"
SelectRTrim(field, alias)	RTRIM("field") AS "alias"
SelectSubstring(field, startParam, lengthParam, alias)	SUBSTRING("field", :start, :length) AS "alias"
SelectReplace(field, searchParam, replaceParam, alias)	REPLACE("field", :search, :replace) AS "alias"
SelectConcat(alias, fields...)	CONCAT("field1", "field2") AS "alias"

Math Functions

Method	SQL Output
SelectAbs(field, alias)	ABS("field") AS "alias"
SelectCeil(field, alias)	CEIL("field") AS "alias"
SelectFloor(field, alias)	FLOOR("field") AS "alias"
SelectRound(field, alias)	ROUND("field") AS "alias"
SelectSqrt(field, alias)	SQRT("field") AS "alias"
SelectPower(field, exponentParam, alias)	POWER("field", :exp) AS "alias"

Date/Time Functions

Method	SQL Output
SelectNow(alias)	NOW() AS "alias"
SelectCurrentDate(alias)	CURRENT_DATE AS "alias"
SelectCurrentTime(alias)	CURRENT_TIME AS "alias"
SelectCurrentTimestamp(alias)	CURRENT_TIMESTAMP AS "alias"

Null Handling

Method	SQL Output
SelectCoalesce(alias, params...)	COALESCE(:p1, :p2, ...) AS "alias"
SelectNullIf(param1, param2, alias)	NULLIF(:p1, :p2) AS "alias"

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

Method	SQL Output
SelectSum(field, alias)	SUM("field") AS "alias"
SelectAvg(field, alias)	AVG("field") AS "alias"
SelectMin(field, alias)	MIN("field") AS "alias"
SelectMax(field, alias)	MAX("field") AS "alias"
SelectCount(field, alias)	COUNT("field") AS "alias"
SelectCountStar(alias)	COUNT(*) AS "alias"
SelectCountDistinct(field, alias)	COUNT(DISTINCT "field") AS "alias"

FILTER Aggregates (PostgreSQL)

Aggregate with FILTER clause for conditional aggregation:

Method	SQL Output
SelectSumFilter(field, condField, condOp, condParam, alias)	SUM("field") FILTER (WHERE "condField" op :param) AS "alias"
SelectAvgFilter(field, condField, condOp, condParam, alias)	AVG("field") FILTER (WHERE ...) AS "alias"
SelectMinFilter(field, condField, condOp, condParam, alias)	MIN("field") FILTER (WHERE ...) AS "alias"
SelectMaxFilter(field, condField, condOp, condParam, alias)	MAX("field") FILTER (WHERE ...) AS "alias"
SelectCountFilter(field, condField, condOp, condParam, alias)	COUNT("field") FILTER (WHERE ...) AS "alias"
SelectCountDistinctFilter(field, condField, condOp, condParam, alias)	COUNT(DISTINCT "field") FILTER (WHERE ...) AS "alias"

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

Method	Description
SelectCase()	Start a CASE expression, returns *SelectCaseBuilder
When(condition, resultParam)	Add a WHEN...THEN clause
Else(resultParam)	Set the ELSE clause
As(alias)	Set the result alias
End()	Complete and return to parent builder

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

Method	SQL Function
SelectRowNumber()	ROW_NUMBER()
SelectRank()	RANK()
SelectDenseRank()	DENSE_RANK()
SelectNtile(nParam)	NTILE(:n)
SelectLag(field, offsetParam)	LAG("field", :offset)
SelectLead(field, offsetParam)	LEAD("field", :offset)
SelectFirstValue(field)	FIRST_VALUE("field")
SelectLastValue(field)	LAST_VALUE("field")
SelectSumOver(field)	SUM("field") OVER
SelectAvgOver(field)	AVG("field") OVER
SelectCountOver()	COUNT(*) OVER
SelectMinOver(field)	MIN("field") OVER
SelectMaxOver(field)	MAX("field") OVER

Window Builder Methods

Method	Description
PartitionBy(fields...)	Add PARTITION BY clause
OrderBy(field, direction)	Add ORDER BY clause
Frame(start, end)	Add ROWS BETWEEN frame clause
As(alias)	Set result alias
End()	Complete and return to parent builder

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

Error	Description
ErrNotFound	Query expects at least one row but finds none
ErrMultipleRows	Query expects exactly one row but finds multiple
ErrNoRowsAffected	Operation expects to affect rows but affects none
ErrEmptyTableName	Table name is empty
ErrNilRenderer	Renderer is nil
ErrUnsafeUpdate	UPDATE without WHERE clause
ErrUnsafeDelete	DELETE without WHERE clause

Validation Errors

Sentinel	Matches
ErrInvalidField	Invalid field name
ErrInvalidParam	Invalid parameter name
ErrInvalidOperator	Unsupported operator
ErrInvalidDirection	Invalid sort direction
ErrInvalidNullsOrdering	Invalid NULLS FIRST/LAST
ErrInvalidTable	Invalid table name
ErrInvalidCondition	Invalid condition
ErrInvalidAggregateFunc	Invalid aggregate function

Query Errors

Sentinel	Matches
ErrQueryFailed	Query execution failure
ErrScanFailed	Result scanning failure
ErrIterationFailed	Row iteration failure
ErrRenderFailed	Query rendering failure

Builder Errors

Sentinel	Matches
ErrBuilderHasErrors	Builder accumulated 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)
}