SQL Statements

To run SQL that queries do not cover, write the statement with the db.$query template and await it:

const updatedCount = await db.$query`
  UPDATE ${db.form} SET published = true
  WHERE id IN (SELECT form_id FROM ${db.field} GROUP BY form_id HAVING count(*) >= ${3})
`
// updatedCount: number

It resolves to the number of rows that PostgreSQL reports for the statement, such as the updated rows. To require exactly one such row, such as when updating a record by its key, add take: it rejects with NotFoundError without the row, unless you add optional.

Interpolated values become query parameters, sql fragments stay SQL, a table of db, such as db.form, becomes its name in the PostgreSQL schema of db, and another query becomes a subquery. The statement runs in the current transaction, like any query.

Rows

To get the rows that the statement returns, pass the types of their columns to select:

import * as t from "kern-orm/types"

const forms = await db.$query`
  INSERT INTO ${db.form} (id, name) VALUES (${formId}, ${name})
  ON CONFLICT (id) DO UPDATE SET name = excluded.name
  RETURNING id, name, (xmax = 0) AS inserted
`.select({
  id: t.uuid,
  name: t.text,
  inserted: t.boolean,
})
// forms: { id: string; name: string; inserted: boolean }[]

select reads each field from the column of its name, whatever their order. Give a column that can be NULL a nullable type.

To get one row, add take, and optional or nullable when the row may be missing, as for one row of a query. take adds no LIMIT to the statement, so it rejects several rows.

kern-orm checks the rows after the statement runs, so a statement that rejects in take or while decoding its rows has already made its change. To undo the change with the error, run the statement in a transaction.

To get the values of a single column, pass its type to pluck, or to get for one value:

const total = await db.$query`SELECT count(*) FROM ${db.form}`.get(t.integer)
// total: number

Queries in statements

To compute what queries cannot, such as the median number of fields of published forms, interpolate a query into the statement. It becomes a subquery in parentheses; name its columns after its alias, in the order of the fields of the query, leaving out literals:

const median = await db.$query`
  SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY field_count)
  FROM ${db.form.where({ published: true }).select(form => ({
    fieldCount: form.fields.count(),
  }))} AS form (field_count)
`.get(t.doublePrecision.nullable())
// median: number | null

A query of one value, such as of count, becomes its value, as in a condition. Start the query from the same Db as the statement.

Names known at runtime

To name a column or another object known only at runtime, interpolate identifier with its name:

import { identifier } from "kern-orm"

await db.$query`UPDATE ${db.form} SET ${identifier(column)} = ${value}`

identifier does not add a PostgreSQL schema to the name. To name an object in a schema, pass the schema before the name:

await db.$query`ANALYZE ${identifier(schemaName, tableName)}`

Scripts

To run several statements at once, such as the SQL of a migration file, pass them to db.$script:

import { readFile } from "node:fs/promises"

await db.$script(await readFile("migrations/001-forms.sql", "utf8"))

A script has no parameters, so its template interpolates only tables of db, names from identifier, and queries and sql fragments without parameters:

await db.$script`
  CREATE INDEX form_name ON ${db.form} (name);
  ANALYZE ${db.form};
`

The script runs in the current transaction; outside one, PostgreSQL applies all its statements or none. Leave transactions to kern-orm: do not write BEGIN, COMMIT, or ROLLBACK in a script.

In code that has the database but no Db, run statements with database.query and scripts with database.script; see SQL Queries and Scripts.