Checks | OKModel

Where this fits

An index does not reject a row. A check does. A picklist already adds its own check.

checks is an object. Each key is a name you choose. Each value receives column handles and returns sql text. schema() stores that text. This is not a query builder. A return that is not sql text is OKM1060: Check notes.title_present must return sql text.

SQL name

sql inserts a handle as the SQL name schema() stores, unquoted. A casing of "snake" has already been applied. A string is inserted as you wrote it. A number or a boolean is inserted as text.

Name

The key is not the constraint name. The constraint is {table}_{key}_check. Snake casing does not rewrite the key.

key

The key below is title_present, so the constraint is notes_title_present_check.

name.ts

import { sql, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    title: t.text(),
  },
  {
    checks: {
      title_present: (columns) => sql`${columns.title} <> ''`, 
    },
  },
);

rename

Keep that key when you rename a column the check uses. The constraint stays tasks_present_check. The plan renames the column and does not drop the constraint.

rename.ts

import { sql, table, t } from "okmodel/pg";

export const tasks = table(
  "tasks",
  {
    id: t.identity(),
    contact: t.text().renamedFrom("email"), 
  },
  {
    checks: {
      present: (columns) => sql`${columns.contact} <> ''`, 
    },
  },
);

A new table adds the check with alter table. A table that already exists adds it not valid, then validates it.

A row that fails the check is Check violation on notes (title_present). The kind is check. constraint is notes_title_present_check.

OKM1023

A picklist check is {table}_{sqlColumn}_check. A key equal to that SQL column name is Duplicate identity for constraint notes_title_check.

OKM1122

An empty key is Name key is empty.

Expression

The function receives column handles and returns sql text.

one

One column compares the stored value with SQL you write.

one-column.ts

import { sql, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    title: t.text(),
  },
  {
    checks: {
      title_present: (columns) => sql`${columns.title} <> ''`, 
    },
  },
);

two

Two handles compare two columns. This check is notes_ends_after_start_check, and the text is endsAt > startsAt when those are the SQL names.

two-columns.ts

import { sql, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    startsAt: t.timestamptz(),
    endsAt: t.timestamptz(),
  },
  {
    checks: {
      ends_after_start: (columns) => sql`${columns.endsAt} > ${columns.startsAt}`, 
    },
  },
);

number

A number is inserted as text. This expression is rank >= 1.

number.ts

import { sql, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    rank: t.integer(),
  },
  {
    checks: {
      rank_positive: (columns) => sql`${columns.rank} >= ${1}`, 
    },
  },
);

text

Quotes around text belong in the template. sql`${columns.status} <> 'draft'` is status <> 'draft'. Interpolating the string "draft" inserts draft with no quotes.

text.ts

import { sql, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    status: t.text(),
  },
  {
    checks: {
      not_draft: (columns) => sql`${columns.status} <> 'draft'`, 
    },
  },
);

several

Several checks are several keys. Each key is its own constraint: notes_title_present_check and notes_rank_positive_check.

several.ts

import { sql, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    title: t.text(),
    rank: t.integer(),
  },
  {
    checks: {
      title_present: (columns) => sql`${columns.title} <> ''`, 
      rank_positive: (columns) => sql`${columns.rank} >= ${1}`, 
    },
  },
);

sql name

A SQL name you set is the name inside the expression. Here the column is published_at and the table is note, so the constraint is note_after_open_check and the text uses published_at.

sql-name.ts

import { sql, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    openedAt: t.timestamptz(),
    publishedAt: t.timestamptz().sqlName("published_at"), 
  },
  {
    sqlName: "note", 
    checks: {
      after_open: (columns) => sql`${columns.publishedAt} > ${columns.openedAt}`,
    },
  },
);

casing

"snake" rewrites a name you did not set. publishedAt is published_at in the expression. The key stays after_epoch, so the constraint is notes_after_epoch_check.

casing.ts

import { schema, sql, table, t } from "okmodel/pg";

const notes = table(
  "notes",
  {
    id: t.identity(),
    publishedAt: t.timestamptz(), 
  },
  {
    checks: {
      after_epoch: (columns) => sql`${columns.publishedAt} > '2020-01-01'`,
    },
  },
);

export const app = schema({
  tables: [notes],
  casing: "snake", 
});

On this page