Queries | OKModel

Where this fits

The table is declared. This page is each call, in the order you meet it: the first rows of a relation, a window, search, a tree, a stream, then the writes.

one returns the row or null. More than one match is not_unique: one() on notes matched more than one row. Pass orderBy to take the first, or narrow where. Basic Usage is insert, find, one, count, exists, update, and delete. A caller-supplied filter goes through filters.

Page

page returns { items, next }. next is null on the last page. Pass it back as after with the same orderBy. The cursor is the order plus the primary key, so a page stays stable while rows are inserted.

Input	Result
`limit`	Required. The page size.
omitted `after`	The first page.
`after`	The cursor from `next`.
`next`	`null` on the last page.

first

limit is required. Omit after for the first page.

first.ts

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

const notes = table("notes", { id: t.identity(), title: t.text() });
const app = schema({ tables: [notes] });

export async function first(url: string) {
  await using db = connect(url, { schema: app });
  return db.notes.page({ orderBy: { title: "asc" }, limit: 20 }); 
}

Pass next back as after, with the same orderBy.

page.ts

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

const notes = table("notes", { id: t.identity(), title: t.text() });
const app = schema({ tables: [notes] });

export async function next(url: string, after: string | null) {
  await using db = connect(url, { schema: app });
  return db.notes.page({ orderBy: { title: "asc" }, limit: 20, after }); 
}

OKM1101

Omitting limit is page() needs a limit.

OKM1130

A cursor this call did not return is after is not a cursor that page() returned.

Aggregate

aggregate counts, sums, averages, and finds extremes. There is no having.

Input	Result
no `groupBy`	One row.
`groupBy`	One row per group. Needs `limit`, or `.all(reason)`.
`sum`, `avg`	A list of field names.
`count: true`	The row count.

count

With no groupBy the result is one row.

count.ts

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

const tasks = table("tasks", {
  id: t.identity(),
  status: t.text(),
  amount: t.integer(),
});
const app = schema({ tables: [tasks] });

export async function totals(url: string) {
  await using db = connect(url, { schema: app });
  return db.tasks.aggregate({ count: true }); 
}

grouped

groupBy needs limit, or .all(reason). sum is a list of field names.

aggregate.ts

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

const tasks = table("tasks", {
  id: t.identity(),
  status: t.text(),
  amount: t.integer(),
});
const app = schema({ tables: [tasks] });

export async function byStatus(url: string) {
  await using db = connect(url, { schema: app });
  return db.tasks.aggregate({
    groupBy: ["status"],
    count: true,
    sum: ["amount"], 
    limit: 20,
  });
}

OKM1101

aggregate() with groupBy needs a limit, or .all(reason) for every group.

Include

limit on a to-many include is the first N related rows of each parent.

Option	Result
`limit`	Per parent, inside one lateral join.
`orderBy`	Order inside that parent. The primary key follows, ascending, for any key you did not name.
omitted `orderBy`	The primary key.
no related rows	`[]`.
`select`, a nested include, a preset	Apply. A `select` that names a hidden column drops that column and does not error.
`matches`, `relevance`	Apply inside the include.
`search`	Stays on `find` and `count`.
a window in `select`	Not in this version.

simple

limit is the first related rows of each parent.

include-limit.ts

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

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

export async function thread(url: string) {
  await using db = connect(url, { schema: app });
  return db.posts.find({
    limit: 20,
    include: { comments: { limit: 3 } }, 
  });
}

complex

orderBy sorts inside that parent. select names the fields.

include.ts

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

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

export async function latest(url: string) {
  await using db = connect(url, { schema: app });
  return db.posts.find({
    limit: 20,
    include: {
      comments: { orderBy: { score: "desc" }, limit: 3, select: ["id", "body"] }, 
    },
  });
}

Windows

rowNumber, rank, denseRank, and runningTotal go in select on find or one.

Helper	Result
`rowNumber`	One number per row. The primary key is appended, so a tie stays stable.
`rank`	Equal values share a rank. The next rank skips.
`denseRank`	Equal values share a rank. The next rank is the next integer.
`runningTotal`	The column's type, or null. An integer is a number or null. A column that reads as text, including `numeric`, is that string or null.

