Select

The read shortcuts: select(), selectOne() and selectExactlyOne() for rows, count(), sum(), avg(), min() and max() for one number. Each option, the SQL it adds and what it does to the type, then the types they share.

`select()`

db.select(table, where, options?, mode?, aggregate?)

Builds one SELECT of the rows of table that match where. It returns a SQLFragment: .run(pool) gives an array of the table's JSONSelectable rows, [] when none match.

table: a table of the generated schema.
where: a Whereable, an object of columns and their values or conditions, its keys in alphabetical order, joined by AND in one pair of parentheses, a condition or sql value in its own (("id" = $1 AND ("studio_id" > $2) AND "title" = $3)); a sql fragment, written as it is; or db.all, for no WHERE at all.
options: the options below, all optional.
mode and aggregate: what selectOne(), count() and the others pass; see SelectResultMode.

TSYou write Open in playground

const films
 = await db.select
('films', { studio_id
: 1000 }).run
(pool
);

SQLPostgres runs

sql

SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT to_jsonb("films".*) AS result
  FROM "films"
  WHERE ("studio_id" = $1)
) AS "sq_films"

values [1000]

::TypeScript knows

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

The SQL it writes

Each row is one jsonb value, to_jsonb("films".*), so dates and times come back as strings and bigint as a number: the JSONSelectable types say so (The catalog).
The rows are gathered into one array, coalesce(jsonb_agg(result), '[]'), around a subquery aliased "sq_<alias or table>", so ORDER BY and LIMIT apply to the rows. One query, one value back.
Inside, the clauses come in this order: SELECT [DISTINCT …] · the columns AS result · FROM the table [AS alias] · each LEFT JOIN LATERAL · WHERE · GROUP BY · HAVING · ORDER BY · LIMIT or FETCH FIRST … WITH TIES · OFFSET · each FOR … lock.
Every value is a parameter ($1, $2); every name is quoted as Postgres quotes it, any " in it doubled. Zapatos 6.6.1 let a name that held a " close its quotes; pgcast fixed this (changelog).

Options

The SQL of each row is what the built library compiles, on the docs schema; … stands for a sql fragment.

Option	SQL it adds	Effect on the type
`columns: ['title']`	`jsonb_build_object($1::text, "title")` in place of `to_jsonb("films".*)`	only those keys (`JSONOnlyColsForTable`)
`order: { by: 'title', direction: 'ASC' }`	`ORDER BY "title" ASC`	none
`limit: 2`	`LIMIT $1`	none
`offset: 2`	`OFFSET $1`	none
`withTies: true`	`FETCH FIRST $1 ROWS WITH TIES` in place of `LIMIT $1`	none
`extras: { cinema: 'name' }`	`\|\| jsonb_build_object($1::text, "name")`	a key per extra (`ExtrasResult`)
`lateral: { cast: … }`	`LEFT JOIN LATERAL (…) AS "lateral_cast" ON true`, read by `\|\| jsonb_build_object($1::text, "lateral_cast".result)`	a key per subquery (`LateralResult`)
`lateral: …`, one subquery	`"lateral_passthru".result` in place of the columns, and its `LEFT JOIN LATERAL`	the subquery's result, in place of the row
`alias: 'mentor'`	`FROM "crew" AS "mentor"`, then `"mentor"` for the table	none
`distinct: true`	`SELECT DISTINCT`	none
`distinct: 'cinema_id'`	`SELECT DISTINCT ON ("cinema_id")`	none
`groupBy: 'film_id'`	`GROUP BY "film_id"`	none
`having: …`	`HAVING sum("votes") >= $1`	none
`lock: { for: 'UPDATE' }`	`FOR UPDATE`	none

Strict

columns, extras and lateral change the type only with strictNullChecks (or strict) on. Without it, the result stays the full JSONSelectable and an extra or a lateral key is not in the type; the type refuses nothing, not even columns beside one lateral subquery, and one subquery is typed as the table's row while its own result comes back.

`columns`

columns?: readonly ColumnForTable<T>[], a ColumnsOption: only these columns, each a key of the result object, in place of the whole row. It does not rename a column: extras does.

A readonly array, ['id', 'title'] as const or a readonly ColumnForTable<T>[], gives the same SQL and the same type as a mutable one. With strictNullChecks, Zapatos 6.6.1 typed its rows as never, which any type accepted; a type that the query does not return is now refused:

