Selecting

Fields

List the columns you need in select:

const forms = await db.form.select("id", "name")

A record has no fields without select, so a result never carries data that the code did not ask for.

To select all columns of the table, call select("*").

To rename columns or select results of operators, functions, and sql fragments, pass a callback that returns them under keys of your choice:

import { sql } from "kern-orm"
import * as t from "kern-orm/types"

const forms = await db.form.select(form => ({
  id: form.id,
  title: form.name,
  isDraft: form.name.ilike("draft%"),
  slug: sql(t.text)`regexp_replace(lower(${form.name}), '[^a-z0-9]+', '-', 'g')`,
}))
// forms: { id: string; title: string; isDraft: boolean; slug: string }[]

A field keeps the type of its expression, so a column or a fragment of sql(type) returns values of its type. A selected fragment needs a type, such as t.text of slug above.

Each select adds new fields to those selected before:

const forms = await db.form.select("id").select(form => ({
  title: form.name,
}))
// forms: { id: string; title: string }[]

Selecting an existing key replaces its expression and type; the last selection wins:

const forms = await db.form.select("id", "name").select(form => ({
  name: form.name.length(),
}))
// forms: { id: string; name: number }[]

select("*") also replaces fields under column keys and keeps the other fields.

clearSelect removes all selected fields and keeps the other clauses:

const publishedForms = db.form.where({ published: true }).select("id", "name")

const names = await publishedForms.clearSelect().select("name")
// names: { name: string }[]

The keys of selected fields name only the fields of records: where keeps filtering by the columns of the table.

To select the related records of each row, such as the fields of each form, see Related Records.

Values from code

To fill a field from code instead of reading it from the database, such as to skip a column that this call does not need, pass the value to literal:

import { literal } from "kern-orm"

const fields = await db.field.select(field => ({
  id: field.id,
  hint: withHints ? field.hint : literal(null),
  form: withForms ? field.form.select("name") : literal(null),
}))
// fields: { id: string; hint: string | null; form: { name: string } | null }[]

The value stays out of the SQL statement, so the database does not read the skipped column or run the skipped query. Every record gets that value itself rather than a copy, whatever it is, such as an object or a Date.

literal works as a field in the callback of select, including of nested rows and mutations, and in $select. To use a value in a condition or another expression, pass it directly or through param.

One row

To get one row instead of an array, call take:

const form = await db.form.where({ name: "Survey" }).select("id", "published").take()
// form: { id: string; published: boolean }

take can come before select: take().select("id") also returns one record. Call limit and offset before take.

Without a row, take rejects with NotFoundError, unless the query is of a singleton table, which resolves to undefined; when the row may be missing, add optional to get undefined instead:

const form = await db.form.where({ name: "Survey" }).select("id").take().optional()
// form: { id: string } | undefined

To get null instead, add nullable:

const form = await db.form.where({ name: "Survey" }).select("id").take().nullable()
// form: { id: string } | null

required makes such a query reject without a row again.

Record by key

To get a record by its primary key, pass the value of the key to find:

const form = await db.form.find(formId).select("name", "published")
// form: { name: string; published: boolean }

For a primary key of several columns, pass the value of each column in the order of the key, such as db.membership.find(userId, groupId).

To get a record by a unique key, pass the values of its columns to findBy:

const user = await db.user.findBy({ email }).select("id")
// user: { id: string }

find(formId) filters like where({ id: formId }) and returns one row like take(), and findBy({ email }) works the same way. Without the record, they reject with NotFoundError unless you add optional or nullable, or the query is of a singleton table, as for one row. Call them on any query, such as form.fields.find(fieldId) of a relation, and pass them what where takes, such as a column of the parent row in another nested query.

Values

To get the values of a column or an expression instead of records, call pluck; it replaces the selected fields:

const names = await db.form.where({ published: true }).pluck("name")
// names: string[]

const draftFlags = await db.form.pluck(form => form.name.ilike("draft%"))
// draftFlags: boolean[]

The callback also receives the relations of the table, as do those of get and of aggregates.

Distinct rows

To remove duplicate rows, such as to list the labels of the fields once each, call distinct:

const labels = await db.field.pluck("label").distinct()
// labels: string[]

distinct compares the selected fields, so select the ones that tell the rows apart:

const placements = await db.field.select("formId", "position").distinct()

To sort the rows, sort by the selected fields, since duplicates can differ in any other column:

