CRUD Operations

Atscript's DB layer provides a type-safe API for creating, reading, updating, and deleting records. All operations go through AtscriptDbTable, which handles validation, default values, nested object flattening, and adapter translation automatically.

Getting a Table Instance

import { DbSpace } from "@atscript/db";
import { User } from "./schema/user.as";

const users = db.getTable(User); // AtscriptDbTable<typeof User>

getTable() returns a cached instance — calling it again with the same type returns the same table. See Setup for how to create a DbSpace.

Inserting Records

Insert One

Insert a single record and get back the generated primary key:

const result = await users.insertOne({
  email: "alice@example.com",
  name: "Alice",
  status: "active",
});
// result: { insertedId: 1 }

Fields with @db.default.* annotations (@db.default.increment, @db.default.uuid, @db.default.now, or @db.default 'value') are applied automatically — you can omit them from the input. An undefined value counts as omitted (since 0.1.128); null is an explicit NULL — see Defaults.

Write payloads are typed as DbInsert<Row> (insert: every key optional), DbPatch<Row> (update: every key optional, plus field operators and $cas) and DbRow<Row> (replace: required keys stay required, plus $cas); all accept null on optional columns. A value of an interface type fits them — the generated model type or your own interface Product (since 0.1.168; before, a payload type with an index signature rejected interfaces). An untyped table takes any object. DbInsert makes every field optional, so it does not catch a missing required field or a field the server sets (a @db.default.increment key, a timestamp stamp) — validation at insert does. Defaults are applied before validation: static @db.default 'x' values are filled on every adapter, function defaults (now / uuid / increment) only when the adapter does not generate them natively (since 0.1.128; before, SQL adapters left static defaults to the DDL DEFAULT clause). To see or enrich the defaulted, validated rows before they are written, pass a write guard.

insertedId typing

