qb

package
v1.0.1 Latest Latest
Warning

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

 Go to latest Published: May 15, 2026 License: MIT Imports: 7 Imported by: 0

Documentation

Overview

Package qb is a standalone PostgreSQL query builder.

qb has no runtime dependency on a database connection — it only uses pgx for safe identifier quoting. Import it independently of any pool code.

Building Queries

All builder methods return the same pointer for chaining. Errors accumulate and are returned on the first Build* call: 

sql, args, err := qb.New("students").
    Columns("id", "name").
    Where(qb.Where("school_id", qb.OpEq, schoolID)).
    Where(qb.WhereExists(
        qb.New("enrolments").Where(qb.Where("student_id", qb.OpEq, studentID)),
    )).
    OrderBy("name", qb.Asc, qb.NullsLast).
    Limit(20).
    BuildSelect()

Supported Statements

Condition Constructors

Use the Where* helpers instead of raw Condition literals: 

qb.Where("price", qb.OpGt, 100)
qb.WhereIn("status", []string{"active", "pending"})
qb.WhereNull("deleted_at")
qb.WhereTextSearch("search_vector", "go & postgres")
qb.WhereJSONContains("metadata", map[string]any{"plan": "pro"})
qb.WhereExists(qb.New("orders").Where(qb.Where("user_id", qb.OpEq, uid)))
qb.WhereRaw("lower(email) = ?", email)

Security

Column names passed to Builder.Columns and Where* helpers are quoted via QuoteIdent. Raw expressions (e.g. Builder.Having, JOIN conditions, Builder.OnConflict) are written verbatim — never interpolate user input into those fields.

File Layout 

types.go     — enum-like constants (JoinType, SortDir, LockMode, …)
builder.go   — Builder struct, New, Clone, lifecycle
quote.go     — identifier sanitisation (security-critical)
params.go    — placeholder helpers ($N shifting, ? injection)
select.go    — Columns/Distinct/WindowCol + BuildSelect
insert.go    — OnConflict + BuildInsert + BuildInsertBatch
update.go    — BuildUpdate
delete.go    — BuildDelete
where.go     — Where, WhereGroup, condition rendering
join.go      — INNER/LEFT/RIGHT/FULL/LATERAL joins
cte.go       — With, WithRecursive
union.go     — Union, UnionAll
clauses.go   — GROUP BY, ORDER BY, LIMIT, OFFSET, locking, RETURNING setters
condition.go — Operator constants + Condition constructors

Index

Examples

Constants

View Source 
const MaxQueryParams = 65535

MaxQueryParams is the PostgreSQL limit on bound parameters per query.

Variables

View Source 
var (
	// ErrNilBuilder is returned when a Builder method is called on a nil builder.
	ErrNilBuilder = fmt.Errorf("qb: builder is nil")

	// ErrNoTable is returned when the Builder has no target table.
	ErrNoTable = fmt.Errorf("qb: table name must not be empty")

	// ErrEmptyData is returned when INSERT or UPDATE data is empty.
	ErrEmptyData = fmt.Errorf("qb: data map must not be empty")

	// ErrEmptyRows is returned when batch rows are empty.
	ErrEmptyRows = fmt.Errorf("qb: batch rows must not be empty")
)

Functions

func OrGroup 

func OrGroup(conds ...Condition) condGroup

OrGroup wraps conditions joined with OR inside parentheses: 

WHERE (status = $1 OR status = $2) AND total > $3

func QuoteIdent 

func QuoteIdent(name string) string

QuoteIdent safely quotes a single PostgreSQL identifier. 

QuoteIdent("my table") → "my table"

Types

type Builder 

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

Builder constructs parameterised PostgreSQL queries. All methods return the same pointer for chaining. Errors accumulate and are returned on the first Build* call.

func New 

func New(table string) *Builder

New creates a Builder targeting table.

Example 
package main

import (
	"fmt"

	"github.com/rajangupta9/pgkit/qb"
)

