# SQL template

Source: https://pgcast.dev/sql-template

`sql` is the tagged template every query in pgcast is built from, the shortcuts included. This page lists what it takes, what each interpolation compiles to, and the `SQLFragment` it returns.

## `sql`

```ts
db.sql<
  Interpolations = db.SQL,
  RunResult = pg.QueryResult['rows'],
  Constraint = never,
>`…`: SQLFragment<RunResult, Constraint>
```

Returns a [`SQLFragment`](https://pgcast.dev/sql-template#SQLFragment): the literal parts as you wrote them, each interpolation compiled as below, every value a numbered parameter. Nothing is sent until [`run()`](https://pgcast.dev/sql-template#run).

You write:

```ts
const films = await db.sql<s.films.SQL, s.films.Selectable[]>`SELECT *
  FROM ${'films'} WHERE ${'release_year'} < ${db.param(2000)}`.run(pool);
```

Postgres runs:

```sql
SELECT *
FROM "films"
WHERE "release_year" < $1
```

Values: `[2000]`

TypeScript knows:

```ts
const films: s.films.Selectable[]
// that is
const films: {
  genres: string[];
  id: number;
  release_year: number;
  runtime_minutes: number;
  studio_id: number;
  tagline: string | null;
  title: string;
}[]
```

### Type arguments

| Argument | Default | What it sets |
|---|---|---|
| `Interpolations` | [`db.SQL`](https://pgcast.dev/sql-template#SQL): every table and column of the schema | what may be interpolated; `s.films.SQL` allows only the names of `films`, and `cols()` / `vals()` of any value |
| `RunResult` | `pg.QueryResult['rows']` (`any[]`) | what `run()` resolves to, and the type of the fragment as a `lateral` or `extras` value |
| `Constraint` | `never` | the value type a condition compares with; set by [`conditions`](https://pgcast.dev/conditions#conditions), checked by a Whereable. You rarely give it. |

`RunResult` is not checked against the SQL: it is your word for what comes back. The shortcuts set it for you.

A name outside `Interpolations` does not compile:

```ts
db.sql<s.films.SQL>`SELECT ${'name'} FROM ${'films'}`;  // error TS2345
```

### Interpolations

Each value between `${…}` compiles by what it is. The SQL is the built library's `.compile()`.

| Value | Compiles to | Entry |
|---|---|---|
| a string | a quoted name: `'films'` → `"films"`, `'public.films'` → `"public"."films"` | [Identifiers](https://pgcast.dev/sql-template#identifiers) |
| `param(x, cast?)` | `$1`, or `CAST($1 AS "timestamptz")`; `x` in the values | [`param()`](https://pgcast.dev/sql-template#param) |
| `raw(s)` | `s`, as it is | [`raw()`](https://pgcast.dev/sql-template#raw) |
| `cols(o)` / `cols(a)` | the object's keys, sorted, or the array's items, quoted: `"film_id", "tag"` | [`cols()`](https://pgcast.dev/sql-template#cols) |
| `vals(o)` / `vals(a)` | the values, in the order of `cols(o)`, or the array's: `$1, $2` | [`vals()`](https://pgcast.dev/sql-template#vals) |
| any other object | a Whereable of its own enumerable keys, plain or not: `("studio_id" = $1)`; `{}` → `TRUE`; a built-in object, such as a `Date`, throws | [Whereable objects](https://pgcast.dev/sql-template#whereable) |
| `self` | the current column, in a Whereable's fragment value | [`self`](https://pgcast.dev/sql-template#self) |
| `parent(c?)` | the column of the parent query: `"studios"."id"` | [`parent()`](https://pgcast.dev/sql-template#parent) |
| `Default` | `DEFAULT` | [`Default`](https://pgcast.dev/sql-template#Default) |
| a `sql` fragment | its own SQL, its parameters numbered on | [Nested fragments](https://pgcast.dev/sql-template#nested-fragments) |
| an array | each item in turn, nothing between; `[]` → nothing | [Arrays](https://pgcast.dev/sql-template#arrays) |

Anything else throws while compiling, so `run()` rejects before any query, and the type refuses it (`null` and `undefined` only with `strictNullChecks`): a number or a boolean (`Alien object while interpolating SQL: 42`), `undefined`, a symbol such as [`all`](https://pgcast.dev/sql-template#all), and `null`: "Interpolating `null` has no meaning in SQL: for a `NULL` value, interpolate `db.param(null)`…". Zapatos 6.6.1 threw a `TypeError` on `null`.

The global object, which the type lets through, throws too: "Did you use `self` (the global object) where you meant `db.self` (the Zapatos value)?…".

## Identifiers

An interpolated string is a table or column name. It is double-quoted, so its case is kept and a keyword is a name, and split on `.`. It can never close its quotes:

| String | Compiles to | |
|---|---|---|
| `'films'` | `"films"` | |
| `'public.films'` | `"public"."films"` | split on `.` |
| `'a"b'` | `"a""b"` | a `"` is doubled, as `quote_ident` does |
| `'a""b'` | `"a""b"` | already escaped by hand: as it is |
| `'"Films"."title"'` | `"Films"."title"` | already a well-formed quoted name: as it is |
| `'"films".title."x"'` | `"films".title."x"` | well-formed, ending with a quoted part: as it is |
| `'"films".title'` | `"""films"""."title"` | not well-formed, as it ends unquoted: each `"` doubled, split on `.` |
| `'"a" \|\| "b"'` | `"""a"" \|\| ""b"""` | not well-formed: one name |

- Well-formed: a quoted identifier, then quoted, `U&` quoted or unquoted ones after each `.`, with or without spaces around it, the last one quoted.
- The cost: a real column named `a""b` compiles to `"a""b"`, the column `a"b`.
- Zapatos 6.6.1 wrapped the string in `"` without escaping, and wrote as it is any string that started and ended with `"`: a name could splice SQL around it. The generated `Table` and `Column` types already refused such names; the why is in [Security](https://pgcast.dev/security).

Interpolate names rather than writing them in the literal: they are type-checked, and they follow the schema when you regenerate it.

## `param()`

```ts
db.param<T>(value: T, cast?: boolean | string): Parameter<T>
```

A numbered parameter: `$1`, `$2`…, numbered across the whole query, and `value` at that place in the values. The value never enters the SQL text.

You write:

```ts
const screenings = await db.sql<s.screenings.SQL, s.screenings.Selectable[]>`SELECT *
  FROM ${'screenings'}
  WHERE ${'starts_at'} >= ${db.param('2030-03-02T00:00:00Z', 'timestamptz')}`.run(pool);
```

Postgres runs:

```sql
SELECT *
FROM "screenings"
WHERE "starts_at" >= CAST($1 AS "timestamptz")
```

Values: `["2030-03-02T00:00:00Z"]`

TypeScript knows:

```ts
const screenings: s.screenings.Selectable[]
// that is
const screenings: {
  cinema_id: number;
  film_id: number;
  id: number;
  starts_at: Date;
  subtitled: boolean | null;
}[]
```

The last two rows apply when [`castArrayParamsToJson`](https://pgcast.dev/runtime-config#Config.castArrayParamsToJson) or [`castObjectParamsToJson`](https://pgcast.dev/runtime-config#Config.castObjectParamsToJson) is on; both are off by default.

| `cast` | Compiles to | Value sent |
|---|---|---|
| none | `$1` | `value`, as `pg` serialises it |
| `'timestamptz'` | `CAST($1 AS "timestamptz")` | `value` |
| `'pg_catalog"."text'` | `CAST($1 AS "pg_catalog"."text")` | `value` |
| `'public.crew_job'`, `'int4[]'` | `CAST($1 AS "public"."crew_job")`, `CAST($1 AS "int4"[])` | `value` |
| `true` | `CAST($1 AS "json")` for an array or a plain object; `$1` otherwise | `JSON.stringify(value)`; otherwise `value` |
| `false` | `$1` | `value`, never as JSON |
| none, for an array with `castArrayParamsToJson` on, or a plain object with `castObjectParamsToJson` on | `CAST($1 AS "json")` | `JSON.stringify(value)` |
| a string, for an array or a plain object under its option | the cast, as above: `CAST($1 AS "jsonb")` | `JSON.stringify(value)`; under `castArrayParamsToJson`, an array whose cast ends with `[]` or `[n]`, `CAST($1 AS "int4"[])`, sends `value` |

- A string `cast` without a `"` is split on `.`, each part quoted; array bounds at its end, `[]` or `[n]`, follow the quotes: `'public.crew_job[]'` gives `CAST($1 AS "public"."crew_job"[])`.
- With a `"`, it is written as `"cast"` if that is well-formed (quoted parts joined by `.`), or as it is if it is already quoted that way: a type whose own name holds a `.` is `'"crew.job"'`, as `'crew.job'` is type `job` in schema `crew`.
- Any other string with a `"` is one quoted name, any `"` doubled, so it cannot close its quotes: `'int4") || (SELECT 1) || ("int4'` gives `CAST($1 AS "int4"") || (SELECT 1) || (""int4")`, a type that does not exist.
- Zapatos 6.6.1 wrote any `cast` as it is between quotes, so `'public.crew_job'` and `'int4[]'` named types that do not exist.
- Each part stays quoted, so a name only the SQL grammar knows is no type here: `'int'`, `'double precision'`, `'varchar(10)'` fail (42704). Give the type's own name (`'int4'`, `'float8'`, `'timestamptz'`), or write the cast in `sql`: `` db.sql`CAST(${db.param(x)} AS varchar(10))` ``.
- Under `castArrayParamsToJson` or `castObjectParamsToJson`, a string cast is the type written, and the value stays the JSON text the option sends (the last row of the table): `param({ a: 1 }, 'jsonb')` reaches `=` and `@>` on a `jsonb` column.
- Under `castArrayParamsToJson`, an array whose cast ends with `[]` or `[n]` goes as an array: `'_int4'` and a domain over an array still send JSON, which Postgres refuses as an array (22P02).
- Zapatos 6.6.1 dropped a string cast under either option for `CAST($1 AS "json")` ([#36](https://github.com/AlexandreDemartin/pgcast/issues/36)), so a cast that names another type than its target now applies: `param(arr, 'int4[]')` into a `json` column is refused (42804), into a `text` column stores `{1,2}` for `[1,2]`.
- Without a cast, `castArrayParamsToJson` makes `` db.sql`${db.param(ids)}::int4[]` `` `CAST($1 AS "json")::int4[]`, which Postgres rejects (42846): write `db.param(ids, 'int4[]')`, or pass `false`, which keeps the value out of JSON under either option: `$1::int4[]`.
- As a Whereable value or in `vals([…])` (so in [`isIn()`](https://pgcast.dev/conditions#conditions.isIn)), a `Parameter` compiles as it is, with its cast: `{ title: db.param('x', 'text') }` gives `("title" = CAST($1 AS "text"))`. Zapatos 6.6.1 wrapped it in a second parameter, sent as JSON (`'{"value":"x","cast":"text"}'`).

### `Parameter`

```ts
class Parameter<T = any> { value: T; cast?: boolean | string }
```

What `param()` returns. The generated Whereable, Insertable and Updatable types take a `Parameter<T>` of the column's type wherever they take a `T`.

## `raw()`

```ts
db.raw(sql: string): DangerousRawString
```

The string, written into the SQL as it is: for syntax a parameter cannot stand for, such as a keyword.

You write:

```ts
const films = await db.sql<s.films.SQL, s.films.Selectable[]>`SELECT *
  FROM ${'films'}
  ORDER BY ${'tagline'} ${db.raw('DESC NULLS LAST')}`.run(pool);
```

Postgres runs:

```sql
SELECT *
FROM "films"
ORDER BY "tagline" DESC NULLS LAST
```

Values: `[]`

TypeScript knows:

```ts
const films: s.films.Selectable[]
// that is
const films: {
  genres: string[];
  id: number;
  release_year: number;
  runtime_minutes: number;
  studio_id: number;
  tagline: string | null;
  title: string;
}[]
```

> **Security**
>
> Whatever reaches `raw()` runs as SQL. Never give it a caller's text; a value is a [`param()`](https://pgcast.dev/sql-template#param). How to keep hand-written SQL safe: [Raw SQL](https://pgcast.dev/raw-sql).

As a filter value, `raw()` throws while compiling, and so do `cols()` and `vals()`. As a Whereable value, in `isIn()` / `isNotIn()`, and in `vals([…])` within a filter:

> `` `db.raw()`, `db.cols()` and `db.vals()` have no meaning as a value in a Whereable (a filter), nor in `isIn()`: to compare with SQL, use a `sql` fragment value, where `db.self` stands for the column; to compare with a value, give the value itself, or `db.param()`. ``

As the value of `eq()`, `add()` and the other [conditions](https://pgcast.dev/conditions):

> `` `db.raw()`, `db.cols()` and `db.vals()` have no meaning as the value of `eq()`, `add()` and the other conditions: for SQL, write a `sql` fragment with `db.self` in place of the condition; for a value, give the value itself, or `db.param()`. ``

In [`vals({…})`](https://pgcast.dev/sql-template#vals), so as an `insert()`, `upsert()` or `update()` value, and in a `sql` template of your own, in a `lateral` or not:

> `` `db.raw()`, `db.cols()` and `db.vals()` have no meaning as a value in `vals({…})`, so as an `insert()`, `upsert()` or `update()` value: to write SQL, use a `sql` fragment value (in `update()`, `db.self` there stands for the column); to write a value, give the value itself, or `db.param()`. ``

The type refuses them there and as an `insert()` or `update()` value, on every column it types (a column typed `any`, as a `zapatos/custom` placeholder is, takes anything); it takes them in `vals({…})` of a `sql` template:

```ts
db.sql<s.films.SQL>`SELECT * FROM ${'films'} WHERE ${{ title: db.raw("'x'") }}`;  // error TS2345
```

- To compare with SQL: a fragment value, `` { title: db.sql`${db.self} = ${db.raw("'x'")}` } ``.
- Zapatos 6.6.1 sent them there as one parameter, as JSON (`'{"value":…}'`): no row on a text or `jsonb` column, every row with `ne()` and `isNotIn()`, an error (22P02) on other types; as a write value, a text or `jsonb` column stored it.
- Outside a filter, `vals([db.raw('x')])` is still a parameter, the wrapper as JSON: `'{"value":"x"}'`, which a text column stores as it is; in `upsert()`'s `updateValues`, `raw()` is SQL ([`upsert()`](https://pgcast.dev/insert#upsert)).

### `DangerousRawString`

```ts
class DangerousRawString { value: string }
```

What `raw()` returns. A type-only member keeps it from passing as a `Parameter`, and an object of the same shape does not compile in its place (TS2741):

```ts
const notRaw: db.DangerousRawString = { value: 'x' };  // error TS2741
```

## `cols()`

```ts
db.cols<T>(namesOrObject: T): ColumnNames<T>
```

A comma-separated list of quoted names:

- an object: its keys, sorted, `cols({ tag, film_id })` → `"film_id", "tag"`;
- an array: its items in their order, each an [identifier](https://pgcast.dev/sql-template#identifiers), `cols(['id', 'title'])` → `"id", "title"`.

With `vals()` of the same object, an `INSERT` in which keys and values line up:

You write:

```ts
function insertTag(tag: s.tags.Insertable) {
  return db.sql<s.tags.SQL, s.tags.Selectable[]>`INSERT INTO ${'tags'} (${db.cols(tag)})
    VALUES (${db.vals(tag)}) RETURNING *`;
}

const inserted = await insertTag({ tag: 'coastal', film_id: 1000 }).run(pool);
```

Postgres runs:

```sql
INSERT INTO "tags" ("film_id", "tag")
VALUES ($1, $2)
RETURNING *
```

Values: `[1000, "coastal"]`

TypeScript knows:

```ts
const inserted: s.tags.Selectable[]
// that is
const inserted: {
  film_id: number;
  tag: string;
}[]
```

With arrays, a `SELECT` of some columns and an `IN` list; `s.films.OnlyCols<…>` is the row type of those columns:

You write:

```ts
const films = await db.sql<s.films.SQL, s.films.OnlyCols<['id', 'title']>[]>`SELECT ${db.cols(['id', 'title'])}
  FROM ${'films'} WHERE ${'id'} IN (${db.vals([1000, 1002])})`.run(pool);
```

Postgres runs:

```sql
SELECT "id", "title"
FROM "films"
WHERE "id" IN ($1, $2)
```

Values: `[1000, 1002]`

TypeScript knows:

```ts
const films: s.films.OnlyCols<["id", "title"]>[]
// that is
const films: {
  id: number;
  title: string;
}[]
```

As a filter value, and as a value in `vals({…})` (so in a write), `cols()` throws, as [`raw()`](https://pgcast.dev/sql-template#raw) does.

### `ColumnNames`

```ts
class ColumnNames<T> { value: T }
```

What `cols()` returns. A type-only member keeps it from passing as a `Parameter`.

## `vals()`

```ts
db.vals<T>(valuesOrObject: T): ColumnValues<T>
```

A comma-separated list of values. Each value compiles as follows.

| Value | In `vals({…})` | In `vals([…])` |
|---|---|---|
| a plain value | a parameter, `$1` | a parameter, `$1` |
| a `Parameter` | as it is, with its cast | as it is, with its cast |
| a `sql` fragment | its SQL, with its key as the current column (so [`self`](https://pgcast.dev/sql-template#self) works) | its SQL |
| `Default` | `DEFAULT` | `DEFAULT`; throws in a filter |
| `parent(c?)` | the [parent column](https://pgcast.dev/sql-template#parent), without `c` the column of its key; throws outside a nested query | the [parent column](https://pgcast.dev/sql-template#parent) |
| `raw()`, `cols()`, `vals()` | throw | throw in a filter; a parameter outside one |
| `self` | a parameter | throws in a filter; a parameter outside one |

- An object's values come in the order of its sorted keys, as `cols()` lists them.
- "In a filter" is wherever `vals([…])` has a current column: in `isIn()` and `isNotIn()`, and in a fragment value of a Whereable or of `vals({…})`. There:
  - `Default` throws "`` `db.Default` has no meaning in a Whereable (a filter)… ``";
  - `self` "`` `db.self` has no meaning as a value in a Whereable (a filter), nor in `isIn()`… ``";
  - `raw()`, `cols()` and `vals()` as [`raw()`](https://pgcast.dev/sql-template#raw) says.
- As a filter value itself, or a value in `vals({…})`, `vals()` throws, as [`raw()`](https://pgcast.dev/sql-template#raw) does.
- `insert()`, `upsert()` and `update()` compile their values with `vals({…})`, outside a nested query: `parent()`, `raw()`, `cols()` and `vals()` throw there, before any query.
- Zapatos 6.6.1 wrapped a `Parameter`, `Default` and `parent()` in a second parameter in an array: `'{"value":…}'`, `'Symbol(DEFAULT)'`, `'{"value":"id"}'`; and `parent()`, `raw()`, `cols()` and `vals()` in an object, which the writes stored on a text or `jsonb` column.

### `ColumnValues`

```ts
class ColumnValues<T> { value: T }
```

What `vals()` returns. A type-only member keeps it from passing as a `Parameter`.

## Whereable objects

An object is a Whereable of the table: each own enumerable key a column, each value what it must match. The keys are sorted, each pair compiled as below, joined by `AND`, the whole in parentheses; there is no `WHERE`, which you write. An empty object is `TRUE`.

You write:

```ts
const films = await db.sql<s.films.SQL, s.films.Selectable[]>`SELECT *
  FROM ${'films'} WHERE ${{
    studio_id: 1000,
    runtime_minutes: db.sql`${db.self} > ${db.param(100)}`,
  }}`.run(pool);
```

Postgres runs:

```sql
SELECT *
FROM "films"
WHERE (("runtime_minutes" > $1) AND "studio_id" = $2)
```

Values: `[100, 1000]`

TypeScript knows:

```ts
const films: s.films.Selectable[]
// that is
const films: {
  genres: string[];
  id: number;
  release_year: number;
  runtime_minutes: number;
  studio_id: number;
  tagline: string | null;
  title: string;
}[]
```

| Value | Compiles to |
|---|---|
| a plain value, `null` and arrays included | `("title" = $1)` |
| a `Parameter` | `("title" = CAST($1 AS "text"))`, as it is |
| a `sql` fragment | `(…)`, compiled with the key as the current column: [`self`](https://pgcast.dev/sql-template#self) is the column |
| a [condition](https://pgcast.dev/conditions) | the same: a condition is a fragment, `like('The%')` |
| `parent(c?)` | `("studio_id" = "studios"."id")`, in a `lateral` subquery |
| `Default`, `self` | throws |
| `raw()`, `cols()`, `vals()` | throws ([`raw()`](https://pgcast.dev/sql-template#raw)) |

- `null` is a parameter too, and `= NULL` matches no row: use [`conditions.isNull`](https://pgcast.dev/conditions#conditions.isNull).
- `Default` throws "`` `db.Default` has no meaning in a Whereable (a filter): leave the key out, or compare with a value. `DEFAULT` is for INSERT and UPDATE values. ``"; Zapatos 6.6.1 sent it as `'Symbol(DEFAULT)'`.
- The shortcuts take the same object as their `where` ([`select()`](https://pgcast.dev/select#select)).

> **Security**
>
> An interpolated object that is not one of the other [interpolations](https://pgcast.dev/sql-template#interpolations) compiles as a Whereable, plain or not: a class instance `{ title }` gives `("title" = $1)`, and one with no own enumerable key gives `TRUE`, even of a class that extends a built-in (`class Premiere extends Date {}`). Check what reaches a `where` at run time.

A built-in object throws while compiling, before any query, even with own keys added (`Object.assign(new Date(0), { id: 1 })`): `Date`, `Map`, `Set`, `WeakMap`, `WeakSet`, `RegExp`, `Promise`, `Error` and its native subtypes (`TypeError`, …), `ArrayBuffer`, a typed array, `Buffer`, `DataView`, and the boxes `Number`, `String` and `Boolean`:

> `` A built-in `…` has no meaning as a Whereable (a filter): it would compile to its own enumerable keys, and to `TRUE`, every row, when it has none. To compare with a value, give it as `db.param(…)`; to filter, give a plain object (`{ column: value }`); await a `Promise` first. ``

Zapatos 6.6.1 compiled it as a Whereable of its own keys: `TRUE` for most, so `` db.sql`DELETE FROM ${'films'} WHERE ${new Date()}` `` was `DELETE FROM "films" WHERE TRUE` and deleted every row; `("0" = $1 …)` for a typed array.

The type refuses most of them (TS2345), not where a column shares one of their members: `db.deletes('actors', new Error('x'))` type-checks, since `actors` has a `name` column.

Only the built-ins of the realm that runs pgcast throw, matched by their exact prototype. As in Zapatos 6.6.1:

- a `Date` from another realm, such as `vm.runInNewContext('new Date(0)')` (Jest runs each test file in a `vm` context), gives `TRUE`;
- an instance of a class that extends a built-in keeps its own keys: `new Frames(1)`, with `class Frames extends Uint8Array {}`, gives `("0" = $1)`.

## `self`

```ts
db.self
```

The current column, in a Whereable's fragment value: `` { runtime_minutes: db.sql`${db.self} > ${db.param(100)}` } `` gives `(("runtime_minutes" > $1))`. In a fragment value of `vals({…})`, the current column is its key: `` vals({ tag: db.sql`upper(${db.self})` }) `` gives `upper("tag")`.

- Where there is no current column, it throws: "The 'self' column alias has no meaning here".
- As a filter value it throws: it is the column, not a value to compare with it. Zapatos 6.6.1 sent it as the string `'Symbol(self)'`.
  - As a Whereable value, in `isIn()` / `isNotIn()` and in `vals([…])` within a filter: "`` `db.self` has no meaning as a value in a Whereable (a filter), nor in `isIn()`: compare with a value, or use it in a `sql` fragment value, where it stands for the column. ``"
  - As the value of `eq()`, `add()` and the other conditions: "`` `db.self` has no meaning as the value of `eq()`, `add()` and the other conditions: it is already the column on the left. To compare the column with itself, write a `sql` fragment with `db.self` on both sides. ``"

### `SelfType`

```ts
type SelfType = typeof self
```

## `parent()`

```ts
db.parent<T extends Column | undefined = Column | undefined>(x?: T): ParentColumn<T>
```

A column of the parent query's table, in a subquery given to `lateral` ([Joins](https://pgcast.dev/lateral)): `"<parent table or alias>"."<column>"`.

You write:

```ts
const studios = await db.select('studios', db.all, {
  lateral: {
    films: db.select('films', { studio_id: db.parent('id') }, { columns: ['title'] }),
  },
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT to_jsonb("studios".*) ||
    jsonb_build_object($1::text, "lateral_films".result) AS result
  FROM "studios"
  LEFT JOIN LATERAL (
    SELECT coalesce(jsonb_agg(result), '[]') AS result
    FROM (
      SELECT jsonb_build_object($2::text, "title") AS result
      FROM "films"
      WHERE ("studio_id" = "studios"."id")
    ) AS "sq_films"
  ) AS "lateral_films" ON true
) AS "sq_studios"
```

Values: `["films", "title"]`

TypeScript knows:

```ts
const studios: (s.studios.JSONSelectable & db.LateralResult<{
  films: db.SQLFragment<db.JSONOnlyColsForTable<"films", "title"[]>[], never>;
}>)[]
// that is
const studios: {
  id: number;
  name: string;
  films: db.JSONOnlyColsForTable<"films", "title"[]>[];
}[]
```

- Without a column, the column of the filter it sits in: `{ film_id: db.parent() }`, in a `lateral` of `roles`, gives `("film_id" = "roles"."film_id")`. With no filter, it throws before writing anything: "`db.parent()` with no column has no meaning here: name the column, `db.parent('id')`…". Zapatos 6.6.1 wrote the table and its `.`, then threw "Alien object while interpolating SQL: undefined".
- It works as a Whereable value, in a `sql` fragment, in `eq()` and in `vals([…])`, so in `isIn()`: `{ film_id: isIn([db.parent()]) }` gives `(("film_id" IN ("roles"."film_id")))`. In `vals([…])`, Zapatos 6.6.1 sent it as JSON: `isIn([parent('id')])` compared with `'{"value":"id"}'`.
- In `vals({…})` too, where without a column it takes the column of its key: `` db.sql`SELECT ${db.vals({ name: db.parent() })} AS result` ``, in a `lateral` of `studios`, gives `SELECT "studios"."name" AS result`. Zapatos 6.6.1 sent it there as JSON too.
- Outside a nested query, it throws "The 'parent' table alias has no meaning here", so `run()` rejects before any query. So does every `insert()`, `upsert()` or `update()` value, where Zapatos 6.6.1 stored `{"value":"id"}` on a text column.

### `ParentColumn`

```ts
class ParentColumn<T extends Column | undefined = Column | undefined> { value?: T }
```

What `parent()` returns. A type-only member keeps `cols()` and `vals()` from passing as a `ParentColumn`.

## `Default`

```ts
db.Default
```

The keyword `DEFAULT`: a column's default, in an `INSERT` or `UPDATE` value, alone or in `vals()`.

You write:

```ts
const bookings = await db.sql<s.seat_bookings.SQL, s.seat_bookings.Selectable[]>`INSERT INTO ${'seat_bookings'}
  (${'screening_id'}, ${'seat'}, ${'customer'}, ${'booked_at'})
  VALUES (${db.vals([1000, 'C7', 'Wren Hollis', db.Default])}) RETURNING *`.run(pool);
```

Postgres runs:

```sql
INSERT INTO "seat_bookings"
  ("screening_id", "seat", "customer", "booked_at")
VALUES ($1, $2, $3, DEFAULT)
RETURNING *
```

Values: `[1000, "C7", "Wren Hollis"]`

TypeScript knows:

```ts
const bookings: s.seat_bookings.Selectable[]
// that is
const bookings: {
  booked_at: Date;
  customer: string;
  screening_id: number;
  seat: PgSeat_label;
}[]
```

- In a filter it throws while compiling.
  - As a Whereable value ([above](https://pgcast.dev/sql-template#whereable)), in `isIn()` / `isNotIn()`, and in `vals([…])` with a current column ([`vals()`](https://pgcast.dev/sql-template#vals)): "`` `db.Default` has no meaning in a Whereable (a filter): leave the key out, or compare with a value. `DEFAULT` is for INSERT and UPDATE values. ``"
  - As the value of `eq()`, `add()` and the other conditions: "`` `db.Default` has no meaning as the value of `eq()`, `add()` and the other conditions: compare with a value. To set a column to its default in an UPDATE, give `db.Default` as the value itself: `{ n: db.Default }`. ``"
- For `add()` and `subtract()`, set the column itself: `{ booked_at: db.Default }`.
- Zapatos 6.6.1 sent it as the string `'Symbol(DEFAULT)'` in all of these, and in `vals([…])`, where it is now `DEFAULT`.

### `DefaultType`

```ts
type DefaultType = typeof Default
```

## Nested fragments

A `sql` fragment interpolated in another compiles in place. Its parameters are numbered on from the outer ones, and it inherits the outer parent table and current column. A fragment that compiles to nothing, or an empty array, lets a query grow by condition:

You write:

```ts
function screeningsAt(cinemaId: number, subtitledOnly: boolean) {
  const subtitled = subtitledOnly ? db.sql<s.screenings.SQL>` AND ${'subtitled'}` : [];
  return db.sql<s.screenings.SQL, s.screenings.Selectable[]>`SELECT *
    FROM ${'screenings'} WHERE ${'cinema_id'} = ${db.param(cinemaId)}${subtitled}`;
}

const subtitledAtTheLantern = await screeningsAt(1000, true).run(pool);
```

Postgres runs:

```sql
SELECT *
FROM "screenings"
WHERE "cinema_id" = $1 AND "subtitled"
```

Values: `[1000]`

TypeScript knows:

```ts
const subtitledAtTheLantern: s.screenings.Selectable[]
// that is
const subtitledAtTheLantern: {
  cinema_id: number;
  film_id: number;
  id: number;
  starts_at: Date;
  subtitled: boolean | null;
}[]
```

A nested fragment's `RunResult` and `runResultTransform` are not used: only the outer fragment's `run()` counts.

## Arrays

An array's items compile one after the other, each as if interpolated alone, with nothing between them: ``[db.sql`a`, ' ', 'b']`` gives `a" ""b"`, the string `' '` being a name. An empty array compiles to nothing. To put something between the items, [`mapWithSeparator()`](https://pgcast.dev/sql-template#mapWithSeparator).

## `mapWithSeparator()`

```ts
db.mapWithSeparator<TIn, TSep, TOut>(
  arr: readonly TIn[],
  separator: TSep,
  cb: (x: TIn, i: number, a: readonly TIn[]) => TOut,
): (TOut | TSep)[]
```

Maps `arr` with `cb`, `separator` between each two results; `[]` for no items. A plain array, so it compiles as [one](https://pgcast.dev/sql-template#arrays):

You write:

```ts
const films = await db.sql<s.films.SQL, s.films.Selectable[]>`SELECT *
  FROM ${'films'} WHERE ${db.mapWithSeparator(
    ['The Salt Orchard', 'A Lantern for Edith'],
    db.sql` OR `,
    title => ({ title }),
  )}`.run(pool);
```

Postgres runs:

```sql
SELECT *
FROM "films"
WHERE ("title" = $1) OR ("title" = $2)
```

Values: `["The Salt Orchard", "A Lantern for Edith"]`

TypeScript knows:

```ts
const films: s.films.Selectable[]
// that is
const films: {
  genres: string[];
  id: number;
  release_year: number;
  runtime_minutes: number;
  studio_id: number;
  tagline: string | null;
  title: string;
}[]
```

## `all`

```ts
db.all
```

The `where` of `select()`, `count()` and the other selects that matches every row: `db.select('films', db.all)` writes no `WHERE` ([`select()`](https://pgcast.dev/select#select)). It is not an interpolation: in `sql`, the type refuses it, and it throws.

### `AllType`

```ts
type AllType = typeof all
```

## `SQLFragment`

```ts
class SQLFragment<RunResult = pg.QueryResult['rows'], Constraint = never>
```

What `sql` and every shortcut return: a query, or part of one, not yet compiled. You never construct one with `new`.

| Member | What it is |
|---|---|
| [`run(queryable, force?)`](https://pgcast.dev/sql-template#run) | compiles, runs, and resolves to `RunResult` |
| [`compile()`](https://pgcast.dev/sql-template#compile) | `{ text, values }`, the query `run()` sends |
| [`runResultTransform`](https://pgcast.dev/sql-template#runResultTransform) | turns `pg`'s result into `RunResult` |
| [`prepared(name?)`](https://pgcast.dev/sql-template#prepared) | makes it a prepared statement |
| `copy(override?)` | a shallow copy, some properties replaced |
| `noop`, `noopResult` | set by `insert()` and `upsert()` given `[]`: `run()` sends nothing and resolves to `noopResult` |
| `parentTable` | set on a `lateral` subquery by its parent, for [`parent()`](https://pgcast.dev/sql-template#parent) |
| `preparedName` | set by `prepared()` |
| `compileExpression()` | used by `compile()`, internal |

### `run()`

```ts
fragment.run(queryable: Queryable, force = false): Promise<RunResult>
```

1. Compiles the fragment ([`compile()`](https://pgcast.dev/sql-template#compile)); an error there rejects, before any query.
2. Calls [`queryListener`](https://pgcast.dev/runtime-config#Config.queryListener) with the query and the transaction's id, if set.
3. Sends the query to `queryable`, and passes `pg`'s result through [`runResultTransform`](https://pgcast.dev/sql-template#runResultTransform). A `noop` fragment sends nothing and gives `noopResult`, unless `force` is `true`.
4. Calls [`resultListener`](https://pgcast.dev/runtime-config#Config.resultListener) with the result, the transaction's id, the time taken and the query, if set.

A `noop` fragment still compiles, its SQL behind a comment. `force` sends it, and it inserts nothing: `.run()` gives `[]`.

You write:

```ts
const forced = await db.insert('tags', []).run(pool, true);
```

Postgres runs:

```sql
/* marked no-op: won't hit DB unless forced -> */ INSERT INTO "tags"
SELECT null
WHERE false
```

Values: `[]`

TypeScript knows:

```ts
const forced: tags.JSONSelectable[]
// that is
const forced: {
  film_id: number;
  tag: string;
}[]
```

### `compile()`

```ts
fragment.compile(): SQLQuery
```

The query as `pg` takes it ([`SQLQuery`](https://pgcast.dev/runtime-config#SQLQuery)): `text`, its parameters numbered from `$1`, and `values`, in their order; `name` too, once [prepared](https://pgcast.dev/sql-template#prepared). It sends nothing, and throws where an interpolation has no meaning. Its arguments are for its own recursion: call it with none.

The text is not a stable API: a minor version may change it, as long as the result does not ([Testing](https://pgcast.dev/testing#the-sql-text-is-not-a-stable-api)).

You write:

```ts
const compiled = db.sql<s.films.SQL>`SELECT *
  FROM ${'films'} WHERE ${{ studio_id: 1000 }}`.compile();

await pool.query(compiled);  // what run() would send
```

Postgres runs:

```sql
SELECT *
FROM "films"
WHERE ("studio_id" = $1)
```

Values: `[1000]`

TypeScript knows:

```ts
const compiled: db.SQLQuery
// that is
const compiled: {
  text: string;
  values: any[];
  name?: string | undefined;
}
```

### `runResultTransform`

```ts
fragment.runResultTransform: (qr: pg.QueryResult) => any  // default: qr => qr.rows
```

Applied by `run()` to what `pg` returns. The shortcuts set their own (`selectOne()` takes the first row's `result`). Assign one to return something else; `RunResult` must then be the type it returns:

You write:

```ts
function filmCountQuery() {
  const query = db.sql<s.films.SQL, number>`SELECT count(*) FROM ${'films'}`;
  query.runResultTransform = qr => Number(qr.rows[0].count);
  return query;
}

const filmCount = await filmCountQuery().run(pool);
```

Postgres runs:

```sql
SELECT count(*)
FROM "films"
```

Values: `[]`

TypeScript knows:

```ts
const filmCount: number
```

It applies only to `run()` on that fragment: not when it is nested in another, nor given to `lateral` or `extras`.

### `prepared()`

```ts
fragment.prepared(name = `_zapatos_prepared_${n}`): this
```

Sets the `name` that `compile()` adds to the query, so that Postgres prepares the statement once per connection and runs it by name after ([node-postgres](https://node-postgres.com/features/queries#prepared-statements)). Without a name, `_zapatos_prepared_0`, `_1`… in the order of the calls.

The values are parameters, not part of the text, so one name serves every call of a query function whose text does not change; without a name, each call prepares another statement:

```ts
const filmById = (id: number) => db.selectOne('films', { id }).prepared('film_by_id');

console.log(filmById(1000).compile().name);  // film_by_id

const client = await pool.connect();
try {
  await filmById(1000).run(client);  // Postgres prepares the statement on this connection, then runs it
  await filmById(1001).run(client);  // and runs it by name: the same text, other values
} finally {
  client.release();
}
```

A name stands for one text: on a connection that has prepared `'p'`, a fragment of another text named `'p'` makes `pg` reject with "Prepared statements must be unique - 'p' was used for a different statement".

## `RunResultForSQLFragment`

```ts
type RunResultForSQLFragment<F extends SQLFragment<any, any>>
```

The `RunResult` of `F`, with `undefined` read as `null`: a fragment nested as a `lateral` or `extras` value comes back as JSON, where a missing row is `null`. So `selectOne()`'s `crew.JSONSelectable | undefined` becomes `crew.JSONSelectable | null` there:

```ts
const mentor = db.selectOne('crew', { id: db.parent('mentor_id') }, { alias: 'mentor' });

type Mentor = db.RunResultForSQLFragment<typeof mentor>;
const nameOf = (row: Mentor) => row?.name ?? 'no mentor';

const crew = await db.select('crew', db.all, { lateral: { mentor } }).run(pool);
const mentors = crew.map(member => nameOf(member.mentor));
```

## `Queryable`

```ts
type Queryable = pg.ClientBase | pg.Pool
```

What `run()` takes: a pool, a client, or the client a [`transaction()`](https://pgcast.dev/transaction#transaction) gives its callback. Typed so, a query function runs on any of them:

```ts
const titlesOf = (queryable: db.Queryable, studio_id: number) =>
  db.select('films', { studio_id }, { columns: ['title'] }).run(queryable);

const larkspur = await titlesOf(pool, 1000);
const inTxn = await db.readCommitted(pool, txnClient => titlesOf(txnClient, 1000));
```

`Queryable` takes a transaction's client whatever its level: a function that needs `SERIALIZABLE` takes `pg.Pool | TxnClientForSerializable` instead ([`TxnClientForSerializable`](https://pgcast.dev/transaction#TxnClientForSerializable), [Book inside a larger transaction](https://pgcast.dev/retries#book-inside-a-larger-transaction)).

## `SQL`

```ts
type SQL = SQLExpression | SQLExpression[]
```

The default `Interpolations` of `sql`: an expression, or an array of them. Each table of the generated schema has its own, `s.films.SQL`, which takes the names and Whereables of that table only; `cols()` and `vals()` of any value, as in Zapatos 6.6.1.

So `` db.sql<s.films.SQL>`INSERT INTO ${'films'} (${db.cols({ seat: 1 })}) VALUES (${db.vals({ seat: 1 })})` `` type-checks, and fails only in Postgres (42703: `films` has no `seat`).

### `SQLExpression`

```ts
type SQLExpression = Table | ColumnNames<Updatable | (keyof Updatable)[]> | ColumnValues<Updatable | any[]>
  | Whereable | Column | ParentColumn | GenericSQLExpression
```

One interpolation: a table or column name of any table, a Whereable, `parent()`, `cols()` / `vals()` of an Updatable, or a [`GenericSQLExpression`](https://pgcast.dev/sql-template#GenericSQLExpression), which takes `cols()` / `vals()` of any value.

### `GenericSQLExpression`

```ts
type GenericSQLExpression = SQLFragment<any, any> | Parameter | DefaultType | DangerousRawString
  | SelfType | ColumnNames<any> | ColumnValues<any>
```

The interpolations whose type is the same for every table: they fit in any table's `SQL`. `ColumnNames<any>` and `ColumnValues<any>` are among them, so the keys of `cols()` and `vals()` are not checked against the table.