insertedId is typed as unknown (the PK type isn't always inferable — UUID, ObjectId, composite, etc.). Cast it to your PK type when you need a typed value: result.insertedId as number, or as { orderId: number; productId: number } for a composite key.

Composite primary keys

On a table with a composite primary key (several @meta.id fields), insertedId is the primary-key object, keyed by field name (since 0.1.169). insertMany reports one such object per row in insertedIds, and so do the onConflict: "ignore" results. A single-field key keeps its scalar id.

// @meta.id orderId: number, @meta.id productId: number
const { insertedId } = await orderLines.insertOne({ orderId: 1, productId: 2, qty: 3 });
// insertedId: { orderId: 1, productId: 2 }
await orderLines.findById(insertedId); // the row just inserted

A key part the database generates (a @db.default.increment part, or a native uuid default) is in the object when the adapter learns its value from the insert:

Adapter	Generated key part
PostgreSQL	Always — read from RETURNING.
MongoDB	Always — the adapter allocates increments itself.
Memory	Always — the adapter allocates increments itself.
MySQL	When the row was written by a one-row INSERT (every insertOne); not for rows of a batched insert.
SQLite	Never — SQLite generates no value for one part of a composite key.

Each part is reported in the form a read answers (a MySQL DATETIME part as epoch ms), so it is the value findById and GET /one?… take. On MySQL an explicit 0 in an AUTO_INCREMENT part counts as generated. When a part is unknown, the row reports no id (insertedId: undefined, an undefined entry in insertedIds) rather than a wrong one, and returning answers no row. Read such a row back by a unique field instead. See Upgrading for what changed in 0.1.169.

Read the stored row back: returning

Pass returning to get the row as stored — server defaults, number.timestamp.created / .updated and the version included — read by its primary key in the insert's transaction (since 0.1.165):

const { insertedId, row } = await users.insertOne(
  { email: "alice@example.com", name: "Alice" },
  { returning: true }, // or a field list: ["id", "createdAt"]
);

With onConflict: 'ignore', a skipped row answers { conflict: true } and no row. returning is an insertOne option only.

Insert Many

Insert multiple records in a single transaction:

const result = await users.insertMany([
  { email: "alice@example.com", name: "Alice" },
  { email: "bob@example.com", name: "Bob" },
  { email: "charlie@example.com", name: "Charlie" },
]);
// result: { insertedCount: 3, insertedIds: [1, 2, 3] }

Rows may carry different sets of fields: each row is stored as insertOne would store it, and a column a row omits gets its default (or NULL).

PostgreSQL and MySQL before 0.1.132

Up to 0.1.131 the PostgreSQL and MySQL adapters built the column list from the first row only, so any column absent from row 1 was silently dropped from every row of the batch. If you inserted heterogeneous batches on those adapters, check the affected columns. SQLite, MongoDB and memory were not affected.

Skip conflicting rows

Pass onConflict: "ignore" (since 0.1.148) to skip rows that collide on the primary key or any unique index instead of failing the call with CONFLICT. The result has one slot per input row, by index:

const result = await users.insertMany(
  [
    { email: "alice@example.com", name: "Alice" }, // new
    { email: "bob@example.com", name: "Bob" }, // already stored
    { email: "alice@example.com", name: "Alice again" }, // duplicate inside the batch
  ],
  { onConflict: "ignore" },
);
// result: { insertedCount: 1, insertedIds: [7], inserted: [0], conflicts: [1, 2] }

const one = await users.insertOne(
  { email: "bob@example.com", name: "Bob" },
  { onConflict: "ignore" },
);
// one: { conflict: true }   — or { insertedId, conflict: false }

• insertedIds holds the ids of the inserted rows only (dense, input order); inserted gives each one's input index, and conflicts lists the skipped input indices.
• A row repeating the key of an earlier row of the same batch is a conflict too: the earlier row wins. A key with a NULL / missing component never collides (SQL NULLS DISTINCT).
• Only uniqueness collisions are skipped. Validation, NOT NULL, foreign-key, guard and check failures still throw and roll the call back. The guard sees every submitted row (a forbidden row is an error even if it would have conflicted); check sees the inserted rows only.
• A payload that creates a related parent (a nested @db.rel.to object with more than the target's key) is rejected with INVALID_QUERY: the parent would be orphaned when the row is skipped. A @db.rel.to object that names only the target's key ({ author: { id: 5 } }) creates nothing: it links the existing parent and becomes the row's foreign key (its existence is checked like any foreign key); a different value in the row's own foreign key field is INVALID_QUERY. Nested children (@db.rel.from) and @db.rel.via links of a skipped row are not written; a conflict inside a nested child insert is not ignored (nested inserts use the default mode, so it throws and rolls the call back).
• SDK-side defaults of skipped rows are discarded; database sequences and increments may leave gaps.
• A skipped row never aborts the surrounding transaction.
• A conflict reveals that the value exists, exactly as the default mode's 409 does: a skipped row tells the caller that a row with that unique value is stored, even one the caller cannot read. An application that scopes uniqueness per tenant should declare per-tenant unique indexes (a composite unique index that includes the tenant column) instead of a global one.
• An adapter that does not implement conflict-ignoring inserts throws DbError("ON_CONFLICT_NOT_SUPPORTED"); the built-in adapters all do. Over HTTP the mode is POST /?$onConflict=ignore (CRUD endpoints).

Ignore, then update the skipped rows: lockConflicts

A common pattern inside a transaction is "insert what is new, then update what was already there". Since 0.1.153 an ignore-insert inside a caller's transaction leaves no lock on the stored rows it skipped (on MySQL it used to, and two such transactions updating the same row deadlocked). Pass lockConflicts: true (since 0.1.153) when the skipped rows must also stay untouched by others until you update them:

await adapter.withTransaction(async () => {
  const result = await stock.insertMany(rows, { onConflict: "ignore", lockConflicts: true });
  // every stored row the batch collided with is now locked until COMMIT
  for (const i of result.conflicts) {
    await stock.updateOne({ sku: rows[i].sku, qty: { $inc: rows[i].qty } });
  }
});

• MySQL / PostgreSQL: the stored conflicting rows are locked with SELECT … FOR UPDATE, in primary-key order, before the rest is inserted. Only rows that exist are locked (no gap locks), so a concurrent transaction doing the same waits for yours to commit instead of deadlocking. A row that a concurrent writer commits while the call runs is still skipped correctly: PostgreSQL locks it after the insert; on MySQL it keeps the shared lock of the failed INSERT (a later update can then deadlock — retry, see below).
• SQLite, MongoDB, in-memory: accepted and ignored. SQLite's write lock already serializes writers; MongoDB has no row locks (a concurrent write to the same document aborts one transaction, which the driver retries).
• Outside a caller's transaction it is a no-op: the call's own transaction ends when it returns. Without onConflict: "ignore" it throws INVALID_QUERY.

Retry on DEADLOCK / LOCK_TIMEOUT

Since 0.1.153 a deadlock victim (MySQL errno 1213, PostgreSQL 40P01) and a lock wait timeout (MySQL 1205, PostgreSQL 55P03) throw DbError with code DEADLOCK / LOCK_TIMEOUT (PostgreSQL 40001: SERIALIZATION_FAILURE) and retryable: true (isRetryableDbError(error)), on any write — since 0.1.175 also on reads and at COMMIT, with SQLite's SQLITE_BUSY at COMMIT and MongoDB's exhausted transient-error retries mapped too. Retry the whole transaction — see Transactions.

Nested Creation

Both insertOne and insertMany support nested relation data — inserting related records across foreign keys in a single call. This is covered in Relations — Deep Operations.

Reading Records

Find by ID

Look up a single record by primary key:

const user = await users.findById(1);
// Returns the record or null

findById is flexible — it accepts:

• A scalar — tried against the primary key and every single-field unique index
• An object with primary-key fields, or with all fields of a compound unique index

Add @db.table.preferredId.uniqueIndex to a table to make a non-PK unique index the canonical id (e.g., slug). Scalar ids then resolve only against that index — no PK fallback — which keeps URLs and external references deterministic.

Which row an id addresses

An id always addresses exactly one row, primary key first (since 0.1.143). A scalar can be type-compatible with several identifications — with a string primary key next to a string unique slug, "abc" may be one row's id and another row's slug:

1. The identifications are tried in order — primary key first, then each unique index. The first one that matches a row wins, and that row is then addressed by its exact primary key.
2. An object id that carries the complete primary key resolves by the primary key alone, like a write payload.
3. When nothing matches, the id addresses no row (findById → null, deleteOne → { deletedCount: 0 }).

findById, deleteOne and the write guards' current() all follow this rule. deleteOne pins the row inside its transaction, so the guard, the cascade and the delete all see the same row.

Row scope. Pass { scope: filter } (a row-level overlay such as a tenant filter) to resolveRowFilter or deleteOne and only rows matching it count while the identifications are tried: an out-of-scope row can never shadow an in-scope one, so the answer is exactly what it would be if that row did not exist. resolveRowFilter still returns the first identification when nothing in scope matches — AND the scope onto its result before reading. deleteOne also applies the scope to the delete itself: an out-of-scope row is not deleted ({ deletedCount: 0 }, and the guard's current() is null).

To READ the addressed row under a scope, use findOneByRow (since 0.1.143): it resolves the id the same way and returns the row itself — scope applied to the result, your controls applied to the read — in one step, without a separate resolve-then-read:

const scope = { tenant: currentTenant() };
const row = await slugs.findOneByRow("abc", { scope, controls: { $select: ["id", "title"] } });
await slugs.deleteOne("abc", { scope });

// The same, by hand:
const filter = await slugs.resolveRowFilter("abc", { scope });
const same = filter ? await slugs.findOne({ filter: { $and: [filter, scope] } }) : null;

Public helpers that turn an id (or a write payload) into a filter or a row:

Method	Returns
resolveRowFilter(id, opts?)	Promise<FilterExpr | null> — the one-row filter described above (since 0.1.143). Use it whenever an id must name a single row, e.g. before AND-ing it with a read scope. null when the id fits no identification.
findOneByRow(id, opts?)	Promise<row | null> — the row that filter addresses, read with opts.controls; null when it is missing or outside opts.scope (since 0.1.143).
resolveIdFilter(id, opts?)	FilterExpr | null (sync) — the $or of every identification the id fits. It can match more than one row.
recordFilter(payload, opts?)	FilterExpr (sync, tables only) — the filter updateOne / replaceOne of that payload target: primary key when complete, else a unique index. Throws NOT_FOUND without identifying fields (since 0.1.143).

All take { isFieldVisible }: a unique index over a hidden field is skipped.

// rows: { id: "zz-b", slug: "abc" }, { id: "abc", slug: "a-own" }
await slugs.resolveRowFilter("abc"); // { id: "abc" }   — the primary-key row
await slugs.resolveRowFilter("a-own"); // { id: "abc" } — unique-key fallback, pinned to its PK
slugs.resolveIdFilter("abc"); // { $or: [{ id: "abc" }, { slug: "abc" }] }

Find One

Return the first record matching a filter:

const user = await users.findOne({
  filter: { email: "alice@example.com" },
});
// Returns the first match or null

Find Many

Return all records matching a filter, with optional sorting and pagination:

const active = await users.findMany({
  filter: { status: "active" },
  controls: {
    $sort: { name: 1 },
    $limit: 10,
    $skip: 0,
  },
});

For a full reference on filter operators and controls, see Queries & Filters.

findOne, findMany and findById also take { lock: 'update' | 'share', wait? } inside a transaction (since 0.1.162). This turns the call into a row-locking read. See Transactions — Locking reads.

Count

Count matching records without fetching data:

const total = await users.count({
  filter: { status: "active" },
});

Pass no arguments to count all records:

const allUsers = await users.count();

Find Many with Count

Get both data and total count in one call — useful for paginated UIs:

const { data, count } = await users.findManyWithCount({
  filter: { status: "active" },
  controls: { $limit: 10, $skip: 20 },
});
// data: first 10 records after skipping 20
// count: total matching records (ignoring $limit/$skip)

Updating Records

Update One

Partially update a record. The primary key field(s) must be included to identify the record — only the other provided fields are changed:

const result = await users.updateOne({
  id: 1,
  name: "Alice Smith",
});
// result: { matchedCount: 1, modifiedCount: 1 }

matchedCount is what the identifying filter (plus $cas, when present) matched at execution time — never assumed. A payload with only the identifying fields writes nothing and reports { 1, 0 } when the row exists or { 0, 0 } when it does not; an undefined value is treated as absent (since 0.1.128). See Update & Patch.

null clears an optional column (updateOne({ id, note: null }) → NULL), an omitted key keeps it. An optional field marked @meta.required (@meta.required note?: string) rejects null (since atscript 0.1.103): it can be left unset but not cleared — omit the key, or type the field string | null to make null a valid value. Since 0.1.128 the readable's filter types admit null for optional columns too (findMany({ filter: { note: null } }), { note: { $ne: null } }) — see Queries — Null Values.

Patch Operators & Field Operations

For atomic increments/decrements ($inc, $dec, $mul) and embedded array patch operators ($insert, $remove, etc.), see Update & Patch.

Optimistic concurrency

For tables that declare @db.column.version, add $cas to make the update conditional on the current row version:

const ok = await users.updateOne({
  id: 1,
  status: "active",
  $cas: { version: row.version },
});
// ok.matchedCount === 0 on stale-read OR missing row — caller retries.

See Optimistic Concurrency (Row Versioning) for the full reference.

Update Many

Update all records matching a filter:

const result = await users.updateMany(
  { status: "inactive" }, // filter
  { status: "archived" }, // data to set
);
// result: { matchedCount: 5, modifiedCount: 5 }

updateMany writes own fields only — a navigation field in the payload throws DbError("INVALID_QUERY"). The payload is validated in patch mode, like updateOne (since 0.1.162): a wrong type, null on a non-nullable field, an empty @meta.required field or a failed @expect.* constraint throws a ValidatorError before any statement runs. Every update and replace also sets @db.onUpdate.now fields (number.timestamp.updated) to the current time and increments @db.onUpdate.inc counters, overriding a value in the payload. An empty patch ({} or one that prunes to nothing) issues no UPDATE: it returns the honest match count with modifiedCount: 0 and bumps no version. Any other patch bumps each matched row, except version-exempt ones (updateMany never checks the version).

Replacing Records

Replace One

Replace an entire record by primary key. Unlike updateOne, all fields must be provided — missing fields are not preserved: an omitted optional column becomes NULL (absent on document stores) on every adapter. Since 0.1.128 this holds on SQLite, PostgreSQL and MySQL too — their UPDATE-based replace now assigns every column (a column whose function default the engine owns, e.g. a native @db.default.now, is reset to its DEFAULT); before, an omitted column silently kept its old value there.

A top-level number.timestamp.created is the exception (since 0.1.165, also as number.timestamp.created | null): it is written by the insert only. A nested one (audit: { at: number.timestamp.created }) is part of its object and is replaced with it. A replace keeps the stored value whether or not the payload carries the field, and no replace or patch (updateOne, updateMany, replaceMany, HTTP PUT / PATCH) can overwrite it. Before 0.1.165 a replace that left it out reset it to the current time.

const result = await users.replaceOne({
  id: 1,
  email: "alice.new@example.com",
  name: "Alice Smith",
  status: "active",
});

Replace vs. Update

• updateOne — sends only the fields you want to change (partial)
• replaceOne — replaces the entire record with new data (full)

replaceOne also supports $cas on versioned tables — same semantics as updateOne.

Replace Many

Replace every record matching a filter with the same payload — the filter-based sibling of updateMany. Unlike replaceOne, it does not null-fill: on every adapter it assigns the given columns on each matched row (a $set-style merge — SQL UPDATE … SET, MongoDB updateMany + $set, memory merge), so omitted optional fields keep their stored values. All required fields must still be provided (the payload is validated in replace mode); a versioned table bumps version on every matched row:

const result = await users.replaceMany(
  { status: "inactive" },
  { email: "archived@example.com", name: "Archived User", status: "archived" },
);
// result: { matchedCount, modifiedCount }

The primary key is not required in the payload — the filter picks the rows (since 0.1.162; before, the payload was not validated at all). Navigation fields are rejected, as in updateMany.

For replacing many records with different payloads (each identified by its primary key), use bulkReplace instead.

Deleting Records

Delete One

Delete a single record by ID:

const result = await users.deleteOne(1);
// result: { deletedCount: 1 }

deleteOne accepts the same flexible ID format as findById — primary key, composite key object, or unique index value — and deletes the one row that id addresses, primary key first. An optional { guard } runs inside the delete's transaction once the id has resolved to a filter — see Write guards.

Delete Many

Delete all records matching a filter:

const result = await users.deleteMany({
  status: "archived",
});
// result: { deletedCount: 12 }

Cascade & Set-Null

When a deleted record is referenced by other tables via foreign keys, cascade and set-null behaviors are handled automatically based on @db.rel.onDelete annotations. See Relations for details.

Bounded Writes — limit

deleteMany and updateMany take an optional { limit, sort } (since 0.1.162) that bounds one call to the first limit matching rows — for retention sweeps and backfills that should keep each statement, its locks and its replication footprint small:

// delete expired sessions 1000 at a time
while (
  (await sessions.deleteMany({ expiresAt: { $lt: Date.now() } }, { limit: 1000 })).deletedCount
) {}

// archive the 500 oldest open tasks
await tasks.updateMany(
  { status: "open" },
  { status: "archived" },
  { limit: 500, sort: { createdAt: 1 } },
);

• limit — a positive integer; anything else throws DbError("INVALID_QUERY") (errors[0].path === "limit"). Without it the call is unbounded, as before.
• sort — $sort shape; it picks which rows go first. The default is the primary key ascending, and the primary key is always appended as the final tie-breaker (in the direction of the last key), so the order is deterministic. sort without limit throws.
• Loop until the count is 0, not until it is below limit — for updateMany only when the patch moves the rows out of the filter (status: "open" → "archived"), or the loop never ends. Under concurrency a round can touch fewer than limit rows while matches remain: another transaction may delete or change the rows it picked between the pick and the write. A round never exceeds limit.
• 0 means nothing matched; a retryable error when contention persists. When a round touches no rows although a row still matches the filter (it lost every picked row to a concurrent writer), it runs again, up to three more times; the checks and the retries run in the caller's transaction when there is one. If every attempt loses while rows still match, it throws DbError("SERIALIZATION_FAILURE") (retryable: true, errors[0].path === "limit") — retry the call, or the whole transaction inside one (Retrying). So 0 never comes from a race, and a loop until 0 does not stop early. MySQL's and SQLite's native statements take the next matching rows themselves and need no retry.
• Requires a primary key — a table without one throws INVALID_QUERY.
• Everything else is unchanged — filter guards, payload validation, version bumps and exemption, @db.onUpdate.* stamps; $cas stays rejected on updateMany.
• Not exposed over HTTP — moost-db has no bulk delete or filter-update endpoint.

Adapter	How the bound runs
MySQL	Native DELETE / UPDATE … ORDER BY … LIMIT n (the unique order keeps it safe for statement-based replication)
PostgreSQL	One statement re-keyed on the primary key: … WHERE f AND (pk) IN (SELECT pk … WHERE f ORDER BY … LIMIT n)
SQLite	One statement: … WHERE f AND rowid IN (SELECT rowid … ORDER BY … LIMIT n)
MongoDB, memory	The first n keys are read in order, then the write targets the filter and those keys (inside the caller's transaction / session when there is one)

With application-level cascades (MongoDB, memory) a bounded deleteMany pins the keys first, inside its transaction: the cascade and the delete act on exactly those rows, and only while they still match the filter (a row a concurrent writer moved out of it is left alone). Native foreign-key cascades are left to the database.

Bulk Operations

For batched writes that apply different changes to each record (vs. updateMany, which applies the same change to many rows), use bulkUpdate and bulkReplace. Both accept an array of payloads (each identified by its primary key) and an optional { maxDepth, guard } options object (see Write guards), and they participate in the surrounding transaction.

Method	Purpose
bulkUpdate(items, opts?)	A different patch per row
bulkReplace(items, opts?)	A different full replacement per row
touchMany(keys, opts?)	Batch versioned touch (since 0.1.129, versioned tables only). See Versioning — Batch touch

import { $dec } from "@atscript/db/ops";

await products.bulkUpdate([
  { id: 1, stock: $dec(2) },
  { id: 2, stock: $dec(5) },
  { id: 3, stock: $dec(1) },
]);

await users.bulkReplace([
  { id: 1, email: "alice.new@example.com", name: "Alice" /* …all fields */ },
  { id: 2, email: "bob.new@example.com", name: "Bob" /* …all fields */ },
]);

See Update & Patch for the full operator catalog and per-payload options.

Nested writes need @db.depth.limit

Insert / replace / patch payloads that nest into @db.rel.from or @db.rel.via children are rejected at the validator boundary unless the table declares @db.depth.limit N with N >= 1. The default — annotation absent — is 0, which blocks every nested write. See Relations — Deep Operations.

Write Guards

Since 0.1.128 every keyed write — insertOne / insertMany, replaceOne / bulkReplace, updateOne / bulkUpdate — accepts { guard } in its options, and deleteOne(id, { guard }) too. The table invokes the guard exactly once, inside its own transaction, after undefined props were pruned, defaults applied, the rows normalized and validated, and before encryption and the nested-relation phases. It is the validated stage: what the guard sees is what the table is about to write.

import type { TDbWriteGuardContext } from "@atscript/db";

await orders.insertMany(rows, {
  guard: async (ctx: TDbWriteGuardContext<Order>) => {
    // ctx.action: 'insert' | 'insertMany' | 'replace' | 'replaceMany' | 'update' | 'updateMany'
    for (let i = 0; i < ctx.rows.length; i++) {
      const row = ctx.rows[i]; // validated, defaults applied (insert / replace), `$cas` removed (update)
      row.updatedBy = currentUserId(); // enrich in place — the table validates the rows again
      const before = await ctx.current(i); // lazy, memoised pre-image read inside the transaction
      if (before?.locked) throw new Error("locked"); // rolls the transaction back, propagates unchanged
      // ctx.expectedVersions[i]: the version a `$cas` predicate expects, or undefined
    }
  },
});

await orders.deleteOne(id, {
  guard: async (ctx) => {
    // ctx.id, ctx.filter (the resolved identifying filter), ctx.current() → the row or null
    if ((await ctx.current())?.isFallback) throw new Error("cannot delete the fallback row");
  },
});

• ctx.current(i) reads the row exactly as the write identifies it — by its primary key, else by a unique index — so it is always the row the write targets. It never throws for a row without an identifying key (an auto-increment insert): it resolves to null.

• ctx.currentAll() (since 0.1.143) reads every row's pre-image in one findMany and resolves to an array parallel to ctx.rows — each entry exactly what current(i) would give. It fills the same per-index memo: an index current(i) already read is reused, and a later current(i) reads nothing. Prefer it to a current(i) loop on batches.

• ctx.filterFor(i) (since 0.1.143) is the exact filter the write identifies rows[i] by — its primary key, else the unique index it carries, each naming at most one row — or null when the row has no identifying key yet. It is the filter current(i) reads by, memoised per index on first use (change a row's identifying fields before asking, not after). It lets a guard check a whole batch in the database without reading it:

  // Row-level USING: every row this update targets must be the caller's.
  guard: async (ctx) => {
    const filters = ctx.rows.map((_, i) => ctx.filterFor(i)).filter((f) => f !== null);
    if (filters.length === 0) return;
    const distinct = new Set(filters.map((f) => JSON.stringify(f))).size;
    const owned = await orders.count({ filter: { $and: [{ $or: filters }, { ownerId: me }] } });
    if (owned !== distinct) throw new Error("row outside your scope"); // missing rows count as misses
  };

• A delete guard's ctx.filter is the exact filter the delete targets (primary key first).

• A throw rolls the table's transaction back — including anything the guard itself wrote through other tables that joined it — and the same error propagates to the caller.

• The guard runs for the root call only, never for the nested re-entries a deep write performs on related tables.

• An id that resolves to no filter makes deleteOne answer { deletedCount: 0 } without calling the guard.

• In @atscript/moost-db, overriding guardWrite / guardRemove on a controller passes that override as the guard — see Customization — Write Hooks.

Post-write Check

Since 0.1.143, insertOne / insertMany, replaceOne / bulkReplace and updateOne / bulkUpdate accept { check }. A guard sees the rows before the write. A check sees what was actually written, through the database's own filter semantics. Use it for a row-level "WITH CHECK" policy: every written row must still match a filter.

The table invokes the check exactly once per call, inside its transaction, after the main write and every nested-relation phase. It receives a TDbWriteCheckContext:

Field	Meaning
action	'insert' · 'insertMany' · 'replace' · 'replaceMany' · 'update' · 'updateMany'
filters	One exact primary-key filter per written row, de-duplicated. Inserted rows appear by their resulting key, auto-increment and generated ids included. Updated / replaced rows appear by the key of the row the write targeted. Rows the write matched nothing for are absent.
transactional	true when a throw rolls the write back. false on adapters whose transaction is a pass-through (the memory adapter, a standalone MongoDB): the write is already durable.
count(filter)	Counts matching rows inside the same transaction.

import type { TDbWriteCheckContext } from "@atscript/db";

// Row-level WITH CHECK: every row this call wrote must belong to the caller's tenant.
const withCheck = async (ctx: TDbWriteCheckContext) => {
  if (ctx.filters.length === 0) return;
  const inScope = await ctx.count({ $and: [{ $or: ctx.filters }, { tenant: currentTenant() }] });
  if (inScope !== ctx.filters.length) {
    throw new Error("row outside your tenant"); // rolls back, propagates unchanged
  }
};

await tasks.updateOne({ id: 1, title: "Renamed" }, { check: withCheck }); // ok
await tasks.updateOne({ id: 1, tenant: "other" }, { check: withCheck }); // throws, nothing written

• A throw rolls the table's transaction back and propagates unchanged — only when ctx.transactional is true. Without a real transaction, a caller that needs a hard guarantee must validate before the write: for inserts and replaces, the full rows; for updates, the patched fields.
• Like the guard, the check runs for the root call only. The nested re-entries a deep write performs on related tables get neither guard nor check nor isFieldVisible. See Nested write rules.
• In @atscript/moost-db, overriding checkWrite on a controller passes that override as the check.

Validation

Tables automatically validate data on every write operation using constraints from your .as definitions (@expect.* annotations). Validation is purpose-aware:

Purpose	Used by	Behavior
'insert'	insertOne, insertMany	PK, defaulted, and FK fields become optional
'bulkUpdate'	updateOne, bulkUpdate, updateMany	Top level is partial; merge-strategy objects partial, replace-strategy objects require all fields
'bulkReplace'	replaceOne, bulkReplace, replaceMany	All non-optional fields required (replaceMany: except the primary key)
'patch'	Available for manual checks	Fully partial; useful when you need to validate a partial payload yourself

Since 0.1.162 updateMany and replaceMany validate their payload too (before, only foreign-key references were checked, and a bad value reached the database — a silent coercion or a constraint error). See Upgrading.

To validate rows the way a write would — without writing them (imports, dry runs) — use validateRows (since 0.1.165). Each row is cloned, given the defaults and stamps that write applies, normalized and validated; the result lists every problem with the row's index, [] when all rows are valid:

const problems = await users.validateRows(rows); // mode: "insert" (default) | "replace" | "patch"
// [{ index: 3, path: "email", message: "Expected string, got undefined", code: "TYPE", params: {…} }, ...]

const withFks = await users.validateRows(rows, "insert", { fk: true });
// also: { index, path: "teamId", message: 'References a non-existent record in "teams" (7)',
//         code: "FK_MISSING", params: { fields: ["teamId"], target: "teams", values: "7" } }

Each problem carries what the same write's error carries for it (since 0.1.173): the validator's code and params (custom: true when the message is your own annotation text, details for a union), a DbError code such as DEPTH_EXCEEDED, or FK_MISSING. A translator renders them like a write's errors. To answer a dry run over HTTP, throw them as a ValidatorError with the row index in the path — the same paths a bulk write reports — and moost-db answers the usual 400, translated per request:

import { ValidatorError } from "@atscript/typescript/utils";

const problems = await this.table.validateRows(rows);
if (problems.length > 0) {
  throw new ValidatorError(
    problems.map(({ index, ...e }) => ({ ...e, path: `[${index}].${e.path}` })),
  );
}

fk: true looks every non-null foreign key up — on every adapter, also those that leave foreign keys to the engine at write time. Rows that already failed type validation are not looked up. The lookup reads the existing target keys in chunks of 500 distinct keys, so its cost grows with the number of distinct keys, not with missing ones, and large batches stay under every bind-parameter limit. The caller's rows are not modified.

For a single object, the validators are available directly. Called without a context, a standard purpose's validator validates in that write's mode (since 0.1.165 — before, getValidator("insert") rejected rows without their primary key, defaulted, foreign-key or navigation fields):

const validator = users.getValidator("insert");
if (!validator.validate(data, true)) {
  // safe = true → returns false instead of throwing
  console.log(validator.errors);
  // [{ path: 'email', message: 'Required field' }, ...]
}

getValidator does not apply defaults or normalizers; validateRows does.

Normalization: @normalize.*

Since 0.1.170 every write applies the type's @normalize.trim, @normalize.lowercase and @normalize.uppercase annotations and stores the result. The order is defaults → normalize → validate: a value is validated as it will be stored, so @expect.pattern '^[a-z0-9@.]+$' on a lowercased email accepts " Ann@Example.COM " and stores "ann@example.com".

@db.table 'users'
export interface User {
    @meta.id
    @db.default.increment
    id: number

    @db.index.unique 'user_email_idx'
    @normalize.trim
    @normalize.lowercase
    email: string.email

    @normalize.trim
    name: string
}

await users.insertOne({ email: "  Ann@Example.COM ", name: " Ann " });
// stored: { id: 1, email: "ann@example.com", name: "Ann" }
await users.insertOne({ email: "ann@example.com", name: "Twin" }); // CONFLICT — same stored key

What is normalized:

• Every write path: insertOne / insertMany (also with onConflict: "ignore"), replaceOne / bulkReplace / replaceMany, updateOne / bulkUpdate / updateMany, and validateRows. Over HTTP, moost-db's POST / PUT / PATCH write through these, so they are covered too.
• Patches: plain values, nested objects, and the items of the array operators — $replace, $insert, $upsert, and the $update / $remove items whose keys find array elements ($remove: [" RED "] removes the stored "red"). $inc / $dec / $mul are left as they are.
• Nested writes: rows written through @db.rel.to, @db.rel.from and @db.rel.via — on insert and replace, and in patch operators ($insert rows, $update patches) — once, at the top-level call.
• Defaults: a field's @db.default is normalized once, so a nested row's default is stored and validated like a direct insert's, and the column DEFAULT schema sync writes (and the value MongoDB fills a new field with) is the normalized value too — see Static Defaults.
• Keys: a patch addressed by a unique field ({ email: " ANN@example.com", … }) finds the row by the normalized value, and so do recordFilter(payload) and the keys of touchMany.

The caller's payload is never changed: the table normalizes a copy, and only the objects that change are copied. The cost is near-zero: a type that reaches no @normalize.* annotation is not walked at all; a type that reaches one only through a relation (a @db.rel.to to a table with a normalized field) walks each row once, which is small next to the write itself. A write guard sees the normalized rows; values it sets are validated but not normalized again.

Lookups

Equality lookups compare the normalized value too, so a lookup by what a user typed finds what the write stored:

await users.findMany({ filter: { email: " Ann@Example.COM" } }); // finds "ann@example.com"
await users.findById("ANN@example.com"); // by the unique email

• Normalized: the operands of implicit equality, $eq, $ne, $in and $nin on a field with @normalize.* (an array field's element operand by its element type) — in the filters of reads, counts, aggregates, updateMany / deleteMany, $with filters and relation predicates ($some / $none); and every id: findById, resolveRowFilter / resolveIdFilter (so deleteOne and moost-db's GET /one/:id), composite and unique-key ids. moost-db's query string goes through the same filters.
• Compared as given: $regex, $search / fulltext, range operators ($gt, $gte, $lt, $lte), $exists, aggregate conditions (count(*|email=…)) and $having.
• Compared as stored: the keys the table reads from stored rows itself — relation loading ($with), application-level cascades and RESTRICT checks, and a row it addresses by the primary key it just read (a delete by a unique key, for example). A row written before its key field gained @normalize.* is still loaded, cascaded and deleted with the rows that reference it.

Rows written before 0.1.170 are not rewritten — see Upgrading for what that means for lookups and the data step that rewrites them.

table.normalizePayload(payload, mode) (since 0.1.173) returns a payload — one row, or an array of rows — as a write of mode stores it: copy-on-change, nothing validated and no defaults applied. moost-db's onWrite receives the request body this way. normalizeWritePayload(type, payload, mode, opts?) from @atscript/db (also @atscript/db/validator) applies the same rules to one payload — mode is "insert", "replace" or "patch"; opts are the validator options the result will be validated with (dbValidatorOptions(type, mode)), used to pick between union members.

Stored JSON values under a tightened type

Every write validates the whole value it carries, so tightening a nested type stored in a @db.json column (a new @expect.maxLength, a narrower union, a renamed property) makes the next write of an old row fail — for example an update that carries the stored value back unchanged. Rewrite the stored rows once, with a data step that ships with the schema change:

await syncSchema(db, types, {
  dataSteps: [
    {
      id: "2026-11-shorten-comment-text",
      async run(space) {
        const posts = space.getTable(Post);
        for (const post of await posts.findMany({ filter: {}, controls: {} })) {
          const comments = post.comments.map((c) => ({ ...c, text: c.text.slice(0, 2000) }));
          await posts.updateOne({ id: post.id, comments });
        }
      },
    },
  ],
});

To write a row exactly as given — a legacy row in a test, a migration that must bypass the model — use the adapter: space.getAdapter(Post).insertOne(row). It applies no defaults, no validation and no encryption, and takes the row as stored: physical column names (@db.column) and, on the SQL adapters, a nested object as its flattened parent__child columns.

Error Handling

Database operations throw DbError with a code property indicating the error type:

Code	Meaning
CONFLICT	Unique constraint violation; a nested write that names a related row outside the record's relation (since 0.1.143, see Nested write rules)
FK_VIOLATION	Foreign key constraint violated
NOT_FOUND	Record not found
CASCADE_CYCLE	Circular cascade detected
INVALID_QUERY	Malformed query or filter
DEPTH_EXCEEDED	Nested-write payload deeper than @db.depth.limit N (also a DepthLimitExceededError)
VERSION_COLUMN_WRITE	Direct write to a @db.column.version column — use $cas instead. See Versioning
CAS_EXHAUSTED	withOptimisticRetry exhausted maxAttempts (also a CasExhaustedError). See Versioning
CAS_MISMATCH	touchMany (require: 'all') found a stale or missing key: refused before the first write, or rolled back on SQL (also a CasMismatchError). HTTP 409. See Versioning
BUCKET_NOT_SUPPORTED	The adapter cannot group by the requested calendar bucket unit, or has no calendar buckets at all. HTTP 400. Since 0.1.132
BUCKET_TZ_UNAVAILABLE	The database cannot convert to the calendar bucket's time zone (e.g. MySQL time zone tables not loaded). HTTP 501. Since 0.1.132
AGG_FN_NOT_SUPPORTED	A grouped query uses an aggregate function the adapter does not render (e.g. countDistinct on a custom adapter). HTTP 400
AGG_EXPR_NOT_SUPPORTED	A grouped query uses an arithmetic entry and the adapter's supportsAggregateExpressions() is false (a custom adapter). HTTP 400. Since 0.1.148
LOCK_NOT_SUPPORTED	A locking read on an adapter that cannot lock rows (MongoDB). HTTP 501. Since 0.1.162
ISOLATION_NOT_SUPPORTED	withTransaction(fn, { isolation }) asked for a level the adapter cannot provide (MongoDB serializable, or any level on a standalone MongoDB). HTTP 501. Since 0.1.162 — see Isolation level

Handle errors by checking the code:

import { DbError } from "@atscript/db";

try {
  await users.insertOne({ email: "alice@example.com", name: "Alice" });
} catch (err) {
  if (err instanceof DbError) {
    switch (err.code) {
      case "CONFLICT":
        console.log("Email already exists:", err.errors);
        break;
      case "FK_VIOLATION":
        console.log("Referenced record missing:", err.errors);
        break;
    }
  }
}

Each error includes an errors array with { path, message } entries for detailed diagnostics.

Constraint violations

A unique or foreign-key violation — whether the engine or the core reports it — has one entry per field of the violated constraint, each with a code and params for translating it (since 0.1.165). Match on the code, not on the message text; the engine's own text is kept in the entry's details for logs (moost-db never sends it to clients).

Entry code	Error code	params	When
UNIQUE	CONFLICT	{ index, fields } (index: the declared unique-index name, or "primaryKey")	A unique key or the primary key already holds the value — compound keys: one entry per field
FK_MISSING	FK_VIOLATION	{ fields, target } (+ ambiguous: true when the adapter could not tell which key failed)	A foreign key names a row that does not exist — only the failing key's fields
FK_RESTRICT	CONFLICT	{ table, referencedBy?, fields? }	A delete is blocked by referencing rows (path is the table name)

catch (err) {
  if (err instanceof DbError && err.code === "CONFLICT") {
    for (const e of err.errors) {
      if (e.code === "UNIQUE") form.setError(e.path, t("unique", e.params));
    }
  }
}

Error Paths in Nested Data

When validation fails inside nested or array payloads, error paths use dot notation to pinpoint the exact location:

Context	Example path	Meaning
Top-level field	"title"	The title field failed validation
TO navigation	"project.title"	The title field inside inline project data
FROM array element	"comments.0.body"	The body field of the first comment in the array
Deep nesting	"tasks.2.project.title"	The title of the project in the third task

This makes it straightforward to map errors back to specific fields in complex nested payloads — useful for building form validation UIs.

Next Steps

• Queries & Filters — Advanced filtering, sorting, and projection
• Update & Patch — Embedded array and object patch operators
• Transactions — Atomic multi-table operations
• Relations — Deep Operations — Nested creation and replacement across relations