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; every interpolation is in SQL template.

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:

TSYou write Open in playground

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
);

SQLPostgres 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

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.

db.sql
<s
.films.SQL
>`SELECT * FROM ${'screenings'}`;Argument of type '"screenings"' is not assignable to parameter of type 'SQL'.

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:

TSYou write Open in playground

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
);

SQLPostgres 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

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

db.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(), or in a Whereable object, which makes a parameter of each value:

TSYou write Open in playground

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
);

SQLPostgres runs

sql

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

values [1000, "drama"]

::TypeScript knows

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() takes it, so conditions work in it too: 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).
A part of the query that only some calls need is a nested fragment, or [] for nothing (Nested fragments).

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

function filmTitled
(title
: string) {
  return db.sql
<s
.films.SQL
>`SELECT * FROM ${'films'} WHERE ${'title'} = ${title}`;Argument of type 'string' is not assignable to parameter of type 'SQL'.
}

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), 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). 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). The check below catches {}, not a key whose value compiles to true, such as film_id: db.conditions.isNotIn([]):

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:

TSYou write Open in playground

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
);

SQLPostgres 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

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 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). Any other string cast keeps the JSON, cast to that type, '_int4' included.
With castObjectParamsToJson on, a plain object is sent as JSON whatever its cast, cast to the type written.

Every form of the cast: 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:

TSYou write Open in playground

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
);

SQLPostgres runs

sql

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

values []

::TypeScript knows

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() 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:

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 standing for the column:

TSYou write Open in playground

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
);

SQLPostgres runs

sql

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

values ["the salt orchard"]

::TypeScript knows

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(): a _ or a % in the caller's text matches only itself.
The same object works as the where of 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() rejects before any query.

Reach the outer row, through `parent()`

A hand-written subquery can go in a shortcut's lateral, where db.parent() names a column of the outer query's table:

TSYou write Open in playground

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
);

SQLPostgres 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

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).
Outside a lateral, parent() throws "The 'parent' table alias has no meaning here", so run() rejects before any query.

Joins through lateral, step by step: Joins.

Check the SQL before you run it

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

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.

Raw SQL ​

Write the query, its names interpolated ​

Pass each value as a parameter ​

Cast a parameter Postgres can't type ​

Choose SQL at run time from your own constants ​

Compare a column with SQL, through self ​

Reach the outer row, through parent() ​

Check the SQL before you run it ​

See also ​

Raw SQL

Write the query, its names interpolated

Pass each value as a parameter

Cast a parameter Postgres can't type

Choose SQL at run time from your own constants

Compare a column with SQL, through `self`

Reach the outer row, through `parent()`

Check the SQL before you run it

See also