Tables and Schemas

Tables

Define each table of the database with defineTable, giving its name and columns:

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

export const form = defineTable("form", {
  id: t.uuid.primaryKey(),
  name: t.text,
  published: t.boolean,
})

See Types for the built-in types, column attributes, and custom types.

Column names

A column is named after its key. To name it differently, set the name with columnName:

export const field = defineTable("form_field", {
  id: t.uuid.primaryKey(),
  formId: t.uuid.columnName("form_id"),
  label: t.text,
})

Naming conventions

To name the columns of your tables in snake case, such as form_id for formId, create your own defineTable with createTableFactory and use it to define your tables:

// db/define-table.ts
import { createTableFactory, toSnakeCase } from "kern-orm"

export const defineTable = createTableFactory({ columnName: toSnakeCase })

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

import { defineTable } from "./db/define-table"

export const field = defineTable("form_field", {
  id: t.uuid.primaryKey(),
  formId: t.uuid, // form_id
  createdAt: t.timestamptz, // created_at
})

A run of capital letters is one word, such as user_id for userID and json_data for JSONData, and digits stay in their word, such as line2. columnName overrides the derived name of a column, such as t.text.columnName("line_2").

For another convention, pass your own function, which gets the key of each column and returns its name:

export const defineTable = createTableFactory({
  // personCategoryId for person_category_id
  columnName: key => key.replace(/_(.)/g, (_, letter: string) => letter.toUpperCase()),
})

Keys

Mark the primary key column with primaryKey() and each column of unique values with unique():

export const user = defineTable("user", {
  id: t.uuid.primaryKey(),
  email: t.text.unique(),
})

List the columns of a key of several columns in the options of defineTable:

export const membership = defineTable(
  "membership",
  {
    userId: t.uuid,
    groupId: t.uuid,
    position: t.integer,
  },
  {
    primaryKey: ["userId", "groupId"],
    unique: [["groupId", "position"]],
  },
)

Declare the keys that the database guarantees, such as by a constraint, a unique index, or the query of a view: relations and find and findBy rely on them, and a query of a relation one fails when it finds several records.

Tables of one row

To keep a table to one row, such as the settings of an application, declare it with singleton: true in the options of defineTable, and give it a unique index of (true) in its migration:

export const settings = defineTable(
  "settings",
  {
    id: t.uuid.primaryKey().hasDefault(),
    locale: t.text,
  },
  { singleton: true },
)

A query of the table is then a query of its row, such as await db.settings.upsert({ locale }); see Singleton Tables.

Schemas

Put the tables you query in a schema and bind it to the database with bindSchema:

import { bindSchema, defineSchema } from "kern-orm"

export const schema = defineSchema({ form, field })

export const db = bindSchema(schema, database)

Table keys cannot start with $, which schemas and Db reserve for their own members.

The schema only describes the tables and their relations, and db executes queries on the database. Start queries from the tables of db, such as db.form; see Queries. Where code has no Db, such as with dependency injection, start them from the schema instead; see Queries without a Db.

In code that has only db, such as a script, reach its database with $database:

await db.$database.close()

To reuse tables in several schemas, such as tenant schemas and a shared application schema, include one schema in another; see Multiple Schemas.

PostgreSQL schemas

To query tables in a specific PostgreSQL schema, pass schemaName; without it, PostgreSQL finds them through search_path, which you can override for a transaction:

export const db = bindSchema(schema, database, { schemaName: "app" })

For a PostgreSQL schema per tenant, pass a function that returns the schema of the current request; one db then serves every tenant, since the function is called on every query:

// useTenant() returns the tenant of the current request
export const db = bindSchema(schema, database, { schemaName: () => useTenant().schema })

await db.form.select("id") // FROM "tenant_1"."form"

Read the PostgreSQL schema of a Db with $schemaName, which calls the function, if any; without schemaName, it is undefined rather than the schema that search_path finds:

db.$schemaName // "tenant_1"

Relations

Declare relations with $relations, listing the relations of each table by name under the key of the table, and use the schema it returns:

import { defineSchema, many, one } from "kern-orm"

export const schema = defineSchema({ form, field }).$relations({
  form: {
    fields: many(field, { from: "id", to: "formId" }),
  },
  field: {
    form: one(form, { from: "formId", to: "id", required: true }),
  },
})

• many(field, { from: "id", to: "formId" }) relates a form to the fields whose formId equals its id.
• one(form, { from: "formId", to: "id", required: true }) relates a field to the form whose id equals its formId.

Include a key of the target in the columns to of one, such as its primary key, so that they match at most one record.

Without the record, a query of a relation one gives null, and a query of a required relation rejects with RequiredRelationError unless it filters the record. Set required: true when the database guarantees the record, such as by a foreign key, and the columns from are not nullable.

The name of a relation cannot start with $ or repeat a column of its table: the callbacks of select and where get both columns and relations.

To load related records in queries, see Related Records, and to filter by them, see Filtering.

For a composite key, list its columns in arrays of the same length:

contest: one(contest, { from: ["contestId", "rubricId"], to: ["id", "rubricId"] }),

Relate columns of the same SQL type, such as two uuid columns; smallint, integer, and bigint columns relate to each other as well, and so do text and varchar ones. A custom type needs the operators of equalityOperators to be related.

To share relations between schemas, see Multiple Schemas.