Query builder

Ptah’s core/query package is a fluent builder for parameterized SELECT statements. It is the DML counterpart to the DDL AST: a builder produces an *query.SelectStatement, and query.RenderSelect turns that into a SQL string plus its arguments for every dialect Ptah renders, YDB included. See Dialect coverage for how each dialect is written and for what each one refuses.

This is a bounded slice of the DML work in issue #98. It exists so callers can stop hand-rolling dynamic WHERE / ORDER BY / IN (…) clauses with manual placeholder counters.

Scope

Implemented so far:

• SELECT with an explicit column list or *, and SELECT DISTINCT;
• INNER, LEFT, RIGHT, and FULL OUTER joins, with table aliases and columns qualified by a table or alias (see Joins);
• a composable WHERE (and join ON) expression tree: =, <>, <, <=, >, >=, IN, IS NULL, IS NOT NULL, and the boolean combinators AND, OR, NOT;
• aggregate functions — COUNT(*), COUNT, COUNT(DISTINCT …), SUM, AVG, MIN, MAX — in the projection and in HAVING (see Aggregates, GROUP BY, and HAVING);
• GROUP BY (bare or qualified columns) and HAVING;
• ORDER BY with per-column ASC/DESC;
• LIMIT and OFFSET;
• single-table INSERT (one or more VALUES rows), UPDATE, and DELETE, each with an optional RETURNING clause (see Writes);
• LIKE and NOT LIKE, with the pattern bound rather than interpolated;
• arithmetic (+, -, *, /, %) and non-aggregate function calls, in the projection and anywhere else an expression is accepted;
• subqueries: IN (SELECT …), EXISTS / NOT EXISTS, and a derived table in FROM;
• window functions — an aggregate with OVER (PARTITION BY … ORDER BY …);
• common table expressions (WITH name AS (…), one or more);
• ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE, and INSERT … SELECT;
• YDB’s UPSERT INTO, built by UpsertInto (see UPSERT).

One thing is deliberately absent: a window frame clause. Without one the engine applies its default, which is what an unframed window means everywhere, and guessing a frame would change results.

Dialect coverage

RenderSelect, RenderInsert, RenderUpdate, and RenderDelete render for every dialect renderer.SupportedDialects() returns. What differs between them is the placeholder, the identifier quoting, and how a row limit is written. The first column holds the strings you pass as the dialect, so the table is also that list of names:

Dialect	Placeholder	Identifiers	LIMIT / OFFSET
postgresql postgres cockroachdb yugabytedb spanner	$1, $2, …	"id"	LIMIT $n OFFSET $n
mysql mariadb	?	`id`	LIMIT ? OFFSET ?
clickhouse	?	`id`	LIMIT ? OFFSET ?
sqlite sqlite3	?	"id"	LIMIT ? OFFSET ?
sqlserver mssql	@p1, @p2, …	[id]	OFFSET 0 ROWS FETCH NEXT @pn ROWS ONLY
oracle	:1, :2, …	bare id	OFFSET 0 ROWS FETCH NEXT :n ROWS ONLY
ydb	$p1, $p2, …	`id`	LIMIT $pn OFFSET $pn

Four of those rows carry a decision worth stating:

• SQL Server writes its row limit as T-SQL’s row-limiting clause, which requires an OFFSET before the FETCH and an ORDER BY before either — a limited query with no ordering is a syntax error there rather than an unordered result. RenderSelect therefore synthesizes ORDER BY (SELECT NULL) OFFSET 0 ROWS when the caller ordered nothing. Both are structural constants and bind no placeholder, so the argument list stays the caller’s values in caller order.

• Oracle takes the same ANSI row-limiting clause and no sentinel ORDER BY, because it needs none. The omission is load-bearing: ORDER BY (SELECT NULL) is accepted on 23.26, where a SELECT needs no FROM, and refused on 21.3 with ORA-00923. Identifiers are bare because a table created as ora_posts is ORA_POSTS in the catalog, and a query naming "ora_posts" would look for a table nobody created — the same decision the Oracle DDL renderer makes.

• Spanner renders byte-identically to PostgreSQL, including RETURNING. It has no live coverage in this repository, so the support matrix caveat applies — review the generated SQL before relying on it.