const columns
 = ['id', 'title'] as const
;

async function filmCards
(queryable
: db.Queryable
) {
  const cards
: { id
: number; title
: string }[] = await db.select
('films', db.all
, { columns
 }).run
(queryable
);
  // @ts-expect-error tagline is not among the columns
  const withTaglines
: { title
: string; tagline
: string | null }[] = await db.select
('films', db.all
, { columns
 }).run
(queryable
);
  return [cards
, withTaglines
];
}

`order`, `limit`, `offset` and `withTies`

order: one { by, direction, nulls? } or an array of them, in that order. by is a column or any SQL of the table, its SQL type: a sql fragment (db.sql`array_length(${'genres'}, 1)`), db.parent(); direction is 'ASC' or 'DESC'; nulls, 'FIRST' or 'LAST'.
limit and offset: numbers, sent as parameters.
withTies: true, with limit and order: also the rows that tie with the last one on the order. Without limit it adds nothing; without order, Postgres refuses the query (42601).

TSYou write Open in playground

const page
 = await db.select
('films', db.all
, {
  columns
: ['title', 'release_year'],
  order
: [
    { by
: 'release_year', direction
: 'DESC' },
    { by
: 'title', direction
: 'ASC' },
  ],
  limit
: 2,
  offset
: 2,
}).run
(pool
);

SQLPostgres runs

sql

SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "title", $2::text, "release_year") AS result
  FROM "films"
  ORDER BY "release_year" DESC, "title" ASC
  LIMIT $3
  OFFSET $4
) AS "sq_films"

values ["title", "release_year", 2, 2]

::TypeScript knows

const page: db.JSONOnlyColsForTable<"films", ("release_year" | "title")[]>[]
// that is
const page: {
  release_year: number;
  title: string;
}[]

TSYou write Open in playground

const leads
 = await db.select
('roles', db.all
, {
  columns
: ['film_id', 'character'],
  order
: { by
: 'billing', direction
: 'ASC' },
  limit
: 1,
  withTies
: true,
}).run
(pool
);

SQLPostgres runs

sql

SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "film_id", $2::text, "character") AS result
  FROM "roles"
  ORDER BY "billing" ASC FETCH FIRST $3 ROWS WITH TIES
) AS "sq_roles"

values ["film_id", "character", 1]

::TypeScript knows

const leads: db.JSONOnlyColsForTable<"roles", ("film_id" | "character")[]>[]
// that is
const leads: {
  film_id: number;
  character: string;
}[]

Paging through a long list: Pagination.

`extras`

extras?: { [key: string]: column | SQLFragment }, an ExtrasOption: more keys on each result object. A column gives its value under another key; a sql fragment gives the type of its second type parameter, db.sql<s.cinemas.SQL, number>`…`, so number here. The keys are added to the row with ||, after columns.

TSYou write Open in playground

const cinemas
 = await db.select
('cinemas', db.all
, {
  columns
: ['id'],
  extras
: {
    cinema
: 'name',
    km
: db.sql
<s
.cinemas.SQL
, number>`${'location'} <-> point(${db.param
(0)}, ${db.param
(0)})`,
  },
}).run
(pool
);

SQLPostgres runs

sql

SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "id") ||
    jsonb_build_object($2::text, "name", $3::text, "location" <-> point($4, $5)) AS result
  FROM "cinemas"
) AS "sq_cinemas"

values ["id", "cinema", "km", 0, 0]

::TypeScript knows

const cinemas: db.ReturningTypeForTable<"cinemas", "id"[], {
  cinema: "name";
  km: db.SQLFragment<number, never>;
}>[]
// that is
const cinemas: {
  id: number;
  cinema: string;
  km: number;
}[]

`lateral`

lateral?: { [key: string]: SQLFragment } | SQLFragment, a LateralOption: other shortcuts, nested, each joined with LEFT JOIN LATERAL … ON true. In them, db.parent() names a column of this query's table.

An object: each key becomes a key of the result, holding its subquery's result: an array for select(), an object or null for selectOne(), a number for count(). The keys are joined in alphabetical order; each is one identifier, "lateral_<key>".
One subquery: its result is passed through in place of each row. Then columns and extras have nothing to add to, and the type refuses them, with strictNullChecks on.

