---
title: Ordering
description: Keep the members of a list in the order you choose, and move one by writing one row
icon: list-ordered
---

`ordering()` is the official extension for user-defined order: cards in the
columns of a kanban, tracks in a playlist, items of a checklist. Each member
holds a key, reads come back in key order, a new member goes last of its
list, and `move` places a member anywhere by writing that one row.

```ts
import { ordering, orderingKey } from "viborm/ordering";

const card = s.model({
  id: s.string().id().ulid(),
  columnId: s.string(),
  rank: s.string().schema(orderingKey),
  title: s.string(),
  column: s.toOne(() => column).fields("columnId").references("id"),
}).index(["columnId", "rank", "id"]);

const db = base.$extends(ordering({ models: { card: { key: "rank", within: ["columnId"] } } }));

await db.card.create({ data: { columnId, title: "Write the docs" } }); // last of its column
await db.card.move({ where: { id }, after: { id: otherId } });
await db.card.move({ where: { id }, to: { columnId: doneId } });       // last of another column
await db.column.findMany({ include: { cards: true } });                // cards in their order
```

The extension lives in its own entry point, and is written with the public
extension parts any author can import: `defaults` (each list's order, a key
written on create), the statement values `computed`, `joined` and `stored`,
and `modelMethods` (`move`, `rekey`). An application that does not import it
carries none of its code.

## Configuration

`ordering({ models: { [model]: { key, within } } })`, as `softDelete` and
`permissions` take their models (before 1.9.1 the models were the whole
argument, `ordering({ card: … })`, which still works, deprecated):

- `key`: a required string field. The extension writes it. Its default, if
  any, is a literal string, for rows the extension did not create (see
  adoption, under `rekey`).
- `within`: the fields whose values name a list (`["columnId"]`: one list
  per column). A `NULL` value is a list of its own. Leave it out for one list
  holding every row of the model.

The model needs a primary or unique key. Declare the index
`(within…, key, id)`: every ordered read is a range of it. Do not declare a
unique constraint on `(within…, key)`: respacing rewrites a list's keys in
one statement, and the constraint would refuse it midway.

`within` fields are compared as the values they hold: a list field under a
case-insensitive collation (`"a"` and `"A"` one list to the database, two to
the extension) is not supported.

`orderingKey` is a validator for the key field: with it, a write that sets
the key to something that is not one (in a nested write too), or to a key of
10^8 or more, is refused with a `ValidationError`. The bound leaves room to
append after any key a client writes. It is optional, and recommended.

## Reads

A read of an ordered model that gives no `orderBy` is sorted by the list's
fields, then the key, then the model's key: a root `findMany` or `findFirst`,
a to-many relation read at any depth (`include`, `select`, recursive), and
the rows a paged `count` or `aggregate` counts. An explicit `orderBy`
replaces it; `orderBy: []` turns it off. `findUnique` and `groupBy` are not
sorted.

## Creates

