Inserting, Updating, and Deleting

Inserting

To insert a record, pass the values of its columns to insert:

await db.form.insert({ name: "Survey", published: false })

Omit a column that the database fills in, such as an ID generated by a default, and mark it with hasDefault() in the table. You can also omit a nullable column: it gets NULL, or the default that the database declares for it.

To insert several records in one statement, pass them to insertMany:

await db.field.insertMany([
  { formId, position: 1, label: "Name" },
  { formId, position: 2, label: "Email" },
])

A column that a record omits gets its default, also when other records give it a value. insertMany takes at least one record.

A value is what the type of its column takes, null for NULL, or a sql fragment:

import { sql } from "kern-orm"

await db.form.insert({ name: sql<string>`initcap(${name})`, published: false })

A value can also be a query of one value, which reads it in the same statement:

await db.field.insert({
  formId: db.form.where({ name: "Survey" }).get("id"),
  position: 1,
  label: "Name",
})

Without its row, such a query gives NULL, so PostgreSQL rejects the statement for a column that is not nullable.

Records that already exist

To save a record whether it exists or not, such as the answer of a submission to a field, choose it by its key with find or findBy, and pass the values of its other columns to upsert:

await db.answer.findBy({ submissionId, fieldId }).upsert({ value })

upsert inserts the record of the key with the values, or sets the values of the existing record, in one statement, so that a concurrent request that inserts the same record does not make it fail on a duplicate key. Pass the values that insert takes, apart from the columns of the key, since a missing record is inserted. A column that you omit, or give as undefined, gets its default in a new record and keeps its value in an existing one. upsert takes at least one value other than undefined.

Call upsert right after find or findBy, without other methods, such as where, and select the fields of the record after it, such as upsert(values).get("id").

To save the row of a singleton table, call upsert on the query of the table itself, such as db.settings.upsert({ locale }).

To insert a record unless a record with the same key exists, such as to add a user to a group they may already belong to, call onConflict with the columns of the key right after insert, and then doNothing:

await db.membership
  .insert({ userId, groupId, position })
  .onConflict("userId", "groupId")
  .doNothing()

Pass the columns of a primary or unique key of the table, in any order. A record that conflicts only on another key, such as on groupId and position, still rejects; call onConflict() without columns to skip a record that conflicts on any key.

A skipped record is not returned: insert resolves to 0, and the fields that you select after doNothing to undefined. After insertMany, select the fields of the inserted records, such as to notify only the users who were added:

const addedUserIds = await db.membership
  .insertMany(members)
  .onConflict("userId", "groupId")
  .doNothing()
  .pluck("userId")

To update the existing record instead, call merge with the columns to take from the inserted record, such as to save the fields of a form editor, inserting the new fields and updating the others by their IDs:

await db.field.insertMany(fields).onConflict("id").merge("label", "position")

Give each column that merge sets in every record, since a record that omits it would set its default. Without columns, merge sets every column that the records give, apart from the key.

To set other values on the existing record than on a new one, pass them to update, as to an update of records; its callback also receives the columns of the inserted record, such as to add a count to the existing one:

await db.formView
  .insert({ formId, day, count: 1 })
  .onConflict("formId", "day")
  .update((view, excluded) => ({
    count: sql(t.integer)`${view.count} + ${excluded.count}`,
  }))

After merge and update, the number of the changed rows counts both the inserted and the updated records, and the fields that you select are returned for both. PostgreSQL rejects a statement that updates one existing record twice, so pass insertMany at most one record of each key.

Updating

To update records, choose them with where, find, or findBy, and pass the new values to update:

await db.form.find(formId).update({ published: true })

Omitted columns, and columns given as undefined, keep their values, so you can pass a partial object, such as the fields that a request changes. update takes at least one value other than undefined. A value is anything that insert takes, such as a query of one value.

To compute a value from the columns of the record, pass a callback:

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

await db.field.where({ formId }).update(field => ({
  position: sql(t.integer)`${field.position} + 1`,
}))

To compute a value from related records, such as the number of fields of each form, return a query of one value from the callback:

await db.form.where({ published: true }).update(form => ({
  fieldCount: form.fields.count(),
  hasFields: form.fields.exists(),
}))

Deleting

To delete records, choose them with where, find, or findBy, and call delete:

await db.form.find(formId).delete()

Rows to change

update and delete change every row that the filters of the query choose. A query without filters, such as db.form.update(values), rejects instead, so that a forgotten filter, or a filter object that ends up empty, such as of the optional fields of a request, does not change the whole table by mistake. To change every row of the table, call all first:

await db.form.all().update({ published: false })

A query of a singleton table, such as db.settings.update(values), changes its row without all.

PostgreSQL cannot limit the rows of an update or a delete, so choose them with filters rather than take, limit, offset, or orderBy.

Results

insert, update, and delete resolve to the number of the rows they change:

const deleted = await db.form.where({ published: false }).delete()
// deleted: number

After find or findBy, they reject with NotFoundError without the record, unless you add optional or nullable, or the query is of a singleton table, which resolves to 0 without its row:

const deleted = await db.form.find(formId).optional().delete()
// deleted: 0 without the record

To get the fields of the changed records instead, call select, get, or pluck after insert, update, or delete:

const formId = await db.form.insert({ name: "Survey", published: false }).get("id")
// formId: string

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

update returns the values of the records after the update, and delete the values of the deleted records.

To convert what a mutation returns in code, call map or transform after it:

const form = await db.form
  .insert({ name: "Survey", published: false })
  .select("id", "name")
  .map(form => ({ ...form, slug: slugify(form.name) }))
// form: { id: string; name: string; slug: string }

transform also converts the number of the changed rows:

const deleted = await db.form
  .find(formId)
  .optional()
  .delete()
  .transform(count => count === 1)
// deleted: boolean

To return the same fields from a query and a mutation, define them in a helper.

insertMany may return the records in a different order from the one you pass them in, since PostgreSQL does not guarantee the order. To tell which returned record is which, select a column whose values you pass, such as position:

const fields = await db.field.insertMany(records).select("id", "position")
const idByPosition = new Map(fields.map(field => [field.position, field.id]))

Select the related records of the changed records in the callback of select:

const form = await db.form
  .find(formId)
  .update({ published: true })
  .select(form => ({
    name: form.name,
    fields: form.fields.select("label"),
  }))

The change and its related records run in one statement, so related records are read as the database was before the change: a related record of the same table, such as another changed form, has its values before the change. Query such records in a separate query after the change instead.

A query that rejects while reading what it returns, such as with NotFoundError of a query of find in the callback of select, or by a callback of map or transform that throws, has already made its change. To undo the change with the error, run the query in a transaction.

Many records

A statement takes at most 65535 parameters: each plain value takes one, and a sql fragment or a query takes those of the values in it. To insert more values, such as tens of thousands of records, call insertMany with batches of them in a transaction.