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.
PaginateAfter(size, token)	You want to seek through a large set, one cheap statement per page, no total.
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
PostgreSQL only	@>, <@, && (array, range and JSON containment / overlap), >>, <<, >>=, <<= (networks) — refused with ErrUnsupportedFeature on other engines

A null check takes nil as its value:

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

Searching for text the user typed

Where(col, "LIKE", pattern) hands the engine a pattern: % and _ are wildcards, and what a backslash means is whatever the engine decides by default — an escape on PostgreSQL, MySQL and MariaDB, an ordinary character on SQLite, SQL Server and Oracle. That is fine for a pattern you wrote. It is wrong for text a user typed into a search box, where a % should find the rows that contain a percent sign, not every row.

For that text there are three helpers. They escape the text, wrap it in the wildcards you asked for, and declare the escape character on the statement, so the result is the same on every engine:

quark.For[Doc](ctx, client).WhereContains("title", userText).List()   // %text%
quark.For[Doc](ctx, client).WhereStartsWith("title", userText).List() // text%
quark.For[Doc](ctx, client).WhereEndsWith("title", userText).List()   // %text

When you compose the pattern yourself and still want it to mean the same everywhere, WhereLike (and WhereNotLike) declare the escape character and let you place the wildcards, with quark.EscapeLike protecting the parts that came from the user:

pattern := "invoice-" + quark.EscapeLike(number) + "-%"
quark.For[Doc](ctx, client).WhereLike("ref", pattern).List()
// ref LIKE ? ESCAPE '\'   (spelled `ESCAPE '\\'` on MySQL and MariaDB)

A pattern that ends in a dangling backslash is refused with ErrInvalidQuery before it reaches the engine. The same three searches exist on the typed columns generated by quark gen (Contains, StartsWith, EndsWith, LikeEscaped) and on the expression AST (quark.Contains, quark.Like).

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()

Where with IN or NOT IN takes the slice as it is, whatever its element type: []string, []int64, a slice of UUIDs, a named slice type such as type IDs []int64, or an array. BETWEEN and NOT BETWEEN take their two bounds the same way:

users, err := quark.For[User](ctx, client).
    Where("role", "IN", []string{"admin", "editor"}).
    Where("age", "BETWEEN", [2]int{18, 65}).
    List()

A byte slice ([]byte, json.RawMessage, net.IP) and a bare UUID are single values to database/sql, not lists: passing one, or any other value that is not a slice, returns ErrInvalidQuery before any SQL runs. To match a single value, use =, or wrap it: []any{v}. BETWEEN with fewer than two values returns ErrInvalidQuery too. An empty slice of any type behaves like an empty []any, so the advice above about IN () applies to it.

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 = ?)

The filters Quark adds on its own — deleted_at IS NULL on a soft-delete model, the tenant predicate under RowLevelSecurityClient — are ANDed with your conditions as a whole, so an Or group cannot bring back a row they exclude. On a soft-delete model the query above reads WHERE deleted_at IS NULL AND (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)

That is page-number pagination: it costs a COUNT and an OFFSET per page, and page N makes the server walk past the pages before it. When you are paging through a feed or an export and do not need a total, seek instead:

first, err := quark.For[User](ctx, client).OrderBy("created_at", "DESC").PaginateAfter(50, "")
// first.Items, first.HasMore, first.Next — an opaque token naming the last row

next, err := quark.For[User](ctx, client).OrderBy("created_at", "DESC").PaginateAfter(50, first.Next)
// WHERE created_at < ? OR (created_at = ? AND id > ?) ORDER BY created_at DESC, id ASC LIMIT 51

PaginateAfter emits one statement per page and seeks to the last row read through the ordering columns, so the thousandth page costs what the first did. The primary key is appended to the ordering when it is not there, so ties never skip or repeat a row; every ordering column has to be a column of the model, because the token is built from the last row's values. A token minted under a different ORDER BY is refused rather than seeking to the wrong place.

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.