Option	Result
`partitionBy`	The group. Omit it to number the whole result.
`orderBy`	Required. The order inside the group.
`as`	The name on the row. A column name is refused.

simple

rowNumber numbers the rows in orderBy. Omit partitionBy to number the whole result.

row-number.ts

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

const tasks = table("tasks", {
  id: t.identity(),
  status: t.text(),
  amount: t.integer(),
});
const app = schema({ tables: [tasks] });

export async function numbered(url: string) {
  await using db = connect(url, { schema: app });
  return db.tasks.find({
    select: ["id", rowNumber({ orderBy: { amount: "desc" }, as: "n" })], 
    limit: 20,
  });
}

complex

rank skips after a tie. denseRank does not. runningTotal adds the column.

windows.ts

import { connect } from "okmodel/pg/postgresjs";
import { denseRank, rank, rowNumber, runningTotal, schema, table, t } from "okmodel/pg";

const tasks = table("tasks", {
  id: t.identity(),
  status: t.text(),
  amount: t.integer(),
});
const app = schema({ tables: [tasks] });

export async function numbered(url: string) {
  await using db = connect(url, { schema: app });
  return db.tasks.find({
    select: [
      "id",
      "status",
      rowNumber({ partitionBy: ["status"], orderBy: { amount: "desc" }, as: "n" }), 
      rank({ partitionBy: ["status"], orderBy: { amount: "desc" }, as: "r" }),
      denseRank({ partitionBy: ["status"], orderBy: { amount: "desc" }, as: "d" }),
      runningTotal("amount", { partitionBy: ["status"], orderBy: { id: "asc" }, as: "soFar" }),
    ],
    limit: 20,
  });
}

OKM1120

page() reads select as field names, so a window there is OKM1120: select must be a list of field names. An as that is a column name is rowNumber as id clashes with a column of tasks. An empty as is rowNumber needs as, the name of the result key. A control character is rowNumber as has a control character. A name on Object.prototype, or then, is rowNumber as __proto__ is a prototype key. Use another name for the result. orderBy is required: rowNumber needs orderBy, the order inside the group. lag, lead, and a frame are not in this version.

OKM1124

runningTotal on a column that is not a number or a string is refused.

SQL fragment

fragment is a SQL expression you can nest. A string, number, boolean, or null is a parameter. ident names the field, the same name where uses, and a hidden field is refused (OKM1120); hidden({ filterable: true }) is allowed in where only. fragment.column is the field the fragment is attached to in where. .as(name) puts a numeric expression in select on find or one. The tenant predicate stays in front of the fragment.

The check sql tag is a different function. It builds a check, an index predicate, or a default. It does not accept parameters.

where and select

length(${fragment.column}) uses the title field this fragment filters. ident("status") is the field status. len is a number.

fragment.ts

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

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
  status: t.text(),
});
const app = schema({ tables: [notes] });

const open = fragment`${ident("status")} = ${"open"}`;

export async function openNotes(url: string) {
  await using db = connect(url, { schema: app });
  return db.notes.find({
    where: { title: fragment`length(${fragment.column}) > ${3} and ${open}` }, 
    select: ["id", fragment`length(${ident("title")})`.as("len")], 
    limit: 20,
  });
}

OKM1125

A fragment that closes the surrounding predicate, a comment, a semicolon, a $, or a backslash in the literal is OKM1125. So is an interpolation inside quotes, an empty fragment, and a select fragment that is not a number. Pass a value that needs a backslash as an interpolation. ident("") is OKM1122.

Text search

find({ search }) and count({ search }) match a search column. A string is enough when the table has one. Rows come back closest first, then by primary key, unless you pass orderBy. count uses the same match and does not order.

`match`	Query
`natural`	`websearch_to_tsquery`. The default. Unquoted words are combined with AND.
`all-words`	`plainto_tsquery`.
`phrase`	`phraseto_tsquery`.
`advanced`	`to_tsquery`. Raw syntax. A syntax error in that query is OKM1120. Other syntax errors stay a driver error.

language is a text search language such as english. Leave it out for the database default. A well-formed name that Postgres does not know as a language reaches the server and comes back as a driver error. When the table has more than one search column, name it with in. The error lists the names. { mode, config } is not accepted. Ranking is ts_rank. matches and relevance take the same { match, language }.

simple

A string is enough when the table has one search column. Rows come back closest first.