Steps, nesting and self-joins: Joins. Since pgcast 6.6.2, a key or an alias with a " in it stays one identifier; in Zapatos 6.6.1 it could close its quotes (changelog).

`alias`

alias?: string: a name for the table in this query, needed to join a table to itself (crew and its mentor_id). It replaces the table name in the columns, in "sq_<alias>" and for the parent() of each lateral subquery, and lock.of takes it. Its steps: Join a table to itself.

`distinct`

With true: SELECT DISTINCT, on the whole result object. The select list is that one result, so Postgres refuses an order on a column with it (42P10).

TSYou write Open in playground

const cinemaIds
 = await db.select
('screenings', db.all
, {
  columns
: ['cinema_id'],
  distinct
: true,
}).run
(pool
);

SQLPostgres runs

sql

SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT DISTINCT jsonb_build_object($1::text, "cinema_id") AS result
  FROM "screenings"
) AS "sq_screenings"

values ["cinema_id"]

::TypeScript knows

const cinemaIds: db.JSONOnlyColsForTable<"screenings", "cinema_id"[]>[]
// that is
const cinemaIds: {
  cinema_id: number;
}[]

With a column, an array of columns or a sql fragment: SELECT DISTINCT ON (…), one row for each value. Give an order that starts with the same columns, or which row you get is up to Postgres.

TSYou write Open in playground

const firstShows
 = await db.select
('screenings', db.all
, {
  columns
: ['cinema_id', 'film_id', 'starts_at'],
  distinct
: 'cinema_id',
  order
: [
    { by
: 'cinema_id', direction
: 'ASC' },
    { by
: 'starts_at', direction
: 'ASC' },
  ],
}).run
(pool
);

SQLPostgres runs

sql

SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT DISTINCT ON ("cinema_id") jsonb_build_object($1::text, "cinema_id", $2::text, "film_id", $3::text, "starts_at") AS result
  FROM "screenings"
  ORDER BY "cinema_id" ASC, "starts_at" ASC
) AS "sq_screenings"

values ["cinema_id", "film_id", "starts_at"]

::TypeScript knows

const firstShows: db.JSONOnlyColsForTable<"screenings", ("film_id" | "cinema_id" | "starts_at")[]>[]
// that is
const firstShows: {
  film_id: number;
  cinema_id: number;
  starts_at: db.TimestampTzString;
}[]

`groupBy` and `having`

groupBy: a column, an array of columns (GROUP BY "film_id", "stars") or a sql fragment.
having: a Whereable, in parentheses as in where, or a sql fragment.

Postgres returns only the grouped columns, those a grouped primary key determines, and aggregates, and refuses any other (42803): the whole row, without columns, runs when groupBy holds the primary key.

TSYou write Open in playground

const popular
 = await db.select
('ratings', db.all
, {
  columns
: ['film_id'],
  extras
: { votes
: db.sql
<s
.ratings.SQL
, number>`sum(${'votes'})` },
  groupBy
: 'film_id',
  having
: db.sql
<s
.ratings.SQL
>`sum(${'votes'}) >= ${db.param
(10)}`,
}).run
(pool
);

SQLPostgres runs

sql

SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "film_id") ||
    jsonb_build_object($2::text, sum("votes")) AS result
  FROM "ratings"
  GROUP BY "film_id"
  HAVING sum("votes") >= $3
) AS "sq_ratings"

values ["film_id", "votes", 10]

::TypeScript knows

const popular: db.ReturningTypeForTable<"ratings", "film_id"[], {
  votes: db.SQLFragment<number, never>;
}>[]
// that is
const popular: {
  film_id: number;
  votes: number;
}[]

`lock`

lock?: SelectLockingOptions | SelectLockingOptions[]: a locking clause each, in order, written after everything else.

Key	Values	SQL
`for`	`'UPDATE'`, `'NO KEY UPDATE'`, `'SHARE'`, `'KEY SHARE'`	`FOR UPDATE`
`of`, optional	a table name, or the `alias`; or an array of them	`OF "screenings"`
`wait`, optional	`'NOWAIT'`, `'SKIP LOCKED'`	`NOWAIT`

Postgres refuses a lock with DISTINCT, GROUP BY or an aggregate (0A000).