func main() {
	sql, args, err := qb.New("users").
		Columns("id", "name").
		Where(qb.Where("active", qb.OpEq, true)).
		OrderBy("created_at", qb.Desc).
		Limit(10).
		BuildSelect()
	if err != nil {
		return
	}

	fmt.Println(sql)
	fmt.Println(args)

}

Output:
SELECT "id", "name" FROM "users" WHERE "active" = $1 ORDER BY "created_at" DESC LIMIT 10
[true]

func (*Builder) BuildDelete 

func (b *Builder) BuildDelete() (sql string, args []any, err error)

BuildDelete produces: 

[WITH …] DELETE FROM "table" WHERE … [RETURNING …]

func (*Builder) BuildInsert 

func (b *Builder) BuildInsert(data map[string]any) (sql string, args []any, err error)

BuildInsert produces: 

[WITH …] INSERT INTO "table" (cols) VALUES ($1,…) [ON CONFLICT …] RETURNING "id"

Default RETURNING is "id". Override with ReturningAll() / Returning(…) / ReturningNone().

func (*Builder) BuildInsertBatch 

func (b *Builder) BuildInsertBatch(rows []map[string]any) (sql string, args []any, err error)

BuildInsertBatch inserts multiple rows in one statement. 

INSERT INTO "table" (col1, col2) VALUES ($1,$2), ($3,$4), … RETURNING "id"

All rows must have identical key sets (first row defines the column list).

func (*Builder) BuildSelect 

func (b *Builder) BuildSelect() (sql string, args []any, err error)

BuildSelect produces a full parameterised SELECT statement starting at $1.

func (*Builder) BuildUpdate 

func (b *Builder) BuildUpdate(data map[string]any) (sql string, args []any, err error)

BuildUpdate produces: 

[WITH …] UPDATE "table" SET col=$1,… WHERE … [RETURNING …]

func (*Builder) Clone 

func (b *Builder) Clone() *Builder

Clone returns a deep copy of the builder. Safe to modify without affecting the original.

func (*Builder) Columns 

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

Columns appends expressions to the SELECT list. Plain column names are auto-quoted; expressions like "COUNT(*)", "u.email", "SUM(total) AS total" are written verbatim.

func (*Builder) Distinct 

func (b *Builder) Distinct() *Builder

Distinct adds DISTINCT to the SELECT clause.

func (*Builder) ForShare 

func (b *Builder) ForShare(wait LockWait) *Builder

ForShare appends FOR SHARE [NOWAIT | SKIP LOCKED].

func (*Builder) ForUpdate 

func (b *Builder) ForUpdate(wait LockWait) *Builder

ForUpdate appends FOR UPDATE [NOWAIT | SKIP LOCKED].

func (*Builder) FullJoin 

func (b *Builder) FullJoin(table, condition string) *Builder

FullJoin appends a FULL JOIN clause.

func (*Builder) GroupBy 

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

GroupBy appends columns to GROUP BY.

func (*Builder) HasReturning 

func (b *Builder) HasReturning() bool

HasReturning reports whether the builder has an explicit RETURNING clause.

func (*Builder) Having 

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

Having sets a raw HAVING expression. Use ? as placeholder. SECURITY: expr is written verbatim; never let user input flow in. 

b.Having("SUM(total) > ?", 500)

func (*Builder) InnerJoin 

func (b *Builder) InnerJoin(table, condition string) *Builder

InnerJoin appends an INNER JOIN clause.

func (*Builder) Join 

func (b *Builder) Join(kind JoinType, table, condition string) *Builder

Join appends any JOIN variant. table is quoted as a table expression; condition is written verbatim. Never pass user input directly to condition. For CROSS JOIN pass "" as condition.

func (*Builder) LateralJoin 

func (b *Builder) LateralJoin(kind JoinType, sub *Builder, alias string) *Builder

LateralJoin appends a LATERAL subquery join: 

LEFT JOIN LATERAL (SELECT …) AS alias ON TRUE

func (*Builder) LeftJoin 