simple-search.ts

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

const pages = table("pages", {
  id: t.identity(),
  title: t.text(),
  body: t.tsvector(),
});
const app = schema({ tables: [pages] });

export async function found(url: string) {
  await using db = connect(url, { schema: app });
  return db.pages.find({ search: "deploy rollback", limit: 20 }); 
}

complex

match and language name the query. where and orderBy use the same text, so the filter and the rank agree.

search.ts

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

const pages = table("pages", {
  id: t.identity(),
  title: t.text(),
  body: t.tsvector(),
});
const app = schema({ tables: [pages] });

export async function ranked(url: string) {
  await using db = connect(url, { schema: app });
  return db.pages.find({
    search: { for: "deploy rollback", match: "phrase", language: "english" }, 
    where: { body: matches("deploy rollback", { match: "phrase", language: "english" }) },
    orderBy: {
      body: relevance("deploy rollback", { match: "phrase", language: "english" }),
      id: "asc",
    },
    limit: 20,
  });
}

OKM1120

page(), one, and exists do not take search. A language that is not text is refused. A highlighted snippet and a search of a plain text column are not in this version.

OKM1122

A missing text search configuration is OKM1122, including public.english. A malformed advanced search is OKM1120 and names the search input. An empty or malformed name is still OKM1122 when the statement is built.

Tree

descendants(id) reads the rows below id. ancestors(id) reads the rows above it. Declare a relation from the table to itself. ltree is not required.

Option	Result
`relation`	Required when the self-relations use different columns. The fix lists the names.
`depth`	Stops after that many steps. `0` returns nothing unless `self: true`, which returns the start row.
`where`	Filters the rows that come back. It does not cut the walk short.
a missing id	No rows.
a cycle	A row already on the path is not entered again.
`include`	Not available.

descendants

descendants(id) reads the rows below id. depth stops the walk.

descendants.ts

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

const nodes = table(
  "nodes",
  {
    id: t.identity(),
    parentId: t.bigint().nullable().references("nodes"),
    name: t.text(),
  },
  { relations: { children: many("nodes", "parentId") } },
);
const app = schema({ tables: [nodes] });

export async function below(url: string, id: string) {
  await using db = connect(url, { schema: app });
  return db.nodes.descendants(id, { depth: 2, select: ["id", "name"] }); 
}

ancestors

ancestors(id) reads the rows above id.

ancestors.ts

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

const nodes = table(
  "nodes",
  {
    id: t.identity(),
    parentId: t.bigint().nullable().references("nodes"),
    name: t.text(),
  },
  { relations: { children: many("nodes", "parentId") } },
);
const app = schema({ tables: [nodes] });

export async function above(url: string, id: string) {
  await using db = connect(url, { schema: app });
  return db.nodes.ancestors(id, { select: ["id", "name"] }); 
}

OKM1120

No self-relation is nodes has no relation to itself. More than one, and no relation, is nodes has more than one relation to itself. Pass relation. A primary key that is not exactly one column besides the tenant key is nodes needs one primary key column besides the tenant key. depth must be an integer from 0 up.

Sync

sync(id, relation, ids) makes a to-many relation hold exactly those ids. It locks the parent, then writes, and returns { added, removed }. Duplicate ids count once. A join table gains and loses link rows. A nullable foreign key is set or cleared, and the related row stays. A link that would have to delete a row is refused.

A missing parent is not_found: sync on nodes did not find that id. An id that is not on the related table is not_found: sync on nodes.children is missing 1 ids. The relation must be many or manyThrough: sync on nodes.children is not a to-many relation. A tree-shaped many (the same table, through its own foreign key) refuses a list that contains the parent's own id. manyThrough accepts that link, including a row that follows itself. An update that sets a tree parent to the row's own id, or to a descendant, is OKM1120 when where is the primary key. A filter that is not the key is not checked.

simple

One call sets the relation to those ids. Duplicate ids count once.

sync.ts

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

const nodes = table(
  "nodes",
  {
    id: t.identity(),
    parentId: t.bigint().nullable().references("nodes"),
    name: t.text(),
  },
  { relations: { children: many("nodes", "parentId") } },
);
const app = schema({ tables: [nodes] });

