Read rowsCopy page

Each call on this page reads rows from one table. find returns a list, one returns one row or null, count and exists return one value, page returns a page and a cursor, .stream() walks a large set in batches, and explain shows the plan Postgres chose. Each example below is a function that takes a database URL. The output under it comes from running it on a notes table that holds two rows, Hello (id 1) and World (id 2).

Find

find returns an array of rows. Pass where, orderBy, and a limit.

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

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

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

[
  {
    id: "1",
    title: "Hello",
  }
]

id is a string because an identity column is a Postgres bigint, which does not fit a JavaScript number. The function awaits the call before it returns, because await using closes the client when the function exits.

The limit rule

find, page, and .stream() need a limit, or .all(reason) to read every row on purpose. A bare find() is refused with OKM1101 until you set limits in connect. With limits.find set, a find that omits limit takes that number, and TypeScript makes the argument optional. Query limits lists every key.

The rule does not apply to one, count, or exists. one adds limit 2 itself, so it can tell one match from several. count and exists return a single value.

One

one returns the row, or null when nothing matched.

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

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

export async function byId(url: string) {
  await using db = connect(url, { schema: app });
  return await db.notes.one({ where: { id: "1" } }); 
}

{
  id: "1",
  title: "Hello",
}

When more than one row matches, one throws an OkmError whose code is "not_unique". That is an error kind, not an OKM number. With orderBy, one reads with limit 1 and returns the first row instead.

Count

count returns how many rows match. It does not return the rows.

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

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

export async function total(url: string) {
  await using db = connect(url, { schema: app });
  return await db.notes.count(); 
}

2

Exists

exists returns true when a row matches. It does not return the row.

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

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

export async function has(url: string) {
  await using db = connect(url, { schema: app });
  return await db.notes.exists({ where: { id: "1" } }); 
}

true

Page

page returns { items, next }. next is a cursor string, or null on the last page. Pass it back as after, with the same orderBy, to read the next page. The example reads both rows one page at a time.

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

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

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

{
  first: {
    items: [
      {
        id: "1",
        title: "Hello",
      }
    ],
    next: "WyJub3Rlczp0aXRsZS5hc2MubGFzdCxpZC5hc2MubGFzdCIsIkhlbGxvIiwiMSJd",
  },
  second: {
    items: [
      {
        id: "2",
        title: "World",
      }
    ],
    next: null,
  },
}

What a cursor holds

The cursor records the orderBy it was made for, and the sort values and primary key of the last row on the page. The next page asks for rows that sort after those values, so it is keyset paging, not an offset:

• Rows already returned do not shift onto the next page.
• A row inserted before the cursor position does not appear on a later page. A row inserted after it does.
• A row whose sort value changes can move: it can be skipped or returned a second time. A deleted row is simply gone.

When orderBy names a hidden or sensitive column, the cursor does not carry that value. It carries only the primary key of the last row (a tuple for a composite key), and the next page reads the sort values back in the same query, under the same tenant and archive filters. If that row was deleted, the next page is refused with OKM1130 The cursor is no longer valid.

Order by a relation

orderBy can name a relation. A to-one relation sorts by one of its columns, through a left join, so a row whose relation is missing stays in the result. A to-many relation sorts by count, the number of related rows: { notes: { count: "desc" } }. find, one, .stream(), and page accept it. The related table's tenant and archive filters sit on the join, so another tenant's row or an archived row never supplies a sort value.

Here Plan belongs to project Alpha, Write to Beta, and Loose has no project:

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

const projects = table("projects", { id: t.identity().primaryKey(), name: t.text() });
const tasks = table(
  "tasks",
  {
    id: t.identity().primaryKey(),
    title: t.text(),
    projectId: t.bigint().nullable().references("projects"),
  },
  { relations: { project: one("projects", "projectId") } },
);
export const app = schema({ tables: [projects, tasks] });

export async function byProject(url: string) {
  await using db = connect(url, { schema: app });
  return await db.tasks.find({ orderBy: { project: { name: "asc" } }, limit: 20 }); 
}

[
  {
    id: "2",
    title: "Plan",
    projectId: "2",
  }, {
    id: "1",
    title: "Write",
    projectId: "1",
  }, {
    id: "3",
    title: "Loose",
    projectId: null,
  }
]

A page cursor carries the related sort value with the others. A hidden or sensitive related column uses the primary-key cursor described above.

Distinct

distinct: true keeps one row for each different combination of the select fields. NULL counts as one value. orderBy may name selected fields only, and without it the order of the rows is not defined.

distinctOn: [fields] keeps the first row for each value of those fields. orderBy must start with the same fields, and the fields after them pick which row is first. The example keeps the note with the largest amount for each title.

The table here has an amount column and four rows: Hello with 5 and 9, World with 3 and NULL.

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

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

export async function titles(url: string) {
  await using db = connect(url, { schema: app });
  const kinds = await db.notes.find({
    distinct: true, 
    select: ["title"],
    orderBy: { title: "asc" },
    limit: 20,
  });
  const biggest = await db.notes.find({
    distinctOn: ["title"], 
    orderBy: { title: "asc", amount: { dir: "desc", nulls: "last" } },
    limit: 20,
  });
  return { kinds, biggest };
}

{
  kinds: [
    {
      title: "Hello",
    }, {
      title: "World",
    }
  ],
  biggest: [
    {
      id: "2",
      title: "Hello",
      amount: 9,
    }, {
      id: "3",
      title: "World",
      amount: 3,
    }
  ],
}

include, search, page, and one refuse both options.

Stream

.stream() reads a find in batches and yields one row at a time. Each batch is its own keyset query, ordered by orderBy and then the primary key, so it does not hold a server cursor or a connection between batches.

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

const notes = table("notes", { id: t.identity().primaryKey(), title: t.text() });
export 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;
}

[ "Hello", "World" ]

select, include, a preset, and the archive set work as they do on page(). Because each batch is a separate query, a row committed while the stream runs may or may not show up. On a client with replicas, the first batch chooses the endpoint and later batches stay there.

To read a consistent set, run the stream in a repeatable read transaction, so every batch sees the same snapshot of the data (Transactions):

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

const notes = table("notes", { id: t.identity().primaryKey(), 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;
    }
  });
}

Explain

explain(table, query) runs Postgres EXPLAIN on a query you built, and returns the statement text, the bound parameters, and the plan lines.

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

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

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

{
  text: "select t.\"id\", t.\"title\" from \"notes\" t limit $1",
  params: [ "1" ],
  plan: [ "Limit  (cost=0.00..0.02 rows=1 width=40)", "  ->  Seq Scan on notes t  (cost=0.00..22.00 rows=1200 width=40)" ],
}

The costs depend on the Postgres version and the table statistics. A hidden or sensitive parameter is [redacted] in params and in the plan text. Postgres still receives the real value, so the plan is the one it would choose.

In production, explain sends nothing to the database. Production means the process has NODE_ENV set to production; no other setting is read. The same call then returns the statement with no parameters and no plan:

{
  text: "select t.\"id\", t.\"title\" from \"notes\" t limit $1",
  params: [],
  plan: null,
}

Errors

Every code below is an OkmError thrown before the statement is sent, except OKM1130 for a deleted cursor row, which is found by the query.

OKM1120 is the code for a read option the table cannot honour. The message names the option and the table, so the same code covers the four OKM1120 rows above.