func (b *Builder) LeftJoin(table, condition string) *Builder

LeftJoin appends a LEFT JOIN clause.

func (*Builder) Limit 

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

Limit sets the maximum number of rows. 0 = no limit.

func (*Builder) Lock 

func (b *Builder) Lock(mode LockMode, wait LockWait) *Builder

Lock sets a custom lock mode.

func (*Builder) Offset 

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

Offset sets the number of rows to skip. 0 = no offset.

func (*Builder) OnConflict 

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

OnConflict sets the ON CONFLICT clause for INSERT (upsert). The clause is written verbatim — never let user input flow in. 

b.OnConflict("(email) DO UPDATE SET name = EXCLUDED.name, updated_at = NOW()")
b.OnConflict("(id) DO NOTHING")

func (*Builder) OrderBy 

func (b *Builder) OrderBy(col string, dir SortDir, nulls ...NullsOrder) *Builder

OrderBy appends a column to ORDER BY. 

b.OrderBy("created_at", qb.Desc, qb.NullsLast)

func (*Builder) Returning 

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

Returning appends RETURNING col1, col2, …

func (*Builder) ReturningAll 

func (b *Builder) ReturningAll() *Builder

ReturningAll appends RETURNING *.

func (*Builder) ReturningID 

func (b *Builder) ReturningID() *Builder

ReturningID appends RETURNING "id". Default for BuildInsert.

func (*Builder) ReturningNone 

func (b *Builder) ReturningNone() *Builder

ReturningNone suppresses any RETURNING clause, overriding the INSERT default.

func (*Builder) RightJoin 

func (b *Builder) RightJoin(table, condition string) *Builder

RightJoin appends a RIGHT JOIN clause.

func (*Builder) Union 

func (b *Builder) Union(other *Builder) *Builder

Union appends UNION (deduplicating) with another builder.

func (*Builder) UnionAll 

func (b *Builder) UnionAll(other *Builder) *Builder

UnionAll appends UNION ALL (no deduplication).

func (*Builder) Where 

func (b *Builder) Where(c Condition) *Builder

Where appends a single Condition ANDed into the WHERE clause.

func (*Builder) WhereGroup 

func (b *Builder) WhereGroup(g condGroup) *Builder

WhereGroup appends a condGroup (single or OR-group) ANDed into WHERE. 

b.WhereGroup(qb.OrGroup(
    qb.Where("status", qb.OpEq, "active"),
    qb.Where("status", qb.OpEq, "pending"),
))
→ WHERE ("status" = $1 OR "status" = $2)

func (*Builder) WindowCol 

func (b *Builder) WindowCol(fn, partitionBy, orderBy, alias string) *Builder

WindowCol appends a window function expression to the SELECT list.

fn is the window function call (e.g. "RANK()", "SUM(total)", "LAG(price)"). partitionBy is the PARTITION BY column (empty string = no partition). orderBy is the ORDER BY expression written verbatim (e.g. "total DESC"). alias is the AS name for the column.

SECURITY: fn and orderBy are written verbatim — never pass user input. 

qb.New("orders").
    Columns("user_id", "country", "total").
    WindowCol("RANK()", "country", "total DESC", "rank").
    WindowCol("SUM(total)", "country", "created_at", "running_total")
→ SELECT "user_id", "country", "total",
         RANK() OVER (PARTITION BY "country" ORDER BY total DESC) AS "rank",
         SUM(total) OVER (PARTITION BY "country" ORDER BY created_at) AS "running_total"

func (*Builder) With 

func (b *Builder) With(name, query string, args ...any) *Builder

With prepends a CTE. CTE args are prepended to the full argument list so their $N parameters come first. 

b.With("recent", "SELECT id FROM orders WHERE created_at > $1", cutoff)

func (*Builder) WithRecursive 

func (b *Builder) WithRecursive(name, query string, args ...any) *Builder

WithRecursive prepends a RECURSIVE CTE.

type Condition 

