Composing Queries

To add a clause only when a condition is met, pass a callback to use and return the query with or without the clause:

const forms = await db.form
  .use(form => (onlyPublished ? form.where({ published: true }) : form))
  .select("id", "name")
// forms: { id: string; name: string }[]

The callback returns the query it receives or one built from it.

Branches of the callback can return different rows or fields, and the result is then the result of either branch. Call the methods that change the rows or fields, such as select, take, and get, before use or in each branch rather than after it:

const forms = await db.form.select("id", "name").use(form => (single ? form.take() : form))
// forms: { id: string; name: string } | { id: string; name: string }[]

To return the fields of an insert, an update, or a delete only when a condition is met, pass a callback to its use in the same way:

const result = await db.form
  .insert({ name: "Survey", published: false })
  .use(query => (returnFields ? query.select("id", "name") : query))
// result: number | { id: string; name: string }

Helpers

To reuse clauses in queries of a table, define them once with defineHelper and apply them with use:

import { defineHelper } from "kern-orm"

const withName = defineHelper(db.form, form => form.where({ published: true }).select("name"))

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

use passes the arguments that follow the helper to the further parameters of its callback:

const search = defineHelper(db.form, (query, pattern: string) =>
  query.where(form => form.name.ilike(pattern)),
)

const forms = await db.form.select("id", "name").use(search, "%survey%")

A helper is also a function of a query: search(db.form, "%survey%") is the same as db.form.use(search, "%survey%").

A helper can also return one row with take, optionally followed by optional, nullable, or required, sort and paginate the rows, remove their duplicates, keep the first row of each group, group them, and lock them. Apply a helper that calls limit, offset, distinct, or distinctOn before take, get, aggregates, such as count, and exists. Apply a helper that calls groupBy before aggregates, exists, and map; a query with groupBy cannot lock rows. To discard the entire selection, return values, an aggregate, or whether rows exist instead of records, or convert the whole result, call clearSelect, get, pluck, the aggregate, exists, or transform on the query rather than in a helper.

A helper can also select related records.

A helper can also convert the fields that it selects with map, and then choose the rows after it where you apply it:

const withSlug = defineHelper(db.form, form =>
  form.select("id", "name").map(row => ({ ...row, slug: slugify(row.name) })),
)

const forms = await db.form.use(withSlug).where({ published: true }).orderBy("name")
// forms: { id: string; name: string; slug: string }[]

To return the same result from an insert, an update, or a delete, apply the helper to it with use:

const form = await db.form.insert({ name: "Survey", published: false }).use(withSlug)
// form: { id: string; name: string; slug: string }

A helper for a mutation can call only select and map: a mutation has no where, orderBy, or take.

To name the type of the records that a helper selects, such as of the records that a function returns, pass the helper to ResultOf:

import type { ResultOf } from "kern-orm"

const selectSummary = defineHelper(db.form, form => form.select("id", "name"))

type FormSummary = ResultOf<typeof selectSummary>
// FormSummary: { id: string; name: string }

It gives one record, with only the fields that the helper selects, or what its map gives.

When the code that defines a helper has no Db, such as with dependency injection or in code shared by several Dbs, define it with a table of the schema, such as schema.form. Pass the table returned by defineTable, such as form, when the helper does not need the relations of the table.

For a reusable condition or value, use defineExpression.