• YDB has no positional parameter, so the builder writes YQL’s named parameters, $p1, $p2, …, and returns each argument as a sql.NamedArg of that name. A YDB connection binds a parameter by name, Ptah’s own and the YDB SDK’s alike. YQL types every value, and a bound value’s Go type is the type it binds as: an int64 is Int64, so an Int32 column takes an int32. LIMIT and OFFSET bind as uint64, the type YQL reads them as, and a negative one is refused. A table in a directory is one name, `app/users`.

YDB lacks constructs the other dialects render, and the builder refuses each before it writes anything, with the capability key the target lacks: a WITH clause (common_table_expressions), a subquery that reads a column of the query around it (correlated_subqueries), a join condition other than equalities between the joined tables’ columns (non_equi_joins), and an OFFSET without a LIMIT (offset_without_limit). ON CONFLICT is refused too: YDB has none and refuses INSERT OR IGNORE, and its upsert is UPSERT INTO.

One statement is refused, and for an engine reason rather than an untaught one:

renderer: UPDATE is not a portable statement on ClickHouse: a plain UPDATE runs
only on a table with the materialized _block_number column, which a statement
cannot declare; use ALTER TABLE … UPDATE, or enable enable_block_number_column
on the table

Every dialect name renderer.SupportedDialects() returns is pinned against all four render functions — one cell per (dialect, verb) pair, each pinned to an exact SQL string or an exact error — in core/query/dialect_matrix_test.go, so the builder cannot acquire or lose a dialect without that table saying so. What each dialect renders is also executed against a live server for SQL Server and ClickHouse in integration/gonative/dml_execution_integration_test.go, and for YDB in integration/dbschema/ydb/data_live_test.go: a renderer test proves the string, and only the server proves the SQL.

Release lines

The four render functions render for the dialect’s current release line, the capabilities capability.ForDialect returns. RenderSelectWithCapabilities, RenderInsertWithCapabilities, RenderUpdateWithCapabilities and RenderDeleteWithCapabilities take the capabilities of the line you run against, such as the ones a connection reports in conn.Info().Capabilities. They decide RETURNING on every dialect, and on YDB the constructs above.

stmt := query.Update("users").Set("plan", "paid").Where(query.Eq("id", int64(7))).Returning("id").Build()
sql, args, err := query.RenderUpdateWithCapabilities(stmt, platform.YDB, capability.YDB251())
// err: renderer: RETURNING, which requires target capability returning_clause,
//      unavailable on this ydb target

Safety model

The builder keeps identifiers and values in separate lanes, so the classic “concatenate a value into SQL” injection cannot happen through this API:

• Values are always bound. Arguments to Eq, In, and the other comparison helpers are typed as any and travel to the database as bound parameters. They are never interpolated into the SQL text. LIMIT and OFFSET values are bound the same way. The renderer emits the dialect’s placeholder — $1, $2, … for PostgreSQL, ? for MySQL, MariaDB, ClickHouse and SQLite, @p1, @p2, … for SQL Server, :1, :2, … for Oracle, and $p1, $p2, … for YDB — and returns the values in a matching []any, as sql.NamedArg values on YDB.
• Identifiers are always quoted. Table and column names are emitted through dialect-aware identifier quoting, so an attacker-shaped identifier cannot terminate the quoted identifier and inject SQL. As with Ptah’s DDL rendering, deciding which identifiers a caller may supply — for example, an allow-list of sortable columns — remains the caller’s responsibility. The builder guarantees quoting and the absence of value interpolation.

Placeholder numbering is assigned by the renderer in a single left-to-right pass, so argument order always matches placeholder order and callers never manage indices by hand.

Usage

import (
  "ptah.run/core/platform"
  "ptah.run/core/query"
  "ptah.run/core/renderer"
)

stmt := query.Select("id", "name").
  From("commodities").
  Where(query.And(
    query.Eq("draft", false),
    query.In("status", []string{"in_use", "sold"}),
    query.Or(
      query.IsNotNull("deleted_at"),
      query.Not(query.Gt("count", int64(10))),
    ),
  )).
  OrderBy(query.Asc("name"), query.Asc("id")).
  Limit(24).
  Offset(0).
  Build()

