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, underrekey).within: the fields whose values name a list (["columnId"]: one list per column). ANULLvalue 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 });
wherenames the row;afterandbeforename 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 leftcolumnId. 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.tonames another list by itswithinfields, withfirstorlast(lastwhen alone).datawrites other fields of the row in the same UPDATE as the key, as anupdate’s data (permissions judge the fields it writes): the model’s own fields, never the key, awithinfield (tomoves a row between lists) nor a relation, which could change the row’s list.select,includeandomitshape the result, asupdate’s do;movereturns whatupdatereturns.- 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:
- 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. - Deploy with
ordering()applied. rekeyeach list in the order it should have (orderBy).- 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 withoutorderingtakes 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().