Relations | OKModel

Where this fits

The foreign key is the column. This page is the edge you include: one row, many rows, a join table, or one relation across several tables.

The key in relations is the name you pass to include and to where. schema() finds the foreign key. A value that is not one(), many(), manyThrough(), morph(), or morphMany() is OKM1061.

OKM1020

A missing table is refused. The message names it and lists the accepted names. For a join, that includes the join table.

To-one

one(table, field) is one related row. Include returns that row, or null when it is missing. true loads the row. select, where, and orderBy narrow it.

table

The argument is the related table name. The foreign key is a column on this table that points there.

to-one.ts

import { connect } from "okmodel/pg/postgresjs";
import { one, schema, table, t } from "okmodel/pg";

const users = table("users", {
  id: t.identity(),
  name: t.text(),
});
const notes = table(
  "notes",
  {
    id: t.identity(),
    authorId: t.bigint().references("users"),
    title: t.text(),
  },
  { relations: { author: one("users") } }, 
);
const app = schema({ tables: [users, notes] });

export async function authorName(url: string) {
  await using db = connect(url, { schema: app });
  const rows = await db.notes.find({ limit: 20, include: { author: true } }); 
  return rows[0]?.author?.name;
}

field

Pass field when this table has more than one foreign key to that table. It is the local field name. Omit it when there is one key. Zero keys and more than one key are both OKM1021. The message lists the columns, or none.

two-keys.ts

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

export const notes = table(
  "notes",
  {
    id: t.identity(),
    authorId: t.bigint().references("users"),
    editorId: t.bigint().references("users"),
    title: t.text(),
  },
  {
    relations: {
      author: one("users", "authorId"), 
      editor: one("users", "editorId"), 
    },
  },
);

OKM1021

Relation notes.author on users is ambiguous. Accepted columns: authorId, editorId.

To-many

many(table, field) is the rows that point here. Include returns a list. A to-many include needs limit, or the call is OKM1105: Include comments is to-many and has no limit. Pass limit, or call .all(reason).

table

The argument is the other table. The foreign key is a column on that table that points here.

to-many.ts

import { connect } from "okmodel/pg/postgresjs";
import { many, schema, table, t } from "okmodel/pg";

const notes = table(
  "notes",
  { id: t.identity(), title: t.text() },
  { relations: { comments: many("comments") } }, 
);
const comments = table("comments", {
  id: t.identity(),
  noteId: t.bigint().references("notes"),
  body: t.text(),
});
const app = schema({ tables: [notes, comments] });

export async function firstComment(url: string) {
  await using db = connect(url, { schema: app });
  const rows = await db.notes.find({
    limit: 20,
    include: { comments: { limit: 1 } }, 
  });
  return rows[0]?.comments[0]?.body;
}

field

Pass field when the other table has more than one foreign key here. It is that table's field name. Omit it when there is one key. Zero keys and more than one key are both OKM1021.

many-field.ts

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

export const notes = table(
  "notes",
  { id: t.identity(), title: t.text() },
  { relations: { edits: many("comments", "noteId") } }, 
);

Filter

has, none, and every go in where under the relation name. A morph to-one refuses them: OKM1120, and the message says to filter the type and id columns or include the relation.

Call	Keeps a row when
`has`	At least one related row matches.
`none`	No related row matches.
`every`	Every related row matches. No related rows also matches.

has

has keeps a row that has a match.

has.ts

import { connect } from "okmodel/pg/postgresjs";
import { has, many, schema, table, t } from "okmodel/pg";

const notes = table(
  "notes",
  { id: t.identity(), title: t.text() },
  { relations: { comments: many("comments") } },
);
const comments = table("comments", {
  id: t.identity(),
  noteId: t.bigint().references("notes"),
  body: t.text(),
});
const app = schema({ tables: [notes, comments] });

export async function discussed(url: string) {
  await using db = connect(url, { schema: app });
  return db.notes.find({
    limit: 20,
    where: { comments: has({ body: "ship it" }) }, 
  });
}

none

none keeps a row that has no match.

none.ts

import { connect } from "okmodel/pg/postgresjs";
import { many, none, schema, table, t } from "okmodel/pg";

const notes = table(
  "notes",
  { id: t.identity(), title: t.text() },
  { relations: { comments: many("comments") } },
);
const comments = table("comments", {
  id: t.identity(),
  noteId: t.bigint().references("notes"),
  body: t.text(),
});
const app = schema({ tables: [notes, comments] });

export async function quiet(url: string) {
  await using db = connect(url, { schema: app });
  return db.notes.find({
    limit: 20,
    where: { comments: none({ body: "ship it" }) }, 
  });
}

every

every keeps a row whose related rows all match.

every.ts

import { connect } from "okmodel/pg/postgresjs";
import { every, many, schema, table, t } from "okmodel/pg";

const notes = table(
  "notes",
  { id: t.identity(), title: t.text() },
  { relations: { comments: many("comments") } },
);
const comments = table("comments", {
  id: t.identity(),
  noteId: t.bigint().references("notes"),
  body: t.text(),
});
const app = schema({ tables: [notes, comments] });