sql, args, err := renderer.RenderSelect(stmt, platform.Postgres)

The PostgreSQL output is:

SELECT "id", "name" FROM "commodities"
WHERE ("draft" = $1 AND "status" IN ($2, $3)
       AND ("deleted_at" IS NOT NULL OR NOT ("count" > $4)))
ORDER BY "name" ASC, "id" ASC
LIMIT $5 OFFSET $6

with args equal to []any{false, "in_use", "sold", int64(10), int64(24), int64(0)}.

The same statement rendered with platform.MySQL uses backtick-quoted identifiers and ? placeholders; platform.SQLite uses double-quoted identifiers and ? placeholders. The argument slice is identical across dialects.

Shared WHERE fragments

Expression constructors return plain expression nodes, so a filter can be built once and attached to more than one statement — for example, the paged list and the COUNT(*) that share the same filter:

filter := query.And(query.Eq("draft", false), query.Eq("tenant_id", tenantID))

page := query.Select("id", "name").From("commodities").
  Where(filter).OrderBy(query.Asc("name")).Limit(20).Offset(0).Build()

total := query.Select("id").From("commodities").
  Where(filter).Build()

Joins

Alias the source table with FromAs, add joins with InnerJoin, LeftJoin, RightJoin, or FullJoin, and qualify columns with Col so they render as "alias"."col". A qualified column works everywhere a column is accepted: the projection (via .Columns), the join ON condition, WHERE, and ORDER BY.

A join ON is an ordinary expression, so an equi-join is Col(left).EqCol(Col(right)) and richer predicates compose with And, Or, and Not. Col(table, name) also carries the comparison helpers (Eq, Ne, Lt, Le, Gt, Ge, IsNull, IsNotNull) and the ordering helpers (Asc, Desc) for the qualified column.

stmt := query.Select().
  Columns(query.Col("u", "id"), query.Col("u", "name"), query.Col("o", "total")).
  FromAs("users", "u").
  InnerJoin("orders", "o", query.Col("o", "user_id").EqCol(query.Col("u", "id"))).
  Where(query.And(
    query.Col("o", "status").Eq("paid"),
    query.Col("u", "active").Eq(true),
  )).
  OrderBy(query.Col("u", "name").Asc()).
  Limit(20).
  Build()

sql, args, err := renderer.RenderSelect(stmt, platform.Postgres)

The PostgreSQL output is:

SELECT "u"."id", "u"."name", "o"."total"
FROM "users" "u"
INNER JOIN "orders" "o" ON "o"."user_id" = "u"."id"
WHERE ("o"."status" = $1 AND "u"."active" = $2)
ORDER BY "u"."name" ASC
LIMIT $3

with args equal to []any{"paid", true, int64(20)}. Tables render as table alias (no AS), which every supported dialect accepts. A value inside a join ON is bound before any WHERE value, because joins render first — so placeholder numbering still follows left-to-right emission order across ON, WHERE, and LIMIT/OFFSET.

Join-type support by dialect

Not every dialect can express every join type. RenderSelect rejects an unsupported join at render time — returning a clear error — rather than emit SQL that fails at execution time against the database.

Join type	PostgreSQL family, SQL Server, Oracle, ClickHouse, YDB	MySQL / MariaDB	SQLite
INNER	yes	yes	yes
LEFT	yes	yes	yes
RIGHT	yes	yes	no (added in 3.39)
FULL OUTER	yes	no (never supported)	no (added in 3.39)

• SQLite gained RIGHT and FULL OUTER JOIN only in version 3.39 (2022). Because Ptah targets a range of SQLite versions and cannot assume 3.39+, both are rejected (renderer: SQLite does not support RIGHT JOIN).
• MySQL and MariaDB have no FULL [OUTER] JOIN in any version — it must be emulated with a UNION of a LEFT and a RIGHT join — so FULL is rejected (renderer: mysql does not support FULL OUTER JOIN). RIGHT renders normally.
• The PostgreSQL family (including CockroachDB, YugabyteDB, and Spanner), SQL Server, Oracle, ClickHouse and YDB support all four.
• YDB joins only on equalities between columns of the two joined tables, or a conjunction of them. Any other ON condition — an inequality, a comparison with a value, an OR — is refused with the key non_equi_joins; the server answers JOIN ON expression must be a conjunction of equality predicates.