Distinct counts and conditional aggregates

CountDistinct renders COUNT(DISTINCT <expr>), and Case builds a searched CASE so a conditional aggregate is expressible without raw SQL:

rows, err := quark.For[Order](ctx, client).
    GroupBy("user_id").
    SelectExpr("customers", quark.CountDistinct(quark.Col("user_id"))).
    SelectExpr("paid", quark.Func("SUM", quark.Case().
        When(quark.Eq(quark.Col("status"), quark.Lit("paid")), quark.Col("amount")).
        Else(quark.Lit(0)))).
    List()

Case() takes any number of When(condition, result) branches, which render in the order you add them, and an optional Else. A Case with no branch is rejected.

Give one CASE branch a known type

Every Lit binds as a parameter, so a CASE whose branches are all literals has no branch with a type PostgreSQL can infer. It settles on text, and wrapping the result in SUM then fails with function sum(text) does not exist. Using a column in one branch — as above — fixes the type of the whole expression. The other five engines are more forgiving, which is what makes this easy to miss until a PostgreSQL deploy.

Oracle also rejects a bare aggregate in a query that does not group (ORA-00937), so pair CountDistinct with GroupBy there.

Updating a column from its own value

UpdateMap treats an Expr value as SQL rather than data, so an update can read the column it writes:

quark.For[Product](ctx, client).Where("id", "=", id).
    UpdateMap(map[string]any{
        "stock": quark.Subtract(quark.Col("stock"), quark.Lit(1)),
    })

Add, Subtract, Multiply and Divide render parenthesised arithmetic. This is not the same as loading the row, subtracting in Go and writing it back: that version loses the atomicity, and two concurrent decrements can leave one of them unapplied.

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", ?) = ?

Projecting a JSON member

WhereJSON filters on a JSON member; JSONExtract puts one in the SELECT list:

rows, err := quark.For[User](ctx, client).
    SelectExpr("tier", quark.JSONExtract("profile", "tier")).
    List()

The accessor is named differently by every engine — jsonb_extract_path_text on PostgreSQL, JSON_EXTRACT on MySQL and SQLite, JSON_VALUE on SQL Server and Oracle — and the dialect renders the right one, so the call is the same everywhere. The path is a dotted identifier chain ("address.city"), the same grammar WhereJSON uses, not JSONPath.

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.

Full, cross, and literals in the ON clause

FullJoin and CrossJoin complete the set. CrossJoin returns the query directly — a cartesian product has no ON clause to attach:

quark.For[Order](ctx, client).CrossJoin("calendar").List()

On and OnRaw take caller strings, so they are held to an identifier-only grammar and reject literals: the ON clause is one of the few places a string would reach SQL without a bind marker. OnExpr takes an expression instead, whose literals bind:

quark.For[Order](ctx, client).
    LeftJoin("order_items").
    OnExpr(quark.And(
        quark.Eq(quark.Col("orders.id"), quark.Col("order_items.order_id")),
        quark.Gt(quark.Col("order_items.qty"), quark.Lit(1)),
    )).
    List()

That difference matters for an outer join. Moving the same predicate to WHERE turns a LEFT JOIN into an inner one: the unmatched rows it was meant to preserve arrive with NULLs, and the WHERE drops them.

FULL OUTER JOIN on MySQL and MariaDB

Neither implements it. Quark does not rewrite it into the LEFT-UNION-RIGHT form they need — the rewrite changes how the query plans and how duplicates behave, and doing it silently would hide that.

Projecting onto a DTO

For[T] takes both the row shape and the source table from T, so a struct that is a result shape rather than a model sends the query at a table named after the DTO. FromTable names the source:

type OrderEmail struct {
    OrderID int64  `db:"order_id"`
    Email   string `db:"email"`
}