export async function links(url: string, id: string, child: string) {
  await using db = connect(url, { schema: app });
  return db.nodes.sync(id, "children", [child]); 
}

complex

An empty list clears the links. The next call sets one id. Each call returns { added, removed }.

sync-clear.ts

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

const nodes = table(
  "nodes",
  {
    id: t.identity(),
    parentId: t.bigint().nullable().references("nodes"),
    name: t.text(),
  },
  { relations: { children: many("nodes", "parentId") } },
);
const app = schema({ tables: [nodes] });

export async function replace(url: string, id: string, child: string) {
  await using db = connect(url, { schema: app });
  const cleared = await db.nodes.sync(id, "children", []); 
  const set = await db.nodes.sync(id, "children", [child]);
  return { cleared, set };
}

Stream

.stream() walks find in keyset batches. It does not open a server cursor. Leaving the loop does not hold a connection.

Option	Result
`batch`	An integer from 1 to 2147483647. The default is 1000.
`limit`	The total. `0` yields nothing and runs no query.
`.all(reason)`	The set, with no `limit`. Both together still honor `limit`.
`orderBy`	Then the primary key. Omit it and the rows follow the primary key.
`timeout`	The time for the whole stream. On a driver that cannot cancel, the check is between batches.
`signal`	Stops the stream.

select, include, a preset, and the archive set work as they do on page(). A row committed while the stream runs can show up, or not. tx({ isolation: "repeatable read" }) is one snapshot of the primary, and that transaction holds the connection. On a client with replicas, the first batch chooses the endpoint and later batches stay there.

limit

limit is the total. batch is how many rows each query reads.

stream.ts

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

const notes = table("notes", { id: t.identity(), title: t.text() });
const app = schema({ tables: [notes] });

export async function titles(url: string): Promise<string[]> {
  await using db = connect(url, { schema: app });
  const out: string[] = [];
  for await (const row of db.notes.find({ limit: 20 }).stream({ batch: 20 })) { 
    out.push(row.title);
  }
  return out;
}

snapshot

.all(reason) is the whole set. tx({ isolation: "repeatable read" }) is one snapshot, and that transaction holds the connection.

stream-all.ts

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

const notes = table("notes", { id: t.identity(), title: t.text() });
const app = schema({ tables: [notes] });

export async function each(url: string): Promise<void> {
  await using db = connect(url, { schema: app });
  await db.tx({ isolation: "repeatable read" }, async (tx) => {
    for await (const row of tx.notes.find({}).all("export").stream({ batch: 500 })) { 
      if (row.title === "") break;
    }
  });
}

OKM1120

A table with no primary key is refused. one(), count(), exists(), search, and a window are refused.

OKM1101

A stream with no limit and no .all(reason) is refused.

OKM1121

batch outside 1 to 2147483647 is refused. A timeout that is not a number above 0 is refused.

Unknown keys

An unknown key on insert, update, upsert / onConflict, or first-or-create is OKM1120. The message names the key and the table. Typos are no longer dropped silently. Guarded, sealed, and computed fields keep their existing codes (OKM1190 and the rest).

OKM1120

Field nope is not on notes. Use a field from the catalog.

Supplied id

A client-generator t.id({ default: uuidv4 }) omits id from insert. { allow: ["id"] } writes the id you pass and skips the generator for that row. A list can mix a supplied id with a row that omits it. Without allow, a supplied id is OKM1190. On a list, allow has to be exactly ["id"]. A longer list does not open id in the type.

t.id() and t.id({ default: "uuidv7" }) are database defaults. The type accepts allow: ["id"] on that id, and the runtime fails with OKM1190. Update does not change the key. A primary key with no client generator, including .primaryKey().guarded(), stays refused. A guarded field that is not the key still opens with allow.

supplied-id.ts

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

const users = table("users", { id: t.id({ default: uuidv4 }), name: t.text() });
const app = schema({ tables: [users] });

export async function keep(url: string, id: string) {
  await using db = connect(url, { schema: app });
  return db.users.insert(
    [{ id, name: "Ada" }, { name: "Grace" }],
    { allow: ["id"] },
  );
}

OKM1190

A supplied id without allow, a database-default id named in allow, and an update of the key are refused.

First or create

onConflict decides what an insert does when a unique is already taken.