TSYou write Open in playground

const screening
 = await db.selectOne
('screenings', { id
: 1000 }, {
  lock
: { for
: 'UPDATE', wait
: 'NOWAIT' },
}).run
(pool
);

SQLPostgres runs

sql

SELECT to_jsonb("screenings".*) AS result
FROM "screenings"
WHERE ("id" = $1)
LIMIT $2
FOR UPDATE NOWAIT

values [1000, 1]

::TypeScript knows

const screening: screenings.JSONSelectable | undefined
// that is
const screening: {
  cinema_id: number;
  film_id: number;
  id: number;
  starts_at: db.TimestampTzString;
  subtitled: boolean | null;
} | undefined

Errors

select() throws these as it builds the query, before any is sent:

Message	When	Since
`Direction must be ASC/DESC, not '…'`	an `order` with another `direction`	Zapatos
`Nulls must be FIRST/LAST/undefined, not '…'`	an `order` with another `nulls`	Zapatos
`Lock strength must be UPDATE/NO KEY UPDATE/SHARE/KEY SHARE, not '…'`	a `lock` with another `for`	pgcast 6.6.2
`Lock wait must be NOWAIT/SKIP LOCKED/undefined, not '…'`	a `lock` with another `wait`	pgcast 6.6.2
`Aggregate must be a function name (e.g. sum, or my_schema.my_agg), not '…'`	an `aggregate` that is not one, in `SelectResultMode.Numeric`	pgcast 6.6.2

The type refuses a bad direction, nulls or lock, not a bad aggregate, a string. Zapatos 6.6.1 wrote a bad lock or aggregate into the SQL as it was, where it could run a second statement. A value that has no meaning in where or having (db.Default, db.self, db.raw()) throws as the query compiles: Conditions.

`selectOne()`

db.selectOne(table, where, options?)

As select(), with LIMIT $1, its value 1, and no jsonb_agg: .run(pool) gives the first row, or undefined when none matches. Any limit you pass is replaced by 1; offset and the other options are kept. In a lateral, its key holds the row or null.

With withTies and order, it writes FETCH FIRST $1 ROWS WITH TIES, here and in selectExactlyOne(). .run() then gives one of the tied rows; in a lateral, the parent row comes back once per tie.

TSYou write Open in playground

const latest
 = await db.selectOne
('films', { studio_id
: 1000 }, {
  order
: { by
: 'release_year', direction
: 'DESC' },
}).run
(pool
);

SQLPostgres runs

sql

SELECT to_jsonb("films".*) AS result
FROM "films"
WHERE ("studio_id" = $1)
ORDER BY "release_year" DESC
LIMIT $2

values [1000, 1]

::TypeScript knows

const latest: films.JSONSelectable | undefined
// that is
const latest: {
  genres: string[];
  id: number;
  release_year: number;
  runtime_minutes: number;
  studio_id: number;
  tagline: string | null;
  title: string;
} | undefined

`selectExactlyOne()`

db.selectExactlyOne(table, where, options?)

As selectOne(), but its type has no undefined: when no row matches, .run() rejects with a NotExactlyOneError. It checks for at least one row, not exactly one: with LIMIT $2, its value 1, it takes the first. In a lateral, a missing row gives null and no error, though the type says otherwise.

TSYou write Open in playground

const studio
 = await db.selectExactlyOne
('studios', { id
: 1000 }).run
(pool
);

SQLPostgres runs

sql

SELECT to_jsonb("studios".*) AS result
FROM "studios"
WHERE ("id" = $1)
LIMIT $2

values [1000, 1]

::TypeScript knows

const studio: studios.JSONSelectable
// that is
const studio: {
  id: number;
  name: string;
}

With selectExactlyOneChecksForSecondRow, the query run on its own asks for two rows, LIMIT $2 with 2, and .run() rejects on a second row too. Nested in another query, in a lateral or a sql template, it keeps LIMIT 1, option or not: a second row there would join the parent row twice.

`NotExactlyOneError`

class NotExactlyOneError extends Error {
  query: SQLFragment;
}

What selectExactlyOne(), the mode ExactlyOne of select(), rejects with when no row matches. Its name is 'NotExactlyOneError', its message "One result expected but none returned (hint: check .query.compile() on this Error)"; with selectExactlyOneChecksForSecondRow, a second row gives "One result expected but more returned (hint: check .query.compile() on this Error)".

