gohan

README

gohan

Documentation: https://oddbit-project.github.io/gohan/

gohan is a SQL query builder where values are always bound and identifiers are always quoted and escaped by construction, so a caller cannot reintroduce SQL injection through the normal API.

Three escape hatches exist for trusted input only, and must never be built from request data: Raw's SQL text, Fn's function name, and Cast's type string.

Why "gohan"?

gohan is a spiritual successor to goqu, the expressive Go SQL builder. The name is a nod to that lineage: goqu reads like Goku, and in Dragon Ball, Gohan is Goku's son — the next generation, raised on what came before, carrying it forward in its own way. (Gohan is also the Japanese word for a meal of rice, which suits a library you use every day.)

What it inherits from goqu

• A fluent builder: Select, From, Where, Join, OrderBy, Insert, Update, Delete, OnConflict, Returning, CTEs and UNION read the way goqu users expect.
• Dialect awareness: one query definition renders for PostgreSQL, SQLite, ClickHouse or generic ANSI SQL.
• Map conditions (Match(map), in the spirit of goqu.Ex) and struct-based inserts and updates.
• goqu's struct-tag vocabulary: goqu:"skipinsert", goqu:"skipupdate", goqu:"omitnil" and goqu:"omitempty" are honoured, so existing record types keep working.

What it does differently

• Values are always bound. There is no interpolated mode that writes values into the SQL text, so there is no mode in which a forgotten setting turns user input into SQL.
• Identifiers are always quoted and escaped, with per-dialect rules (including ClickHouse's backslash escapes and SQLite's backtick quoting).
• Refuses dangerous or ambiguous SQL at build time: an UPDATE or DELETE without a WHERE needs an explicit All(), and ClickHouse values the driver would format unsafely are rejected.
• Builds, never runs. Build(dialect) returns SQL and arguments; execution belongs to database/sql or your driver.
• Immutable builders: every method returns a copy, so a base query can be shared and extended safely.

gohan is an independent project, written from scratch; it is not a fork of goqu and is not affiliated with its authors.

Install

go get github.com/oddbit-project/gohan

Documentation

• oddbit-project.github.io/gohan: the guides below, published as a site.
• docs/: guides for getting started, SELECT, INSERT and upsert, UPDATE and DELETE, expressions, raw SQL and escape hatches, dialects, records and struct tags, the security model and errors.
• API reference on pkg.go.dev, with a runnable example for most functions and methods.
• examples/: end-to-end programs for SQLite, PostgreSQL and ClickHouse, in a separate Go module so gohan itself gains no driver dependencies.

Overview

• Select, Insert, Update, Delete builders, each immutable — every method returns a new value and never mutates the receiver.
• Three built-in dialects: gohan.Postgres(), gohan.SQLite(), gohan.ClickHouse() (plus gohan.Generic(), ANSI SQL — never register it for MySQL, where a double-quoted string is a literal, not an identifier). gohan.ClickHouseNamed() is a fourth, opt-in ClickHouse variant with @pN named placeholders — see Named parameters; it exists because clickhouse-go's positional ? binding silently truncates a bound time.Time to seconds precision, losing the sub-second part of a DateTime64 column.
• Build(dialect) (string, []any, error) renders a statement; nothing runs a query — gohan has no I/O.
• The field subpackage (github.com/oddbit-project/gohan/field) extracts struct metadata for Insert(...).Rows(...) and Update(...).SetRecord(...); see "Struct tags" below.

String-position rule

Whether a Go string is treated as a column name or as a bound value depends on where it appears:

• Column position: the argument to Count, Sum, Avg, Min, Max, select-list columns, GROUP BY, ORDER BY, and INSERT/UPDATE column names — a string there is a column name.
• Value position: comparison right-hand sides, Fn arguments, Raw arguments, Cast's value, Case's THEN/ELSE, and Val — a string there is a bound value. Pass Col("x") when a column is meant.

SELECT, WHERE, ORDER BY, LIMIT

sql, args, err := gohan.Select("id", "name").
    From("users").
    Where(gohan.Col("active").Eq(true)).
    OrderBy(gohan.Col("name").Asc()).
    Limit(10).
    Build(gohan.Postgres())
// SELECT "id", "name" FROM "users" WHERE "active" = $1 ORDER BY "name" ASC LIMIT 10 [true]

