# Raw SQL

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

When no shortcut writes the query you need (a join, a window function, an `INSERT … SELECT`), write it in the `sql` template, and keep every value that comes from outside out of its text. You already know the shortcuts from the [tutorial](https://pgcast.dev/tutorial); every interpolation is in [SQL template](https://pgcast.dev/sql-template).

**At a glance**

- Names go between `${…}`, checked against the tables you give `sql`; a value from outside goes in as `db.param()` or in a Whereable.
- Syntax a caller chooses, such as a sort direction, is one of your own constants: never give `raw()` a caller's text.
- `.compile()` shows the `text` and `values` a query would send: a test that freezes `text` keeps every value from outside in `values`.

## Write the query, its names interpolated

Write the SQL as you would in `psql`, but put each table and column name between `${…}` instead of in the text. Give `sql` the tables the query may name, then the type of the rows you expect:

You write:

```ts
type Tables = s.films.SQL | s.screenings.SQL | s.cinemas.SQL;
type Listing = Pick<s.films.Selectable, 'title'> & Pick<s.screenings.Selectable, 'starts_at'>;

function listings(cinema: string) {
  return db.sql<Tables, Listing[]>`SELECT ${'films'}.${'title'}, ${'screenings'}.${'starts_at'}
    FROM ${'screenings'}
    JOIN ${'films'} ON ${'films'}.${'id'} = ${'screenings'}.${'film_id'}
    JOIN ${'cinemas'} ON ${'cinemas'}.${'id'} = ${'screenings'}.${'cinema_id'}
    WHERE ${'cinemas'}.${'name'} = ${db.param(cinema)}
    ORDER BY ${'screenings'}.${'starts_at'}`;
}

const listed = await listings('The Lantern').run(pool);
```

Postgres runs:

```sql
SELECT "films"."title", "screenings"."starts_at"
FROM "screenings"
JOIN "films" ON "films"."id" = "screenings"."film_id"
JOIN "cinemas" ON "cinemas"."id" = "screenings"."cinema_id"
WHERE "cinemas"."name" = $1
ORDER BY "screenings"."starts_at"
```

Values: `["The Lantern"]`

TypeScript knows:

```ts
const listed: Listing[]
// that is
const listed: {
  title: string;
  starts_at: Date;
}[]
```

- An interpolated name is quoted, `"films"`, and checked against the first type argument: here `Tables`, the `SQL` type of each table the query names. Qualify a column as `${'films'}.${'title'}`: the type knows each table's columns, not `'films.title'`.
- The second type argument is your word for what comes back: nothing checks it against the SQL. The rows come as `pg` parses them, as a `Selectable` describes them (a `Date` for a `timestamptz`), not as the JSON of the shortcuts' `JSONSelectable`. A `count(*)` is a `bigint`, which `pg` gives as a string: `'4'`.
- Keywords stay in the text. SQL you write yourself is safe there; only what comes from outside needs the steps below.

A name the type doesn't know fails to compile: a query that names `screenings` needs `s.screenings.SQL` in its first type argument.

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

A write is written the same way. This `INSERT … SELECT` copies one film's tags to another, and `RETURNING *` gives back the rows it added:

You write:

```ts
function copyTags(fromFilm: number, toFilm: number) {
  const columns: s.tags.Column[] = ['film_id', 'tag'];
  return db.sql<s.tags.SQL, s.tags.Selectable[]>`INSERT INTO ${'tags'} (${db.cols(columns)})
    SELECT ${db.param(toFilm)}, ${'tag'} FROM ${'tags'} WHERE ${{ film_id: fromFilm }}
    ON CONFLICT DO NOTHING RETURNING *`;
}

const copied = await copyTags(1000, 1003).run(pool);
```

Postgres runs:

```sql
INSERT INTO "tags" ("film_id", "tag")
SELECT $1, "tag"
FROM "tags"
WHERE ("film_id" = $2)
ON CONFLICT DO NOTHING
RETURNING *
```

Values: `[1003, 1000]`

TypeScript knows:

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

- [`db.cols()`](https://pgcast.dev/sql-template#cols) writes the column list, `"film_id", "tag"`. `columns`, typed `s.tags.Column[]`, checks the names: `db.cols()` alone takes any, so `'tagz'` would compile, and Postgres refuse it (42703).
- `ON CONFLICT DO NOTHING` skips a tag the film has already. Without it, Postgres refuses the whole `INSERT` with 23505, and adds none.

## Pass each value as a parameter

A value from a caller (a form, a URL, a row you read elsewhere) goes in as [`db.param()`](https://pgcast.dev/sql-template#param), or in a [Whereable object](https://pgcast.dev/sql-template#whereable), which makes a parameter of each value:

You write:

```ts
function studioFilms(studioId: number, genre: string) {
  return db.sql<s.films.SQL, s.films.Selectable[]>`SELECT * FROM ${'films'}
    WHERE ${{ studio_id: studioId }} AND ${db.param(genre)} = ANY(${'genres'})`;
}

const dramas = await studioFilms(1000, 'drama').run(pool);
```

Postgres runs:

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

Values: `[1000, "drama"]`

TypeScript knows:

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

- `${{ studio_id: studioId }}` is `("studio_id" = $1)`: a filter of `films`, as the `where` of [`select()`](https://pgcast.dev/select#select) takes it, so [conditions](https://pgcast.dev/conditions) work in it too: [`gte()`](https://pgcast.dev/conditions#conditions.gte) in `{ release_year: db.conditions.gte(1990) }`.
- `${db.param(genre)}` is `$2`, in `$2 = ANY("genres")`: a parameter fits wherever SQL takes a value.
- An array is one parameter: `` ${'id'} = ANY(${db.param(ids)}) `` sends `ids` as a Postgres array by default; with `castArrayParamsToJson` on, it does not ([below](https://pgcast.dev/raw-sql#cast-a-parameter-postgres-can-t-type)).
- A part of the query that only some calls need is a nested fragment, or `[]` for nothing ([Nested fragments](https://pgcast.dev/sql-template#nested-fragments)).

A bare string between `${…}` is a name, not a value, so the type refuses it:

```ts
function filmTitled(title: string) {
  return db.sql<s.films.SQL>`SELECT * FROM ${'films'} WHERE ${'title'} = ${title}`;  // error TS2345
}
```

If it got past the type (an `any`, a `@ts-ignore`), it would still be a name, and the caller would pick it:

- A real column matches every row: `'title'` compiles to `"title" = "title"`, true wherever `title` is not null, and so do `'films.title'` and `'"title"'`. In a `DELETE`, that deletes them.
- An unknown one fails: `'The Salt Orchard'` gives `"title" = "The Salt Orchard"`, a column that does not exist (42703).
- It can be any column in scope, a system column such as `xmin` included, and, where a table goes, any relation the role can read, `'schema.table'` included: `` FROM ${'pg_catalog.pg_roles'} `` lists the database's roles.
- Any `"` in it is doubled, so it can't close its quotes (in Zapatos 6.6.1 it could, [Identifiers](https://pgcast.dev/sql-template#identifiers)), but that does not make it a value.

The keys of an interpolated object are names too. The type refuses an unknown key only in an object literal (TS2353): an object built at run time (a `Record<string, string>`, `Object.fromEntries()`, a variable with extra keys) passes it with any key.

> **Security**
>
> An interpolated object that is not a `param()`, a fragment or another of pgcast's values compiles as a Whereable, a class instance included.
>
> A built-in object throws while compiling, before any query: `` db.sql`DELETE FROM ${'ratings'} WHERE ${when}` ``, with `when` a `Date` typed `any`, no longer compiles to `DELETE FROM "ratings" WHERE TRUE`, as in Zapatos 6.6.1, which deleted every rating ([Whereable objects](https://pgcast.dev/sql-template#whereable)). Pass a date as `db.param(when)`.
>
> Any object with no keys is `TRUE`, a typed Whereable included: `const where: s.ratings.Whereable = {}`, given a `film_id` only `if (filmId)`, compiles with no error to the same `DELETE`. Check for an empty filter before a `DELETE` or an `UPDATE` ([Whereable objects](https://pgcast.dev/sql-template#whereable)). The check below catches `{}`, not a key whose value compiles to `true`, such as `film_id: db.conditions.isNotIn([])`:
>
> ```ts
> function removeRatings(filmId?: number) {
>   const where: s.ratings.Whereable = {};
>   if (filmId) where.film_id = filmId;
>   if (Object.keys(where).length === 0) throw new Error('No filter: that would delete every rating');
>   return db.deletes('ratings', where);
> }
> ```
>

## Cast a parameter Postgres can't type

Postgres gives a parameter a type from where it stands. Compared with a column, it takes the column's type; elsewhere, Postgres may infer another. Give it the type as `param()`'s second argument:

You write:

```ts
function screeningsWithin(from: Date, hours: number) {
  return db.sql<s.screenings.SQL, s.screenings.Selectable[]>`SELECT * FROM ${'screenings'}
    WHERE ${'starts_at'} >= ${db.param(from)}
      AND ${'starts_at'} < ${db.param(from, 'timestamptz')}
        + make_interval(hours => ${db.param(hours)})`;
}

const evening = await screeningsWithin(new Date('2030-03-01T18:00:00Z'), 4).run(pool);
```

Postgres runs:

```sql
SELECT *
FROM "screenings"
WHERE "starts_at" >= $1
      AND "starts_at" < CAST($2 AS "timestamptz")
        + make_interval(hours => $3)
```

Values: `["2030-03-01T18:00:00.000Z", "2030-03-01T18:00:00.000Z", 4]`

TypeScript knows:

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

- `$1`, compared with `starts_at` in `"starts_at" >= $1`, is a `timestamptz` already.
- `$2`, in `CAST($2 AS "timestamptz") + make_interval(hours => $3)`, is added to an `interval`, so without its cast Postgres reads it as one, and fails: `operator does not exist: timestamp with time zone < interval` (42883).
- `db.param(from, 'timestamptz')` writes `CAST($2 AS "timestamptz")`; `` ${db.param(from)}::timestamptz `` in the text does the same.
- The cast is the type's catalog name, in lower case (`int4`, `timestamptz`, `crew_job`), each part quoted. `'integer'` gives `CAST($1 AS "integer")`, a type that does not exist (42704), as do `'int'` and `'TIMESTAMPTZ'`.
- Name a schema as `'public.crew_job'`, which gives `"public"."crew_job"`, as `'public"."crew_job'` does. An array type keeps its bounds outside the quotes: `'int4[]'` gives `"int4"[]`.
- With [`castArrayParamsToJson`](https://pgcast.dev/runtime-config#Config.castArrayParamsToJson) on, an array parameter is sent as JSON, `CAST($1 AS "json")`, unless its cast ends with `[]` or `[n]`: `db.param(ids, 'int4[]')` gives `CAST($1 AS "int4"[])` with the array ([#36](https://github.com/AlexandreDemartin/pgcast/issues/36)). Any other string cast keeps the JSON, cast to that type, `'_int4'` included.
- With [`castObjectParamsToJson`](https://pgcast.dev/runtime-config#Config.castObjectParamsToJson) on, a plain object is sent as JSON whatever its cast, cast to the type written.

Every form of the cast: [`param()`](https://pgcast.dev/sql-template#param).

## Choose SQL at run time from your own constants

Some choices are syntax, not values: a sort direction, the column to sort on. A parameter can't stand for them, so map the caller's choice to one you wrote:

You write:

```ts
function sortedFilms(by: string, direction: string) {
  const sortable = ['title', 'release_year', 'runtime_minutes'] as const;
  const column = sortable.find(c => c === by) ?? 'title';
  return db.sql<s.films.SQL, s.films.Selectable[]>`SELECT * FROM ${'films'}
    ORDER BY ${column} ${db.raw(direction === 'desc' ? 'DESC' : 'ASC')}`;
}

const longestFirst = await sortedFilms('runtime_minutes', 'desc').run(pool);
```

Postgres runs:

```sql
SELECT *
FROM "films"
ORDER BY "runtime_minutes" DESC
```

Values: `[]`

TypeScript knows:

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

- [`db.raw()`](https://pgcast.dev/sql-template#raw) writes its string as it is. Here it only ever gets `'DESC'` or `'ASC'`, whatever the caller sent.
- The column is a name: quoted, it can't close its quotes, but the caller's string could name any column in scope, a system column such as `xmin` included, or none (42703, or 42P01 for a table outside the query). `find()` keeps one of yours, typed as the template wants.

> **Security**
>
> Whatever reaches `raw()` runs as SQL, and its type takes any string. Give it a caller's text, and the caller writes your query:
>
> ```ts
> const title = "x' OR TRUE --";  // from the caller
> db.sql`SELECT ${'title'} FROM ${'films'} WHERE ${'title'} = ${db.raw(`'${title}'`)}`;
> ```
>
> ```sql
> SELECT "title" FROM "films" WHERE "title" = 'x' OR TRUE --'
> ```
>
> That returns every film. A value is a `param()`, always: `${db.param(title)}` gives `"title" = $1`, and no film.

## Compare a column with SQL, through `self`

A fragment as a Whereable value compares its column with SQL of your own, [`db.self`](https://pgcast.dev/sql-template#self) standing for the column:

You write:

```ts
function filmsTitled(title: string) {
  return db.sql<s.films.SQL, s.films.Selectable[]>`SELECT * FROM ${'films'}
    WHERE ${{ title: db.sql`lower(${db.self}) = lower(${db.param(title)})` }}`;
}

const found = await filmsTitled('the salt orchard').run(pool);
```

Postgres runs:

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

Values: `["the salt orchard"]`

TypeScript knows:

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

- `lower(${db.self})` is `lower("title")`, and the caller's text is still a parameter, `lower($1)`.
- An equality, unlike [`ilike()`](https://pgcast.dev/conditions#conditions.ilike): a `_` or a `%` in the caller's text matches only itself.
- The same object works as the `where` of [`select()`](https://pgcast.dev/select#select) and the other shortcuts.
- Where it has no column to stand for, `self` throws "The 'self' column alias has no meaning here", so [`run()`](https://pgcast.dev/sql-template#run) rejects before any query.

## Reach the outer row, through `parent()`

A hand-written subquery can go in a shortcut's `lateral`, where [`db.parent()`](https://pgcast.dev/sql-template#parent) names a column of the outer query's table:

You write:

```ts
const studios = await db.select('studios', db.all, {
  columns: ['name'],
  lateral: {
    titles: db.sql<s.films.SQL, string | null>`SELECT
        string_agg(${'title'}, ', ' ORDER BY ${'release_year'}) AS result
      FROM ${'films'} WHERE ${'studio_id'} = ${db.parent('id')}`,
  },
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "name") ||
    jsonb_build_object($2::text, "lateral_titles".result) AS result
  FROM "studios"
  LEFT JOIN LATERAL (
    SELECT
        string_agg("title", ', ' ORDER BY "release_year") AS result
    FROM "films"
    WHERE "studio_id" = "studios"."id"
  ) AS "lateral_titles" ON true
) AS "sq_studios"
```

Values: `["name", "titles"]`

TypeScript knows:

```ts
const studios: (db.JSONOnlyColsForTable<"studios", "name"[]> & db.LateralResult<{
  titles: db.SQLFragment<string | null, never>;
}>)[]
// that is
const studios: {
  name: string;
  titles: string | null;
}[]
```

- `${db.parent('id')}` is `"studios"."id"`, the studio of each row.
- The subquery gives one column, named `result`: that is what `lateral` reads. Its type argument, `string | null`, is the key's type: `string_agg()` of no rows is `NULL`.
- With `strict` off, the key is not in the type ([`lateral`](https://pgcast.dev/select#lateral)).
- Outside a `lateral`, `parent()` throws "The 'parent' table alias has no meaning here", so [`run()`](https://pgcast.dev/sql-template#run) rejects before any query.

Joins through `lateral`, step by step: [Joins](https://pgcast.dev/lateral).

## Check the SQL before you run it

[`.compile()`](https://pgcast.dev/sql-template#compile) gives the `text` and `values` that `run()` would send, and sends nothing. Each value from outside belongs in `values`, with a `$n` in its place in `text`. A test that compiles each query with hostile input, expects it in `values` and freezes `text` keeps it that way, here for `listings()` above:

```ts
for (const cinema of [`x' OR TRUE --`, `x" --`]) {
  test(`listings() keeps ${cinema} a value`, () => {
    const { text, values } = listings(cinema).compile();
    assert.deepEqual(values, [cinema]);
    assert.equal(text.replace(/\s+/g, ' '), 'SELECT "films"."title", "screenings"."starts_at" FROM "screenings" '
      + 'JOIN "films" ON "films"."id" = "screenings"."film_id" JOIN "cinemas" ON "cinemas"."id" = "screenings"."cinema_id" '
      + 'WHERE "cinemas"."name" = $1 ORDER BY "screenings"."starts_at"');
  });
}
```

- Here with `test` from `node:test` and `assert` from `node:assert/strict`; any test runner does.
- `text` keeps the line breaks of the template: the test collapses its whitespace, so that only a change to the SQL fails it.
- A search of `text` is not enough: a name compiled from `x" --` is there as `"x"" --"`, its `"` doubled, and a lone `'` matches the query's own literals.

## See also

- [SQL template](https://pgcast.dev/sql-template): every interpolation, and what it compiles to.
- [Security](https://pgcast.dev/security): why values are parameters, and what pgcast quotes.
- [Joins](https://pgcast.dev/lateral): subqueries with `parent()`, nested.