type Condition struct {
	// Column is the quoted column name or qualified reference (e.g. "price",
	// "orders.total"). Ignored for OpExists / OpNotExists / OpRaw.
	Column string

	// Operator is the SQL operator to apply. Use the Op* constants.
	Operator Operator

	// Value is the right-hand side of the predicate. For OpIn / OpNotIn it
	// must be a slice; for OpBetween / OpNotBetween a two-element slice; for
	// OpIsNull / OpNotNull it is ignored; for OpRaw it is a rawExpr.
	Value any

	// Sub is set for EXISTS / NOT EXISTS / column-IN-subquery conditions.
	Sub *Builder
}

Condition is a single predicate in a WHERE clause. Prefer the Where* constructor functions over building Condition literals directly.

func Where 

func Where(col string, op Operator, val any) Condition

Where constructs a simple column predicate.

func WhereAll 

func WhereAll(col string, val any) Condition

WhereAll matches rows where col = ALL(array).

func WhereAny 

func WhereAny(col string, val any) Condition

WhereAny matches rows where col = ANY(array). val must be a typed slice; pgx binds it as a PostgreSQL array.

func WhereArrayContains 

func WhereArrayContains(col string, val any) Condition

WhereArrayContains checks array_col @> val.

func WhereArrayOverlap 

func WhereArrayOverlap(col string, val any) Condition

WhereArrayOverlap checks array_col && val (any element in common).

func WhereBetween 

func WhereBetween(col string, low, high any) Condition

WhereBetween constructs a BETWEEN predicate.

func WhereExists 

func WhereExists(sub *Builder) Condition

WhereExists appends EXISTS (subquery).

func WhereIn 

func WhereIn(col string, vals any) Condition

WhereIn constructs a predicate for IN with a slice of values.

func WhereJSONContains 

func WhereJSONContains(col string, val any) Condition

WhereJSONContains checks jsonb_col @> val (JSON superset).

func WhereJSONHasKey 

func WhereJSONHasKey(col, key string) Condition

WhereJSONHasKey checks jsonb_col ? key.

func WhereNotBetween 

func WhereNotBetween(col string, low, high any) Condition

WhereNotBetween constructs a NOT BETWEEN predicate.

func WhereNotExists 

func WhereNotExists(sub *Builder) Condition

WhereNotExists appends NOT EXISTS (subquery).

func WhereNotIn 

func WhereNotIn(col string, vals any) Condition

WhereNotIn constructs a predicate for NOT IN with a slice of values.

func WhereNotNull 

func WhereNotNull(col string) Condition

WhereNotNull constructs a predicate for IS NOT NULL.

func WhereNull 

func WhereNull(col string) Condition

WhereNull constructs a predicate for IS NULL.

func WhereRaw 

func WhereRaw(expr string, args ...any) Condition

WhereRaw appends a raw SQL fragment. Use ? as placeholder. 

WhereRaw("lower(email) = ?", email)
WhereRaw("age BETWEEN ? AND ?", 18, 65)

func WhereSubquery 

func WhereSubquery(col string, op Operator, sub *Builder) Condition

WhereSubquery appends col op (subquery) — e.g. id IN (SELECT id FROM ...). op should be OpIn / OpNotIn or a comparison operator.

func WhereTextSearch 

func WhereTextSearch(col, tsquery string) Condition

WhereTextSearch checks tsvector_col @@ tsquery.

func (Condition) Validate 

func (c Condition) Validate() error

Validate checks that the condition contains the required fields for the selected operator.

type JoinType 

type JoinType string

JoinType is the SQL JOIN variant used by Builder.Join and its helpers. 

const (
	JoinInner JoinType = "INNER" // INNER JOIN — only matching rows from both sides.
	JoinLeft  JoinType = "LEFT"  // LEFT JOIN — all rows from the left, matched rows from the right.
	JoinRight JoinType = "RIGHT" // RIGHT JOIN — all rows from the right, matched rows from the left.
	JoinFull  JoinType = "FULL"  // FULL JOIN — all rows from both sides.
	JoinCross JoinType = "CROSS" // CROSS JOIN — cartesian product; no ON condition.
)