const labels = await db.field.pluck("label").distinct().orderBy("label")

A query with distinct cannot select related records.

To count the distinct rows, such as for the number of the rows of all pages, call count after distinct:

const total = await db.field.select("formId", "position").distinct().count()
// total: number

The other aggregates and exists read the distinct rows too. An aggregate of a column takes only a selected column, since duplicates can differ in any other column:

const labels = await db.field.pluck("label").distinct().orderBy("label").stringAgg("label", ", ")
// labels: string | null

To count the distinct values of one column, call countDistinct without distinct.

First row of each group

To get one row of each group of rows, such as the latest submission of each user, list the columns of the groups in distinctOn, and sort the query by them first and then by the order that chooses the row:

const latest = await db.submission
  .distinctOn("userId")
  .orderBy("userId", { createdAt: "desc" })
  .select("id", "userId", "createdAt")

Each group gives its first row in the order of the query, and the rows keep that order. Without an order, each group gives any of its rows.

To use an expression as a key, such as the day of a submission, select it and take it from the second parameter of the callbacks of distinctOn and orderBy, as for grouping:

import { sql } from "kern-orm"
import * as t from "kern-orm/types"

const firstOfDay = await db.submission
  .select(submission => ({
    day: sql(t.date)`${submission.createdAt}::date`,
    id: submission.id,
  }))
  .distinctOn((submission, selected) => selected.day)
  .orderBy((submission, selected) => [selected.day, submission.createdAt])

To count the groups, such as the users who submitted, call count after distinctOn:

const latest = db.submission.distinctOn("userId").orderBy("userId", { createdAt: "desc" })

const users = await latest.count()
// users: number

The other aggregates and exists read the first rows too, in the order of the query, such as latest.min("createdAt") for the latest submission of the user who submitted least recently.

One value

To get one value, such as the ID of a form, call get with a column or an expression:

const id = await db.form.where({ name: "Survey" }).get("id")
// id: string

const isDraft = await db.form.where({ name: "Survey" }).get(form => form.published.eq(false))
// isDraft: boolean

get("id") is a shorthand for pluck("id").take().

Without a row, get rejects with NotFoundError; when the row may be missing, add optional to get undefined instead:

const id = await db.form.where({ name: "Survey" }).get("id").optional()
// id: string | undefined

A NULL value is null, so it differs from a missing row.

To get another value instead of NULL, pass it to coalesce:

const hint = await db.field.find(fieldId).get("hint").coalesce("")
// hint: string

coalesce replaces only NULL: without a row, the query still rejects or gives undefined. After pluck, it replaces each NULL value. To replace a missing row too, or NULL of a column of each record, use the function coalesce.

Aggregates

To count the rows of a query, such as the published forms, call count:

const publishedCount = await db.form.where({ published: true }).count()
// publishedCount: number

To compute another aggregate of a column or an expression over the rows of a query, such as the last position of the fields of a form, call its method with the column or a callback, as for get:

const lastPosition = await db.field.where({ formId }).max("position")
// lastPosition: number | null

• count of a column counts its values other than NULL, and countDistinct the distinct ones.
• min and max give the smallest and the largest value.
• sum and avg give the sum and the average.
• every and some tell whether all or any of the Boolean values are true.
• stringAgg joins the values with the delimiter that it takes after the column, in the order of the query, set by orderBy before or after it.

To average a bigint column, pass it in a fragment of a type that can represent fractional values, such as avg(file => sql(t.doublePrecision)`${file.size}`), since its average can have a fractional part.

An aggregate skips NULL values and gives null without other values, while the counts give 0. To get another value instead, such as 0 for a form without fields, pass it to coalesce:

const lastPosition = await db.field.where({ formId }).max("position").coalesce(0)
// lastPosition: number

coalesce also takes an expression of the values of the aggregate, such as a column of the parent row of related records, or a query of one such value.

An aggregate keeps the filters of the query and ignores its selected fields, apart from those that distinct compares. With limit and offset, it aggregates the rows of the page, which the order of the query chooses.

Several aggregates at once

To read several aggregates in one statement, such as the number of the fields of a form and the position of the last one, select the functions of the same names as the aggregate methods:

import { count, max } from "kern-orm"

const stats = await db.field
  .where({ formId })
  .select(field => ({
    total: count(),
    lastPosition: max(field.position),
  }))
  .take()