export async function agreed(url: string) {
  await using db = connect(url, { schema: app });
  return db.notes.find({
    limit: 20,
    where: { comments: every({ body: "ship it" }) }, 
  });
}

Join table

manyThrough(table, { through, from, to }) is to-many through a join table. The join table has one foreign key to this table and one to the related table. Include is to-many, so it needs limit. Tenancy and archive apply to the join rows and to the related rows.

through

through is the join table name.

through.ts

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

const teams = table("teams", { id: t.identity(), name: t.text() });
const memberships = table("memberships", {
  id: t.identity(),
  userId: t.bigint().references("users"),
  teamId: t.bigint().references("teams"),
});
const users = table(
  "users",
  { id: t.identity(), name: t.text() },
  { relations: { teams: manyThrough("teams", { through: "memberships" }) } }, 
);

export const app = schema({ tables: [users, teams, memberships] });

from

from is the join-table field that references this table. Pass it when more than one foreign key on the join table points here.

from.ts

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

export const users = table(
  "users",
  { id: t.identity(), name: t.text() },
  {
    relations: {
      teams: manyThrough("teams", { through: "memberships", from: "userId" }), 
    },
  },
);

to

to is the join-table field that references the related table. Pass it when more than one foreign key points there. The same field for both ends is OKM1021.

from-to.ts

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

export const users = table(
  "users",
  { id: t.identity(), name: t.text() },
  {
    relations: {
      teams: manyThrough("teams", { through: "memberships", from: "userId", to: "teamId" }), 
    },
  },
);

OKM1021

Relation users.teams through memberships is ambiguous at from. Accepted columns: (none). The same field for both ends says Pass from and to.

Morph

One table points at a closed list of others. Include returns the matching row, with the type field set to that table's name, or null when the row is missing, archived, outside the tenant, or the stored type is outside the list.

morph

morph(name, targets) is declared on the table that stores the pointer. name is the column stem. The targets are table names. An empty name, an empty target, or a target missing from the schema is OKM1020. The same target twice is OKM1023.

The table has ${name}Type and ${name}Id. The type column is text, varchar, char, or citext. A varchar(n) has to fit the longest target name. The id column matches each target's primary key, and that key is one column. The two columns are both nullable, or neither. A missing column is OKM1021. A type or id mismatch is OKM1022.

morph-declare.ts

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

export const comments = table(
  "comments",
  {
    id: t.text().primaryKey(),
    body: t.text(),
    commentableType: t.text(),
    commentableId: t.text(),
  },
  { relations: { commentable: morph("commentable", ["tasks", "lists"]) } }, 
);

morphMany

morphMany(table, name) is the reverse, declared on each target. table holds the type and id columns. name is the same stem. That table must declare morph(name, targets) and list this table. Include is to-many, so it takes limit, orderBy, where, and select. has, none, and every work on this side.

morph-many.ts

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

export const tasks = table(
  "tasks",
  { id: t.text().primaryKey(), title: t.text() },
  { relations: { comments: morphMany("comments", "commentable") } }, 
);

morphTo

morphTo(name, type, id) sets the type and id columns together. Spread it into insert or update. Setting one without the other, or clearing one and setting the other, is OKM1121. Clearing both is allowed. A type outside the list is OKM1120, and the message lists the accepted tables. An empty name or type is OKM1020. A null name, a null type, or an id that is not a non-empty string is OKM1120. A write can store another tenant's id. The include reads as the caller, so that target is null.

morph-to.ts

import { morphTo } from "okmodel/pg";

export const link = {
  id: "c1",
  body: "ship it",
  ...morphTo("commentable", "tasks", "t1"), 
};

include

A morph include is true. select, where, orderBy, and limit are OKM1120. Nesting it inside another include is OKM1120. A write can store another tenant's id. The include reads as the caller, so that target is null.

morph.ts

import { connect } from "okmodel/pg/postgresjs";
import { morph, morphMany, morphTo, schema, table, t } from "okmodel/pg";

const tasks = table(
  "tasks",
  { id: t.text().primaryKey(), title: t.text() },
  { relations: { comments: morphMany("comments", "commentable") } },
);
const lists = table(
  "lists",
  { id: t.text().primaryKey(), name: t.text() },
  { relations: { comments: morphMany("comments", "commentable") } },
);
const comments = table(
  "comments",
  {
    id: t.text().primaryKey(),
    body: t.text(),
    commentableType: t.text(),
    commentableId: t.text(),
  },
  { relations: { commentable: morph("commentable", ["tasks", "lists"]) } },
);
const app = schema({ tables: [tasks, lists, comments] });

export async function attach(url: string, taskId: string) {
  await using db = connect(url, { schema: app });
  await db.comments.insert({
    id: "c1",
    body: "ship it",
    ...morphTo("commentable", "tasks", taskId),
  });
  const rows = await db.comments.find({ limit: 20, include: { commentable: true } }); 
  return rows[0]?.commentable;
}

On this page