# Queries

<Callout type="info" title="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.
</Callout>

`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](/docs/start/basic-usage) is insert, find, one, count, exists, update, and delete. A caller-supplied filter goes through [filters](/docs/define/table/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.

```ts title="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 }); // [!code focus]
}
```

#### next

Pass `next` back as `after`, with the same `orderBy`.

```ts title="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 }); // [!code focus]
}
```

<Callout type="error" title="OKM1101">
Omitting `limit` is `page() needs a limit.`
</Callout>

<Callout type="error" title="OKM1130">
A cursor this call did not return is `after is not a cursor that page() returned.`
</Callout>

### 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.

```ts title="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 }); // [!code focus]
}
```

#### grouped

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

```ts title="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"], // [!code focus]
    limit: 20,
  });
}
```

<Callout type="error" title="OKM1101">
`aggregate() with groupBy needs a limit, or .all(reason) for every group.`
</Callout>

### 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.

```ts title="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 } }, // [!code focus]
  });
}
```

#### complex

`orderBy` sorts inside that parent. `select` names the fields.

```ts title="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"] }, // [!code focus]
    },
  });
}
```

### 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.

```ts title="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" })], // [!code focus]
    limit: 20,
  });
}
```

#### complex

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

```ts title="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" }), // [!code focus]
      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,
  });
}
```

<Callout type="error" title="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.
</Callout>

<Callout type="error" title="OKM1124">
`runningTotal` on a column that is not a number or a string is refused.
</Callout>

### 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.

```ts title="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}` }, // [!code focus]
    select: ["id", fragment`length(${ident("title")})`.as("len")], // [!code focus]
    limit: 20,
  });
}
```

<Callout type="error" title="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.
</Callout>

### Text search

`find({ search })` and `count({ search })` match a [search column](/docs/define/table/columns#search). 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.

```ts title="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 }); // [!code focus]
}
```

#### complex

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

```ts title="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" }, // [!code focus]
    where: { body: matches("deploy rollback", { match: "phrase", language: "english" }) },
    orderBy: {
      body: relevance("deploy rollback", { match: "phrase", language: "english" }),
      id: "asc",
    },
    limit: 20,
  });
}
```

<Callout type="error" title="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.
</Callout>

<Callout type="error" title="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.
</Callout>

### Tree

`descendants(id)` reads the rows below `id`. `ancestors(id)` reads the rows above it. Declare a [relation](/docs/define/table/relations) 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.

```ts title="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"] }); // [!code focus]
}
```

#### ancestors

`ancestors(id)` reads the rows above `id`.

```ts title="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"] }); // [!code focus]
}
```

<Callout type="error" title="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.
</Callout>

### 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.

```ts title="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]); // [!code focus]
}
```

#### complex

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

```ts title="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", []); // [!code focus]
  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.

```ts title="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 })) { // [!code focus]
    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.

```ts title="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 })) { // [!code focus]
      if (row.title === "") break;
    }
  });
}
```

<Callout type="error" title="OKM1120">
A table with no primary key is refused. `one()`, `count()`, `exists()`, `search`, and a window are refused.
</Callout>

<Callout type="error" title="OKM1101">
A stream with no `limit` and no `.all(reason)` is refused.
</Callout>

<Callout type="error" title="OKM1121">
`batch` outside 1 to 2147483647 is refused. A `timeout` that is not a number above 0 is refused.
</Callout>

### 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).

<Callout type="error" title="OKM1120">
`Field nope is not on notes.` Use a field from the catalog.
</Callout>

### 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`.

```ts title="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"] },
  );
}
```

<Callout type="error" title="OKM1190">
A supplied id without `allow`, a database-default id named in `allow`, and an update of the key are refused.
</Callout>

### 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.

```ts title="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 } }); // [!code focus]
}
```

#### ignore

`"ignore"` skips a row whose unique is taken.

```ts title="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" }); // [!code focus]
}
```

#### update

`update` names the columns copied from the rejected row.

```ts title="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"] } }); // [!code focus]
}
```

<Callout type="error" title="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.`
</Callout>

<Callout type="info" title="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.
</Callout>

### 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. |

```ts title="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" } }, // [!code focus]
    { where: { id: second }, set: { title: "B" } },
  ]);
}
```

<Callout type="error" title="OKM1120">
The same target twice is `update on notes lists the same target twice.` An empty `set` is `update set is empty.`
</Callout>

### Increment

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

```ts title="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) } }); // [!code focus]
}
```

<Callout type="error" title="OKM1124">
A text column is `inc adds to a number. title is a text column.`
</Callout>

### 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. |

```ts title="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 }, // [!code focus]
    limit: 1,
    lock: "skip",
  });
  if (claimed.count === 0) return [];
  return db.jobs.find({ where: { claimedBy: token }, limit: claimed.count }); // [!code focus]
}
```

<Callout type="error" title="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.`
</Callout>

<Callout type="error" title="OKM1121">
`limit` must be a whole number from 1 to 2147483647.
</Callout>

### 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`.

```ts title="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" })], // [!code focus]
    count: true,
    limit: 31,
  });
}
```

#### complex

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

```ts title="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" })], // [!code focus]
    count: true,
    sum: ["amount"],
    limit: 12,
  });
}
```

<Callout type="error" title="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.
</Callout>

### 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.

```ts title="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`, // [!code focus]
  );
}
```

<Callout type="error" title="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`.
</Callout>

<Callout type="error" title="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.
</Callout>

### 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.

```ts title="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 })); // [!code focus]
}
```