Its query is the SQLFragment that ran: err.query.compile() gives its SQL and values. err instanceof db.NotExactlyOneError recognises it; catching it: Tell a missing row from the others.

`count()`

db.count(table, where, options?)

SELECT count(*) AS result, with no jsonb_agg: .run(pool) gives a number, 0 when no row matches the where. The bigint that Postgres returns goes through Number().

TSYou write Open in playground

const roleCount
 = await db.count
('roles', { actor_id
: 1000 }).run
(pool
);

SQLPostgres runs

sql

SELECT count(*) AS result
FROM "roles"
WHERE ("actor_id" = $1)

values [1000]

::TypeScript knows

const roleCount: number

count() and the four below take the options of select(), with these differences:

Option	SQL it adds	Effect on the type
`columns: ['subtitled']`	`count("subtitled")` in place of `count(*)`: the rows where it is not `NULL`; one column, as several (or none, `[]`) throw	none
`distinct: true`	`SELECT DISTINCT count(*)`, on the one result: not `count(DISTINCT …)`, which needs a `sql` template	none
`groupBy: 'film_id'`	`GROUP BY "film_id"`; `.run()` gives the first group's value only, and with no group rejects with a `TypeError`	none
`extras`	nothing	none
`lateral: { … }`	its `LEFT JOIN LATERAL`s, whose results are not read; a single subquery throws, as there is no row to pass it through to	none
`having`, `limit`, `offset`	as in `select()`; when they leave no row (a `having` that keeps none, `limit: 0`, `offset: 1`), `.run()` rejects with a `TypeError`	none

count() and the four below throw these while compiling, so .run() rejects with a plain Error, not a DatabaseError, before any query, and .compile() throws; so does a query that nests one:

Message	When	Since
`…() needs one column to aggregate: pass columns: ['column']`	`sum()`, `avg()`, `min()` or `max()` without `columns`	pgcast 6.7.0
`…() takes one column in columns, not …`	`columns` with several columns, or none (`[]`)	pgcast 6.7.0
`…() cannot pass a lateral query through: nest it in the lateral of a select() instead`	a single subquery as `lateral`	pgcast 6.7.0

Zapatos 6.6.1 sent the SQL, which Postgres refused: sum(*) and count("id", "mood") (42883), count() (42809), SELECT AS result (42601). The type is that of 6.6.1, which lets these calls through, a single subquery as lateral when there is no columns nor extras.

Two calls that ran under 6.6.1 now reject too:

An aggregate of your own that takes another number of arguments under a built-in name. After CREATE AGGREGATE max(int4, int4) (…), db.max('ratings', db.all, { columns: ['stars', 'votes'] }) sent max("stars", "votes"). db.select('ratings', db.all, { columns: ['stars', 'votes'] }, db.SelectResultMode.Numeric, 'max') sends the same SQL.
A refused aggregate marked noop, which resolved to its noopResult: run() compiles the query before it reads noop.

With columns, it counts two of the four screenings: the other two have subtitled NULL.

TSYou write Open in playground

const subtitledKnown
 = await db.count
('screenings', db.all
, {
  columns
: ['subtitled'],
}).run
(pool
);

SQLPostgres runs

sql

SELECT count("subtitled") AS result
FROM "screenings"

values []

::TypeScript knows

const subtitledKnown: number

count(DISTINCT …) is a sql template. Postgres's count() returns a bigint, which pg gives as a string ('3'): ::int makes it a number, here 3 cinemas.

TSYou write Open in playground

const distinctCinemas
 = await db.sql
<s
.screenings.SQL
, { cinemas
: number }[]>`SELECT count(DISTINCT ${'cinema_id'})::int AS cinemas
  FROM ${'screenings'}`.run
(pool
);

SQLPostgres runs

sql

SELECT count(DISTINCT "cinema_id")::int AS cinemas
FROM "screenings"

values []

::TypeScript knows

const distinctCinemas: {
  cinemas: number;
}[]

where, alias, withTies and lock add what they add to select(). So does order, by a groupBy column or a sql fragment (db.sql`count(*)`): by another column, Postgres refuses the query (42803).

