# Query builder Build parameterized, dialect-aware SELECT statements with the core/query package. Source: https://docs.ptah.run/v0.8.0/extend/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](#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`](https://github.com/stokaro/ptah/issues/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](#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](#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](#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](../../databases/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: ```text 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 ```go 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: ```sql 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: ```go 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. ```go 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: ```sql 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. ```go 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: ```sql 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: ```go 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`. ```go 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: ```sql 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. ```go 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: ```sql 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 ```go 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. ```go // 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.