rows, err := quark.For[OrderEmail](ctx, client).
    FromTable("orders").
    Join("users").On("orders.user_id", "=", "users.id").
    SelectExpr("order_id", quark.Col("orders.id")).
    SelectExpr("email", quark.Col("users.email")).
    List()

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()

Filtering what a relation loads

Preload is variadic over relation names, so a condition cannot ride in it. PreloadWhere takes one:

// users, each carrying only their paid orders
quark.For[User](ctx, client).
    PreloadWhere("Orders", "status", "=", "paid").
    List()

The condition applies to the relation's own query: it narrows which children load, and does not drop parents that end up with none. Repeated calls on the same relation AND together, and the relation is registered for preloading, so PreloadWhere alone is enough. The operators are the ones Where takes, with the same values: IN and NOT IN with a []any, BETWEEN with two values, IS NULL with none.

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)
NTile(n)	NTILE(?) — which of n equal buckets the row falls in
PercentRank() / CumeDist()	PERCENT_RANK() / CUME_DIST()
FirstValue(col) / LastValue(col)	FIRST_VALUE(<col>) / LAST_VALUE(<col>)
NthValue(col, n)	NTH_VALUE(<col>, ?), 1-based

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.

Two engine limits worth knowing

SQL Server has no NTH_VALUE. The other five engines do. Quark does not emulate it — an emulation would differ on NULLs and frames — so a query using NthValue is not portable to SQL Server.

LastValue rarely means what it reads like. With the default frame the "last" row of the window is the current one, so it returns the current row's value. Windows do not expose a frame clause yet.

Frames

Rows and Range set which rows of the partition the function actually sees:

// three-row moving average
w := quark.NewWindow().
    OrderBy(quark.Col("day"), false).
    Rows(quark.Preceding(2), quark.CurrentRow())

Bounds come from Preceding, Following, CurrentRow, UnboundedPreceding and UnboundedFollowing. Rows counts physical rows; Range counts values of the ORDER BY expression, so rows comparing equal enter and leave together.

A window without a frame is not a smaller query

With an ORDER BY and no frame, the default runs from the start of the partition through the current row. That default is what makes SUM() OVER (ORDER BY …) a running total — and it is also why a "moving average" written without a frame is silently a running average instead.

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.

A recursive CTE needs a body of the form anchor UNION ALL step, where the step joins the CTE being defined. UnionAll composes exactly that, and AsSubquery captures it:

anchor := quark.For[Category](ctx, client).Where("id", "=", rootID)
step := quark.For[Category](ctx, client).
    Join("tree").On("categories.parent_id", "=", "tree.id")

body, _ := anchor.UnionAll(step).AsSubquery()

rows, err := quark.For[Category](ctx, client).
    WithRecursive("tree", body).
    FromCTE("tree").
    List()

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

Reading from the CTE

With declares the CTE; it does not change what the outer SELECT reads. By default the outer query still reads the model's table, so a CTE is reached by joining it — which is the right shape when you want rows from both.

When the derived rows are the query, FromCTE(name) replaces the model's table in the FROM:

// average number of orders per user: the aggregate is over the derived
// rows, and the base table must not be in the FROM at all
perUser, _ := quark.For[Order](ctx, client).
    GroupBy("user_id").
    SelectExpr("n", quark.Func("COUNT", quark.Col("*"))).
    AsSubquery()

avg, err := quark.For[Order](ctx, client).
    With("per_user", perUser).
    FromCTE("per_user").
    Avg("n")

FromCTE affects reads only. Update, Delete and Create keep writing to the model's table — a CTE is not a write target.

Two implicit scopes behave differently under FromCTE, on purpose:

Scope	Under FromCTE	Why
Soft delete	dropped	It filters the model's table, and a CTE need not carry deleted_at. Apply it in the subquery that builds the CTE.
Tenant	kept	Dropping it would turn a missing column into cross-tenant reads, so the CTE has to project the tenant column.

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.