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 *ast.SelectStatement, and renderer.RenderSelect turns that into a SQL string plus its positional arguments for the PostgreSQL family, MySQL, MariaDB, and SQLite. See Dialect coverage for the exact set and for the dialects that are refused.

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.

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

Three 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.

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/renderer/dml_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: a renderer test proves the string, and only the server proves the SQL.

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, and :1, :2, … for Oracle — and returns the values in a matching []any.
• 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 (
  "go.5x5.cz/ptah/core/platform"
  "go.5x5.cz/ptah/core/query"
  "go.5x5.cz/ptah/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	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, and ClickHouse support all four.

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.

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 dialect that cannot execute it, rather than emit SQL that fails at execution time.

Dialect	RETURNING
PostgreSQL family (incl. CockroachDB, YugabyteDB, Spanner)	yes
SQLite	yes (since 3.35, 2021)
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: mariadb does not support RETURNING).
• SQLite gained RETURNING in 3.35 (2021). Ptah emits it; if you must target an older SQLite, avoid Returning.
• 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 (renderer: sqlserver does not support RETURNING).
• 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.
.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 (PostgreSQL family and SQLite only).
.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.