.run() reads the first row of the result. When the query returns none, it rejects with TypeError: Cannot read properties of undefined (reading 'result'); so do sum(), avg(), min() and max().

`sum()`

db.sum(table, where, options?)

SELECT sum("column") AS result, a number; otherwise as count(). Give one column in columns: without it, .run() rejects before any query, where Zapatos 6.6.1 sent sum(*), which Postgres refuses (42883). The type lets that call through, as in 6.6.1. When no row matches the where, Postgres returns NULL, and Number(null) makes it 0.

TSYou write Open in playground

const minutes
 = await db.sum
('films', { studio_id
: 1000 }, {
  columns
: ['runtime_minutes'],
}).run
(pool
);

SQLPostgres runs

sql

SELECT sum("runtime_minutes") AS result
FROM "films"
WHERE ("studio_id" = $1)

values [1000]

::TypeScript knows

const minutes: number

`avg()`

db.avg(table, where, options?)

SELECT avg("column") AS result: the mean, through Number(), so 0 when no row matches. Otherwise as sum().

TSYou write Open in playground

const meanRuntime
 = await db.avg
('films', db.all
, {
  columns
: ['runtime_minutes'],
}).run
(pool
);

SQLPostgres runs

sql

SELECT avg("runtime_minutes") AS result
FROM "films"

values []

::TypeScript knows

const meanRuntime: number

`min()`

db.min(table, where, options?)

SELECT min("column") AS result, through Number(), so 0 when no row matches. The type says number whatever the column:

a date or time column (date, timestamp, timestamptz) gives its milliseconds since 1970, as node-postgres reads it as a Date;
text or an enum gives NaN, unless the text reads as a number: '9' gives 9.

Otherwise as sum().

TSYou write Open in playground

const earliest
 = await db.min
('films', db.all
, {
  columns
: ['release_year'],
}).run
(pool
);

SQLPostgres runs

sql

SELECT min("release_year") AS result
FROM "films"

values []

::TypeScript knows

const earliest: number

`max()`

db.max(table, where, options?)

SELECT max("column") AS result; as min(): 0 when no row matches, a date's milliseconds, and NaN on text or an enum unless the text reads as a number.

TSYou write Open in playground

const longest
 = await db.max
('films', { studio_id
: 1000 }, {
  columns
: ['runtime_minutes'],
}).run
(pool
);

SQLPostgres runs

sql

SELECT max("runtime_minutes") AS result
FROM "films"
WHERE ("studio_id" = $1)

values [1000]

::TypeScript knows

const longest: number

Types

What the signatures above are made of, from zapatos/db. T is a table, C the columns, E the extras, L the lateral and A the alias.

`SelectResultMode`

enum SelectResultMode { Many, One, ExactlyOne, Numeric }

The fourth argument of select(): an array of rows (select()), a row or undefined (selectOne()), a row or an error (selectExactlyOne()), a number (count() and the others).

With Numeric, the fifth argument names the aggregate, 'count' by default; since pgcast 6.6.2 it must be a function name, plain, quoted or U& quoted, in up to three dotted parts (stddev_pop, my_schema."MyAgg"). Any aggregate that returns a number works:

TSYou write Open in playground

const spread
 = await db.select
('films', db.all
, {
  columns
: ['runtime_minutes'],
}, db.SelectResultMode
.Numeric
, 'stddev_pop').run
(pool
);

SQLPostgres runs

sql

SELECT stddev_pop("runtime_minutes") AS result
FROM "films"

values []

::TypeScript knows

const spread: number

`SelectOptionsForTable`

SelectOptionsForTable<T, C, L, E, A>: the options object of select(), selectOne(), selectExactlyOne() and the aggregates, every key optional, as in Options.

`SelectLockingOptions`

interface SelectLockingOptions<A extends string> {
  for: 'UPDATE' | 'NO KEY UPDATE' | 'SHARE' | 'KEY SHARE';
  of?: Unprefixed<Table> | A | (Unprefixed<Table> | A)[];
  wait?: 'NOWAIT' | 'SKIP LOCKED';
}

One locking clause of lock. of takes a table name without its schema, or the query's alias (A).

`ColumnsOption`

ColumnsOption<T> = readonly ColumnForTable<T>[] | undefined: the type of columns, and of returning in insert() and the other writes. A readonly array gives the same type as a mutable one (columns).