Aggregates, GROUP BY, and HAVING

Distinct() renders SELECT DISTINCT. GroupBy adds GROUP BY columns — pass Col(table, name) for a qualified column across joins, or Col("", name) for a bare column. GROUP BY carries only identifiers, so it never binds a placeholder.

Aggregates are built with CountStar, Count, CountDistinct, Sum, Avg, Min, and Max (bare-column free functions), or the matching methods on a qualified column: Col("o", "total").Sum(), Col("u", "id").Count(), and so on. Each returns an expression usable in two places:

• the projection, via Exprs (no alias) or ExprAs (with an AS alias);
• a HAVING predicate, by wrapping it with Expr to reach the comparison helpers — Expr(query.CountStar()).Gt(int64(5)) — which compose with And, Or, and Not like any other expression.

CountStar() is the primary way to count all rows; Count("*") is an equivalent convenience for COUNT(*). Every other "*" aggregate argument — a non-COUNT aggregate, COUNT(DISTINCT *), or a qualified star such as Col("u", "*").Count() — has no portable star form and is rejected at render time rather than emitting an invalid quoted "*".

A function name (COUNT, SUM, …) is a keyword emitted verbatim and never quoted; its column arguments are quoted, and any value it is compared against is bound. The renderer rejects a function name that is not a simple identifier rather than emit it.

stmt := query.Select("status").
  ExprAs(query.CountStar(), "n").
  From("orders").
  Where(query.Eq("tenant_id", tenantID)).
  GroupBy(query.Col("", "status")).
  Having(query.Expr(query.CountStar()).Gt(int64(5))).
  OrderBy(query.Asc("status")).
  Limit(10).
  Build()

sql, args, err := renderer.RenderSelect(stmt, platform.Postgres)

The PostgreSQL output is:

SELECT "status", COUNT(*) AS "n"
FROM "orders"
WHERE "tenant_id" = $1
GROUP BY "status"
HAVING COUNT(*) > $2
ORDER BY "status" ASC
LIMIT $3

with args equal to []any{tenantID, int64(5), int64(10)}. A HAVING value is bound after every WHERE value and before LIMIT/OFFSET, so placeholder numbering still follows left-to-right emission order across WHERE, HAVING, and LIMIT/OFFSET.

Aggregates work over qualified columns in join queries too:

stmt := query.Select().
  Columns(query.Col("u", "name")).
  ExprAs(query.Col("o", "id").Count(), "orders").
  ExprAs(query.Col("o", "total").Sum(), "spent").
  FromAs("users", "u").
  InnerJoin("orders", "o", query.Col("o", "user_id").EqCol(query.Col("u", "id"))).
  GroupBy(query.Col("u", "name")).
  Build()

// SELECT "u"."name", COUNT("o"."id") AS "orders", SUM("o"."total") AS "spent"
// FROM "users" "u" INNER JOIN "orders" "o" ON "o"."user_id" = "u"."id"
// GROUP BY "u"."name"

Writes

InsertInto, Update, and DeleteFrom build the write-side statements. Values passed to Values and Set are bound exactly like WHERE values — never concatenated into SQL — and table and column names are quoted. Each statement has its own renderer entry point, all returning (sql string, args []any, err error):

• renderer.RenderInsert(stmt, dialect)
• renderer.RenderUpdate(stmt, dialect)
• renderer.RenderDelete(stmt, dialect)

Validation of degenerate input happens at render time (as with SELECT), so a builder call never fails and Build never returns an error.

UPSERT

UpsertInto builds YDB’s UPSERT INTO. It writes each row over the row with the same primary key, keeps the columns the statement does not name, and inserts the row where there is none. Columns, Values, FromSelect and Returning work as they do for InsertInto.

stmt := query.UpsertInto("users").
  Columns("id", "name").
  Values(int64(1), "alice").
  Values(int64(2), "bob").
  Build()