A row created without its key goes last of its list, on every create path:
`create`, `createMany` (which keeps the array's order within each list),
nested creates, `upsert`'s create branch and `connectOrCreate`'s. A key you
write is used as is.

The key is computed by the INSERT itself, from the last key of its list: a
create sends the statements it sends without the extension, and so does an
`upsert` (one `INSERT … ON CONFLICT` where the database returns rows). Two creates into
one list at the same moment both go after every member that was there; which
of the two comes first is arbitrary.

On MySQL, that INSERT would deadlock under concurrent creates into one list
(its read of the list takes shared locks), so a create first reads the last
key of its list with a plain read that locks nothing: one more statement per
create, array transactions included. Inside an interactive transaction under
MySQL's default isolation (`REPEATABLE READ`), that read sees the
transaction's snapshot: a row another transaction appended since lands after
the created one.

A value of the key field with a letter in it, or empty (a backfilled `""`),
is never a list's last: an append goes after the list's keys. A value of
digits is read as a key, so do not backfill one (`"900"` is not a key, and
appends would land before it).

## `move`

```ts
await db.card.move({ where: { id }, after: { id: otherId } });
await db.card.move({ where: { id }, before: { id: otherId } });
await db.card.move({ where: { id }, first: true });
await db.card.move({ where: { id }, last: true });
await db.card.move({ where: { id }, to: { columnId: doneId } });              // last of that list
await db.card.move({ where: { id }, to: { columnId: doneId }, first: true });
```

- `where` names the row; `after` and `before` name the anchor, both with the
  model's unique inputs. With an anchor, the row joins the anchor's list.
  An anchor's other fields are a condition it must hold: `after: { id,
  columnId }` is not found (`NotFoundError`) once that row has left
  `columnId`. A move written in one statement checks it there; one that
  reads (MySQL, a used-up gap, a row already in place) checks it on the
  anchor it reads.
- `to` names another list by its `within` fields, with `first` or `last`
  (`last` when alone).
- `data` writes other fields of the row in the same UPDATE as the key, as an
  `update`'s data (permissions judge the fields it writes): the model's own
  fields, never the key, a `within` field (`to` moves a row between lists) nor
  a relation, which could change the row's list.
- `select`, `include` and `omit` shape the result, as `update`'s do; `move`
  returns what `update` returns.
- Moving a row to where it already is writes nothing but its `data`.

A model without `within` keeps one order for the whole table, whatever a
view groups its rows by. A Kanban grouped by a column then changes a card's
column and its place in one move, one statement, by `data`:

```ts
// One order for the board and every other view of the table.
const db = base.$extends(ordering({ models: { card: { key: "rank" } } }));
await db.card.move({ where: { id }, after: { id: neighbourId }, data: { columnId } });
```

A move is one `update` through the client: validation, permissions and
[write hooks](/docs/extensions/on) apply as to any update of the row. A
row placed elsewhere is not a row changed: its `updatedAt()` fields keep
their value, unless `data` writes one, and they are no field the move
writes when [write rules](/docs/extensions/create#judge-writes-only)
judge it. Its new key is computed by the UPDATE itself, from the keys
around the row's place, read by subqueries in the same statement:

```sql
UPDATE "card" SET "rank" = <a key between the anchor's and the next one's>
WHERE "id" = $1 AND "columnId" IS NOT DISTINCT FROM <the anchor's column> …
RETURNING …
```

- The key alone is written while the row stays in its list, so a reorder
  needs no permission on the list fields. Where the row is in another list,
  a second UPDATE writes the key and the list.
- The anchor and the neighbours are read as the caller sees them, in the
  same statement: where an extension hides rows of the model ([soft
  delete](/docs/extensions/soft-delete),
  [permissions](/docs/extensions/permissions), your own), the UPDATE's
  subqueries take those rows' filters, as every read does. A hidden anchor
  is a `NotFoundError`, as a missing one is, and a hidden member is no
  neighbour: the key a move writes depends on the rows the caller sees
  alone. A read-gated field still anchors: a field gate shapes what a call
  returns, not what the engine reads.

| `move` (PostgreSQL, PGlite, SQLite, libSQL, D1) | Statements |
|---|---|
| after / before, within the list | 1 |
| after / before, into another list | 2 |
| `to` another list, `first` / `last` | 1 |
| `first` / `last` of its own list | 1 |

Where no UPDATE can place the row (its neighbours tie, its gap is used up,
the anchor or the row is missing, or it is already in place), the move reads
the anchor's neighbours through your extensions, respaces the list if it
must, then writes; a row already in place is not written.

On MySQL a move always takes that path: 4 statements (the read of the
neighbours, which locks nothing, then the `update`, which locks the row,
writes it and reads it back). MySQL locks every row an UPDATE's subqueries
read: computed in the statement, concurrent moves into one list deadlocked
(37 of 240 under four writers, measured), and never on this path.

The new key is any key between its neighbours: the integer after the lower
one where the gap holds one, else the lower one extended by two random
digits. A key grows by one or two digits each time a row is placed into the
same gap; when a gap is used up (a key would pass 64 digits), the move
respaces the list (as `rekey` does, below), then places the row.

Two moves of one row at the same moment: the last write wins.

### With a key you computed

Where the caller already has the neighbours (a board rendered with its
cards' keys), it can compute the key itself and hand it to `move`, which
writes it and reads nothing: one `update`, so 1 statement on PostgreSQL,
SQLite, libSQL and D1, and 3 on MySQL (lock, write, read back) with no
subquery to contend on.

```ts
import { keyBetween } from "viborm/ordering";

