Query Builder

quark.For[Model](ctx, client) returns a *quark.Query[Model]. The builder is immutable: every method that changes the query returns a clone, so a shared base query is safe to branch and reuse.

base := quark.For[User](ctx, client).Where("active", "=", true)

admins := base.Where("role", "=", "admin")  // base is untouched
editors := base.Where("role", "=", "editor")

Pick an execution method

A query does nothing until you finish it with one of these:

Method	Use it when
List()	You want a slice. (Applies a safe default Limit(100) if you didn't set one.)
First()	You want one matching row, or ErrNotFound.
Find(id)	You want one row by a simple primary key.
Count()	You want a matching-row count.
Paginate(size, page)	You want rows plus total/page metadata.
Iter(fn) / Cursor()	You want to stream rows instead of buffering them.

The rest of this page comes in two halves. First the everyday building blocks: filter, sort, paginate, aggregate, join, stream. Then advanced composition — the expression AST, subqueries, set operators, window functions, CTEs — and locking, for when you need them.

---

Filtering

users, err := quark.For[User](ctx, client).
    Where("active", "=", true).
    Where("age", ">=", 18).
    Where("email", "LIKE", "%@acme.com").
    OrderBy("created_at", "DESC").
    Limit(25).
    List()

Column names and operators are validated before any SQL is built, and values always travel as bound parameters. The operators Where accepts:

Family	Operators
Equality	=, !=, <>
Comparison	<, <=, >, >=
Pattern	LIKE, NOT LIKE
Null	IS NULL, IS NOT NULL
Set / range	IN, NOT IN, BETWEEN, NOT BETWEEN

A null check takes nil as its value:

quark.For[User](ctx, client).Where("deleted_at", "IS NULL", nil).List()

Strict column names

The identifier validation above stops injection, but a typo is still a valid identifier — and what happens next depends on the engine: PostgreSQL fails at runtime with its own error, while SQLite degrades the double-quoted unknown column to a string literal, so Where("agee", ">", 1) silently matches every row with no error at all.

The opt-in WithStrictColumns client option closes that gap: plain column references in Where / WhereIn / WhereBetween / OrderBy / GroupBy / Select / Having and the aggregates must be columns the model declares, or the query fails with ErrInvalidQuery naming the unknown column and listing the valid ones:

client, err := quark.New("pgx", dsn, quark.WithStrictColumns())

_, err = quark.For[User](ctx, client).Where("agee", ">", 1).List()
// invalid query: unknown column "agee" for table users (known columns: id, name, age) …

Escape hatches, by design: queries with joins are exempt (they legitimately reference the other table's columns), the expression AST and raw paths are never checked, and OrderBy / GroupBy may reference a SelectExpr alias. The option is off by default for backward compatibility.

Sets and ranges

users, err := quark.For[User](ctx, client).
    WhereIn("role", []any{"admin", "editor"}).
    WhereBetween("created_at", start, end).
    List()

Don't call WhereIn with an empty slice: most engines reject IN (). Handle the empty case in Go instead:

if len(ids) == 0 {
    return []User{}, nil
}
users, err := quark.For[User](ctx, client).WhereIn("id", ids).List()

When your values arrive as a typed slice — []int64 of ids from a previous query is the common case — use WhereInOf instead of hand-converting to []any. It is a package-level function (Go methods cannot add a second type parameter) with the same semantics as WhereIn:

ids := []int64{1, 2, 3}
users, err := quark.WhereInOf(quark.For[User](ctx, client), "id", ids).List()

DeleteBatchOf does the same for DeleteBatch.

Negation and OR groups

WhereNot wraps a condition in NOT. Or takes a callback whose conditions are grouped with AND, then attached to the outer query with OR:

users, err := quark.For[User](ctx, client).
    Where("active", "=", true).
    Or(func(q *quark.Query[User]) *quark.Query[User] {
        return q.Where("role", "=", "admin").Where("verified", "=", true)
    }).
    List()
// WHERE active = ? OR (role = ? AND verified = ?)

For predicates that nest deeper than Or reads well, reach for the expression AST further down.

Sorting, limits, and pagination

users, err := quark.For[User](ctx, client).
    OrderBy("created_at", "DESC").
    OrderBy("id", "ASC").
    Limit(20).
    Offset(40).
    List()

The direction is validated: "ASC" and "DESC" match case-insensitively, the empty string means ASC, and anything else fails with ErrInvalidQuery at execution — it is not silently treated as ascending.

Paginate(pageSize, page) runs the count and the limited select together and hands back a Page[T] with everything a paginated UI needs. Pages are zero-indexed — Paginate(20, 0) is the first page:

page, err := quark.For[User](ctx, client).
    Where("active", "=", true).
    OrderBy("id", "ASC").
    Paginate(20, 0)

fmt.Println(page.Total, page.TotalPages, len(page.Items))
// => 3 1 3   (3 matching rows, 1 page, 3 on this page)

Selecting specific columns

users, err := quark.For[User](ctx, client).
    Select("id", "email", "name").
    Limit(100).
    List()

Select takes column identifiers from the model's table and validates each one. It is deliberately not a raw projection API: expressions like COUNT(*) AS count or orders.total belong to the aggregate helpers below, to a view-backed read model, or to raw SQL.

Distinct pairs with simple column selection:

roles, err := quark.For[User](ctx, client).Select("role").Distinct().List()

Aggregates

total, err := quark.For[Order](ctx, client).Where("status", "=", "paid").Sum("amount")
// => 600

avg, _ := quark.For[Order](ctx, client).Avg("amount")
min, _ := quark.For[Order](ctx, client).Min("amount")
max, _ := quark.For[Order](ctx, client).Max("amount")

Count, Sum, Avg, Min, and Max respect Where, the soft-delete filter, and tenant isolation.

Grouped aggregates and HAVING

GroupBy with HavingAggregate filters on a group's aggregate without dropping to raw SQL:

groups, err := quark.For[Order](ctx, client).
    GroupBy("status").
    HavingAggregate("COUNT", "*", ">", 5).
    List()
// SELECT * FROM "orders" GROUP BY "status" HAVING COUNT(*) > $1

HavingAggregate(fn, column, op, value) rules:

Rule	Why
fn ∈ COUNT, SUM, AVG, MIN, MAX (case-insensitive)	Whitelist; other functions are rejected.
column == "*" only with COUNT	SUM(*) etc. isn't valid SQL.
Any other column is validated as an identifier	Same safety rule as Where.
op is the standard comparison whitelist	=, !=, <, >=, IN, BETWEEN, IS NULL, …

The plain Having(column, op, value) validates column as a plain identifier, so it's for post-aggregation filtering on a non-aggregate column. For aggregate predicates the helper can't express, use HavingExpr.

JSON predicates

WhereJSON asks the dialect to build a JSON-extraction comparison:

users, err := quark.For[User](ctx, client).
    WhereJSON("metadata", "plan", "=", "enterprise").
    List()

Paths are dotted identifiers, validated against ^[a-zA-Z_][a-zA-Z0-9_]*(\.[a-zA-Z_][a-zA-Z0-9_]*)*$ and capped at 256 characters; anything else returns ErrInvalidJSONPath. Array indexes and engine-specific JSONPath operators are out of scope — use raw SQL for those.

Each engine gets its native shape. The path is bound as a parameter everywhere except Oracle, whose JSON_VALUE requires a literal path; there the validated path is inlined and only the value stays bound:

Dialect	Shape for column="metadata", path="plan"
PostgreSQL	jsonb_extract_path_text(("metadata")::jsonb, $1) = $2
MySQL	JSON_EXTRACT(\metadata`, ?) = ?(path bound as"$.plan"`)
MariaDB / SQL Server / Oracle	JSON_VALUE(...) = ...
SQLite	JSON_EXTRACT("metadata", ?) = ?

Joins

Join, LeftJoin, and RightJoin open a structured join against a table; finish it with .On(left, op, right) (a typed identifier comparison) or .OnRaw(clause) (for compound conditions):

orders, err := quark.For[Order](ctx, client).
    Join("users").On("users.id", "=", "orders.user_id").
    Where("status", "=", "paid").
    Limit(50).
    List()

Both forms run the same validator. The ON clause is identifier-only — both sides must be columns (no literals, functions, or parentheses), with operators =, !=, <>, <, <=, >, >=; anything else returns ErrInvalidJoin. Use .OnRaw for multi-condition joins:

quark.For[Order](ctx, client).
    Join("users").
    OnRaw("users.id = orders.user_id AND users.tenant_id = orders.tenant_id")

Projection under a join

Without an explicit Select, a query over a join projects only the base model's columns: Quark emits SELECT "orders".*, not a bare SELECT *. Shared names like id therefore never collide, and the result stays aligned with T.

The joined table is available for filtering and for ON, but not in the output. To read joined columns, go through a view, a read model, or raw SQL. For the same reason, the soft-delete filter is qualified to the base table ("orders"."deleted_at" IS NULL).

Qualified column names under a join

When the query has at least one join, Where, OrderBy, GroupBy, Select, and the AST leaf Col accept a one-level qualified name — table.column — so you can disambiguate columns both tables share (the primary key included):

orders, err := quark.For[Order](ctx, client).
    Join("users").On("users.id", "=", "orders.user_id").
    Where("users.id", "=", userID).          // unambiguous, filters on users
    OrderBy("orders.total", "DESC").
    List()

Each segment is validated with the same identifier rules as everything else and quoted separately ("users"."id"), so the dotted form adds no injection surface. Without a join the historical rule stands: dotted names are rejected.

Reusable scopes

A scope is a plain function — handy for authorization boundaries, business filters, and repeated dashboard queries:

var ActiveUsers = quark.Scope[User](func(q *quark.Query[User]) *quark.Query[User] {
    return q.Where("active", "=", true)
})

func CreatedAfter(t time.Time) quark.Scope[User] {
    return func(q *quark.Query[User]) *quark.Query[User] {
        return q.Where("created_at", ">=", t)
    }
}

users, err := quark.For[User](ctx, client).
    Apply(ActiveUsers, CreatedAfter(time.Now().AddDate(0, -1, 0))).
    List()

Streaming large result sets

List buffers everything into a slice, and caps at 100 rows when you forget Limit. For exports, backfills, or queues, stream instead.

Iter calls your callback once per row:

err := quark.For[User](ctx, client).
    Where("active", "=", true).
    OrderBy("id", "ASC").
    Iter(func(user User) error {
        return sendToSearchIndex(ctx, user)
    })

Cursor gives you manual control — always close it:

cursor, err := quark.For[User](ctx, client).OrderBy("id", "ASC").Cursor()
if err != nil {
    return err
}
defer cursor.Close()

for cursor.Next() {
    var user User
    if err := cursor.Scan(&user); err != nil {
        return err
    }
    process(user)
}
return cursor.Err()

Strict reads

Iter and Cursor have no implicit cap: forgetting Limit silently streams every matching row. WithStrictReads makes that visible per client, with two levels:

// Log a structured WARN when Iter/Cursor run without an explicit Limit.
client, err := quark.New("postgres", dsn,
    quark.WithStrictReads(quark.StrictReadsWarn),
)

// Or reject the query outright with ErrInvalidQuery.
client, err = quark.New("postgres", dsn,
    quark.WithStrictReads(quark.StrictReadsReject),
)

Intentionally unbounded reads — exports, backfills — opt out per query:

err := quark.For[Event](ctx, client).AllowUnbounded().Iter(export)

List is unaffected: it keeps its safe default cap of 100 rows and its own warning. The default mode (StrictReadsOff) leaves all behavior exactly as it was.

N+1 detection

With strict reads enabled, quark can also flag the classic N+1 pattern — a First/Find by primary key per row of a previous List. Because "repeated read" only means something within one request, counting is scoped to a context you mark at the request boundary:

func handler(w http.ResponseWriter, r *http.Request) {
    ctx := quark.TrackReads(r.Context())

    orders, _ := quark.For[Order](ctx, client).Limit(50).List()
    for _, o := range orders {
        // The 10th single-row read by primary key on customers within this
        // context logs one WARN suggesting Preload. The queries still run.
        c, _ := quark.For[Customer](ctx, client).Find(o.CustomerID)
        render(o, c)
    }
}

The detector logs one warning per context and table, and never fails the query — it is a heuristic. The fix it suggests:

orders, _ := quark.For[Order](ctx, client).Preload("Customer").Limit(50).List()

Without TrackReads (or with strict reads off) nothing is counted and the read path stays allocation-free.

---

Composable expressions

When a predicate nests deeper than Or reads well, build it from the typed expression AST and hand it to WhereExpr (or HavingExpr):

users, err := quark.For[User](ctx, client).WhereExpr(
    quark.And(
        quark.Eq(quark.Col("active"), quark.Lit(true)),
        quark.Or(
            quark.Eq(quark.Col("role"), quark.Lit("admin")),
            quark.And(
                quark.Gt(quark.Col("logins"), quark.Lit(10)),
                quark.Eq(quark.Col("verified"), quark.Lit(true)),
            ),
        ),
    ),
).List()
// WHERE ("active" = $1 AND ("role" = $2 OR ("logins" > $3 AND "verified" = $4)))

Node	Renders
Col(name)	quoted identifier (* only inside Func("COUNT", …))
Lit(value)	bound parameter, never interpolated
Eq/Ne/Lt/Gt/Lte/Gte/Cmp(lhs, op, rhs)	comparison; operator validated
And(...)/Or(...)	parenthesised when ≥2 children, transparent for 1, empty renders nothing
Not(expr)	NOT (...)
In(lhs, vals...)/NotIn(...)	lhs IN (?, ?, …); empty list rejected
Func(name, args...)	function call; name from a whitelist (COUNT, SUM, AVG, MIN, MAX, LOWER, UPPER, LENGTH, COALESCE, ABS)

HavingExpr takes the same AST — the structured counterpart to HavingAggregate:

buckets, err := quark.For[Order](ctx, client).
    GroupBy("customer_id").
    HavingExpr(quark.Gt(quark.Func("SUM", quark.Col("amount")), quark.Lit(int64(1000)))).
    List()
// GROUP BY "customer_id" HAVING SUM("amount") > $1

A bad leaf (invalid identifier, unknown operator, non-whitelisted function) is stashed on the query and surfaces at execution wrapping ErrInvalidQuery. The AST emits a neutral ? marker that Quark rewrites to each dialect's placeholder at render time, so one expression runs unchanged on every engine.

Subqueries

Capture any Query[T] as a *Subquery with AsSubquery(), then embed it in the AST through Sub, Exists, NotExists, InSub, or NotInSub:

sub, err := quark.For[Order](ctx, client).
    Select("user_id").
    Where("amount", ">", 100).
    AsSubquery()

users, err := quark.For[User](ctx, client).
    WhereExpr(quark.InSub(quark.Col("id"), sub)).
    List()
// WHERE "id" IN (SELECT "user_id" FROM "orders" WHERE "amount" > $1)

Wrapper	Renders
Sub(sub)	(<subquery>) — for scalar comparisons
Exists(sub) / NotExists(sub)	[NOT] EXISTS (<subquery>)
InSub(lhs, sub) / NotInSub(lhs, sub)	lhs [NOT] IN (<subquery>)

AsSubquery() renders the inner SELECT eagerly, so a bad inner column surfaces at capture time rather than at the outer query's execution. MustAsSubquery() is the panic-on-error variant, for inline use.

Two details worth knowing: locks on the inner query are rejected (acquire them on the outer query), and inner arguments are threaded into the outer argument list automatically, so a subquery composes correctly on every engine.

Set operators

Union, UnionAll, Intersect, IntersectAll, Except, and ExceptAll combine two queries:

adminEmails := quark.For[User](ctx, client).Select("email").Where("role", "=", "admin")
ownerEmails := quark.For[User](ctx, client).Select("email").Where("role", "=", "owner")

privileged, err := adminEmails.UnionAll(ownerEmails).List()

Method	Renders
Union / UnionAll	... UNION [ALL] ...
Intersect / IntersectAll	... INTERSECT [ALL] ...
Except / ExceptAll	... EXCEPT [ALL] ... (Oracle: MINUS)

Each operand can't carry ORDER BY, LIMIT, locks, its own CTEs, or nested set-ops; ORDER BY/LIMIT on the outer query apply to the combined result.

A statement chains one operator kind only. Union and UnionAll mix freely, since they are the same operator at the same precedence and evaluate left to right everywhere. Mixing kinds — a.Union(b).Intersect(c) — returns ErrUnsupportedFeature instead.

The reason is that the combined statement renders flat, and engines parse a flat mix differently: PostgreSQL, MySQL, MariaDB, and SQL Server give INTERSECT higher precedence, while SQLite and Oracle evaluate left to right. The same chain would quietly return different rows on different engines. Materialize each step into its own query instead.

Per-engine support (an unsupported combination returns ErrUnsupportedFeature instead of emitting SQL the engine rejects):

Operator	PostgreSQL	MariaDB	MySQL	SQL Server	Oracle	SQLite
UNION / UNION ALL	✓	✓	✓	✓	✓	✓
INTERSECT / EXCEPT	✓	✓	—	✓	✓ (MINUS)	✓
INTERSECT ALL / EXCEPT ALL	✓	✓	—	—	—	—

• MySQL only gained INTERSECT/EXCEPT in 8.0.31, a minor version Quark can't assume without a runtime probe, so both return ErrUnsupportedFeature there (rewrite as a JOIN).
• MariaDB has had INTERSECT/EXCEPT since 10.3 and their ALL variants since 10.5. Quark's MariaDB dialect already assumes 10.5 as its floor (it relies on RETURNING, also a 10.5 feature), so all six operators are enabled there.
• Oracle spells EXCEPT as MINUS. Oracle has had INTERSECT ALL and MINUS ALL since 21c, but Quark does not assume 21c without a version probe, so the ALL variants return ErrUnsupportedFeature on Oracle.
• SQL Server and SQLite have INTERSECT/EXCEPT but no ALL variants of either.

Applying Limit without an explicit OrderBy is portable — Quark injects a positional ORDER BY 1 where MSSQL/Oracle need one for pagination.

Window functions

Window functions live in the SELECT list, so they need an alias. SelectExpr projects an AST expression as a named column, and Over(inner, window) wraps it:

sales, err := quark.For[Sale](ctx, client).
    Select("id", "region", "amount").
    SelectExpr("rk", quark.Over(
        quark.Rank(),
        quark.NewWindow().
            PartitionBy(quark.Col("region")).
            OrderBy(quark.Col("amount"), true),
    )).
    List()
// SELECT "id", "region", "amount",
//        RANK() OVER (PARTITION BY "region" ORDER BY "amount" DESC) AS "rk"

Helper	Renders
RowNumber() / Rank() / DenseRank()	ROW_NUMBER() / RANK() / DENSE_RANK()
Lag(col, n) / Lead(col, n)	LAG(<col>, ?) / LEAD(<col>, ?) (offset bound)

For a running total, wrap any whitelisted aggregate: Over(Func("SUM", Col("amount")), NewWindow().OrderBy(Col("id"), false)). Window is immutable, so one definition is reusable across Over calls.

The emitted shapes are standard SQL, and run on PostgreSQL, MySQL 8+, MariaDB 10.2+, SQL Server, Oracle, and SQLite 3.25+.

Common table expressions

With(name, sub) prefixes the outer SELECT with WITH "name" AS (<inner>):

topOrders, _ := quark.For[Order](ctx, client).
    Select("user_id", "amount").
    Where("amount", ">", 100).
    AsSubquery()

users, err := quark.For[User](ctx, client).
    With("top_orders", topOrders).
    Join("top_orders").On("users.id", "=", "top_orders.user_id").
    List()

WithRecursive(name, sub) marks the CTE recursive. The RECURSIVE keyword is dialect-specific: PostgreSQL, MySQL, MariaDB, and SQLite require it, while Oracle and SQL Server infer recursion and reject the keyword. Quark emits the right spelling per engine, so you call WithRecursive the same way everywhere.

On Oracle, a genuinely recursive CTE also needs a column-alias list in the subquery's own SQL.

Pessimistic locking

ForUpdate, ForShare, SkipLocked, and NoWait add a row-level lock to the SELECT; the dialect picks the right shape:

orders, err := quark.For[Order](ctx, client).
    Where("status", "=", "pending").
    ForUpdate().
    SkipLocked().
    Limit(10).
    List()

Dialect	ForUpdate	SkipLocked	NoWait	ForShare
PostgreSQL	FOR UPDATE	✅	✅	FOR SHARE
MySQL 8+	FOR UPDATE	✅	✅	FOR SHARE
MariaDB 10.6+	FOR UPDATE	✅	✅	LOCK IN SHARE MODE ¹
Oracle	FOR UPDATE	✅ (12c+)	✅	❌ → ErrUnsupportedFeature
SQL Server	WITH (UPDLOCK, ROWLOCK)	READPAST	❌ → ErrUnsupportedFeature	WITH (HOLDLOCK, ROWLOCK)
SQLite	❌ → ErrUnsupportedFeature	n/a	n/a	n/a

¹ MariaDB has no FOR SHARE keyword, so ForShare() emits LOCK IN SHARE MODE, which takes no modifiers — combining it with SkipLocked/NoWait returns ErrUnsupportedFeature. SQLite has no row-level lock primitive; use BEGIN IMMEDIATE/EXCLUSIVE in your transaction wrapper instead.

Locks belong inside an explicit transaction — outside one, the lock is released the moment the SELECT completes.

Oracle: locking and row limits don't mix

Oracle rejects FOR UPDATE combined with a row-limiting clause (OFFSET … FETCH) with ORA-02014. So on Oracle:

• ForUpdate().List() works, but the implicit 100-row cap is dropped, so the lock spans every matching row (a WARN is logged) — narrow your WHERE.
• An explicit Limit/Offset with a lock, and ForUpdate().First() (which adds Limit(1)), both return ErrUnsupportedFeature. To lock a bounded set, select the primary keys first, then Where("id", "IN", keys).ForUpdate() in the same transaction.

Other engines allow LIMIT with FOR UPDATE and are unaffected.

Raw SQL

WhereSubquery is off unless the client opts into raw SQL:

limits := quark.DefaultLimits()
limits.AllowRawQueries = true
client, _ := quark.New("postgres", dsn, quark.WithLimits(limits))

users, err := quark.For[User](ctx, client).
    WhereSubquery("id", "IN", "SELECT user_id FROM orders WHERE total > 100").
    List()

Bound parameters protect values, but a raw subquery can still encode unsafe identifiers or structure. Use it only for static or carefully built SQL.

For a full raw read, use client.RawQuery:

rows, err := client.RawQuery(ctx, "SELECT id, email FROM users WHERE active = $1", true)

The v0.3.x string-raw Join(table, on) signature was removed in v0.4; the structured Join(table).On(...) / .OnRaw(...) form above replaces it. See MIGRATION_v0.4.0.md for the mechanical rewrite.