sql, args, err := query.RenderInsert(stmt, platform.YDB)
// UPSERT INTO `users` (`id`, `name`) VALUES ($p1, $p2), ($p3, $p4)
// args: sql.Named("p1", int64(1)), sql.Named("p2", "alice"), ...

The other dialects refuse it. Each of them names the key an upsert watches in the statement, which the builder does not know for a table, so use OnConflictDoUpdate there.

On a table with a unique index, name every column. YDB 26.2 fails an UPSERT that names no column a synchronous index is keyed on with INTERNAL_ERROR, and the builder cannot see the table’s indexes to refuse it; see YDB.

INSERT

Declare the column list with Columns and add one row per Values call. A multi-row insert numbers its values row by row, left to right. Passing nil as a value binds SQL NULL.

stmt := query.InsertInto("users").
  Columns("id", "name").
  Values(int64(1), "alice").
  Values(int64(2), "bob").
  Returning("id").
  Build()

sql, args, err := renderer.RenderInsert(stmt, platform.Postgres)

The PostgreSQL output is:

INSERT INTO "users" ("id", "name") VALUES ($1, $2), ($3, $4) RETURNING "id"

with args equal to []any{int64(1), "alice", int64(2), "bob"}. RenderInsert rejects a statement with no columns, no rows, or a row whose length does not match the column count — ragged input fails cleanly instead of producing mismatched SQL.

UPDATE

Add assignments with Set (each value bound) and a filter with Where. An UPDATE’s SET values are numbered before its WHERE values, matching emission order, so placeholder numbering follows the SQL left to right.

stmt := query.Update("users").
  Set("name", "bob").
  Set("email", "bob@example.com").
  Where(query.Eq("id", int64(7))).
  Build()

sql, args, err := renderer.RenderUpdate(stmt, platform.Postgres)

The PostgreSQL output is:

UPDATE "users" SET "name" = $1, "email" = $2 WHERE "id" = $3

with args equal to []any{"bob", "bob@example.com", int64(7)}. An empty SET list is rejected.

DELETE

stmt := query.DeleteFrom("users").Where(query.Eq("id", int64(7))).Build()

sql, args, err := renderer.RenderDelete(stmt, platform.Postgres)
// DELETE FROM "users" WHERE "id" = $1   args: []any{int64(7)}

A WHERE expression can be built once and shared across statement kinds, exactly as with SELECT — the expression constructors return plain nodes.

Whole-table UPDATE / DELETE guard

An UPDATE or DELETE with no WHERE clause mutates every row, which is rarely intended. Rather than make that the accidental default, the builder requires an explicit opt-in: call .Unconditional(). Without it, the renderer rejects a WHERE-less statement rather than run a whole-table mutation.

// Rejected: "renderer: delete without a WHERE clause must be marked unconditional"
query.DeleteFrom("sessions").Build()

// Deliberate whole-table delete — renders as DELETE FROM "sessions"
query.DeleteFrom("sessions").Unconditional().Build()

RETURNING support by dialect

Returning adds a RETURNING projection to any of the three statements. RETURNING is not portable across every dialect, so RenderInsert / RenderUpdate / RenderDelete reject it — returning a clear error — on a target without the capability returning_clause, rather than emit SQL that fails at execution time.

Dialect	RETURNING
PostgreSQL family (incl. CockroachDB, YugabyteDB, Spanner)	yes
SQLite	yes (since 3.35, 2021)
YDB	yes from 25.3 (see note)
MySQL	no
MariaDB	no (see note)
SQL Server, Azure SQL	no (see note)
Oracle	no (see note)
ClickHouse	no

• MySQL has no RETURNING at all.
• MariaDB supports RETURNING for INSERT and DELETE but not UPDATE. To keep one rule across all three write statements, Ptah treats MariaDB as unsupported and rejects a non-empty RETURNING (renderer: RETURNING, which requires target capability returning_clause, unavailable on this mariadb target).
• SQLite gained RETURNING in 3.35 (2021). RenderInsert emits it for the current SQLite line; RenderInsertWithCapabilities with capability.SQLite324() refuses it.
• YDB 25.1 and 25.2 run INSERT, DELETE and UPSERT with RETURNING and fail UPDATE … RETURNING on a table with a unique index (INTERNAL_ERROR … wrong returning expr type). The same one rule holds, so those lines are refused and 25.3 and later render it.
• SQL Server has OUTPUT, which is a different clause in a different position, not a spelling of this one. Mapping Returning onto it would change what the statement means, so a non-empty Returning is rejected.
• Oracle has the keyword and not this shape: measured on 23.26, INSERT INTO t (id) VALUES (99) RETURNING id INTO :out is accepted with an out-parameter bound, while the same statement ending at RETURNING id answers ORA-00925. A projection has nowhere to go there.