// stats: { total: number; lastPosition: number | null }

count, countDistinct, min, max, sum, avg, every, and some take an expression or a query of one value, such as a column or an aggregate of related records, and give the values that the methods give. count() without an argument counts the rows.

To aggregate only some of the rows, such as to count the published forms beside the total, pass a condition as filter:

const stats = await db.form
  .select(form => ({
    total: count(),
    published: count({ filter: form.published }),
  }))
  .take()
// stats: { total: number; published: number }

To join text values, select stringAgg with a delimiter, and pass orderBy with what the callback of orderBy returns to join them in that order:

import { count, stringAgg } from "kern-orm"

const summary = await db.field
  .where({ formId })
  .select(field => ({
    total: count(),
    labels: stringAgg(field.label, ", ", { orderBy: field.position }),
  }))
  .take()
// summary: { total: number; labels: string | null }

Without orderBy, PostgreSQL joins the values in any order.

Each returns an expression with the operators of its type, so pass it to a function or call an operator on it, such as coalesce(sum(field.position), 0) or count().gt(1).

Such a query returns one row, so call take to get the record instead of an array of it. limit and offset apply to this row rather than to the rows that it aggregates, so limit(10) beside count() counts every row. To aggregate the rows of a page, call the method, such as limit(10).count().

To compute them for each group of rows, such as the number of the fields of each form, see Grouping.

Whether rows exist

To check whether a query has rows, such as whether any form is published, call exists:

const hasPublished = await db.form.where({ published: true }).exists()
// hasPublished: boolean

exists keeps the filters of the query and ignores its selected fields, apart from those that distinct compares; with limit and offset, it checks the rows of the page.

To filter records by whether related records exist, see Filtering.

Several queries at once

To load the results of independent queries in one round trip, such as the forms of a page and the number of users, pass the queries to db.$select under keys of your choice:

const page = await db.$select({
  forms: db.form.where({ published: true }).select("id", "name"),
  userCount: db.user.count(),
})
// page: { forms: { id: string; name: string }[]; userCount: number }

The queries run as one SQL statement, and each field gets the result that its query gives when awaited, such as an array of records or one value. A field can also hold an expression, such as a fragment of sql(type).

When a query of one row, such as one of find, finds no row, $select rejects with NotFoundError, whose path names its field; add optional to the query to get undefined in its field instead, or nullable to a query of records to get null.

Results converted in code

To compute what SQL cannot, such as a slug by a function of your application, pass each record or value to map:

const forms = await db.form.select("id", "name").map(form => ({
  ...form,
  slug: slugify(form.name),
}))
// forms: { id: string; name: string; slug: string }[]

On a query of one row, map converts that row, and a missing row stays missing without a call:

const slug = await db.form.where({ name: "Survey" }).take().get("name").optional().map(slugify)
// slug: string | undefined

A NULL value is a value of its row, so handle null in the callback, or replace it with coalesce before map.

SQL does not see what map converts, so keep choosing the rows after it, such as with where, orderBy, limit, and take:

const formViews = db.form.select("id", "name").map(toView)

const page = await formViews.where({ published: true }).orderBy("name").limit(20)

Call the methods that read the fields, such as select, get, and count, before map. A query with map cannot be used in a condition, an operator, or sql.

To convert the whole result at once, such as to return the rows of a page with its cursor, pass it to transform, which receives what the query resolves to, also null or undefined of a missing row:

const page = await db.form
  .orderBy("id")
  .limit(20)
  .select("id", "name")
  .transform(forms => ({
    forms,
    cursor: forms.at(-1)?.id,
  }))
// page: { forms: { id: string; name: string }[]; cursor: string | undefined }

Call transform after the other methods of a query, since they would change what it receives. After it, the query can only be executed, nested in a selection, or converted with transform again.

Both also convert the field of a nested query, such as of related records:

const forms = await db.form.select(form => ({
  name: form.name,
  labels: form.fields.pluck("label").transform(labels => labels.join(", ")),
  nextPosition: form.fields
    .max("position")
    .coalesce(0)
    .transform(position => position + 1),
}))
// forms: { name: string; labels: string; nextPosition: number }[]

To apply select and map to several queries and mutations alike, define them in a helper. To convert what a mutation returns, see Inserting, Updating, and Deleting.

A callback that throws rejects the query with ResultDecodeError, whose cause is the error and whose path tells the field and the row, when there are any.