(example_test.go's ExampleSelect.)

INSERT and upsert

sql, args, err := gohan.Insert("users").
    Columns("id", "name", "email").
    Values(1, "alice", "alice@example.com").
    OnConflict("id").
    DoUpdateExcluded("name", "email").
    Build(gohan.Postgres())
// INSERT INTO "users" ("id", "name", "email") VALUES ($1, $2, $3)
// ON CONFLICT ("id") DO UPDATE SET "name" = excluded."name", "email" = excluded."email"

(ExampleInsert_upsert.) Rows(records...) builds an INSERT from structs instead of Values/Columns; gohan.InsertColumns(rec) and gohan.RecordColumns(t) report the column list a record would use without building a statement. OnConflict(cols...).DoNothing() / .DoUpdate(map[string]any{...}) are the other two conflict actions.

UPDATE

sql, args, err := gohan.Update("users").
    Set("name", "bob").
    Where(gohan.Col("id").Eq(1)).
    Build(gohan.Postgres())
// UPDATE "users" SET "name" = $1 WHERE "id" = $2 [bob 1]

(ExampleUpdate.) SetRecord(rec, opts...) sets one col = v per updatable field of a struct, with RecordOptions (SkipZeroValues, IncludeFields, ExcludeFields, WithAutoFields) to control which fields are written.

DELETE and UPDATE require a WHERE

Delete and Update refuse to build with no WHERE clause, or one that is trivially true (e.g. And() with no arguments, or an empty NotIn) — there is no accidental "delete every row". Both fail at Build with gohan.ErrNoWhere:

sql, args, err := gohan.Delete("users").Build(gohan.Postgres())
// [] gohan: statement requires a WHERE clause; call All() to affect every row

(ExampleDelete_requiresWhere.) A caller who means to affect every row calls .All(): gohan.Delete("users").All(). On ClickHouse specifically, Delete(t).All() with no other condition renders ... WHERE 1 (ClickHouse's lightweight-delete syntax requires a WHERE clause even to select every row); on PostgreSQL/SQLite the WHERE clause is omitted entirely.

The trivially-true/false check sees through Not/Or and Val/subqueries, not just a bare And()/NotIn(): Not(gohan.Or()) (the negation of an always-false OR) is recognized as always-true and refused the same way. This check is per-condition, not per-statement: a WHERE that combines a real, non-trivial condition with a trivially-true one — e.g. Where(gohan.Col("tenant").Eq(id), gohan.Col("status").NotIn()) ("tenant, and status not in nothing") — is not refused, because the overall AND is not trivially true; it deletes/updates every row of that tenant. NotIn() with no arguments is only ever a bug in the caller's own condition-building code, not a caller expressing "no rows" — write the condition you mean instead of relying on the guard to catch this shape.

Raw: the escape hatch

Raw(sql, args...) renders sql text verbatim, substituting each ? marker with the corresponding argument (bound, or rendered inline if it is itself an Expr); ?? writes a literal ? without consuming an argument — needed because PostgreSQL itself uses a bare ? as an operator (e.g. jsonb's key-exists operator, data ? 'key'), which would otherwise collide with Raw's own placeholder syntax. Raw's SQL text is trusted input and must never be built from request data.

sql, args, err := gohan.Select().
    From("docs").
    Where(gohan.Raw("data ?? ?", "owner")).
    Build(gohan.Postgres())
// SELECT * FROM "docs" WHERE (data ? $1) [owner]

(ExampleRaw.) Note the parentheses: a Raw expression is parenthesized when it is rendered as an operand of And/Or/Not/a comparison (WHERE (data ? $1) above) or as a select-list column (SELECT (k + 1) ...), so it composes safely next to other expressions there. It renders bare — no parentheses — as an UPDATE/DO UPDATE SET value (SET "x" = y + 1, not SET "x" = (y + 1)), including through Val(Raw(...)), which unwraps to the same Raw value. ?? is rejected outright on ClickHouse (ErrRawPlaceholder) — there is no literal ? in ClickHouse Raw text. A $ followed by a digit is also rejected everywhere (it would collide with PostgreSQL's own $n placeholder syntax).

Int for integer constants

Int(n int64) renders n as an inline integer literal (negative values parenthesized), for places where a bound constant would be typed wrong. The canonical case is an integer constant inside a CASE fed to SUM on PostgreSQL: a bound int argument is sent as text, and SUM(CASE ... THEN $1 ...) fails with function sum(text) does not exist. Int avoids the bind entirely:

sql, args, err := gohan.Select(
    gohan.Sum(gohan.Case().
        When(gohan.Col("status").Eq("done"), gohan.Int(1)).
        Else(gohan.Int(0))).As("done_count"),
).From("tasks").Build(gohan.Postgres())
// SELECT SUM(CASE WHEN "status" = $1 THEN 1 ELSE 0 END) AS "done_count" FROM "tasks" [done]

(ExampleCase.) Sub(q) renders a scalar subquery, (<select>), sharing the outer statement's placeholder counter.

UNION

q := gohan.Select("id").From("t1").Union(gohan.Select("id").From("t2"))

sqlPg, _, _ := q.Build(gohan.Postgres())
// SELECT "id" FROM "t1" UNION SELECT "id" FROM "t2"

sqlCh, _, _ := q.Build(gohan.ClickHouse())
// SELECT "id" FROM "t1" UNION DISTINCT SELECT "id" FROM "t2"

(ExampleSelectBuilder_Union.) UnionAll is always UNION ALL. A UNION member cannot have its own ORDER BY, LIMIT, OFFSET, WITH, SETTINGS or UNION (ErrCompoundPart). On ClickHouse, a UNION with an outer ORDER BY/LIMIT/OFFSET is wrapped as SELECT * FROM (<union>) ORDER BY ... LIMIT ... — ClickHouse's own UNION grammar does not accept a trailing ORDER BY/LIMIT the way PostgreSQL/SQLite do.

SelectBuilder.IsCompound() reports whether a builder has any UNION members. Where/Having/ Prewhere called on a compound builder apply to the first member's core only — not the whole result set — while OrderBy/Limit/Offset apply to the compound as a whole; a caller that needs a filter applied across the whole result set (e.g. a repository layer that rejects a compound base query outright) must wrap the compound in a subquery first: gohan.From(q.As("u")).

ClickHouse-specific clauses

FeatureClickHouse gates Final(), Sample(ratio)/SampleRows(n), ArrayJoin/ LeftArrayJoin, Prewhere, and Settings(map[string]any) — each fails with ErrUnsupported on a dialect without the feature. ClickHouse identifiers additionally reject ?, @, {, } and $ followed by a digit (ErrInvalidIdentifier) — these would collide with ClickHouse's own parameterized-query and settings placeholder syntax. Values are checked recursively: a map, struct, or a statement builder passed where a value is expected fails with ErrUnsafeValue / ErrInvalidColumn rather than being sent to the driver, since clickhouse-go does not bind these safely (verified against clickhouse-go v2.40.3). This recursion also rejects a database/sql/driver.Valuer nested inside a slice, pointer or interface argument (ErrUnsafeValue) unless it implements fmt.Stringer — clickhouse-go only calls Value() on a Valuer that is itself the top-level bound argument, so a nested one would otherwise reach the driver unconverted; pass it unwrapped as its own top-level value, or via In(...), which binds each element at top level. A top-level nil pointer whose type implements driver.Valuer is bound as NULL instead of being passed to the driver, which would otherwise panic calling a value-receiver Value() method on a nil pointer. ClickHouse's LEFT JOIN fills non-matching columns with each column's type default (0, '', etc.), not NULL, unless the join_use_nulls = 1 setting is used — a gohan-built LEFT JOIN sends valid SQL either way, but code that checks a joined column for NULL needs that setting set (via .Settings(...)) to see one.

SQLite identifiers

SQLite identifiers are quoted with backticks, not double quotes: SQLite treats an unrecognized double-quoted identifier as a string literal instead of raising an error, which is exactly the kind of silent misbehavior gohan is built to avoid. gohan.SQLite()'s upsert-from-select (InsertBuilder.FromSelect combined with OnConflict) also adds a WHERE true to the last select core when it has none — SQLite's own upsert grammar is otherwise ambiguous between the SELECT's WHERE and the ON CONFLICT clause.

LIKE case sensitivity

On SQLite, LIKE is ASCII case-insensitive; on PostgreSQL, ClickHouse and Generic, LIKE is case-sensitive (use ILIKE there, gated by FeatureILike). Contains, HasPrefix and HasSuffix build a LIKE pattern with %/_ in the input escaped, so untrusted search text cannot inject its own wildcards. ContainsFold, HasPrefixFold and HasSuffixFold are their case-insensitive forms, with the same escaping: ILIKE on PostgreSQL and ClickHouse, LIKE on SQLite (ASCII letters only), ErrUnsupported on Generic.

Record shapes

gohan.RecordColumns(t) / gohan.InsertColumns(rec) (and every place gohan reads a struct — Insert(...).Rows(...), Update(...).SetRecord(...)) reject field shapes the field package cannot map unambiguously, rather than silently mapping the wrong column:

• an embedded pointer struct, an unexported or explicitly tagged embedded struct, or an ambiguous promoted field name — ErrRecordShape;
• the same column mapped by two fields — ErrRecordShape in most layouts, ErrDuplicateColumn when the second field comes from an embedded struct.

Struct tags

The field subpackage (github.com/oddbit-project/gohan/field) extracts Metadata from a struct's exported fields via GetStructMeta, honouring these struct tags:

• db:"name" / ch:"name" — the column name (checked in that order; ch is a ClickHouse-style alias for db). A value of - (db:"-") excludes the field entirely. Additional comma-separated values after the name are kept as DbOptions, except auto, which sets Auto.
• auto:"true" — marks the field as automatically generated (e.g. a serial primary key or a DB-side default); Auto fields are skipped by Insert(...).Rows(...) and, unless WithAutoFields() is passed, by Update(...).SetRecord(...).
• goqu:"skipinsert" / goqu:"skipupdate" — also set Auto (kept for compatibility with the goqu tag vocabulary). goqu:"omitnil" sets OmitNil (a nil pointer field is skipped); goqu:"omitempty" sets OmitEmpty (a zero-value field is skipped).
• grid:"sort" / grid:"filter" / grid:"search" — set Sortable / Filterable / Searchable, for callers that expose a struct through a sortable/filterable listing. grid:"auto" is an alias for auto:"true". Any other value in a grid tag is kept as a DbOptions entry.
• json:"name", xml:"name", alias:"name" — the field's Alias (the first present of alias, json, xml). Defaults to the Go field name.
• Reserved types: a struct-typed field whose type name is registered as "reserved" (time.Time by default) is treated as a leaf value, not recursed into as an embedded struct. field.AddReservedType(name) registers another type (e.g. a custom time or UUID wrapper); field.GetReservedTypes() / field.IsReservedType(name) read the registry back.
• Anonymous (embedded) struct fields are flattened into their parent's field list, unless the embedded field is a pointer, is unexported, or itself carries a db/ch tag — those shapes are rejected by the statement builders (ErrRecordShape) rather than mapped ambiguously.

Dialect support

Feature	PostgreSQL	SQLite	ClickHouse
Placeholders	$n	?	?
Identifier quoting	"..."	`...`	"..." (backslash-escaped, restricted charset)
RETURNING	yes	yes	no
ON CONFLICT upsert	yes	yes	no
INSERT ... DEFAULT VALUES	yes	yes (not with ON CONFLICT)	no
UPDATE statement	yes	yes	no
ILIKE	yes	no (use case-insensitive LIKE)	yes
UNION keyword	UNION	UNION	UNION DISTINCT
FOR UPDATE/FOR SHARE	yes	no	no
FINAL/SAMPLE/ARRAY JOIN/PREWHERE/SETTINGS	no	no	yes
Bound-argument limit	65535	32766	none

gohan.ClickHouseNamed() is the same dialect with @pN named placeholders and sql.NamedArg arguments instead of ? and bare values — everything else in the table above still applies. Use it to keep DateTime64 sub-second precision (see Named parameters).

Limitations

• Raw's SQL text, Fn's function name and Cast's type string are trusted input: never build them from request data — nothing downstream escapes them.
• The ClickHouse dialect does not build UPDATE (including the lightweight UPDATE of recent ClickHouse versions), RETURNING or ON CONFLICT upsert, and ClickHouse has no general-purpose transactions.
• ClickHouse's lightweight DELETE does not work on Distributed tables or tables with projections — a gohan-built DELETE still sends valid SQL, but whether ClickHouse accepts it depends on the table engine.
• A bound value alone in a PostgreSQL select list may need Cast(...) to give it a concrete type (PostgreSQL infers text for an untyped bound parameter in some positions).

Security

Please report vulnerabilities privately — see SECURITY.md. Do not open public issues for security problems.

Every push, pull request and weekly schedule runs Trivy over the repository (vulnerabilities, secrets, misconfigurations; results in GitHub code scanning) and over a CycloneDX SBOM generated with cyclonedx-gomod; CRITICAL/HIGH findings fail the build. Tagged releases carry sbom.json. Gohan has no runtime dependencies, so its SBOM lists no components — test-only dependencies and the drivers used by the separate examples/ module are still covered by the repository scan. Locally: make sbom, make scan.

License

Apache-2.0. See LICENSE.

Documentation

Overview

Package gohan is a SQL query builder where values are always bound and identifiers are always quoted and escaped by construction, so a caller cannot reintroduce SQL injection through the normal API.

Three escape hatches exist for trusted input only, and must never be built from request data: Raw's sql text, Fn's function name, and Cast's type string.

String position rule: whether a string is treated as a column name or as a bound value depends on where it appears.

• Column position: the argument to Count, Sum, Avg, Min, Max, select lists, GROUP BY, ORDER BY, and INSERT/UPDATE column names is a column name if it is a string.
• Value position: comparison right-hand sides, Fn arguments, Raw arguments, Cast's value, Case's THEN/ELSE, and Val treat a string as a bound value. Pass Col("x") when a column is meant.

Generic assumes ANSI double-quoted identifiers and must not be registered for MySQL, where a double-quoted string is a string literal, not an identifier.

On SQLite, LIKE is ASCII case-insensitive; on PostgreSQL, ClickHouse and Generic, LIKE is case-sensitive.

Index

• Constants
• func InsertColumns(rec any) ([]string, error)
• func IsEmpty(cond Expr) bool
• func IsTrivial(cond Expr) bool
• func RecordColumns(t reflect.Type) ([]string, error)
• func Register(driverName string, d Dialect)
• type CaseBuilder
  • func Case() CaseBuilder
  • func (c CaseBuilder) Else(v any) Value
  • func (c CaseBuilder) End() Value
  • func (c CaseBuilder) When(cond Expr, then any) CaseBuilder
• type ConflictBuilder
  • func (c ConflictBuilder) DoNothing() *InsertBuilder
  • func (c ConflictBuilder) DoUpdate(set map[string]any) *InsertBuilder
  • func (c ConflictBuilder) DoUpdateExcluded(cols ...string) *InsertBuilder
• type DeleteBuilder
  • func Delete(table any) *DeleteBuilder
  • func (d *DeleteBuilder) All() *DeleteBuilder
  • func (d *DeleteBuilder) Build(dl Dialect) (string, []any, error)
  • func (d *DeleteBuilder) Returning(cols ...any) *DeleteBuilder
  • func (d *DeleteBuilder) Where(conds ...Expr) *DeleteBuilder
• type Dialect
  • func ClickHouse() Dialect
  • func ClickHouseNamed() Dialect
  • func DialectFor(driverName string) (Dialect, error)
  • func Generic() Dialect
  • func Postgres() Dialect
  • func SQLite() Dialect
  • func (d Dialect) Has(f Feature) bool
  • func (d Dialect) MaxArgs() int
  • func (d Dialect) Name() string
  • func (d Dialect) QuoteIdent(name string) (string, error)
• type Error
  • func (e Error) Error() string
• type Expr
  • func Star() Expr
• type Feature
• type InsertBuilder
  • func Insert(table any) *InsertBuilder
  • func (b *InsertBuilder) Build(d Dialect) (string, []any, error)
  • func (b *InsertBuilder) Columns(cols ...string) *InsertBuilder
  • func (b *InsertBuilder) DefaultValues() *InsertBuilder
  • func (b *InsertBuilder) FromSelect(q *SelectBuilder) *InsertBuilder
  • func (b *InsertBuilder) OnConflict(cols ...string) ConflictBuilder
  • func (b *InsertBuilder) Returning(cols ...any) *InsertBuilder
  • func (b *InsertBuilder) Rows(records ...any) *InsertBuilder
  • func (b *InsertBuilder) SetMap(m map[string]any) *InsertBuilder
  • func (b *InsertBuilder) Values(vals ...any) *InsertBuilder
• type Order
  • func (o Order) NullsFirst() Order
  • func (o Order) NullsLast() Order
• type RecordOption
  • func ExcludeFields(names ...string) RecordOption
  • func IncludeFields(names ...string) RecordOption
  • func SkipZeroValues() RecordOption
  • func WithAutoFields() RecordOption
• type SelectBuilder
  • func From(table any) *SelectBuilder
  • func Select(cols ...any) *SelectBuilder
  • func (s *SelectBuilder) AddColumns(cols ...any) *SelectBuilder
  • func (s *SelectBuilder) ArrayJoin(cols ...any) *SelectBuilder
  • func (s *SelectBuilder) As(alias string) Subquery
  • func (s *SelectBuilder) Build(d Dialect) (string, []any, error)
  • func (s *SelectBuilder) Columns(cols ...any) *SelectBuilder
  • func (s *SelectBuilder) CrossJoin(t any) *SelectBuilder
  • func (s *SelectBuilder) Distinct() *SelectBuilder
  • func (s *SelectBuilder) Final() *SelectBuilder
  • func (s *SelectBuilder) ForKeyShare(of ...string) *SelectBuilder
  • func (s *SelectBuilder) ForNoKeyUpdate(of ...string) *SelectBuilder
  • func (s *SelectBuilder) ForShare(of ...string) *SelectBuilder
  • func (s *SelectBuilder) ForUpdate(of ...string) *SelectBuilder
  • func (s *SelectBuilder) From(table any) *SelectBuilder
  • func (s *SelectBuilder) FullJoin(t any, on Expr) *SelectBuilder
  • func (s *SelectBuilder) GroupBy(cols ...any) *SelectBuilder
  • func (s *SelectBuilder) Having(conds ...Expr) *SelectBuilder
  • func (s *SelectBuilder) IsCompound() bool
  • func (s *SelectBuilder) Join(t any, on Expr) *SelectBuilder
  • func (s *SelectBuilder) JoinUsing(t any, cols ...string) *SelectBuilder
  • func (s *SelectBuilder) LeftArrayJoin(cols ...any) *SelectBuilder
  • func (s *SelectBuilder) LeftJoin(t any, on Expr) *SelectBuilder
  • func (s *SelectBuilder) LeftJoinUsing(t any, cols ...string) *SelectBuilder
  • func (s *SelectBuilder) Limit(n uint64) *SelectBuilder
  • func (s *SelectBuilder) NoWait() *SelectBuilder
  • func (s *SelectBuilder) Offset(n uint64) *SelectBuilder
  • func (s *SelectBuilder) OrderBy(o ...any) *SelectBuilder
  • func (s *SelectBuilder) Prewhere(conds ...Expr) *SelectBuilder
  • func (s *SelectBuilder) RightJoin(t any, on Expr) *SelectBuilder
  • func (s *SelectBuilder) Sample(ratio float64) *SelectBuilder
  • func (s *SelectBuilder) SampleRows(n uint64) *SelectBuilder
  • func (s *SelectBuilder) Settings(m map[string]any) *SelectBuilder
  • func (s *SelectBuilder) SkipLocked() *SelectBuilder
  • func (s *SelectBuilder) Union(q *SelectBuilder) *SelectBuilder
  • func (s *SelectBuilder) UnionAll(q *SelectBuilder) *SelectBuilder
  • func (s *SelectBuilder) Where(conds ...Expr) *SelectBuilder
  • func (s *SelectBuilder) With(name string, q *SelectBuilder) *SelectBuilder
  • func (s *SelectBuilder) WithRecursive(name string, q *SelectBuilder) *SelectBuilder
• type Statement
• type Subquery
• type TableRef
  • func Table(name string) TableRef
  • func (t TableRef) As(alias string) TableRef
  • func (t TableRef) Col(name string) Value
• type UpdateBuilder
  • func Update(table any) *UpdateBuilder
  • func (b *UpdateBuilder) All() *UpdateBuilder
  • func (b *UpdateBuilder) Build(d Dialect) (string, []any, error)
  • func (b *UpdateBuilder) Returning(cols ...any) *UpdateBuilder
  • func (b *UpdateBuilder) Set(col string, v any) *UpdateBuilder
  • func (b *UpdateBuilder) SetMap(m map[string]any) *UpdateBuilder
  • func (b *UpdateBuilder) SetRecord(rec any, opts ...RecordOption) *UpdateBuilder
  • func (b *UpdateBuilder) Where(conds ...Expr) *UpdateBuilder
• type Value
  • func And(exprs ...Expr) Value
  • func Avg(col any) Value
  • func Cast(v any, typ string) Value
  • func Col(name string) Value
  • func Count(col any) Value
  • func CountAll() Value
  • func Excluded(col string) Value
  • func Exists(q *SelectBuilder) Value
  • func Fn(name string, args ...any) Value
  • func Int(n int64) Value
  • func Match(fields map[string]any) Value
  • func Max(col any) Value
  • func Min(col any) Value
  • func Not(e Expr) Value
  • func NotExists(q *SelectBuilder) Value
  • func Or(exprs ...Expr) Value
  • func Raw(sql string, args ...any) Value
  • func Sub(q *SelectBuilder) Value
  • func Sum(col any) Value
  • func Val(v any) Value
  • func (v Value) As(alias string) Expr
  • func (v Value) Asc() Order
  • func (v Value) Between(lo, hi any) Value
  • func (v Value) Contains(s string) Value
  • func (v Value) ContainsFold(s string) Value
  • func (v Value) Desc() Order
  • func (v Value) Eq(x any) Value
  • func (v Value) Gt(x any) Value
  • func (v Value) Gte(x any) Value
  • func (v Value) HasPrefix(s string) Value
  • func (v Value) HasPrefixFold(s string) Value
  • func (v Value) HasSuffix(s string) Value
  • func (v Value) HasSuffixFold(s string) Value
  • func (v Value) ILike(p any) Value
  • func (v Value) In(values ...any) Value
  • func (v Value) IsNotNull() Value
  • func (v Value) IsNull() Value
  • func (v Value) Like(p any) Value
  • func (v Value) Lt(x any) Value
  • func (v Value) Lte(x any) Value
  • func (v Value) Neq(x any) Value
  • func (v Value) NotBetween(lo, hi any) Value
  • func (v Value) NotILike(p any) Value
  • func (v Value) NotIn(values ...any) Value
  • func (v Value) NotLike(p any) Value

Examples

• And
• Case
• CaseBuilder.End
• Cast
• ClickHouseNamed
• Col
• ConflictBuilder.DoNothing
• ConflictBuilder.DoUpdate
• Delete
• Delete (RequiresWhere)
• DeleteBuilder.All
• DeleteBuilder.Where
• Dialect
• Dialect.Has
• Dialect.QuoteIdent
• DialectFor
• Error
• Exists
• Fn
• From
• Insert
• Insert (Upsert)
• InsertBuilder.DefaultValues
• InsertBuilder.FromSelect
• InsertBuilder.FromSelect (SqliteUpsert)
• InsertBuilder.Returning
• InsertBuilder.Rows
• InsertBuilder.SetMap
• InsertBuilder.Values
• InsertColumns
• Int
• IsTrivial
• Match
• Not
• NotExists
• Or
• Raw
• RecordColumns
• Register
• Select
• SelectBuilder.ArrayJoin
• SelectBuilder.As
• SelectBuilder.Distinct
• SelectBuilder.Final
• SelectBuilder.ForShare
• SelectBuilder.ForUpdate
• SelectBuilder.GroupBy
• SelectBuilder.IsCompound
• SelectBuilder.Join
• SelectBuilder.JoinUsing
• SelectBuilder.LeftJoin
• SelectBuilder.Limit
• SelectBuilder.Offset
• SelectBuilder.OrderBy
• SelectBuilder.Prewhere
• SelectBuilder.Sample
• SelectBuilder.Settings
• SelectBuilder.Union
• SelectBuilder.UnionAll
• SelectBuilder.Where
• SelectBuilder.With
• SelectBuilder.WithRecursive
• Sub
• Table
• Update
• Update (ClickHouse)
• UpdateBuilder.All
• UpdateBuilder.SetMap
• UpdateBuilder.SetRecord
• UpdateBuilder.Where
• Val
• Value.As
• Value.Between
• Value.Contains
• Value.ContainsFold
• Value.Eq
• Value.HasPrefix
• Value.ILike
• Value.In
• Value.In (Subquery)
• Value.Like
• Value.NotIn

Constants

const (
	ErrUnknownDialect    = Error("gohan: unknown or zero dialect")
	ErrInvalidIdentifier = Error("gohan: invalid identifier")
	ErrUnsupported       = Error("gohan: not supported by dialect")
	ErrRawArgs           = Error("gohan: raw placeholder count does not match arguments")
	ErrRawPlaceholder    = Error("gohan: raw sql contains a forbidden placeholder sequence")
	ErrInvalidFunction   = Error("gohan: invalid function name")
	ErrInvalidType       = Error("gohan: invalid cast type")
	ErrEmptyMatch        = Error("gohan: Match requires at least one field")
	ErrEmptyCase         = Error("gohan: CASE requires at least one WHEN")
	ErrUnsafeValue       = Error("gohan: value type cannot be bound safely for this dialect")
	ErrTooManyArgs       = Error("gohan: statement exceeds the dialect's bound-argument limit")
	ErrNilExpr           = Error("gohan: nil or zero expression")
	ErrNoWhere           = Error("gohan: statement requires a WHERE clause; call All() to affect every row")
	ErrNoTable           = Error("gohan: statement has no table")
	ErrNeedAlias         = Error("gohan: subquery in FROM requires an alias")
	ErrInvalidColumn     = Error("gohan: column must be a string or an expression")
	ErrInvalidLimit      = Error("gohan: LIMIT/OFFSET out of range for dialect")

	ErrNoColumns        = Error("gohan: statement has no columns to write")
	ErrValueCount       = Error("gohan: value count does not match column count")
	ErrInsertMixed      = Error("gohan: Rows, Values, SetMap, FromSelect and DefaultValues cannot be combined")
	ErrInvalidRecord    = Error("gohan: record must be a non-nil struct or pointer to struct")
	ErrRecordType       = Error("gohan: all records must have the same type")
	ErrRecordShape      = Error("gohan: record type has a field shape gohan cannot map safely")
	ErrInconsistentOmit = Error("gohan: records disagree on omitted fields")
	ErrUnknownField     = Error("gohan: field not found in record")
	ErrDuplicateColumn  = Error("gohan: column set more than once")
	ErrConflictTarget   = Error("gohan: DO UPDATE requires conflict columns")

	ErrCompoundPart   = Error("gohan: a UNION member cannot have ORDER BY, LIMIT, OFFSET, WITH, SETTINGS, its own UNION or a row-locking clause")
	ErrInvalidSample  = Error("gohan: SAMPLE ratio must be in (0, 1]")
	ErrInvalidSetting = Error("gohan: invalid SETTINGS name")
	ErrEmptyClause    = Error("gohan: clause requires at least one column")
	ErrInvalidLock    = Error("gohan: invalid row-locking clause")
)

Functions

func InsertColumns

func InsertColumns(rec any) ([]string, error)

InsertColumns returns the columns an INSERT of rec would write: every mapped column except Auto fields, a nil pointer with OmitNil, and a zero value with OmitEmpty.

Example

ExampleInsertColumns lists the columns an INSERT of a record would write: auto fields, nil omitnil pointers and zero omitempty values are left out.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	type Post struct {
		ID    int64   `db:"id" auto:"true"`
		Title string  `db:"title"`
		Body  string  `db:"body" goqu:"omitempty"`
		Slug  *string `db:"slug" goqu:"omitnil"`
	}

	cols, err := gohan.InsertColumns(Post{Title: "hello"})
	fmt.Println(cols, err)

	slug := "hello"
	cols, err = gohan.InsertColumns(Post{Title: "hello", Body: "...", Slug: &slug})
	fmt.Println(cols, err)
}

Output:
[title] <nil>
[title body slug] <nil>

func IsEmpty

func IsEmpty(cond Expr) bool

IsEmpty reports whether cond places no condition at all: nil, the zero Value, a nil *Value/*Order, or And() with no elements (directly or nested only in other empty Ands).

func IsTrivial

func IsTrivial(cond Expr) bool

IsTrivial reports whether cond does not restrict rows: it IsEmpty, it is syntactically always true (the existing trivial flag), or it references no column at all — a constant such as Raw("1=1"), Raw("true"), Raw("? = ?", 1, 1) or Val(1).Eq(1).

Known limits (not detected): a column compared with itself (Col("id").Eq(Col("id"))), an uncorrelated subquery (its referenced table still counts as a column), and a Raw function call on constants (Raw("abs(1) = 1") — "abs" counts as an identifier).

Example

ExampleIsTrivial shows that IsTrivial catches a column-free Raw condition, not just the empty/always-true cases the builders already know about. A condition that names at least one column is not trivial, even wrapped in a constant.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	fmt.Println(gohan.IsTrivial(nil))
	fmt.Println(gohan.IsTrivial(gohan.And()))
	fmt.Println(gohan.IsTrivial(gohan.Raw("1=1")))
	fmt.Println(gohan.IsTrivial(gohan.Col("a").Eq(1)))
}

Output:
true
true
true
false

func RecordColumns

func RecordColumns(t reflect.Type) ([]string, error)

RecordColumns returns every column t maps (Auto fields included, in field.GetStructMeta order), after the same shape and duplicate checks as recordValues. t may be a struct type or a pointer to one.

Example

ExampleRecordColumns lists every column a struct type maps, auto fields included (e.g. for a SELECT list).

package main

import (
	"fmt"
	"reflect"

	"github.com/oddbit-project/gohan"
)

func main() {
	type User struct {
		ID       int64  `db:"id" auto:"true"`
		Name     string `db:"name"`
		Email    string `db:"email"`
		Password string `db:"-"`
	}

	cols, err := gohan.RecordColumns(reflect.TypeOf(User{}))
	fmt.Println(cols, err)

	sql, _, err := gohan.Select(toAny(cols)...).From("users").Build(gohan.Postgres())
	fmt.Println(sql, err)
}

// toAny converts a []string into the []any that Select accepts.
func toAny(cols []string) []any {
	out := make([]any, len(cols))
	for i, c := range cols {
		out[i] = c
	}
	return out
}

Output:
[id name email] <nil>
SELECT "id", "name", "email" FROM "users" <nil>

func Register

func Register(driverName string, d Dialect)

Register associates driverName with d, overriding any previous registration.

Example

ExampleRegister maps another driver name to a built-in dialect.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	gohan.Register("libsql", gohan.SQLite())

	d, err := gohan.DialectFor("libsql")
	fmt.Println(d.Name(), err)
}

Output:
sqlite <nil>

Types

type CaseBuilder

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

CaseBuilder builds a CASE WHEN ... THEN ... [ELSE ...] END expression.

func Case

func Case() CaseBuilder

Case starts a CASE expression.

Example

ExampleCase shows Int for an integer constant inside a CASE expression: on PostgreSQL, a bound constant here would be typed as text and fail SUM's type check ("function sum(text) does not exist"), so integer constants use Int, not a bound value.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Select(
		gohan.Sum(gohan.Case().
			When(gohan.Col("status").Eq("done"), gohan.Int(1)).
			Else(gohan.Int(0))).As("done_count"),
	).From("tasks").Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT SUM(CASE WHEN "status" = $1 THEN 1 ELSE 0 END) AS "done_count" FROM "tasks" [done] <nil>

func (CaseBuilder) Else

func (c CaseBuilder) Else(v any) Value

Else adds an ELSE v clause and finishes the CASE expression.

func (CaseBuilder) End

func (c CaseBuilder) End() Value

End finishes the CASE expression without an ELSE clause. No When calls fails with ErrEmptyCase at render.

Example

ExampleCaseBuilder_End finishes a CASE without an ELSE branch. THEN values are in value position, so strings are bound.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Select(
		"id",
		gohan.Case().
			When(gohan.Col("score").Gte(90), "A").
			When(gohan.Col("score").Gte(80), "B").
			End().As("grade"),
	).From("exams").Build(gohan.Postgres())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
SELECT "id", CASE WHEN "score" >= $1 THEN $2 WHEN "score" >= $3 THEN $4 END AS "grade" FROM "exams"
[90 A 80 B] <nil>

func (CaseBuilder) When

func (c CaseBuilder) When(cond Expr, then any) CaseBuilder

When adds a WHEN cond THEN then clause.

type ConflictBuilder

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

ConflictBuilder configures the action of an ON CONFLICT clause.

func (ConflictBuilder) DoNothing

func (c ConflictBuilder) DoNothing() *InsertBuilder

DoNothing renders "ON CONFLICT [(cols)] DO NOTHING".

Example

ExampleConflictBuilder_DoNothing ignores rows that violate a unique constraint.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Insert("tags").
		Columns("name").
		Values("go").
		OnConflict("name").DoNothing().
		Build(gohan.SQLite())
	fmt.Println(sql, args, err)
}

Output:
INSERT INTO `tags` (`name`) VALUES (?) ON CONFLICT (`name`) DO NOTHING [go] <nil>

func (ConflictBuilder) DoUpdate

func (c ConflictBuilder) DoUpdate(set map[string]any) *InsertBuilder

DoUpdate renders "ON CONFLICT (cols) DO UPDATE SET ...", with set's keys sorted. A value is rendered in place if it is an Expr (e.g. Excluded or Raw), otherwise bound.

Example

ExampleConflictBuilder_DoUpdate sets explicit values on conflict: an Expr such as Excluded or Raw renders in place, anything else is bound. Keys are written in sorted order.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Insert("page_views").
		Columns("path", "views", "last_seen").
		Values("/home", 1, "2024-06-01").
		OnConflict("path").
		DoUpdate(map[string]any{
			"views":     gohan.Raw("? + 1", gohan.Col("page_views.views")),
			"last_seen": gohan.Excluded("last_seen"),
		}).
		Build(gohan.Postgres())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
INSERT INTO "page_views" ("path", "views", "last_seen") VALUES ($1, $2, $3) ON CONFLICT ("path") DO UPDATE SET "last_seen" = excluded."last_seen", "views" = "page_views"."views" + 1
[/home 1 2024-06-01] <nil>

func (ConflictBuilder) DoUpdateExcluded

func (c ConflictBuilder) DoUpdateExcluded(cols ...string) *InsertBuilder

DoUpdateExcluded renders "ON CONFLICT (cols) DO UPDATE SET c = excluded.c" for each c in cols. Each c must be one of the INSERT's own columns, else Build fails with ErrUnknownField.

type DeleteBuilder

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

DeleteBuilder builds a DELETE statement. Builders are immutable: every method returns a new value and never modifies the receiver.

func Delete

func Delete(table any) *DeleteBuilder

Delete starts a DELETE statement against table: a string or TableRef. A Subquery fails with ErrNoTable at Build.

Example

ExampleDelete deletes matching rows and returns their ids.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Delete("sessions").
		Where(gohan.Col("expires_at").Lt("2024-01-01")).
		Returning("id").
		Build(gohan.SQLite())
	fmt.Println(sql, args, err)
}

Output:
DELETE FROM `sessions` WHERE `expires_at` < ? RETURNING `id` [2024-01-01] <nil>

Example (RequiresWhere)

ExampleDelete_requiresWhere shows that a DELETE with no WHERE clause fails at Build with ErrNoWhere instead of deleting every row. Deleting every row on purpose uses All().

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Delete("users").Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
[] gohan: statement requires a WHERE clause; call All() to affect every row

func (*DeleteBuilder) All

func (d *DeleteBuilder) All() *DeleteBuilder

All allows the DELETE to build with no WHERE clause, or a trivially true one, affecting every row.

Example

ExampleDeleteBuilder_All deletes every row on purpose. ClickHouse's lightweight DELETE requires a WHERE, so it gets "WHERE 1".

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	q := gohan.Delete("scratch").All()

	sqlPg, _, _ := q.Build(gohan.Postgres())
	fmt.Println(sqlPg)

	sqlCh, _, _ := q.Build(gohan.ClickHouse())
	fmt.Println(sqlCh)
}

Output:
DELETE FROM "scratch"
DELETE FROM "scratch" WHERE 1

func (*DeleteBuilder) Build

func (d *DeleteBuilder) Build(dl Dialect) (string, []any, error)

Build renders the DELETE statement under dialect dl using a fresh writer.

func (*DeleteBuilder) Returning

func (d *DeleteBuilder) Returning(cols ...any) *DeleteBuilder

Returning sets the RETURNING column list. Zero cols means no RETURNING clause.

func (*DeleteBuilder) Where

func (d *DeleteBuilder) Where(conds ...Expr) *DeleteBuilder

Where appends conds, ANDed with any existing conditions.

Example

ExampleDeleteBuilder_Where shows that the trivially-true guard looks through Not/Or, but is per statement: a real condition ANDed with a trivially-true one is accepted.

package main

import (
	"errors"
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	_, _, err := gohan.Delete("users").Where(gohan.Not(gohan.Or())).Build(gohan.Postgres())
	fmt.Println(errors.Is(err, gohan.ErrNoWhere))

	sql, args, err := gohan.Delete("users").
		Where(gohan.Col("tenant_id").Eq(3), gohan.Col("status").NotIn()).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
true
DELETE FROM "users" WHERE ("tenant_id" = $1 AND 1=1) [3] <nil>

type Dialect

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

Dialect describes the SQL identifier quoting, placeholder style and feature set of a target database.

Example

ExampleDialect renders one query in every built-in dialect: identifier quoting and placeholder style differ, the bound arguments do not.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	q := gohan.Select("id", "name").
		From("users").
		Where(gohan.Col("name").Eq("alice"), gohan.Col("age").Gte(18)).
		Limit(1)

	for _, d := range []gohan.Dialect{gohan.Postgres(), gohan.SQLite(), gohan.ClickHouse(), gohan.Generic()} {
		sql, args, err := q.Build(d)
		fmt.Println(d.Name()+":", sql, args, err)
	}
}

Output:
postgres: SELECT "id", "name" FROM "users" WHERE ("name" = $1 AND "age" >= $2) LIMIT 1 [alice 18] <nil>
sqlite: SELECT `id`, `name` FROM `users` WHERE (`name` = ? AND `age` >= ?) LIMIT 1 [alice 18] <nil>
clickhouse: SELECT "id", "name" FROM "users" WHERE ("name" = ? AND "age" >= ?) LIMIT 1 [alice 18] <nil>
generic: SELECT "id", "name" FROM "users" WHERE ("name" = ? AND "age" >= ?) LIMIT 1 [alice 18] <nil>

func ClickHouse

func ClickHouse() Dialect

ClickHouse returns the ClickHouse dialect: `?` placeholders, double-quoted identifiers with backslash doubling, ILIKE/ClickHouse clause support, and no bound-argument limit.

clickhouse-go formats a bound time.Time at seconds precision when binding positional `?` placeholders (measured against clickhouse-go v2.40.3 and v2.48.0), so a DateTime64(3/6/9) column silently loses its sub-second part on insert and in comparisons. Use ClickHouseNamed to keep full precision.

func ClickHouseNamed

func ClickHouseNamed() Dialect

ClickHouseNamed returns the ClickHouse dialect with named placeholders: each bound value is written as @p1, @p2, … and returned as a sql.NamedArg{Name: "p1", Value: v}. Use it to pass time values as clickhouse.DateNamed (the only way to keep DateTime64 sub-second precision with clickhouse-go, which formats time.Time at seconds precision through positional binding — see ClickHouse). It is not added to the default registry; call Register if you want it looked up by driver name.

q, args, _ := st.Build(gohan.ClickHouseNamed())
for i, a := range args {
    na := a.(sql.NamedArg)
    if t, ok := na.Value.(time.Time); ok {
        args[i] = clickhouse.DateNamed(na.Name, t, clickhouse.NanoSeconds)
    }
}
rows, err := db.QueryContext(ctx, q, args...)

For clickhouse-go's native API (clickhouse.Conn), convert every sql.NamedArg to clickhouse.Named(na.Name, na.Value) instead — the native API does not understand sql.NamedArg.

Example

ExampleClickHouseNamed renders a statement with @pN placeholders and sql.NamedArg arguments instead of ClickHouse's default positional `?`. Use this mode to keep DateTime64 sub-second precision: convert each time.Time arg to clickhouse.DateNamed before passing args to db.Query, e.g.:

for i, a := range args {
    na := a.(sql.NamedArg)
    if t, ok := na.Value.(time.Time); ok {
        args[i] = clickhouse.DateNamed(na.Name, t, clickhouse.NanoSeconds)
    }
}

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Select("id").
		From("t").
		Where(gohan.Col("a").Eq("x"), gohan.Col("b").In(1, 2)).
		Limit(5).
		Build(gohan.ClickHouseNamed())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
SELECT "id" FROM "t" WHERE ("a" = @p1 AND "b" IN (@p2, @p3)) LIMIT 5
[{{} p1 x} {{} p2 1} {{} p3 2}] <nil>

func DialectFor

func DialectFor(driverName string) (Dialect, error)

DialectFor returns the Dialect registered for driverName, or ErrUnknownDialect if none is registered.

Example

ExampleDialectFor looks a dialect up by database/sql driver name.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	for _, driver := range []string{"pgx", "sqlite", "clickhouse"} {
		d, err := gohan.DialectFor(driver)
		fmt.Println(driver, "->", d.Name(), err)
	}
}

Output:
pgx -> postgres <nil>
sqlite -> sqlite <nil>
clickhouse -> clickhouse <nil>

func Generic

func Generic() Dialect

Generic returns the ANSI SQL dialect: `?` placeholders, ANSI double-quoted identifiers, UPDATE/DEFAULT VALUES support, and a 999 bound-argument limit. It must not be registered for MySQL, where double-quoted text is a string literal, not an identifier.

func Postgres

func Postgres() Dialect

Postgres returns the PostgreSQL dialect: numbered ($n) placeholders, double-quoted identifiers, RETURNING/upsert/ILIKE/UPDATE/row-locking/ DEFAULT VALUES support, and a 65535 bound-argument limit.

func SQLite

func SQLite() Dialect

SQLite returns the SQLite dialect: `?` placeholders, backtick-quoted identifiers, RETURNING/upsert/UPDATE/DEFAULT VALUES support, and a 32766 bound-argument limit.

func (Dialect) Has

func (d Dialect) Has(f Feature) bool

Has reports whether the dialect supports the given feature.

Example

ExampleDialect_Has reports optional features and the bound-argument limit of each dialect.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	for _, d := range []gohan.Dialect{gohan.Postgres(), gohan.SQLite(), gohan.ClickHouse(), gohan.Generic()} {
		fmt.Printf("%s returning=%v upsert=%v ilike=%v update=%v clickhouse=%v maxargs=%d\n",
			d.Name(),
			d.Has(gohan.FeatureReturning),
			d.Has(gohan.FeatureUpsert),
			d.Has(gohan.FeatureILike),
			d.Has(gohan.FeatureUpdate),
			d.Has(gohan.FeatureClickHouse),
			d.MaxArgs(),
		)
	}
}

Output:
postgres returning=true upsert=true ilike=true update=true clickhouse=false maxargs=65535
sqlite returning=true upsert=true ilike=false update=true clickhouse=false maxargs=32766
clickhouse returning=false upsert=false ilike=true update=false clickhouse=true maxargs=0
generic returning=false upsert=false ilike=false update=true clickhouse=false maxargs=999

func (Dialect) MaxArgs

func (d Dialect) MaxArgs() int

MaxArgs returns the dialect's bound-argument limit, or 0 for unlimited.

func (Dialect) Name

func (d Dialect) Name() string

Name returns the dialect's name, or "" for the zero Dialect.

func (Dialect) QuoteIdent

func (d Dialect) QuoteIdent(name string) (string, error)

QuoteIdent quotes and escapes name per the dialect's identifier rules. It is for adapters that need to name an identifier outside a statement.

Example

ExampleDialect_QuoteIdent quotes an identifier outside a statement (e.g. for DDL). A dot separates qualified parts; quote characters inside a part are escaped by doubling.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	for _, d := range []gohan.Dialect{gohan.Postgres(), gohan.SQLite(), gohan.ClickHouse()} {
		q, err := d.QuoteIdent(`audit."log"`)
		fmt.Println(d.Name()+":", q, err)
	}
}

Output:
postgres: "audit"."""log""" <nil>
sqlite: `audit`.`"log"` <nil>
clickhouse: "audit"."""log""" <nil>

type Error

type Error string

Example

ExampleError shows that every failure is reported by Build as an error wrapping one of the Err* constants, for use with errors.Is.

package main

import (
	"errors"
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	_, _, err := gohan.Delete("users").Build(gohan.Postgres())
	fmt.Println(errors.Is(err, gohan.ErrNoWhere))

	_, _, err = gohan.Select("a?b").From("t").Build(gohan.ClickHouse())
	fmt.Println(errors.Is(err, gohan.ErrInvalidIdentifier), err)

	_, _, err = gohan.From("t").Where(gohan.Col("attrs").Eq(map[string]string{"k": "v"})).Build(gohan.ClickHouse())
	fmt.Println(errors.Is(err, gohan.ErrUnsafeValue), err)

	_, _, err = gohan.From("t").Where(gohan.Raw("a = ? AND b = ?", 1)).Build(gohan.Postgres())
	fmt.Println(errors.Is(err, gohan.ErrRawArgs))

	_, err = gohan.DialectFor("mysql")
	fmt.Println(errors.Is(err, gohan.ErrUnknownDialect), err)
}

Output:
true
true gohan: invalid identifier: "a?b"
true gohan: value type cannot be bound safely for this dialect: map[string]string
true
true gohan: unknown or zero dialect: "mysql"

func (Error) Error

func (e Error) Error() string

type Expr

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

Expr is any renderable SQL fragment. It is sealed: only this package can implement it.

func Star

func Star() Expr

Star renders the unquoted `*`.

type Feature

type Feature uint

Feature is a bitmask of optional SQL capabilities a Dialect supports.

const (
	FeatureReturning     Feature = 1 << iota // RETURNING on INSERT/UPDATE/DELETE
	FeatureUpsert                            // INSERT ... ON CONFLICT
	FeatureILike                             // ILIKE operator
	FeatureUpdate                            // UPDATE statement
	FeatureClickHouse                        // FINAL, PREWHERE, SAMPLE, ARRAY JOIN, SETTINGS
	FeatureLocking                           // FOR UPDATE/SHARE row locking
	FeatureDefaultValues                     // INSERT ... DEFAULT VALUES
)

type InsertBuilder

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

InsertBuilder builds an INSERT statement. Builders are immutable: every method returns a new value and never modifies the receiver.

func Insert

func Insert(table any) *InsertBuilder

Insert starts an INSERT statement against table: a string or a TableRef without an alias.

Example

ExampleInsert inserts a single row from explicit columns and values.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Insert("users").
		Columns("name", "email").
		Values("alice", "alice@example.com").
		Build(gohan.SQLite())
	fmt.Println(sql, args, err)
}

Output:
INSERT INTO `users` (`name`, `email`) VALUES (?, ?) [alice alice@example.com] <nil>

Example (Upsert)

ExampleInsert_upsert inserts a row and, on a conflicting primary key, updates the other columns from the values that were about to be inserted (DoUpdateExcluded).

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Insert("users").
		Columns("id", "name", "email").
		Values(1, "alice", "alice@example.com").
		OnConflict("id").
		DoUpdateExcluded("name", "email").
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
INSERT INTO "users" ("id", "name", "email") VALUES ($1, $2, $3) ON CONFLICT ("id") DO UPDATE SET "name" = excluded."name", "email" = excluded."email" [1 alice alice@example.com] <nil>

func (*InsertBuilder) Build

func (b *InsertBuilder) Build(d Dialect) (string, []any, error)

Build renders the INSERT statement under dialect d using a fresh writer.

func (*InsertBuilder) Columns

func (b *InsertBuilder) Columns(cols ...string) *InsertBuilder

Columns replaces the column list used with Values or FromSelect.

func (*InsertBuilder) DefaultValues

func (b *InsertBuilder) DefaultValues() *InsertBuilder

DefaultValues makes the INSERT "INSERT INTO t DEFAULT VALUES": one row, every column taking its default. It cannot be combined with Columns, Values, Rows, SetMap or FromSelect. ErrUnsupported on dialects without FeatureDefaultValues (e.g. ClickHouse).

Example

ExampleInsertBuilder_DefaultValues inserts a single row that takes every column's default. Not all dialects support it: ClickHouse fails at Build with ErrUnsupported.

package main

import (
	"errors"
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Insert("d").
		DefaultValues().
		Returning("id", "code", "name").
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)

	_, _, err = gohan.Insert("d").DefaultValues().Build(gohan.ClickHouse())
	fmt.Println(errors.Is(err, gohan.ErrUnsupported))
}

Output:
INSERT INTO "d" DEFAULT VALUES RETURNING "id", "code", "name" [] <nil>
true

func (*InsertBuilder) FromSelect

func (b *InsertBuilder) FromSelect(q *SelectBuilder) *InsertBuilder

FromSelect makes the INSERT an "INSERT INTO ... SELECT ...", using the column list set by Columns.

Example

ExampleInsertBuilder_FromSelect copies rows with INSERT ... SELECT.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Insert("archived_orders").
		Columns("id", "total").
		FromSelect(gohan.Select("id", "total").From("orders").Where(gohan.Col("created_at").Lt("2020-01-01"))).
		Build(gohan.Postgres())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
INSERT INTO "archived_orders" ("id", "total") SELECT "id", "total" FROM "orders" WHERE "created_at" < $1
[2020-01-01] <nil>

Example (SqliteUpsert)

ExampleInsertBuilder_FromSelect_sqliteUpsert shows the "WHERE true" that SQLite needs between an INSERT ... SELECT and its ON CONFLICT clause; gohan adds it when the SELECT has no WHERE of its own.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, _, err := gohan.Insert("tags").
		Columns("name").
		FromSelect(gohan.Select("name").From("staging_tags")).
		OnConflict("name").DoNothing().
		Build(gohan.SQLite())
	fmt.Println(sql, err)
}

Output:
INSERT INTO `tags` (`name`) SELECT `name` FROM `staging_tags` WHERE true ON CONFLICT (`name`) DO NOTHING <nil>

func (*InsertBuilder) OnConflict

func (b *InsertBuilder) OnConflict(cols ...string) ConflictBuilder

OnConflict starts an ON CONFLICT clause targeting cols (which may be empty for DoNothing).

func (*InsertBuilder) Returning

func (b *InsertBuilder) Returning(cols ...any) *InsertBuilder

Returning sets the RETURNING column list.

Example

ExampleInsertBuilder_Returning returns generated columns.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Insert("users").
		Columns("name").
		Values("alice").
		Returning("id", "created_at").
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
INSERT INTO "users" ("name") VALUES ($1) RETURNING "id", "created_at" [alice] <nil>

func (*InsertBuilder) Rows

func (b *InsertBuilder) Rows(records ...any) *InsertBuilder

Rows appends one row per record (a struct or pointer to struct). Values are extracted immediately (a snapshot): mutating a record afterwards does not change the statement. The first record decides the column set; a later record whose omit decision (OmitNil/OmitEmpty) differs for any field fails at Build with ErrInconsistentOmit.

Example

ExampleInsertBuilder_Rows inserts structs. The first record decides the columns: auto fields (here the id) are skipped, and a nil pointer with goqu:"omitnil" is left out.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	type User struct {
		ID       int64   `db:"id" auto:"true"`
		Name     string  `db:"name"`
		Email    string  `db:"email"`
		Nickname *string `db:"nickname" goqu:"omitnil"`
	}

	sql, args, err := gohan.Insert("users").
		Rows(
			User{Name: "alice", Email: "alice@example.com"},
			&User{Name: "bob", Email: "bob@example.com"},
		).
		Build(gohan.Postgres())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
INSERT INTO "users" ("name", "email") VALUES ($1, $2), ($3, $4)
[alice alice@example.com bob bob@example.com] <nil>

func (*InsertBuilder) SetMap

func (b *InsertBuilder) SetMap(m map[string]any) *InsertBuilder

SetMap sets a single row from m, with columns in sorted key order.

Example

ExampleInsertBuilder_SetMap inserts one row from a map, with columns in sorted key order.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Insert("settings").
		SetMap(map[string]any{"key": "theme", "value": "dark"}).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
INSERT INTO "settings" ("key", "value") VALUES ($1, $2) [theme dark] <nil>

func (*InsertBuilder) Values

func (b *InsertBuilder) Values(vals ...any) *InsertBuilder

Values appends one row of values, in column position.

Example

ExampleInsertBuilder_Values appends one row per call; the value count must match the column count.

package main

import (
	"errors"
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Insert("users").
		Columns("name", "email").
		Values("alice", "alice@example.com").
		Values("bob", "bob@example.com").
		Build(gohan.Postgres())
	fmt.Println(sql)
	fmt.Println(args, err)

	_, _, err = gohan.Insert("users").Columns("name", "email").Values("carol").Build(gohan.Postgres())
	fmt.Println(errors.Is(err, gohan.ErrValueCount))
}

Output:
INSERT INTO "users" ("name", "email") VALUES ($1, $2), ($3, $4)
[alice alice@example.com bob bob@example.com] <nil>
true

type Order

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

Order is an ORDER BY item. It implements Expr.

func (Order) NullsFirst

func (o Order) NullsFirst() Order

NullsFirst appends "NULLS FIRST".

func (Order) NullsLast

func (o Order) NullsLast() Order

NullsLast appends "NULLS LAST".

type RecordOption

type RecordOption func(*recordOptions)

RecordOption configures UpdateBuilder.SetRecord.

func ExcludeFields

func ExcludeFields(names ...string) RecordOption

ExcludeFields removes the named fields (struct field name or db name) from SetRecord.

func IncludeFields

func IncludeFields(names ...string) RecordOption

IncludeFields restricts SetRecord to only the named fields (struct field name or db name). When set, ExcludeFields is ignored.

func SkipZeroValues

func SkipZeroValues() RecordOption

SkipZeroValues skips fields holding their type's zero value.

func WithAutoFields

func WithAutoFields() RecordOption

WithAutoFields includes fields marked Auto, which are otherwise always skipped by SetRecord.

type SelectBuilder

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

SelectBuilder builds a SELECT statement. Builders are immutable: every method returns a new value and never modifies the receiver.

func From

func From(table any) *SelectBuilder

From is shorthand for Select().From(table).

Example

ExampleFrom is shorthand for Select().From(table): with no select list the statement selects every column.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("public.users").
		Where(gohan.Col("id").Eq(42)).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT * FROM "public"."users" WHERE "id" = $1 [42] <nil>

func Select

func Select(cols ...any) *SelectBuilder

Select starts a SELECT statement with the given select-list columns. A string is a column name; anything else that is not an Expr fails with ErrInvalidColumn at Build.

Example

ExampleSelect builds a SELECT with a WHERE, ORDER BY and LIMIT clause against the PostgreSQL dialect.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Select("id", "name").
		From("users").
		Where(gohan.Col("active").Eq(true)).
		OrderBy(gohan.Col("name").Asc()).
		Limit(10).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT "id", "name" FROM "users" WHERE "active" = $1 ORDER BY "name" ASC LIMIT 10 [true] <nil>

func (*SelectBuilder) AddColumns

func (s *SelectBuilder) AddColumns(cols ...any) *SelectBuilder

AddColumns appends to the select list.

func (*SelectBuilder) ArrayJoin

func (s *SelectBuilder) ArrayJoin(cols ...any) *SelectBuilder

ArrayJoin adds an ARRAY JOIN clause over cols (by position; Col("a"). As("x") allowed). Zero columns fails with ErrEmptyClause; calling both ArrayJoin and LeftArrayJoin on the same builder fails with ErrUnsupported. ErrUnsupported unless the dialect has FeatureClickHouse.

Example

ExampleSelectBuilder_ArrayJoin unfolds an array column into rows.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, _, err := gohan.Select("id", "tag").
		From("articles").
		ArrayJoin(gohan.Col("tags").As("tag")).
		Build(gohan.ClickHouse())
	fmt.Println(sql, err)
}

Output:
SELECT "id", "tag" FROM "articles" ARRAY JOIN "tags" AS "tag" <nil>

func (*SelectBuilder) As

func (s *SelectBuilder) As(alias string) Subquery

As turns the SELECT into a Subquery usable as a FROM source, aliased by alias (a single-part identifier).

Example

ExampleSelectBuilder_As turns a SELECT into an aliased subquery usable as a FROM (or JOIN) source. Placeholders are numbered across the whole statement.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	recent := gohan.Select("user_id", gohan.Max("created_at").As("last_order")).
		From("orders").
		Where(gohan.Col("status").Eq("paid")).
		GroupBy("user_id").
		As("r")

	sql, args, err := gohan.From(recent).
		Where(gohan.Col("r.last_order").Lt("2024-01-01")).
		Build(gohan.Postgres())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
SELECT * FROM (SELECT "user_id", MAX("created_at") AS "last_order" FROM "orders" WHERE "status" = $1 GROUP BY "user_id") AS "r" WHERE "r"."last_order" < $2
[paid 2024-01-01] <nil>

func (*SelectBuilder) Build

func (s *SelectBuilder) Build(d Dialect) (string, []any, error)

Build renders the SELECT statement under dialect d using a fresh writer.

func (*SelectBuilder) Columns

func (s *SelectBuilder) Columns(cols ...any) *SelectBuilder

Columns replaces the select list.

func (*SelectBuilder) CrossJoin

func (s *SelectBuilder) CrossJoin(t any) *SelectBuilder

CrossJoin adds a CROSS JOIN against t.

func (*SelectBuilder) Distinct

func (s *SelectBuilder) Distinct() *SelectBuilder

Distinct adds DISTINCT to the select list.

Example

ExampleSelectBuilder_Distinct adds DISTINCT to the select list.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, _, err := gohan.Select("country").From("customers").Distinct().Build(gohan.Postgres())
	fmt.Println(sql, err)
}

Output:
SELECT DISTINCT "country" FROM "customers" <nil>

func (*SelectBuilder) Final

func (s *SelectBuilder) Final() *SelectBuilder

Final adds FINAL to the FROM clause. Fails with ErrUnsupported at Build on a dialect without FeatureClickHouse, and also when the FROM source is a Subquery (ClickHouse or not).

Example

ExampleSelectBuilder_Final reads a ReplacingMergeTree table with FINAL. On a dialect without FeatureClickHouse the clause fails with ErrUnsupported.

package main

import (
	"errors"
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	q := gohan.From("events").Final().Where(gohan.Col("tenant_id").Eq(7))

	sql, args, err := q.Build(gohan.ClickHouse())
	fmt.Println(sql, args, err)

	_, _, err = q.Build(gohan.Postgres())
	fmt.Println(errors.Is(err, gohan.ErrUnsupported))
}

Output:
SELECT * FROM "events" FINAL WHERE "tenant_id" = ? [7] <nil>
true

func (*SelectBuilder) ForKeyShare

func (s *SelectBuilder) ForKeyShare(of ...string) *SelectBuilder

ForKeyShare appends a FOR KEY SHARE clause, optionally naming the FROM items to lock with OF. PostgreSQL only; fails with ErrUnsupported on other dialects at Build.

func (*SelectBuilder) ForNoKeyUpdate

func (s *SelectBuilder) ForNoKeyUpdate(of ...string) *SelectBuilder

ForNoKeyUpdate appends a FOR NO KEY UPDATE clause, optionally naming the FROM items to lock with OF. PostgreSQL only; fails with ErrUnsupported on other dialects at Build.

func (*SelectBuilder) ForShare

func (s *SelectBuilder) ForShare(of ...string) *SelectBuilder

ForShare appends a FOR SHARE clause, optionally naming the FROM items to lock with OF. PostgreSQL only; fails with ErrUnsupported on other dialects at Build.

Example

ExampleSelectBuilder_ForShare locks the joined rows for share, naming which FROM item each clause applies to with OF.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	t := gohan.Table("accounts").As("a")
	u := gohan.Table("transfers").As("u")

	sql, args, err := gohan.From(t).
		Join(u, u.Col("account_id").Eq(t.Col("id"))).
		ForShare("a").
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT * FROM "accounts" AS "a" INNER JOIN "transfers" AS "u" ON "u"."account_id" = "a"."id" FOR SHARE OF "a" [] <nil>

func (*SelectBuilder) ForUpdate

func (s *SelectBuilder) ForUpdate(of ...string) *SelectBuilder

ForUpdate appends a FOR UPDATE clause, optionally naming the FROM items to lock with OF. PostgreSQL only; fails with ErrUnsupported on other dialects at Build.

Example

ExampleSelectBuilder_ForUpdate builds the standard PostgreSQL job-queue query: lock the next unclaimed row and skip any row a concurrent worker already has locked.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("jobs").
		Where(gohan.Col("state").Eq("new")).
		OrderBy(gohan.Col("id").Asc()).
		Limit(1).
		ForUpdate().
		SkipLocked().
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT * FROM "jobs" WHERE "state" = $1 ORDER BY "id" ASC LIMIT 1 FOR UPDATE SKIP LOCKED [new] <nil>

func (*SelectBuilder) From

func (s *SelectBuilder) From(table any) *SelectBuilder

From sets the FROM source: a string ("schema.table" allowed), a TableRef, or a Subquery.

func (*SelectBuilder) FullJoin

func (s *SelectBuilder) FullJoin(t any, on Expr) *SelectBuilder

FullJoin adds a FULL JOIN against t, matched by on.

func (*SelectBuilder) GroupBy

func (s *SelectBuilder) GroupBy(cols ...any) *SelectBuilder

GroupBy appends to the GROUP BY list.

Example

ExampleSelectBuilder_GroupBy aggregates with GROUP BY and filters groups with HAVING. Aggregate arguments given as strings are column names.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Select(
		"customer_id",
		gohan.CountAll().As("orders"),
		gohan.Sum("total").As("revenue"),
	).
		From("orders").
		GroupBy("customer_id").
		Having(gohan.Sum("total").Gt(1000)).
		OrderBy(gohan.Sum("total").Desc()).
		Build(gohan.Postgres())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
SELECT "customer_id", COUNT(*) AS "orders", SUM("total") AS "revenue" FROM "orders" GROUP BY "customer_id" HAVING SUM("total") > $1 ORDER BY SUM("total") DESC
[1000] <nil>

func (*SelectBuilder) Having

func (s *SelectBuilder) Having(conds ...Expr) *SelectBuilder

Having appends conds, ANDed with any existing HAVING conditions.

func (*SelectBuilder) IsCompound

func (s *SelectBuilder) IsCompound() bool

IsCompound reports whether s has any UNION members. Where/Having/ Prewhere called on a compound builder apply to the first member's core only, while ORDER BY/LIMIT/OFFSET apply to the whole compound; callers that need filters applied to the whole result set (e.g. a repository layer) should reject a compound base query.

Example

ExampleSelectBuilder_IsCompound shows the restriction on UNION members: a member may not carry its own ORDER BY, LIMIT, OFFSET, WITH, SETTINGS or UNION.

package main

import (
	"errors"
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	q := gohan.Select("id").From("a").
		Union(gohan.Select("id").From("b").Limit(5))
	fmt.Println(q.IsCompound())

	_, _, err := q.Build(gohan.Postgres())
	fmt.Println(errors.Is(err, gohan.ErrCompoundPart))
}

Output:
true
true

func (*SelectBuilder) Join

func (s *SelectBuilder) Join(t any, on Expr) *SelectBuilder

Join adds an INNER JOIN against t, matched by on.

Example

ExampleSelectBuilder_Join joins two aliased tables. TableRef.Col qualifies a column by the table's alias.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	u := gohan.Table("users").As("u")
	o := gohan.Table("orders").As("o")

	sql, args, err := gohan.Select(u.Col("name"), o.Col("total")).
		From(u).
		Join(o, o.Col("user_id").Eq(u.Col("id"))).
		Where(o.Col("total").Gt(100)).
		Build(gohan.Postgres())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
SELECT "u"."name", "o"."total" FROM "users" AS "u" INNER JOIN "orders" AS "o" ON "o"."user_id" = "u"."id" WHERE "o"."total" > $1
[100] <nil>

func (*SelectBuilder) JoinUsing

func (s *SelectBuilder) JoinUsing(t any, cols ...string) *SelectBuilder

JoinUsing adds an INNER JOIN against t, matched by the named columns. Zero columns fails with ErrEmptyClause at Build.

Example

ExampleSelectBuilder_JoinUsing matches rows on identically named columns.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, _, err := gohan.From("orders").
		JoinUsing("customers", "customer_id").
		Build(gohan.Postgres())
	fmt.Println(sql, err)
}

Output:
SELECT * FROM "orders" INNER JOIN "customers" USING ("customer_id") <nil>

func (*SelectBuilder) LeftArrayJoin

func (s *SelectBuilder) LeftArrayJoin(cols ...any) *SelectBuilder

LeftArrayJoin adds a LEFT ARRAY JOIN clause over cols. Zero columns fails with ErrEmptyClause; calling both ArrayJoin and LeftArrayJoin on the same builder fails with ErrUnsupported. ErrUnsupported unless the dialect has FeatureClickHouse.

func (*SelectBuilder) LeftJoin

func (s *SelectBuilder) LeftJoin(t any, on Expr) *SelectBuilder

LeftJoin adds a LEFT JOIN against t, matched by on.

Example

ExampleSelectBuilder_LeftJoin keeps every row of the left table.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, _, err := gohan.Select("users.id", gohan.Count("orders.id").As("orders")).
		From("users").
		LeftJoin("orders", gohan.Col("orders.user_id").Eq(gohan.Col("users.id"))).
		GroupBy("users.id").
		Build(gohan.SQLite())
	fmt.Println(sql, err)
}

Output:
SELECT `users`.`id`, COUNT(`orders`.`id`) AS `orders` FROM `users` LEFT JOIN `orders` ON `orders`.`user_id` = `users`.`id` GROUP BY `users`.`id` <nil>

func (*SelectBuilder) LeftJoinUsing

func (s *SelectBuilder) LeftJoinUsing(t any, cols ...string) *SelectBuilder

LeftJoinUsing adds a LEFT JOIN against t, matched by the named columns. Zero columns fails with ErrEmptyClause at Build.

func (*SelectBuilder) Limit

func (s *SelectBuilder) Limit(n uint64) *SelectBuilder

Limit sets the LIMIT clause. Limit(0) renders LIMIT 0.

Example

ExampleSelectBuilder_Limit renders LIMIT and OFFSET inline (they are integers, not bound values).

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("users").
		OrderBy("id").
		Limit(20).
		Offset(40).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT * FROM "users" ORDER BY "id" ASC LIMIT 20 OFFSET 40 [] <nil>

func (*SelectBuilder) NoWait

func (s *SelectBuilder) NoWait() *SelectBuilder

NoWait sets NOWAIT on the most recently added lock clause. Calling it with no lock clause on the builder, or after SkipLocked was already set on the same clause, fails with ErrInvalidLock at Build.

func (*SelectBuilder) Offset

func (s *SelectBuilder) Offset(n uint64) *SelectBuilder

Offset sets the OFFSET clause.

Example

ExampleSelectBuilder_Offset shows that an OFFSET without a LIMIT gets "LIMIT -1" on SQLite, whose grammar has no bare OFFSET.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	q := gohan.From("users").Offset(40)

	sqlPg, _, _ := q.Build(gohan.Postgres())
	fmt.Println(sqlPg)

	sqlLite, _, _ := q.Build(gohan.SQLite())
	fmt.Println(sqlLite)
}

Output:
SELECT * FROM "users" OFFSET 40
SELECT * FROM `users` LIMIT -1 OFFSET 40

func (*SelectBuilder) OrderBy

func (s *SelectBuilder) OrderBy(o ...any) *SelectBuilder

OrderBy appends to the ORDER BY list. An Order is used as-is; a string or Value is rendered ascending.

Example

ExampleSelectBuilder_OrderBy mixes a plain column name (rendered ascending) with an explicit Desc().NullsLast() item.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, _, err := gohan.From("users").
		OrderBy("last_name", gohan.Col("last_login").Desc().NullsLast()).
		Build(gohan.Postgres())
	fmt.Println(sql, err)
}

Output:
SELECT * FROM "users" ORDER BY "last_name" ASC, "last_login" DESC NULLS LAST <nil>

func (*SelectBuilder) Prewhere

func (s *SelectBuilder) Prewhere(conds ...Expr) *SelectBuilder

Prewhere appends conds, ANDed with any existing PREWHERE conditions. ErrUnsupported unless the dialect has FeatureClickHouse.

Example

ExampleSelectBuilder_Prewhere filters with PREWHERE before reading the remaining columns.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Select("url", "duration").
		From("requests").
		Prewhere(gohan.Col("date").Eq("2024-06-01")).
		Where(gohan.Col("duration").Gt(1000)).
		Build(gohan.ClickHouse())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
SELECT "url", "duration" FROM "requests" PREWHERE "date" = ? WHERE "duration" > ?
[2024-06-01 1000] <nil>

func (*SelectBuilder) RightJoin

func (s *SelectBuilder) RightJoin(t any, on Expr) *SelectBuilder

RightJoin adds a RIGHT JOIN against t, matched by on.

func (*SelectBuilder) Sample

func (s *SelectBuilder) Sample(ratio float64) *SelectBuilder

Sample adds SAMPLE ratio to the FROM clause. ratio must be in (0, 1] and finite, else ErrInvalidSample at Build. Fails with ErrUnsupported at Build on a dialect without FeatureClickHouse, and also when the FROM source is a Subquery (ClickHouse or not).

Example

ExampleSelectBuilder_Sample reads a sample of a table: Sample takes a ratio in (0, 1], SampleRows an absolute row count.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, _, err := gohan.Select(gohan.CountAll()).From("hits").Sample(0.1).Build(gohan.ClickHouse())
	fmt.Println(sql, err)

	sql, _, err = gohan.Select(gohan.CountAll()).From("hits").SampleRows(10000).Build(gohan.ClickHouse())
	fmt.Println(sql, err)

	_, _, err = gohan.From("hits").Sample(1.5).Build(gohan.ClickHouse())
	fmt.Println(err)
}

Output:
SELECT COUNT(*) FROM "hits" SAMPLE 0.1 <nil>
SELECT COUNT(*) FROM "hits" SAMPLE 10000 <nil>
gohan: SAMPLE ratio must be in (0, 1]

func (*SelectBuilder) SampleRows

func (s *SelectBuilder) SampleRows(n uint64) *SelectBuilder

SampleRows adds SAMPLE n (an absolute row count) to the FROM clause. n must be >= 1, else ErrInvalidSample at Build. Fails with ErrUnsupported at Build on a dialect without FeatureClickHouse, and also when the FROM source is a Subquery (ClickHouse or not).

func (*SelectBuilder) Settings

func (s *SelectBuilder) Settings(m map[string]any) *SelectBuilder

Settings merges m into the SETTINGS clause: existing keys are overwritten, new keys are added. m is copied into a sorted []kv; mutating m after the call has no effect. Keys must match ^[A-Za-z_][A-Za-z0-9_]*$, else ErrInvalidSetting at Build. ErrUnsupported unless the dialect has FeatureClickHouse.

Example

ExampleSelectBuilder_Settings appends a SETTINGS clause. Keys are validated and written in sorted order; values are bound.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("events").
		Limit(100).
		Settings(map[string]any{"max_threads": 4, "join_use_nulls": 1}).
		Build(gohan.ClickHouse())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
SELECT * FROM "events" LIMIT 100 SETTINGS join_use_nulls = ?, max_threads = ?
[1 4] <nil>

func (*SelectBuilder) SkipLocked

func (s *SelectBuilder) SkipLocked() *SelectBuilder

SkipLocked sets SKIP LOCKED on the most recently added lock clause. Calling it with no lock clause on the builder, or after NoWait was already set on the same clause, fails with ErrInvalidLock at Build.

func (*SelectBuilder) Union

func (s *SelectBuilder) Union(q *SelectBuilder) *SelectBuilder

Union adds a UNION member: UNION DISTINCT on ClickHouse, UNION elsewhere. q may not have its own ORDER BY, LIMIT, OFFSET, WITH, SETTINGS or UNION — such a member fails with ErrCompoundPart at Build.

Example

ExampleSelectBuilder_Union shows the UNION keyword difference between PostgreSQL (UNION) and ClickHouse (UNION DISTINCT).

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	q := gohan.Select("id").From("t1").
		Union(gohan.Select("id").From("t2"))

	sqlPg, _, errPg := q.Build(gohan.Postgres())
	fmt.Println(sqlPg, errPg)

	sqlCh, _, errCh := q.Build(gohan.ClickHouse())
	fmt.Println(sqlCh, errCh)
}

Output:
SELECT "id" FROM "t1" UNION SELECT "id" FROM "t2" <nil>
SELECT "id" FROM "t1" UNION DISTINCT SELECT "id" FROM "t2" <nil>

func (*SelectBuilder) UnionAll

func (s *SelectBuilder) UnionAll(q *SelectBuilder) *SelectBuilder

UnionAll adds a UNION ALL member. q may not have its own ORDER BY, LIMIT, OFFSET, WITH, SETTINGS or UNION — such a member fails with ErrCompoundPart at Build.

Example

ExampleSelectBuilder_UnionAll shows an outer ORDER BY / LIMIT on a compound. ClickHouse wraps the compound in a subquery, because its UNION grammar would bind the trailing clauses to the last member only.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	q := gohan.Select("id", "created_at").From("invoices").
		UnionAll(gohan.Select("id", "created_at").From("credit_notes")).
		OrderBy(gohan.Col("created_at").Desc()).
		Limit(10)

	sqlPg, _, _ := q.Build(gohan.Postgres())
	fmt.Println(sqlPg)

	sqlCh, _, _ := q.Build(gohan.ClickHouse())
	fmt.Println(sqlCh)
}

Output:
SELECT "id", "created_at" FROM "invoices" UNION ALL SELECT "id", "created_at" FROM "credit_notes" ORDER BY "created_at" DESC LIMIT 10
SELECT * FROM (SELECT "id", "created_at" FROM "invoices" UNION ALL SELECT "id", "created_at" FROM "credit_notes") ORDER BY "created_at" DESC LIMIT 10

func (*SelectBuilder) Where

func (s *SelectBuilder) Where(conds ...Expr) *SelectBuilder

Where appends conds, ANDed with any existing conditions.

Example

ExampleSelectBuilder_Where shows that repeated Where calls are ANDed, and that Or/And nest with explicit parentheses.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Select("id", "total").
		From("orders").
		Where(gohan.Col("status").Eq("paid")).
		Where(gohan.Or(
			gohan.Col("total").Gte(100),
			gohan.Col("customer_tier").In("gold", "platinum"),
		)).
		Build(gohan.Postgres())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
SELECT "id", "total" FROM "orders" WHERE ("status" = $1 AND ("total" >= $2 OR "customer_tier" IN ($3, $4)))
[paid 100 gold platinum] <nil>

func (*SelectBuilder) With

func (s *SelectBuilder) With(name string, q *SelectBuilder) *SelectBuilder

With adds a WITH entry: "name AS (q)". name must be a single-part identifier.

Example

ExampleSelectBuilder_With adds a common table expression.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	active := gohan.Select("id", "email").From("users").Where(gohan.Col("active").Eq(true))

	sql, args, err := gohan.Select("email").
		With("active_users", active).
		From("active_users").
		Where(gohan.Col("email").HasSuffix("@example.com")).
		Build(gohan.Postgres())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
WITH "active_users" AS (SELECT "id", "email" FROM "users" WHERE "active" = $1) SELECT "email" FROM "active_users" WHERE "email" LIKE $2 ESCAPE '!'
[true %@example.com] <nil>

func (*SelectBuilder) WithRecursive

func (s *SelectBuilder) WithRecursive(name string, q *SelectBuilder) *SelectBuilder

WithRecursive adds a WITH RECURSIVE entry: "name AS (q)". name must be a single-part identifier. If any CTE on the builder was added with WithRecursive, the whole WITH clause renders RECURSIVE.

Example

ExampleSelectBuilder_WithRecursive walks a tree with a recursive CTE.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	tree := gohan.Select("id", "parent_id").From("categories").
		Where(gohan.Col("id").Eq(1)).
		UnionAll(
			gohan.Select("c.id", "c.parent_id").
				From(gohan.Table("categories").As("c")).
				Join("tree", gohan.Col("c.parent_id").Eq(gohan.Col("tree.id"))),
		)

	sql, args, err := gohan.Select("id").
		WithRecursive("tree", tree).
		From("tree").
		Build(gohan.Postgres())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
WITH RECURSIVE "tree" AS (SELECT "id", "parent_id" FROM "categories" WHERE "id" = $1 UNION ALL SELECT "c"."id", "c"."parent_id" FROM "categories" AS "c" INNER JOIN "tree" ON "c"."parent_id" = "tree"."id") SELECT "id" FROM "tree"
[1] <nil>

type Statement

type Statement interface {
	Build(d Dialect) (string, []any, error)
}

Statement is anything that renders to a complete SQL string and its bound arguments.

type Subquery

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

Subquery is a SELECT usable as a FROM/JOIN source. It is not an Expr and cannot appear where an expression is expected.

type TableRef

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

TableRef names a table, with an optional alias.

func Table

func Table(name string) TableRef

Table names table for use as a FROM/DELETE target.

Example

ExampleTable names a table with an alias; TableRef.Col qualifies a column by that alias.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	u := gohan.Table("app.users").As("u")
	sql, args, err := gohan.Select(u.Col("id"), u.Col("name")).From(u).Where(u.Col("id").Eq(1)).Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT "u"."id", "u"."name" FROM "app"."users" AS "u" WHERE "u"."id" = $1 [1] <nil>

func (TableRef) As

func (t TableRef) As(alias string) TableRef

As sets a single-part alias for the table.

func (TableRef) Col

func (t TableRef) Col(name string) Value

Col renders name qualified by the table's alias (if set) or its name.

type UpdateBuilder

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

UpdateBuilder builds an UPDATE statement. Builders are immutable: every method returns a new value and never modifies the receiver.

func Update

func Update(table any) *UpdateBuilder

Update starts an UPDATE statement against table: a string or a TableRef without an alias.

Example

ExampleUpdate builds an UPDATE. A nil or trivially-true WHERE is rejected; see ExampleDelete_requiresWhere.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Update("users").
		Set("name", "bob").
		Where(gohan.Col("id").Eq(1)).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
UPDATE "users" SET "name" = $1 WHERE "id" = $2 [bob 1] <nil>

Example (ClickHouse)

ExampleUpdate_clickHouse shows that the ClickHouse dialect does not build UPDATE statements (it lacks FeatureUpdate).

package main

import (
	"errors"
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	_, _, err := gohan.Update("events").Set("x", 1).Where(gohan.Col("id").Eq(1)).Build(gohan.ClickHouse())
	fmt.Println(errors.Is(err, gohan.ErrUnsupported), err)
}

Output:
true gohan: not supported by dialect: UPDATE

func (*UpdateBuilder) All

func (b *UpdateBuilder) All() *UpdateBuilder

All allows the UPDATE to build with no WHERE clause, or a trivially true one, affecting every row.

Example

ExampleUpdateBuilder_All updates every row on purpose.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Update("users").Set("active", false).All().Build(gohan.SQLite())
	fmt.Println(sql, args, err)
}

Output:
UPDATE `users` SET `active` = ? [false] <nil>

func (*UpdateBuilder) Build

func (b *UpdateBuilder) Build(d Dialect) (string, []any, error)

Build renders the UPDATE statement under dialect d using a fresh writer.

func (*UpdateBuilder) Returning

func (b *UpdateBuilder) Returning(cols ...any) *UpdateBuilder

Returning sets the RETURNING column list.

func (*UpdateBuilder) Set

func (b *UpdateBuilder) Set(col string, v any) *UpdateBuilder

Set appends "col = v" to the SET list.

func (*UpdateBuilder) SetMap

func (b *UpdateBuilder) SetMap(m map[string]any) *UpdateBuilder

SetMap appends one "col = v" per entry of m, in sorted key order.

Example

ExampleUpdateBuilder_SetMap sets several columns from a map, in sorted key order. A value that is an Expr renders in place.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Update("accounts").
		SetMap(map[string]any{
			"balance":    gohan.Raw("balance - ?", 25),
			"updated_by": "system",
		}).
		Where(gohan.Col("id").Eq(9)).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
UPDATE "accounts" SET "balance" = balance - $1, "updated_by" = $2 WHERE "id" = $3 [25 system 9] <nil>

func (*UpdateBuilder) SetRecord

func (b *UpdateBuilder) SetRecord(rec any, opts ...RecordOption) *UpdateBuilder

SetRecord appends one "col = v" per updatable field of rec (a struct or pointer to struct), per opts. Values are extracted immediately (a snapshot): mutating rec afterwards does not change the statement. A shape or option error is deferred and reported at Build.

Example

ExampleUpdateBuilder_SetRecord writes a struct's updatable fields. Auto fields are skipped; IncludeFields/ExcludeFields accept either the Go field name or the column name.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	type User struct {
		ID    int64  `db:"id" auto:"true"`
		Name  string `db:"name"`
		Email string `db:"email"`
		Role  string `db:"role"`
	}
	u := User{ID: 7, Name: "alice", Email: "alice@example.com", Role: "admin"}

	sql, args, err := gohan.Update("users").
		SetRecord(u, gohan.ExcludeFields("Role")).
		Where(gohan.Col("id").Eq(u.ID)).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)

	sql, args, err = gohan.Update("users").
		SetRecord(User{Name: "bob"}, gohan.SkipZeroValues()).
		Where(gohan.Col("id").Eq(8)).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
UPDATE "users" SET "name" = $1, "email" = $2 WHERE "id" = $3 [alice alice@example.com 7] <nil>
UPDATE "users" SET "name" = $1 WHERE "id" = $2 [bob 8] <nil>

func (*UpdateBuilder) Where

func (b *UpdateBuilder) Where(conds ...Expr) *UpdateBuilder

Where appends conds, ANDed with any existing conditions.

Example

ExampleUpdateBuilder_Where shows the trivially-true guard: a WHERE that can only be true (here an empty NotIn) is refused like a missing one.

package main

import (
	"errors"
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	_, _, err := gohan.Update("users").
		Set("active", false).
		Where(gohan.Col("id").NotIn()).
		Build(gohan.Postgres())
	fmt.Println(errors.Is(err, gohan.ErrNoWhere))
}

Output:
true

type Value

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

Value is any scalar or boolean expression. All comparison methods live on Value. The zero Value is invalid and fails to render with ErrNilExpr.

func And

func And(exprs ...Expr) Value

And renders exprs joined by AND, in parentheses (unless there is exactly one). And() renders (1=1).

Example

ExampleAnd shows AND grouping; And() with no arguments is the always-true (1=1).

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("t").
		Where(gohan.Or(
			gohan.And(gohan.Col("a").Eq(1), gohan.Col("b").Eq(2)),
			gohan.Col("c").Eq(3),
		)).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)

	sql, _, _ = gohan.From("t").Where(gohan.And()).Build(gohan.Postgres())
	fmt.Println(sql)
}

Output:
SELECT * FROM "t" WHERE (("a" = $1 AND "b" = $2) OR "c" = $3) [1 2 3] <nil>
SELECT * FROM "t" WHERE (1=1)

func Avg

func Avg(col any) Value

Avg renders AVG(col). col is a column name if it is a string.

func Cast

func Cast(v any, typ string) Value

Cast renders CAST(v AS typ). typ is trusted input — never build it from request data.

Example

ExampleCast gives a bound value a concrete type. The type string is trusted input and is validated.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Select(gohan.Cast("2024-01-01", "date").As("d")).Build(gohan.Postgres())
	fmt.Println(sql, args, err)

	sql, args, err = gohan.Select(gohan.Cast(gohan.Col("price"), "Decimal(10, 2)")).From("items").Build(gohan.ClickHouse())
	fmt.Println(sql, args, err)
}

Output:
SELECT CAST($1 AS date) AS "d" [2024-01-01] <nil>
SELECT CAST("price" AS Decimal(10, 2)) FROM "items" [] <nil>

func Col

func Col(name string) Value

Col renders name as a quoted, escaped identifier.

Example

ExampleCol shows the string-position rule: a string on the right-hand side of a comparison is a bound value; Col makes it a column.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	asValue, args1, _ := gohan.From("t").Where(gohan.Col("a").Eq("b")).Build(gohan.Postgres())
	fmt.Println(asValue, args1)

	asColumn, args2, _ := gohan.From("t").Where(gohan.Col("a").Eq(gohan.Col("b"))).Build(gohan.Postgres())
	fmt.Println(asColumn, args2)
}

Output:
SELECT * FROM "t" WHERE "a" = $1 [b]
SELECT * FROM "t" WHERE "a" = "b" []

func Count

func Count(col any) Value

Count renders COUNT(col). col is a column name if it is a string.

func CountAll

func CountAll() Value

CountAll renders COUNT(*).

func Excluded

func Excluded(col string) Value

Excluded renders "excluded."col"": the proposed-for-insertion value of col, for use inside DoUpdate. ErrUnsupported on dialects without FeatureUpsert.

func Exists

func Exists(q *SelectBuilder) Value

Exists renders "EXISTS (<select>)". If q's combined WHERE is syntactically never (an empty result set) and q has no UNION members, the returned Value is flagged never for the trivially-true/false guards in delete.go/update.go: this over-approximates (e.g. an aggregate subquery over an empty set still returns one row), which only makes the guard refuse more often, never less.

Example

ExampleExists filters with a correlated EXISTS subquery.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From(gohan.Table("users").As("u")).
		Where(gohan.Exists(
			gohan.Select(gohan.Int(1)).From("orders").
				Where(gohan.Col("orders.user_id").Eq(gohan.Col("u.id")), gohan.Col("orders.total").Gt(500)),
		)).
		Build(gohan.Postgres())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
SELECT * FROM "users" AS "u" WHERE EXISTS (SELECT 1 FROM "orders" WHERE ("orders"."user_id" = "u"."id" AND "orders"."total" > $1))
[500] <nil>

func Fn

func Fn(name string, args ...any) Value

Fn renders name(args...). name is trusted input — never build it from request data — and must match ^[A-Za-z_][A-Za-z0-9_]*$.

Example

ExampleFn calls a SQL function. The function name is trusted input and is validated against ^[A-Za-z_][A-Za-z0-9_]*$; arguments are in value position, so pass Col for a column.

package main

import (
	"errors"
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("users").
		Where(gohan.Fn("lower", gohan.Col("email")).Eq("alice@example.com")).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)

	_, _, err = gohan.Select(gohan.Fn("lower(email); --")).From("users").Build(gohan.Postgres())
	fmt.Println(errors.Is(err, gohan.ErrInvalidFunction))
}

Output:
SELECT * FROM "users" WHERE lower("email") = $1 [alice@example.com] <nil>
true

func Int

func Int(n int64) Value

Int renders n as an inline integer literal. Negative values are parenthesized.

Example

ExampleInt renders an integer literal inline instead of binding it; negative values are parenthesized.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Select(gohan.Int(1).As("one"), gohan.Int(-5).As("minus_five")).Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT 1 AS "one", (-5) AS "minus_five" [] <nil>

func Match

func Match(fields map[string]any) Value

Match renders an AND of equality comparisons, one per field, with keys sorted for deterministic output. Match(nil) or an empty map fails with ErrEmptyMatch. It builds each comparison with Eq, so a field value that is untyped nil renders IS NULL; a nil pointer field value is bound as a parameter and matches no row.

Example

ExampleMatch builds an AND of equality comparisons from a map, with keys in sorted order.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("users").
		Where(gohan.Match(map[string]any{"status": "active", "country": "PT"})).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT * FROM "users" WHERE ("country" = $1 AND "status" = $2) [PT active] <nil>

func Max

func Max(col any) Value

Max renders MAX(col). col is a column name if it is a string.

func Min

func Min(col any) Value

Min renders MIN(col). col is a column name if it is a string.

func Not

func Not(e Expr) Value

Not renders NOT (e). If e is a Value, Not swaps its trivial/never flags: the negation of an always-true condition is always-false and vice versa.

Example

ExampleNot negates an expression.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("t").
		Where(gohan.Not(gohan.Or(gohan.Col("a").Eq(1), gohan.Col("b").Eq(2)))).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT * FROM "t" WHERE NOT (("a" = $1 OR "b" = $2)) [1 2] <nil>

func NotExists

func NotExists(q *SelectBuilder) Value

NotExists renders "NOT EXISTS (<select>)". See Exists for the trivial flag.

Example

ExampleNotExists filters with NOT EXISTS.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, _, err := gohan.From(gohan.Table("users").As("u")).
		Where(gohan.NotExists(
			gohan.Select(gohan.Int(1)).From("orders").
				Where(gohan.Col("orders.user_id").Eq(gohan.Col("u.id"))),
		)).
		Build(gohan.SQLite())
	fmt.Println(sql, err)
}

Output:
SELECT * FROM `users` AS `u` WHERE NOT EXISTS (SELECT 1 FROM `orders` WHERE `orders`.`user_id` = `u`.`id`) <nil>

func Or

func Or(exprs ...Expr) Value

Or renders exprs joined by OR, in parentheses (unless there is exactly one). Or() renders (1=0).

Example

ExampleOr shows OR grouping; Or() with no arguments is the always-false (1=0).

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("t").
		Where(gohan.Or(gohan.Col("a").Eq(1), gohan.Col("a").IsNull())).
		Build(gohan.SQLite())
	fmt.Println(sql, args, err)

	sql, _, _ = gohan.From("t").Where(gohan.Or()).Build(gohan.SQLite())
	fmt.Println(sql)
}

Output:
SELECT * FROM `t` WHERE (`a` = ? OR `a` IS NULL) [1] <nil>
SELECT * FROM `t` WHERE (1=0)

func Raw

func Raw(sql string, args ...any) Value

Raw renders sql text verbatim, substituting each `?` marker with the corresponding argument (bound, or rendered inline if it is an Expr). `??` writes a literal `?`, except on ClickHouse where it is rejected. sql is trusted input — never build it from request data.

Example

ExampleRaw shows Raw's two placeholder forms with a real PostgreSQL operator that itself uses `?`: jsonb's key-exists operator. `??` writes a literal `?` (the operator) without consuming an argument; `?` binds the argument in position.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.Select().
		From("docs").
		Where(gohan.Raw("data ?? ?", "owner")).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT * FROM "docs" WHERE (data ? $1) [owner] <nil>

func Sub

func Sub(q *SelectBuilder) Value

Sub renders a scalar subquery: "(<select>)". It shares the outer statement's writer so placeholders keep counting across nesting.

Example

ExampleSub compares against a scalar subquery.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	avg := gohan.Select(gohan.Avg("total")).From("orders").Where(gohan.Col("status").Eq("paid"))

	sql, args, err := gohan.Select("id", "total").
		From("orders").
		Where(gohan.Col("total").Gt(gohan.Sub(avg))).
		Build(gohan.Postgres())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
SELECT "id", "total" FROM "orders" WHERE "total" > (SELECT AVG("total") FROM "orders" WHERE "status" = $1)
[paid] <nil>

func Sum

func Sum(col any) Value

Sum renders SUM(col). col is a column name if it is a string.

func Val

func Val(v any) Value

Val binds v as an argument. Val(nil) renders the keyword NULL; a nil pointer is bound as a parameter (the driver sends NULL), so it no longer has a literal to attach IsNull()/IsNotNull() to — e.g. Val(p).IsNull() renders "$1 IS NULL", which PostgreSQL rejects (42P18: could not determine data type of parameter). Use Val(nil) for a NULL literal. Val(expr) renders expr in place: a *Value or *Order is dereferenced to the value it points to (so it renders and classifies exactly like that value), and Val(nv) with a nil *Value/*Order fails with ErrNilExpr at render.

Example

ExampleVal binds a value where a column name would otherwise be assumed, here as the left-hand side of IN. Val(nil) renders NULL.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("users").
		Where(gohan.Val("admin").In(gohan.Col("role"), gohan.Col("backup_role"))).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)

	sql, args, err = gohan.Select(gohan.Val(nil).As("nothing")).Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT * FROM "users" WHERE $1 IN ("role", "backup_role") [admin] <nil>
SELECT NULL AS "nothing" [] <nil>

func (Value) As

func (v Value) As(alias string) Expr

As renders "v AS alias". alias must be a single-part identifier.

Example

ExampleValue_As aliases an expression in the select list.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, _, err := gohan.Select(gohan.Fn("upper", gohan.Col("name")).As("name_uc")).From("users").Build(gohan.SQLite())
	fmt.Println(sql, err)
}

Output:
SELECT upper(`name`) AS `name_uc` FROM `users` <nil>

func (Value) Asc

func (v Value) Asc() Order

Asc renders "v ASC".

func (Value) Between

func (v Value) Between(lo, hi any) Value

Between renders "v BETWEEN lo AND hi".

Example

ExampleValue_Between renders an inclusive range.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("orders").
		Where(gohan.Col("total").Between(10, 100)).
		Build(gohan.SQLite())
	fmt.Println(sql, args, err)
}

Output:
SELECT * FROM `orders` WHERE `total` BETWEEN ? AND ? [10 100] <nil>

func (Value) Contains

func (v Value) Contains(s string) Value

Contains renders a LIKE match for "%s%", with LIKE wildcards in s escaped.

Example

ExampleValue_Contains escapes LIKE wildcards in the search text, so "50%_off" matches literally. The escape character differs between ClickHouse (backslash) and the other dialects (ESCAPE '!').

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	q := gohan.From("products").Where(gohan.Col("name").Contains("50%_off"))

	for _, d := range []gohan.Dialect{gohan.Postgres(), gohan.SQLite(), gohan.ClickHouse()} {
		sql, args, err := q.Build(d)
		fmt.Println(sql, args, err)
	}
}

Output:
SELECT * FROM "products" WHERE "name" LIKE $1 ESCAPE '!' [%50!%!_off%] <nil>
SELECT * FROM `products` WHERE `name` LIKE ? ESCAPE '!' [%50!%!_off%] <nil>
SELECT * FROM "products" WHERE "name" LIKE ? [%50\%\_off%] <nil>

func (Value) ContainsFold

func (v Value) ContainsFold(s string) Value

ContainsFold is the case-insensitive Contains: it renders ILIKE on dialects with FeatureILike and LIKE on SQLite, whose LIKE only folds ASCII letters. ErrUnsupported on other dialects.

Example

ExampleValue_ContainsFold is the case-insensitive Contains: ILIKE on PostgreSQL and ClickHouse, LIKE on SQLite (ASCII letters only), with the same wildcard escaping as Contains. ErrUnsupported on Generic.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	q := gohan.From("products").Where(gohan.Col("name").ContainsFold("50%_Off"))

	for _, d := range []gohan.Dialect{gohan.Postgres(), gohan.SQLite(), gohan.ClickHouse(), gohan.Generic()} {
		sql, args, err := q.Build(d)
		fmt.Println(sql, args, err)
	}
}

Output:
SELECT * FROM "products" WHERE "name" ILIKE $1 ESCAPE '!' [%50!%!_Off%] <nil>
SELECT * FROM `products` WHERE `name` LIKE ? ESCAPE '!' [%50!%!_Off%] <nil>
SELECT * FROM "products" WHERE "name" ILIKE ? [%50\%\_Off%] <nil>
 [] gohan: not supported by dialect: ILIKE

func (Value) Desc

func (v Value) Desc() Order

Desc renders "v DESC".

func (Value) Eq

func (v Value) Eq(x any) Value

Eq renders "v = x", or "v IS NULL" when x is untyped nil. A nil pointer is not untyped nil: it is bound as a parameter, so "v = NULL" matches no row. Use Eq(nil) or IsNull() to test for NULL.

Example

ExampleValue_Eq shows that comparing with nil renders IS NULL / IS NOT NULL instead of binding a NULL (which would never match).

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("users").
		Where(gohan.Col("deleted_at").Eq(nil), gohan.Col("email").Neq(nil)).
		Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT * FROM "users" WHERE ("deleted_at" IS NULL AND "email" IS NOT NULL) [] <nil>

func (Value) Gt

func (v Value) Gt(x any) Value

func (Value) Gte

func (v Value) Gte(x any) Value

func (Value) HasPrefix

func (v Value) HasPrefix(s string) Value

HasPrefix renders a LIKE match for "s%", with LIKE wildcards in s escaped.

Example

ExampleValue_HasPrefix matches a literal prefix.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("files").Where(gohan.Col("path").HasPrefix("/tmp/")).Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT * FROM "files" WHERE "path" LIKE $1 ESCAPE '!' [/tmp/%] <nil>

func (Value) HasPrefixFold

func (v Value) HasPrefixFold(s string) Value

HasPrefixFold is the case-insensitive HasPrefix; see ContainsFold.

func (Value) HasSuffix

func (v Value) HasSuffix(s string) Value

HasSuffix renders a LIKE match for "%s", with LIKE wildcards in s escaped.

func (Value) HasSuffixFold

func (v Value) HasSuffixFold(s string) Value

HasSuffixFold is the case-insensitive HasSuffix; see ContainsFold.

func (Value) ILike

func (v Value) ILike(p any) Value

ILike renders "v ILIKE p". ErrUnsupported on dialects without ILIKE.

Example

ExampleValue_ILike is available on PostgreSQL and ClickHouse only (FeatureILike); SQLite's LIKE is already ASCII case-insensitive.

package main

import (
	"errors"
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	q := gohan.From("users").Where(gohan.Col("name").ILike("ann%"))

	sql, args, err := q.Build(gohan.Postgres())
	fmt.Println(sql, args, err)

	_, _, err = q.Build(gohan.SQLite())
	fmt.Println(errors.Is(err, gohan.ErrUnsupported), err)
}

Output:
SELECT * FROM "users" WHERE "name" ILIKE $1 [ann%] <nil>
true gohan: not supported by dialect: ILIKE

func (Value) In

func (v Value) In(values ...any) Value

In renders "v IN (values...)", expanding a single slice argument. With no values (after expansion) it renders 1=0. A single *SelectBuilder argument renders "v IN (<select>)".

Example

ExampleValue_In expands a single slice argument into one placeholder per element; an empty list renders the always-false 1=0.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	ids := []int{3, 5, 8}
	sql, args, err := gohan.From("users").Where(gohan.Col("id").In(ids)).Build(gohan.Postgres())
	fmt.Println(sql, args, err)

	sql, args, err = gohan.From("users").Where(gohan.Col("id").In([]int{})).Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT * FROM "users" WHERE "id" IN ($1, $2, $3) [3 5 8] <nil>
SELECT * FROM "users" WHERE 1=0 [] <nil>

Example (Subquery)

ExampleValue_In_subquery passes a *SelectBuilder to In. The subquery shares the outer statement's placeholder numbering.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	banned := gohan.Select("user_id").From("bans").Where(gohan.Col("reason").Eq("spam"))

	sql, args, err := gohan.From("comments").
		Where(gohan.Col("user_id").In(banned), gohan.Col("hidden").Eq(false)).
		Build(gohan.Postgres())
	fmt.Println(sql)
	fmt.Println(args, err)
}

Output:
SELECT * FROM "comments" WHERE ("user_id" IN (SELECT "user_id" FROM "bans" WHERE "reason" = $1) AND "hidden" = $2)
[spam false] <nil>

func (Value) IsNotNull

func (v Value) IsNotNull() Value

IsNotNull renders "v IS NOT NULL".

func (Value) IsNull

func (v Value) IsNull() Value

IsNull renders "v IS NULL".

func (Value) Like

func (v Value) Like(p any) Value

Like renders "v LIKE p".

Example

ExampleValue_Like binds the pattern as-is: wildcards in it are live. Use Contains/HasPrefix/HasSuffix for untrusted search text.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("users").Where(gohan.Col("email").Like("%@example.com")).Build(gohan.Postgres())
	fmt.Println(sql, args, err)
}

Output:
SELECT * FROM "users" WHERE "email" LIKE $1 [%@example.com] <nil>

func (Value) Lt

func (v Value) Lt(x any) Value

func (Value) Lte

func (v Value) Lte(x any) Value

func (Value) Neq

func (v Value) Neq(x any) Value

Neq renders "v <> x", or "v IS NOT NULL" when x is untyped nil. A nil pointer is not untyped nil: it is bound as a parameter, so "v <> NULL" matches no row. Use Neq(nil) or IsNotNull() to test for NOT NULL.

func (Value) NotBetween

func (v Value) NotBetween(lo, hi any) Value

NotBetween renders "v NOT BETWEEN lo AND hi".

func (Value) NotILike

func (v Value) NotILike(p any) Value

NotILike renders "v NOT ILIKE p". ErrUnsupported on dialects without ILIKE.

func (Value) NotIn

func (v Value) NotIn(values ...any) Value

NotIn renders "v NOT IN (values...)", expanding a single slice argument. With no values (after expansion) it renders 1=1. A single *SelectBuilder argument renders "v NOT IN (<select>)".

Example

ExampleValue_NotIn shows NOT IN; an empty list renders the always-true 1=1.

package main

import (
	"fmt"

	"github.com/oddbit-project/gohan"
)

func main() {
	sql, args, err := gohan.From("users").Where(gohan.Col("status").NotIn("banned", "deleted")).Build(gohan.SQLite())
	fmt.Println(sql, args, err)

	sql, args, err = gohan.From("users").Where(gohan.Col("status").NotIn()).Build(gohan.SQLite())
	fmt.Println(sql, args, err)
}

Output:
SELECT * FROM `users` WHERE `status` NOT IN (?, ?) [banned deleted] <nil>
SELECT * FROM `users` WHERE 1=1 [] <nil>

func (Value) NotLike

func (v Value) NotLike(p any) Value

NotLike renders "v NOT LIKE p".