type LockMode 

type LockMode string

LockMode controls the row-level locking clause appended to a SELECT. Pass to Builder.Lock; or use the convenience methods Builder.ForUpdate and Builder.ForShare. 

const (
	LockForUpdate      LockMode = "FOR UPDATE"        // Exclusive lock; blocks all other lock modes.
	LockForShare       LockMode = "FOR SHARE"         // Shared lock; allows other FOR SHARE locks.
	LockForNoKeyUpdate LockMode = "FOR NO KEY UPDATE" // Like FOR UPDATE but does not block FOR KEY SHARE.
	LockForKeyShare    LockMode = "FOR KEY SHARE"     // Minimal lock; only blocks FOR UPDATE.
)

type LockWait 

type LockWait string

LockWait controls the wait behaviour when the desired row lock cannot be acquired immediately. Passed to Builder.ForUpdate, Builder.ForShare, and Builder.Lock. 

const (
	Wait       LockWait = ""            // Block until the lock is available (default behaviour).
	NoWait     LockWait = "NOWAIT"      // Return an error immediately if the lock cannot be acquired.
	SkipLocked LockWait = "SKIP LOCKED" // Skip rows that are already locked rather than waiting.
)

type NullsOrder 

type NullsOrder string

NullsOrder controls NULL placement in ORDER BY, passed as an optional third argument to Builder.OrderBy. 

const (
	NullsFirst NullsOrder = "NULLS FIRST" // NULLs sort before all non-null values.
	NullsLast  NullsOrder = "NULLS LAST"  // NULLs sort after all non-null values.
)

type Operator 

type Operator string

Operator is a SQL comparison or containment operator. 

const (
	// Scalar comparison
	OpEq    Operator = "="
	OpNotEq Operator = "<>"
	OpLt    Operator = "<"
	OpLte   Operator = "<="
	OpGt    Operator = ">"
	OpGte   Operator = ">="

	// Pattern matching
	OpLike  Operator = "LIKE"
	OpILike Operator = "ILIKE"

	// Membership
	OpIn    Operator = "IN"
	OpNotIn Operator = "NOT IN"

	// Null checks (no value needed)
	OpIsNull  Operator = "IS NULL"
	OpNotNull Operator = "IS NOT NULL"

	// Range
	OpBetween    Operator = "BETWEEN"
	OpNotBetween Operator = "NOT BETWEEN"

	// PostgreSQL array operators — value must be a Go slice bound as a pgx array.
	OpAny Operator = "= ANY" // col = ANY($n)
	OpAll Operator = "= ALL" // col = ALL($n)

	// Array containment
	OpArrayContains    Operator = "@>" // array_col @> $n
	OpArrayContainedBy Operator = "<@" // array_col <@ $n
	OpArrayOverlap     Operator = "&&" // array_col && $n

	// JSONB operators
	OpJSONContains    Operator = "@>" // jsonb_col @> $n
	OpJSONContainedBy Operator = "<@" // jsonb_col <@ $n
	OpJSONHasKey      Operator = "?"  // jsonb_col ? $n
	OpJSONHasAllKeys  Operator = "?&" // jsonb_col ?& $n
	OpJSONHasAnyKey   Operator = "?|" // jsonb_col ?| $n

	// Full-text search
	OpTextSearch Operator = "@@" // tsvector_col @@ tsquery

	// Internal — handled specially by Builder.renderCond
	OpExists    Operator = "__EXISTS__"
	OpNotExists Operator = "__NOT_EXISTS__"
	OpSubquery  Operator = "__SUBQUERY__"
	OpRaw       Operator = "__RAW__"
)

type SortDir 

type SortDir string

SortDir is the ORDER BY direction used by Builder.OrderBy. 

const (
	Asc  SortDir = "ASC"  // Ascending order (smallest first).
	Desc SortDir = "DESC" // Descending order (largest first).
)

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.