Value	Result
omitted, or `"error"`	The insert fails.
`"ignore"`	The new row is skipped.
`{ on, update }`	Those columns are set from the new row.
`{ on, return: true }`	The existing row is read and not changed.

on is a unique or the primary key. update and return together are refused.

return

{ on, return: true } inserts the row. When that unique is taken, it reads the existing row and does not change it. The lookup runs only when the insert returned nothing.

create.ts

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

const tags = table("tags", { id: t.identity(), name: t.text().unique() });
const app = schema({ tables: [tags] });

export async function once(url: string) {
  await using db = connect(url, { schema: app });
  return db.tags.insert({ name: "ship" }, { onConflict: { on: "name", return: true } }); 
}

ignore

"ignore" skips a row whose unique is taken.

ignore.ts

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

const tags = table("tags", { id: t.identity(), name: t.text().unique() });
const app = schema({ tables: [tags] });

export async function keep(url: string) {
  await using db = connect(url, { schema: app });
  return db.tags.insert({ name: "ship" }, { onConflict: "ignore" }); 
}

update

update names the columns copied from the rejected row.

upsert.ts

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

const tags = table("tags", { id: t.identity(), name: t.text().unique() });
const app = schema({ tables: [tags] });

export async function rename(url: string) {
  await using db = connect(url, { schema: app });
  return db.tags.insert({ name: "ship" }, { onConflict: { on: "name", update: ["name"] } }); 
}

OKM1120

Anything else is onConflict must be "error", "ignore", { on, update }, or { on, return: true }. update and return together are onConflict takes update or return, not both.

Read committed

The lookup is a second statement and does not set isolation. A second request that commits while the first is waiting is visible, so both callers get the row. Repeatable read and serializable can still miss it. On an archivable table, a unique that is not the primary key matches only a row that is not archived.

List

update of a list writes those rows in one statement. A long list is split so the parameters stay inside the budget. A finished statement over 65,535 parameters is OKM1126 (count and limit in the message). The limit is for the whole statement — filters, inList, limit, and values — not for inList alone. Chunk the work, or filter with = any(array).

Input	Result
the same target twice	OKM1120.
a target that matches nothing	Not an error. `count` is the rows changed.
an empty `set`	OKM1120.
an empty list	No statement.

list.ts

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

const notes = table("notes", { id: t.identity(), title: t.text() });
const app = schema({ tables: [notes] });

export async function rename(url: string, first: string, second: string) {
  await using db = connect(url, { schema: app });
  return db.notes.update([
    { where: { id: first }, set: { title: "A" } }, 
    { where: { id: second }, set: { title: "B" } },
  ]);
}

OKM1120

The same target twice is update on notes lists the same target twice. An empty set is update set is empty.

Increment

inc adds to a number or bigint column inside set. A null number stays null. undefined in set leaves the field unchanged.

inc.ts

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

const tasks = table("tasks", { id: t.identity(), amount: t.integer() });
const app = schema({ tables: [tasks] });

export async function add(url: string, id: string) {
  await using db = connect(url, { schema: app });
  return db.tasks.update({ where: { id }, set: { amount: inc(1) } }); 
}

OKM1124

A text column is inc adds to a number. title is a text column.

Claim

update with lock: "skip" and limit changes up to that many matching rows and leaves rows another transaction already holds. The database chooses which unlocked rows. The result is { count }, the number of rows changed. { returning: true } returns the updated rows and honors select, including when { allow } names a guarded field. Set a claimedBy token in set, then find by that token, or pass returning: true and read the rows from the claim. delete does not accept { returning: true }. That call is OKM1120 and deletes nothing. Pass the field names, or omit returning for { count }.

Input	Result
`limit` without `lock: "skip"`, or `lock: "skip"` without `limit`	Refused.
`orderBy`	Not accepted.
`wait` on an update	Refused.
`lock: "skip"` on a read	Refused. A read uses `lock: "update"` or `"share"` and `wait: "nowait"` or `"skip"` inside `tx()`.
a path-tenancy parent in the same statement	Refused.

claim.ts

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

const jobs = table("jobs", {
  id: t.identity(),
  status: t.text(),
  claimedBy: t.text().nullable(),
});
const app = schema({ tables: [jobs] });

