Skip to content
VibORM
Esc
↑↓navigate↵open⌘Jpreview
On this page

Ordering

Keep the members of a list in the order you choose, and move one by writing one row

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.

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

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:

// 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 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 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:

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

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

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

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

Was this page helpful?