`ExtrasOption`

ExtrasOption<T> = SQLFragmentOrColumnMap<T> | undefined: the type of extras, here and in the writes.

`LateralOption`

LateralOption<C, E>: the type of lateral. An object of subqueries (SQLFragmentMap); or one subquery, only when neither columns nor extras is given.

`SQLFragmentMap`

interface SQLFragmentMap { [k: string]: SQLFragment<any> }: the object form of lateral, a key per subquery.

`SQLFragmentOrColumnMap`

interface SQLFragmentOrColumnMap<T> { [k: string]: SQLFragment<any> | ColumnForTable<T> }: the form of extras, a key per column or fragment.

`JSONOnlyColsForTable`

JSONOnlyColsForTable<T, C> = Pick<JSONSelectableForTable<T>, C[number]>: a row with only the columns (or returning) given. It names that row in a signature:

type FilmCard
 = db.JSONOnlyColsForTable
<'films', ['id', 'title']>;

function filmCardsOf
(queryable
: db.Queryable
, studio_id
: number): Promise
<FilmCard
[]> {
  return db.select
('films', { studio_id
 }, { columns
: ['id', 'title'] }).run
(queryable
);
}

C may be a readonly tuple, as columns may (columns).

`ExtrasResult`

ExtrasResult<T, E>: the keys that extras adds, each typed as its column in JSONSelectable, or as its fragment's result.

`LateralResult`

LateralResult<L> = { [K in keyof L]: RunResultForSQLFragment<L[K]> }: the keys that a lateral object adds, each typed as its subquery's result, with undefined turned into null (RunResultForSQLFragment).

`FullSelectReturnTypeForTable`

FullSelectReturnTypeForTable<T, C, L, E, M>: what .run() gives for a mode M: the row (the JSONSelectable, narrowed by columns, with extras and lateral; or the passed-through subquery's result), as an array, as is, or with undefined; or a number. The row of the writes is ReturningTypeForTable.

`SelectSignatures`

The call signature of select(): table, where, options?, mode? (by default Many) and aggregate?, returning SQLFragment<FullSelectReturnTypeForTable<…>>.

`SelectOneSignatures`

The call signature of selectOne(): table, where, options?, with the mode One.

`SelectExactlyOneSignatures`

The call signature of selectExactlyOne(): table, where, options?, with the mode ExactlyOne.

`NumericAggregateSignatures`

The call signature of count(), sum(), avg(), min() and max(): table, where, options?, returning SQLFragment<number>.

Select ​

select() ​

The SQL it writes ​

Options ​

columns ​

order, limit, offset and withTies ​

extras ​

lateral ​

alias ​

distinct ​

groupBy and having ​

lock ​

Errors ​

selectOne() ​

selectExactlyOne() ​

NotExactlyOneError ​

count() ​

sum() ​

avg() ​

min() ​

max() ​

Types ​

SelectResultMode ​

SelectOptionsForTable ​

SelectLockingOptions ​

ColumnsOption ​

ExtrasOption ​

LateralOption ​

SQLFragmentMap ​

SQLFragmentOrColumnMap ​

JSONOnlyColsForTable ​

ExtrasResult ​

LateralResult ​

FullSelectReturnTypeForTable ​

SelectSignatures ​

SelectOneSignatures ​

SelectExactlyOneSignatures ​

NumericAggregateSignatures ​

Select

`select()`

The SQL it writes

Options

`columns`

`order`, `limit`, `offset` and `withTies`

`extras`

`lateral`

`alias`

`distinct`

`groupBy` and `having`

`lock`

Errors

`selectOne()`

`selectExactlyOne()`

`NotExactlyOneError`

`count()`

`sum()`

`avg()`

`min()`

`max()`

Types

`SelectResultMode`

`SelectOptionsForTable`

`SelectLockingOptions`

`ColumnsOption`

`ExtrasOption`

`LateralOption`

`SQLFragmentMap`

`SQLFragmentOrColumnMap`

`JSONOnlyColsForTable`

`ExtrasResult`

`LateralResult`

`FullSelectReturnTypeForTable`

`SelectSignatures`

`SelectOneSignatures`

`SelectExactlyOneSignatures`

`NumericAggregateSignatures`