// The keys of the cards around the drop, as the board shows them.
const key = keyBetween(above?.rank ?? null, below?.rank ?? null);
await db.card.move({ where: { id }, key });                             // same column
await db.card.move({ where: { id }, key, to: { columnId: doneId } });  // another column
```

The key is placed where its value puts it, whatever happened since the
neighbours were read: if another writer moved one of them, or a respace
gave the list new keys, the row lands among the keys as they are now, not
next to the cards the caller saw. `keyBetween` throws where `lower` is not
below `upper` (tied neighbours), and a key can outgrow `MAX_KEY_LENGTH`
(check `isKeyFormat`): place the row with an anchor then, which respaces. With
`orderingKey`, a key of 10^8 or more is refused. A key that is not one is a
`ValidationError`, `key` with `after` or `before` too.

Errors: a missing or hidden row or anchor, or a write your permissions
refuse (as `update` refuses it), is a `NotFoundError` (its message says
`move` or `move anchor`); no placement,
two placements, `to` with an anchor, or a row as its own anchor is a
`ValidationError`.

`move` runs when it is awaited. It cannot be a member of an array
transaction (`$transaction([...])`), which refuses it before it reads
anything. Inside an interactive transaction (`$transaction(async (tx) =>
…)`), `tx.card.move(…)` runs in it.

## `rekey`

```ts
await db.card.rekey({ where: { columnId } });                            // respace
await db.card.rekey({ where: { columnId }, orderBy: { createdAt: "asc" } }); // reorder
```

`rekey` gives every row of the lists the rows `where` matches fresh keys
(`10`, `11`, `12`, …) in their current order, or in `orderBy` (scalar fields,
`"asc"` or `"desc"`, then the current order among equals) within each list.
Without `where`, every list the caller can see. It returns `{ count }`, the
rows rewritten.

`rekey` is one `updateMany` through the client, one statement on every
database: the UPDATE ranks the lists' members itself (a list holding no row
the caller can see `where` matches is left alone) and binds no value per
member. Every extension applies to it as to any update:
the rows written are those the caller may see and update, write hooks run,
and the [official cache](/docs/extensions/cache) is invalidated as after any
write. `updatedAt()` fields keep their value, as in a move: a list
respaced (by `rekey`, or by a move whose gap is used up) changes no row.

A list's order is shared, so `rekey` never moves a member the caller cannot
write (hidden from it, or seen and not writable), nor writes it. The members
it may update take short keys between the keys of the others around them:
whoever sees the whole list reads it in the order it had. With `orderBy`, the
caller's members are re-sorted among the places they held, never past a
member it cannot write. A list the caller may write whole gets `10`, `11`,
`12`, …. A member is written only while it holds the list and the key it
was ranked with, so one another writer moves while the statement runs stays
where that writer put it. Soft delete's
tombstones are members it does not see unless the call says
`deleted: "with"`.

The new keys sit between the keys of the members the caller cannot see, so
their digits show where such members are in the list, never which rows they
are or what they hold.

It is how an existing table adopts the extension:

1. Add the key field as `s.string().default("")`, and push: the existing
   rows take `""`, which sorts before every key, so rows created from then
   on go after them.
2. Deploy with `ordering()` applied.
3. `rekey` each list in the order it should have (`orderBy`).
4. Optionally, add `.schema(orderingKey)`; the default may stay. A create
   through the client appends a key, so the default `""` is never validated
   there; a client without `ordering` takes the default, and the validator
   refuses it, as it refuses a `""` a caller writes.

## Keys

A key is a string of digits: an integer part that carries its own length,
then a fraction. Comparing two keys as strings compares their values under
every collation that orders digits as digits, so the column needs no special
collation. Letters would not do: Czech sorts `ch` after `h`, Danish sorts
`aa` after `z`. A collation with numeric ordering (ICU `kn-true`) is the one
exception.

`keyBetween(lower, upper)` (`null` for an open end) computes a key between
two, for [`move` with a key](#with-a-key-you-computed); `isKeyFormat(value)`
tells whether a value is written as a key, as every key a list holds is
(`orderingKey` also refuses a write setting a key of 10^8 or more). It was
named `isKey` before 1.9.1, which still works (deprecated).

In a browser, import them from `viborm/ordering/keys`: that entry holds the key
grammar alone (about 3 kB, no client), with `keysBetween(lower, upper, count)`
for several keys at once and `keysAfter(last, count)` for appends.

```ts
import { keyBetween, keysBetween } from "viborm/ordering/keys";
```

Each generated key holds a random part, so two writers placing into one gap
at the same moment get distinct keys. A tie (equal keys) is still possible
with keys you write yourself; reads stay ordered (by the model's key among
equals), and `move` respaces a list when it needs a gap between two tied
members.

## Applying it with other extensions

`ordering()` is one step, applied in any order with soft delete, permissions,
the cache, `on` and `defaultOmit()`: a client that hides rows hides them from
`move` and `rekey` whichever came first, both are writes of that client
(hooks, cache), and `move`'s result is typed from that client (a
later `defaultOmit` leaves its fields out; a later `rule` that hides rows
types a to-one relation it can hide as `| null`; computed `fields` are
added). Called off its delegate (`const { move } = db.card`), the result is
typed `unknown`.

The two-step form of 1.7 (`ordering(config).keys`, then
`ordering(config).methods`) was removed in 1.11, and applying
`ordering(config)` as a function of the client (`ordering(config)(base)`) is
gone too: apply it once with `$extends()`.