Builder reference

Select starts a read query; InsertInto, Update, and DeleteFrom start the write statements.

Function	Result
Select(cols ...string)	Start a builder; "*" or no columns selects all.
.Distinct()	Render SELECT DISTINCT.
.Columns(cols ...Column)	Append qualified columns (from Col) to the projection.
.Exprs(exprs ...ast.Expression)	Append expression projections (for example aggregates).
.ExprAs(expr, alias)	Append one expression projection with an AS alias.
.From(table)	Set the source table (required); clears any alias.
.FromAs(table, alias)	Set the source table with an alias.
.InnerJoin / .LeftJoin / .RightJoin / .FullJoin (table, alias, on)	Append a join with an ON condition.
.Where(expr)	Set the filter expression; a later call replaces the earlier one.
.GroupBy(cols ...Column)	Append GROUP BY columns across calls.
.Having(expr)	Set the HAVING predicate; a later call replaces the earlier one.
.OrderBy(terms ...)	Append sort terms across calls.
.Limit(n) / .Offset(n)	Set bound row limit/offset.
.Build()	Produce the *ast.SelectStatement.

Write builders:

Function	Result
InsertInto(table)	Start an INSERT.
UpsertInto(table)	Start a YDB UPSERT.
.Columns(cols ...string)	Declare the inserted column list.
.Values(vals ...any)	Append one row of bound values; call once per row.
Update(table)	Start an UPDATE.
.Set(col, value)	Append a bound column = value assignment.
DeleteFrom(table)	Start a DELETE.
.Where(expr)	Set the filter (shared by Update and DeleteFrom).
.Unconditional()	Opt in to a whole-table UPDATE/DELETE (required when no Where).
.Returning(cols ...string)	Add a RETURNING projection, where the target has returning_clause.
.Build()	Produce the *ast.InsertStatement / *ast.UpdateStatement / *ast.DeleteStatement.

Expression helpers: Eq, Ne, Lt, Le, Gt, Ge, In, IsNull, IsNotNull, And, Or, Not. Ordering helpers: Asc, Desc. Aggregate helpers: CountStar, Count, CountDistinct, Sum, Avg, Min, Max, and Expr(expr) with .Eq/.Ne/.Lt/.Le/.Gt/.Ge for HAVING comparisons. Qualified columns: Col(table, name), with .Eq/.Ne/.Lt/.Le/.Gt/.Ge, .EqCol (column-to-column, for ON), .IsNull/.IsNotNull, .Asc/.Desc, and the aggregate methods .Count/.CountDistinct/.Sum/.Avg/.Min/.Max.

renderer.RenderSelect(stmt, dialect) returns (sql string, args []any, err error). It returns an error for an unsupported dialect, a missing FROM table, an empty IN list, or a malformed statement.

OFFSET without LIMIT

MySQL, MariaDB, and SQLite only accept OFFSET as a suffix of LIMIT, so setting Offset without Limit renders a dialect-specific “no limit” sentinel in front of the bound OFFSET: LIMIT -1 for SQLite and LIMIT 18446744073709551615 for MySQL and MariaDB. PostgreSQL and ClickHouse accept a bare OFFSET and emit one. SQL Server and Oracle write it as the row-limiting clause with no FETCH, OFFSET @p1 ROWS and OFFSET :1 ROWS, and SQL Server keeps the sentinel ORDER BY (SELECT NULL) that clause requires. The sentinel is a structural constant, not caller data, so it is emitted as a literal and the OFFSET value stays a bound parameter.

YDB has no sentinel that works: a LIMIT of the Uint64 maximum fails the query, and the Int64 maximum leaves it running without an answer. So Offset without Limit is refused there with the key offset_without_limit; set a limit.