export async function take(url: string, token: string) {
  await using db = connect(url, { schema: app });
  const claimed = await db.jobs.update({
    where: { status: "pending" },
    set: { status: "running", claimedBy: token }, 
    limit: 1,
    lock: "skip",
  });
  if (claimed.count === 0) return [];
  return db.jobs.find({ where: { claimedBy: token }, limit: claimed.count }); 
}

OKM1120

limit without the lock is limit on an update needs lock: "skip". The lock without limit is lock: "skip" needs a limit. wait is wait belongs on a read. An update claims rows with lock: "skip". A path column in the same claim is lock: "skip" on tasks cannot change projectId in the same claim.

OKM1121

limit must be a whole number from 1 to 2147483647.

Period

bucket(column, unit, { tz, as }) goes in aggregate groupBy, beside field names. as is the result key. A grouped aggregate needs limit, or .all(reason). There is no having and no gap filling.

Unit	On `timestamptz`	On `date`
`second`, `minute`, `hour`	`Temporal.Instant`, the period start.	OKM1120.
`day`, `week`, `month`, `quarter`, `year`	`Temporal.PlainDate`, the local date the period starts.	The same date.

tz is required for timestamptz and refused for date. A timestamp without a zone is refused. weekStart applies only to week and is monday (the default), sunday, or saturday. A NULL timestamp is one group: the key is present and the value is null.

simple

One day bucket on timestamptz. tz is required. A date column omits tz.

buckets.ts

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

const events = table("events", { id: t.identity(), at: t.timestamptz() });
const app = schema({ tables: [events] });

export async function perDay(url: string) {
  await using db = connect(url, { schema: app });
  return db.events.aggregate({
    groupBy: [bucket("at", "day", { tz: "UTC", as: "day" })], 
    count: true,
    limit: 31,
  });
}

complex

A week starts on Monday unless weekStart says otherwise. sum is the total amount in that week.

weeks.ts

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

const events = table("events", {
  id: t.identity(),
  at: t.timestamptz(),
  amount: t.integer(),
});
const app = schema({ tables: [events] });

export async function perWeek(url: string) {
  await using db = connect(url, { schema: app });
  return db.events.aggregate({
    groupBy: [bucket("at", "week", { tz: "UTC", weekStart: "sunday", as: "week" })], 
    count: true,
    sum: ["amount"],
    limit: 12,
  });
}

OKM1120

An empty as is refused. __proto__, constructor, toString, and hasOwnProperty are refused. An as that clashes with a group field, another bucket, or count, sum, avg, min, or max is refused.

Raw SQL

raw and explain are functions. Pass the table handle, including one from for(). The class names the row: each field needs an initializer, and that order is the select list. "read" is one select. "write" runs on the primary. A scoped tenant read keeps the tenant predicate outside the fragment, so the select list names the primary key with ident.

There is no untyped sql.raw(text, reason) in this version. A key that is a field of the handle's table uses that field's decode. Any other key (a joined column, an expression) uses the initializer: a number, a boolean, or driver text. Joined columns are not projected through another table's codecs.

raw-notes.ts

import { connect } from "okmodel/pg/postgresjs";
import { fragment, ident, raw, schema, table, t } from "okmodel/pg";

class NoteRow {
  id = "";
  title = "";
}

const notes = table("notes", { id: t.id(), title: t.text() });
const app = schema({ tables: [notes] });

export async function titled(url: string) {
  await using db = connect(url, { schema: app });
  return raw(
    db.notes,
    NoteRow,
    "read",
    fragment`select ${ident("id")}, ${ident("title")} from notes`, 
  );
}

OKM1602

Pass "read" or "write", a fragment template, and a class with an initializer on every column. A read starts with select. A write starts with insert, update, or delete.

OKM1702

A raw write on a scoped tenant table sends nothing. A raw read must select the primary key with ident and must not alias it. unscoped("reason") is the hatch for a reviewed statement.

Explain

explain describes a query you already built. Outside production it returns the EXPLAIN lines with the statement text and parameters. In production it does not send the statement: plan is null and params is empty.

explain-notes.ts

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

const notes = table("notes", { id: t.id(), title: t.text() });
const app = schema({ tables: [notes] });

export async function plan(url: string) {
  await using db = connect(url, { schema: app });
  return explain(db.notes, db.notes.find({ limit: 1 })); 
}

On this page