Mutations

The Writing data section of the README is the short tour. This page is the full reference for every write verb forge exposes, the dialect SQL each one compiles to, and the patterns that keep those writes correct under load.

The shape is intentionally Prisma-flavoured: create, createMany, update, updateFirst, updateMany, upsert, delete, deleteFirst, deleteMany, plus atomic number ops and nested writes. The mapping to driver-level statements is adapter-specific and described below.

Contents

• create vs createMany
• update vs updateMany
• upsert
• A filter whose values all vanished is refused
• delete vs deleteMany
• updateFirst and deleteFirst — the row, or null
• Atomic number ops
• List column ops — push, addToSet, pull
• set vs unset
• Nested writes
• createMany + skipDuplicates
• Returning shape
• Idempotency
• Optimistic concurrency
• Pessimistic locking
• Batched throughput
• Mutation events
• Worked patterns

---

create vs createMany

The asymmetry between the two is small but load-bearing.

Verb	where	data	Returns	Per-row work
create	n/a	single object	the inserted row (all columns)	per-call
createMany	n/a	array of objects	{ count: number }	batched

const row = await db.user.create({
  data: { email: 'a@x.co', name: 'Alice' },
});
// → { id: 'u_…', email: 'a@x.co', name: 'Alice', created_at: …, updated_at: … }

const { count } = await db.user.createMany({
  data: [
    { email: 'b@x.co', name: 'Bea' },
    { email: 'c@x.co', name: 'Cleo' },
  ],
});
// → { count: 2 }

Choose createMany when you do not need the inserted rows back. The difference is one round-trip vs N, and on Postgres / MySQL one parsed statement vs N. If you do need the rows back from a bulk insert, see Returning shape — Postgres can do it in one trip, SQLite 3.35+ can do it in one trip, MySQL and MSSQL cannot.

What each adapter compiles to

Adapter	create	createMany
postgres	INSERT INTO … VALUES (…) RETURNING *	INSERT INTO … VALUES (…),(…),… (one statement)
mysql	INSERT INTO … VALUES (…) + SELECT	INSERT INTO … VALUES (…),(…),…
sqlite	INSERT INTO … VALUES (…) RETURNING *	INSERT INTO … VALUES (…),(…),… (one statement)
duckdb	INSERT INTO … VALUES (…) RETURNING *	INSERT INTO … VALUES (…),(…),…
mssql	INSERT INTO … OUTPUT INSERTED.* VALUES	INSERT INTO … VALUES (…),(…),… (rowcount only)
mongo	insertOne	insertMany

Postgres, SQLite 3.35+, DuckDB and MSSQL all support RETURNING / OUTPUT clauses on a single-row insert, so create is one round-trip on every SQL dialect. MySQL has no RETURNING, so create does an INSERT followed by a SELECT keyed on the new row's auto-id / primary key — still one logical call but two statements on the wire.

ID generation timing

Identifiers are generated client-side, before the insert leaves the process. f.id() defaults to a sortable 26-character ULID-style ID, and the value is stamped onto data if it is missing. This means:

• The ID is known before the row commits — you can return it from a handler, log it, enqueue follow-up work referencing it, all without waiting on the database round-trip.
• createMany does not need RETURNING to learn the IDs — they were generated locally and are visible on the input rows.
• Auto-incrementing primary keys (declared with f.id({ type: 'bigserial' })) are the exception — those are assigned by the database, and create reads them back via RETURNING / OUTPUT / the follow-up SELECT.

See Picking a primary-key strategy for when to use which.

---

update vs updateMany

Verb	where	Rows affected	Returns	Notes
update	any filter	exactly one	the updated row	throws if no row matched
updateFirst	any filter	exactly one	the updated row or null	a miss is null, not a throw (2.20.0)
updateMany	any filter	every match	{ count: number }	zero matches is a no-op, not an error

await db.user.update({
  where: { id: 'u1' },
  data:  { name: 'Alice 2' },
});

const { count } = await db.user.updateMany({
  where: { active: false, last_seen: { lt: cutoff } },
  data:  { archived: true },
});

update's where is a full filter, not a unique selector

update takes the same where type as findMany — WhereInput, every operator, every field. It is not narrowed to the model's unique fields, at compile time or at runtime. Nothing checks that your selector is unique, and InferWhereUnique is only Partial<WhereInput>.

What "single-row" means is the number of rows written, not the shape of the filter. Given a filter that matches many rows, update writes one of them, and which one is unspecified — there is no ORDER BY in the emitted statement:

// Legal, and it compiles. One row of the many gets archived.
await db.post.update({ where: { score: { gte: 0 } }, data: { archived: true } });

On Postgres that is UPDATE … WHERE ctid = (SELECT ctid FROM … WHERE … LIMIT 1) (SQLite rewrites the same shape onto its hidden rowid); on Mongo it is findOneAndUpdate. So a non-unique filter on update is not a type error — it is a silent coin toss over which row you wrote. Pass a selector you know identifies one row, or use updateMany when "however many match" is what you mean.

updateMany then re-reading the row is two round trips

update already returns the updated row — every adapter either uses RETURNING / OUTPUT or, on MySQL, follows the write with a SELECT, and Mongo compiles to findOneAndUpdate … returnDocument: 'after'. So this pattern:

await db.user.updateMany({ where: { id }, data: { name } });   // round trip 1
const user = await db.user.findFirst({ where: { id } });       // round trip 2

costs twice what it needs to, and the re-read is not even the row you wrote — another writer can land in between. Write it as one call:

const user = await db.user.update({ where: { id }, data: { name } });

Reach for updateMany when you genuinely want every match updated and the count is the answer. Reach for update when you want one row back and the row must exist, and updateFirst when you want one row back and "not found" is a legitimate answer — that last case is what the two-trip shape above was really working around, and on a soft-deleting patch the re-read returns null however many trips you spend.

What each adapter compiles to

Adapter	update	updateMany
postgres	UPDATE … SET … WHERE … RETURNING *	UPDATE … SET … WHERE … (rowcount)
mysql	UPDATE … SET … WHERE … + SELECT	UPDATE … SET … WHERE … (affectedRows)
sqlite	UPDATE … SET … WHERE … RETURNING *	UPDATE … SET … WHERE … (changes())
duckdb	UPDATE … SET … WHERE … RETURNING *	UPDATE … SET … WHERE … (rowcount)
mssql	UPDATE … SET … OUTPUT INSERTED.* WHERE …	UPDATE … SET … WHERE … (rowcount)
mongo	findOneAndUpdate	updateMany

There is no updateManyAndReturn on the wrapper. If you need the rows back after a bulk update, do the updateMany and a findMany of the same filter inside one $transaction — or, on Postgres only, drop to db.$queryRaw for UPDATE … RETURNING * and parse the result yourself.

Why update throws on no match

An update is asserting that a row to write exists. Letting that silently no-op masks application bugs: the handler thinks it wrote, the database disagrees, and the next read shows stale data.

When "match or not" is a valid outcome, say so with the verb instead: updateFirst returns the row or null in one round trip, and updateMany returns the count when "however many match" is what you mean.

---

upsert

upsert takes three blocks: where, create (used when no row matches), and update (used when one does). Forge compiles to a single atomic statement on every adapter — there is no read-then-write that could race.

where is a full filter, like everywhere else. What upsert needs from it is that its equality leaves name a real unique constraint, because those columns become the conflict target. Forge does not verify that; the database does, or does not — see Constraints on the conflict target.

await db.user.upsert({
  where:  { email: 'a@x.co' },
  create: { email: 'a@x.co', name: 'A', plan: 'free' },
  update: { last_seen: new Date() },
});

The conflict target is inferred from where: forge walks the equality-leaves of the where tree and the resulting column set becomes the ON CONFLICT (…) / ON DUPLICATE KEY UPDATE / MERGE … ON target. Both single-column and AND-of-eq compound selectors are supported.

Adapter	Compiled to
postgres	INSERT … ON CONFLICT (…) DO UPDATE SET … RETURNING *
mysql	INSERT … ON DUPLICATE KEY UPDATE … + SELECT
sqlite	INSERT … ON CONFLICT (…) DO UPDATE SET … RETURNING *
duckdb	INSERT … ON CONFLICT (…) DO UPDATE SET … RETURNING *
mssql	MERGE INTO tgt USING (VALUES …) src ON … WHEN MATCHED …
mongo	updateOne(filter, update, { upsert: true })

MSSQL MERGE shipped in 2.5.0 (see CHANGELOG.md); the previous 2.3 / 2.4 path threw NotImplemented. The MERGE form supports set, increment (via COALESCE(tgt.col, 0) + N), multiply and unset (= NULL) on the WHEN MATCHED branch, and returns the row via OUTPUT INSERTED.*.

Mongo's upsert: true covers the same semantics, but the rule for what the upserted document contains is different: Mongo merges $set / $setOnInsert against the filter, and forge fills $setOnInsert with any field from create not also in update. The net effect is the same "update if found, create if not" — just expressed in Mongo's update doc.

Constraints on the conflict target

The eq-leaves you pass in where must correspond to either the primary key, a UNIQUE constraint, or a unique index. On Postgres / SQLite / DuckDB this is enforced by the dialect itself (the optimiser will refuse ON CONFLICT (col) for a non-unique col). On MySQL the constraint must exist or ON DUPLICATE KEY UPDATE will trigger on the wrong index. On MSSQL the MERGE join can in principle be non-unique, but forge still requires uniqueness to keep semantics consistent across dialects.

If the where includes both an eq-leaf on a unique column and other non-eq filters (e.g. active: true), the non-eq filters are ignored for the conflict target — they would not be valid in ON CONFLICT. Use updateMany when you actually need a compound filter.

---

A filter whose values all vanished is refused

New in 2.18.0, and it is the one behaviour change in this document that can break working code — deliberately, because the code was not working.

undefined is skipped everywhere in the filter surface. That is what makes an optional filter work:

where: { status: maybeStatus }        // maybeStatus undefined → don't filter on status

But when every value in a filter was undefined, the filter disappeared entirely and the statement applied to the whole table:

await db.account.deleteMany({ where: { tenant_id: req.user?.tenantId } });
// Before 2.18.0:  DELETE FROM "accounts"     ← no WHERE at all

One optional chain that returned undefined emptied a tenant table. The single-row verbs were worse rather than safer, because they still constrain to one row: delete({ where: { id: maybeId } }) compiled to WHERE ctid = (SELECT ctid FROM "accounts" LIMIT 1) and destroyed an arbitrary row.

Every builder — findMany, findFirst, findUnique, count, groupBy, update, updateMany, delete, deleteMany — now throws when a filter was written and none of its values survived:

[forge] deleteMany on 'accounts' was given a filter whose every value is
undefined (tenant_id), so it would apply to EVERY row.
  This is almost always an optional value that came back undefined — check
  the source of 'tenant_id'.
  To act on every row on purpose, omit `where` entirely.

The rule is exactly "keys were written, and none of them survived":

Call	Meaning
where omitted	every row — allowed
where: {}	every row — allowed
where: { a: 'x', b: undefined }	filters on a — allowed
where: { a: undefined }	throws

Partial undefined is untouched, which is the whole point of the feature — a query builder that conditionally adds filters keeps working. And an omitted where, or an explicit where: {}, is how you say "every row" on purpose:

await db.session.deleteMany();              // truncate, deliberately
await db.session.deleteMany({ where: {} }); // the same, said out loud

So the fix in a handler is not to reach for where: {} — it is to check the value before you get here:

const tenantId = req.user?.tenantId;
if (!tenantId) throw new Forbidden();
await db.account.deleteMany({ where: { tenant_id: tenantId } });

This is a deliberate divergence from Prisma, which allows the all-undefined case and applies it to the whole table.

---

delete vs deleteMany

Same asymmetry as update.

Verb	where	Rows affected	Returns	Notes
delete	any filter	exactly one	the deleted row	throws if no row matched
deleteFirst	any filter	exactly one	the deleted row or null	deleting something already gone is not an error (2.20.0)
deleteMany	any filter	every match	{ count }	zero matches is a no-op

delete's where is a full filter too, with the same consequence: a selector matching many rows deletes one unspecified row rather than raising a type error.

await db.user.delete({ where: { id: 'u1' } });
await db.user.deleteMany({ where: { archived: true, deleted_at: { lt: cutoff } } });

On SQL, delete uses DELETE … RETURNING * (Postgres / SQLite / DuckDB), OUTPUT DELETED.* (MSSQL), or SELECT + DELETE (MySQL) to return the row. On Mongo, delete is findOneAndDelete and deleteMany is deleteMany.

Cascading

Relations declared with onDelete: 'Cascade' (see Relations) cascade to children when a parent is deleted. On SQL this is a database-enforced FOREIGN KEY … ON DELETE CASCADE, so the cascade happens inside the statement. On Mongo there are no foreign keys; forge walks the schema graph at delete time and issues the child deleteMany calls. The Mongo path is best-effort — it cannot be made transactional outside a replica-set session.

Other onDelete modes (SetNull, Restrict, NoAction) behave the same way: enforced by the database on SQL, simulated by forge on Mongo.

See docs/RELATIONS.md for the full cascade matrix when the doc lands; until then the README section is the authoritative source.

---

updateFirst and deleteFirst — the row, or null

New in 2.20.0. Two verbs that do what update and delete do, and differ from them in exactly one place: the miss.

const user = await db.user.updateFirst({ where: { id }, data: { name } });
if (!user) return res.status(404).end();       // nothing matched, nothing threw

await db.session.deleteFirst({ where: { token } });   // already gone? fine.

updateFirst takes the same arguments as update — where, data, and optionally select / include / omit — writes one row, and returns it. When the filter matched nothing it returns null where update throws a DbKnownError with code P2025. deleteFirst stands in the same relation to delete. Neither narrows where to unique columns, for the same reason update does not: a filter that matches many rows writes one unspecified row.

The four ways to write one update

Call	Rows written	Returns	Round trips
update({ where, data })	one	the row, or throws P2025	1
updateFirst({ where, data })	one	the row, or null	1
updateMany({ where, data })	every match	{ count }	1
updateMany(…) then findFirst(…)	every match	the row, or null	2 — 3 on a soft-deleting patch

The last line is not a verb, it is the shape repository code was written in before these two existed:

// The old shape. Don't.
await db.thing.updateMany({ where: { id }, data });          // round trip 1
const fresh = await db.thing.findFirst({ where: { id } });   // round trip 2
return fresh;

Nobody wrote that because they wanted two queries. They wrote it because a repository method wants "update this row and hand it back, or tell me it is not there", and the only verb that handed the row back would turn "it is not there" into an exception — a 500 on a request that should be a 404. updateMany gives the not-there case a { count: 0 } instead of a throw, so the write went through updateMany and the row had to be fetched separately.

The cost is not theoretical. Measured across one consumer codebase: 330 updateMany call sites in 203 files, and 123 of them are immediately followed by a re-read of the row just written. That is a doubled round trip on essentially every write path in the application.

It compounded with forge's own documentation, which until 2.18.0 said update's where was type-narrowed to the model's unique fields and that a non-unique filter needed updateMany instead. It never was (see update's where is a full filter), and a reader who believed it had no single-round-trip option at all. These verbs close the gap that sentence opened.

The third round trip, on a soft-deleting patch

On a model with a .softDeleteAt() column, the re-read pattern does not merely cost an extra trip — it returns the wrong answer. A read is soft-delete filtered, so when the patch is what soft-deletes the row, the follow-up findFirst cannot see the row it just wrote and comes back null:

await db.post.updateMany({ where: { id }, data: { deleted_at: new Date() } });
const fresh = await db.post.findFirst({ where: { id } });   // → null. Always.

The workaround for that was a read placed before the write, and the returned object assembled by hand from the pre-write row plus the patch — three round trips, and a result that is a reconstruction rather than what is in the table.

updateFirst does not have the problem. Like update, and unlike a read, it is not soft-delete filtered: it can update an already-soft-deleted row, and the row it returns is the post-update row as the driver handed it back, not a re-read. One trip, and the real row:

const post = await db.post.updateFirst({
  where: { id },
  data:  { deleted_at: new Date() },
});
// → the row, deleted_at set, from the UPDATE … RETURNING itself

(softDelete is still the verb to reach for when soft-deleting is the whole intent — see docs/SOFT-DELETE.md. It throws on a miss, as update does.)

Why they are not a try/catch around update

updateFirst is not update wrapped in a catch for P2025, and that is a correctness point rather than a stylistic one. update applies nested writes after the row itself, and a nested write can raise the same not-found code — a connect to a row that does not exist, for instance. A catch at the outside cannot tell the two apart, so it would return null for a row that had in fact been updated, and the caller would 404 a request that wrote to the database. The miss is handled at the one point where the row is found to be absent: the executor returned no document, before nested writes run.

What they compile to

Exactly what update and delete compile to — the same UPDATE … RETURNING / OUTPUT / findOneAndUpdate per dialect, listed under update vs updateMany and delete vs deleteMany. Nothing extra is issued: on MySQL, which has no RETURNING, that is the same follow-up SELECT update already does, and on every other dialect it is one statement.

Migrating the re-read pattern

Replace the pair with one call:

// before
await db.thing.updateMany({ where: { id, tenant_id }, data });
const fresh = await db.thing.findFirst({ where: { id, tenant_id } });
return fresh!;

// after
const row = await db.thing.updateFirst({ where: { id, tenant_id }, data });
if (!row) throw new NotFound();
return row;

Two things to check as you go:

• The result is null-able, and updateFirst is honest about it. Any ! assertion on the old fresh has to become a real null check — a return fresh! becomes if (!row) throw new NotFound() or a 404, or whatever the caller's contract says. The ! was always a lie; the re-read could return null both because the row did not exist and because another writer deleted it in the gap between the two trips.
• where must still identify one row. The re-read pattern updated every match, so a filter that was loose on purpose changes meaning: updateFirst writes one of the matches and which one is unspecified. If "however many match" is what you meant, keep updateMany and keep the count.

Where the row genuinely must exist — an internal invariant rather than a user-supplied id — update is still the right verb. Its throw is the assertion. updateFirst is for the case where "not found" is a legitimate outcome the caller has to handle.

---

Atomic number ops

For number columns you can pass an op object instead of a literal value. Each op compiles to a single in-place arithmetic update — the column is read and written in one statement, so there is no read-then-write race.

await db.post.update({
  where: { id: 'p1' },
  data: {
    views: { increment: 1 },
    score: { multiply: 2 },
    rank:  { divide: 2 },
    debt:  { decrement: 100 },
  },
});

Numeric ops: increment, decrement, multiply, divide, and — new in 2.18.0 — max and min, which clamp in place: the value is written only if it exceeds (max) or falls below (min) what is stored. Each takes a number. Mixing an op with a set on the same column is rejected when the update is built.

data: {
  high_score:  { max: newScore },     // only if newScore is higher
  floor_price: { min: bid },          // only if bid is lower
}

max / min are useful precisely because they need no read: "record this if it is the best so far" is otherwise a read-compare-write, which loses under concurrency exactly like a hand-rolled counter does.

Adapter	increment 5 compiles to	multiply 2 compiles to
postgres	"views" = "views" + 5	"score" = "score" * 2
mysql	`views` = `views` + 5	`score` = `score` * 2
sqlite	"views" = "views" + 5	"score" = "score" * 2
duckdb	"views" = "views" + 5	"score" = "score" * 2
mssql	[views] = [views] + 5	[score] = [score] * 2
mongo	{ $inc: { views: 5 } }	{ $mul: { score: 2 } }

Mongo decrement N is $inc: { col: -N }. All SQL dialects compile decrement and divide to the obvious - N / / N.

divide is an exact division (2.18.0)

Before 2.18.0, divide: n was rewritten into multiply: 1 / n in the IR, before any adapter could see it. divide: 3 became multiply: 0.3333333333333333, so an exact decimal money column drifted a little on every update and an int column rounded. Nothing reported it; the number was just slightly wrong, and then wrong again.

divide is now its own IR op. Every SQL dialect emits a native col = col / $n, so the database's own numeric type decides the result — numeric(12,2) stays exact. IndexedDB divides in JS. divide: 0 is refused when the update is built, rather than reaching the database as a division by zero.

Mongo pays for this with a pipeline update. Mongo has no $div update operator, so an exact divide compiles to an aggregation-pipeline update, which needs MongoDB 4.2 or newer. Every other op in the same call is translated to the matching pipeline expression (increment → $add, multiply → $multiply, push → $concatArrays, unset → $unset) so the whole update stays one atomic statement rather than splitting into two.

One consequence is worth knowing about even if you never write a divide: inside a pipeline, a bare string beginning with $ is a field reference, not text. Forge wraps every literal in $literal, so data: { note: '$5 off', rank: { divide: 2 } } stores the string "$5 off" and not the value of a field named 5 off.

divide plus upsert throws on Mongo. A pipeline update has no $setOnInsert, so "divide the column if the row exists, seed it if it does not" cannot be expressed as one statement. Rather than silently dropping one half, forge raises:

[forge:mongo] upsert cannot combine `create` with a `divide` update. Mongo needs
an aggregation-pipeline update for an exact divide, and a pipeline has no
$setOnInsert. Split it: upsert the row first, then update with the divide.

Split it exactly as the message says — an upsert that only seeds, followed by an update that divides.

There is one case that does not throw: when create seeds the same field the divide touches. That overlap already falls back to update-then-create on Mongo (the 2.7.0 fix that stopped the create seed being dropped), so the pipeline compiler never sees it. It is two round trips either way.

The SQL dialects have no such restriction; INSERT … ON CONFLICT DO UPDATE SET col = col / $n is one statement there.

Concurrency safety

Atomic ops are race-safe at the statement level: two concurrent increment: 1 calls against the same row will produce a final value two greater than the starting value, on every adapter, with no risk of a lost update — because there is no application-side read of the prior value. This is the property that makes atomic ops the right primitive for counters, balances, stock levels, and any other quantity touched by more than one writer at a time.

The guarantee stops at the row. If you need atomicity across multiple rows or multiple statements, wrap them in a $transaction and let the database serialise.

Counter example

async function recordView(postId: string) {
  return db.post.update({
    where: { id: postId },
    data:  { views: { increment: 1 }, last_viewed: new Date() },
  });
}

One round-trip, one statement, race-free across any number of callers. Contrast with the wrong shape — read, add one, write — which loses updates under any concurrency at all.

Counter that has to seed itself

The common version of the counter is "start at N the first time, add one after that", and it needs no read either — upsert handles it, with the atomic op in the update block:

const row = await db.counter.upsert({
  where:  { key: 'invoice-number' },
  create: { key: 'invoice-number', seq: 1000 },   // first call  → 1000
  update: { seq: { increment: 1 } },              // later calls → 1001, 1002, …
});
row.seq;                                          // the new value, returned

The first call applies create only and the later ones apply update only — which is Prisma's semantics, and was a real bug on Mongo until 2.7.0, where the create seed was dropped and the first call came back as 1 instead of 1000. Mongo now runs this as update-then-create with a duplicate-key retry; the SQL dialects get it from INSERT … ON CONFLICT DO UPDATE.

There is no separate sequence API and you do not need one. See UPSERT.

---

List column ops — push, addToSet, pull

For f.stringArray() and f.intArray() columns there are three in-place mutations, so you do not have to read the list, change it in JS, and write the whole thing back:

await db.post.update({
  where: { id: 'p1' },
  data: {
    tags:        { push: 'urgent' },       // append, always
    categories:  { addToSet: 'news' },     // append only if absent
    stale_flags: { pull: 'draft' },        // remove every occurrence
  },
});

addToSet and pull are new in 2.18.0, and all three are only now in the typed update input. push existed in the IR since 2.2 but was never on the public update type, so it was a documented operator that would not typecheck — reachable only through the internal builder.

They are not supported on every dialect

This is the one place in forge where a documented operator is genuinely missing on some databases, and it is worth being plain about rather than papering over.

Postgres and DuckDB have real array columns. MySQL, SQLite and MSSQL store stringArray / intArray as JSON, and JSON has no portable value-based contains-or-remove:

Dialect	push	addToSet	pull
Postgres	array_append	yes	yes
DuckDB	list_append	yes	yes
Mongo	$push	$addToSet	$pull
IndexedDB	in JS	yes	yes
MySQL	JSON_ARRAY_APPEND	yes	no
SQLite	json_insert '$[#]'	no	no
MSSQL	JSON_MODIFY 'append $'	no	no

An unsupported combination throws, naming the dialect and the column, rather than emitting something that quietly works for text arrays and not numeric ones:

[forge:sqlite] 'addToSet' on 'tags' is not supported on this dialect — its
list columns are stored as JSON, which has no value-based contains/remove.
Read the array, change it, and write it back with `{ tags: [...] }` — or
move the collection to Postgres/DuckDB, whose array columns support it
natively.

MySQL refuses pull specifically because removing by value needs the element's index first, and JSON_SEARCH returns a path only for strings — so it would work on a stringArray and silently not on an intArray.

The read-side filters (has, hasSome, hasEvery, isEmpty) have no such gap: they work on all six dialects from 2.18.0. Before 2.18.0 they emitted the Postgres array operators everywhere, so a list column was writable and readable but not filterable on MySQL, SQLite and MSSQL. That was a syntax error, not a wrong result.

If you need addToSet semantics on a JSON-backed dialect, the portable form is a read-modify-write inside a transaction, or a where guard that makes the append conditional:

// append only if absent, on any dialect
await db.$transaction(async (tx) => {
  const row = await tx.post.findUniqueOrThrow({ where: { id } });
  if (!row.categories.includes('news')) {
    await tx.post.update({ where: { id }, data: { categories: [...row.categories, 'news'] } });
  }
});

---

set vs unset

A literal value in data is a set. To clear a nullable column, pass null. To explicitly remove a Mongo field (so the document has no such key, rather than the key being present with a null value), use the unset op:

await db.user.update({
  where: { id: 'u1' },
  data: {
    name: 'Alice',           // set
    avatar_url: null,        // set to NULL on SQL, set to null on Mongo
    profile_meta: { unset: true },   // $unset on Mongo, = NULL on SQL
  },
});

On SQL there is no observable difference between null and unset — the column gets NULL either way, and the row still has it. On Mongo:

• null writes { … profile_meta: null }. The field is present.
• { unset: true } writes { $unset: { profile_meta: '' } }. The field is absent from the document.

The distinction matters when reads filter on exists (where: { profile_meta: { exists: false } }) or when other code is doing type-narrowing on undefined vs null.

---

Nested writes

A relation key in data accepts a small DSL describing what to do with the related rows in the same call:

await db.user.create({
  data: {
    email: 'a@x.co', name: 'Alice',
    posts: {
      create: [
        { title: 'Hello' },
        { title: 'World' },
      ],
      connect: { id: 'p_existing' },
    },
    profile: {
      create: { bio: 'engineer' },
    },
  },
});

The supported actions, per relation kind:

Action	one-to-one	one-to-many	many-to-many
create	yes	yes (array)	yes
createMany	no	yes	yes
connect	yes	yes	yes
connectOrCreate	yes	yes	yes
disconnect	yes	yes	yes
set	no	yes	yes
update	yes	yes (where)	yes (where)
updateMany	no	yes	yes
upsert	yes	yes	yes
delete	yes	yes (where)	yes (where)
deleteMany	no	yes	yes

connect attaches by unique key, disconnect removes the link without deleting the row, set replaces the entire set of linked rows with the listed ones, connectOrCreate is the lookup-or-make pattern in one shape.

Write order and transactionality

On SQL adapters the parent and children are issued inside a single $transaction. The order is fixed: parent first when the parent ID is needed by the children's foreign key (one-to-many, one-to-one with the FK on the child), children first when the relationship is owned by the parent (one-to-one with the FK on the parent). All-or-nothing: if any nested write fails, the whole call rolls back.

On Mongo there is no cross-collection transaction unless the cluster is a replica set with sessions. Forge issues the writes in the same logical order, but the guarantee is best-effort — a failed child write after a successful parent insert leaves the parent in place. For strict cross- document atomicity on Mongo, open a session yourself and use $transaction (it forwards the session to the driver).

Worked example: connectOrCreate

await db.post.create({
  data: {
    title: 'Hello',
    tags: {
      connectOrCreate: [
        { where: { name: 'release' }, create: { name: 'release' } },
        { where: { name: 'v2' },      create: { name: 'v2' } },
      ],
    },
  },
});

Each item runs as a lookup on the unique name, then either attaches the existing row or inserts a new one and attaches that. On SQL this is two statements per tag inside the parent's transaction; on Mongo it is one findOneAndUpdate with upsert: true per tag plus a join-table write where applicable.

---

createMany + skipDuplicates

Bulk insert with conflict tolerance. Pass skipDuplicates: true to skip rows that violate a unique constraint instead of failing the whole batch.

const { count } = await db.event.createMany({
  data: incomingEvents,        // any size
  skipDuplicates: true,
});

Adapter	Compiles to
postgres	INSERT … VALUES (…),(…),… ON CONFLICT DO NOTHING
mysql	INSERT IGNORE INTO … VALUES (…),(…),…
sqlite	INSERT OR IGNORE INTO … VALUES (…),(…),…
duckdb	INSERT … VALUES (…),(…),… ON CONFLICT DO NOTHING
mssql	INSERT INTO … VALUES (…),(…),… (no flag — MERGE path)
mongo	insertMany(docs, { ordered: false }) + drop-dup error

The count returned is the number of rows that actually inserted; the ones that conflicted are silently dropped. On Mongo, ordered: false tells the driver to continue past write errors, and forge filters duplicate key errors out of the result so the batch completes with a partial success count.

MSSQL does not have a direct counterpart to INSERT IGNORE for bulk inserts — the MERGE path is what upsert uses, and createMany with skipDuplicates falls back to the same batched insert without the flag. If duplicate tolerance matters on MSSQL, use upsert per row (still one statement each via MERGE) inside a $transaction.

Bulk size

Every adapter supports tens of thousands of rows in a single createMany. Forge does not chunk for you — that is a deliberate choice, because the optimal chunk size depends on the row width, the driver's parameter limit, and the network. Practical guidance:

• Postgres / SQLite / DuckDB / MSSQL: 10,000 rows in one statement is fine for moderate row widths (under ~20 columns). Above that, chunk to avoid the parameter limit (Postgres: 65,535 parameters; SQLite: SQLITE_MAX_VARIABLE_NUMBER, default 32,766 since 3.32).
• MySQL: chunks of 1,000 - 5,000 rows are conservative; the limit is max_allowed_packet, not a parameter count.
• Mongo: insertMany chunks at the driver level (16 MB BSON size). Pass any number; the driver splits.

For sustained throughput above a few thousand rows per second, see Batched throughput.

---

Returning shape

What you can read back after a write, per dialect:

Operation	postgres	mysql	sqlite (3.35+)	duckdb	mssql	mongo
create	row	row*	row	row	row	row
createMany	count	count	count	count	count	count
update	row	row*	row	row	row	row (findOneAndUpdate)
updateFirst	row / null	row* / null	row / null	row / null	row / null	row / null
updateMany	count	count	count	count	count	count
delete	row	row*	row	row	row	row (findOneAndDelete)
deleteFirst	row / null	row* / null	row / null	row / null	row / null	row / null
deleteMany	count	count	count	count	count	count
upsert	row	row*	row	row	row	row

* MySQL does the second SELECT to materialise the row, so it is one logical call but two statements.

You can shape the returned row with select or include, the same way reads do:

const { id, email } = await db.user.create({
  data:   { email: 'a@x.co', name: 'A' },
  select: { id: true, email: true },
});

const userWithPosts = await db.user.update({
  where:   { id: 'u1' },
  data:    { name: 'A2' },
  include: { posts: true },
});

On Postgres / SQLite / DuckDB the select narrows the RETURNING list. On MSSQL it narrows the OUTPUT INSERTED.* list. On MySQL it narrows the follow-up SELECT. On Mongo select becomes a projection.

include always does a second query (or a Mongo $lookup) — relations do not come back through RETURNING.

---

Idempotency

The simplest correct way to make a write idempotent is to give it a unique key and use upsert. Two common shapes:

Request-id key

const RequestLog = model('request_logs', {
  id:         f.id(),
  request_id: f.string().unique(),
  payload:    f.json(),
  result:     f.json().optional(),
});

async function handle(req: Request, body: Body) {
  return db.$transaction(async (tx) => {
    const log = await tx.requestLog.upsert({
      where:  { request_id: req.headers['idempotency-key'] },
      create: { request_id: req.headers['idempotency-key'], payload: body },
      update: {},   // no-op on re-arrival
    });
    if (log.result) return log.result;          // already processed

    const result = await doWork(tx, body);
    await tx.requestLog.update({
      where: { id: log.id },
      data:  { result },
    });
    return result;
  });
}

The unique constraint on request_id is what makes the upsert atomic under concurrent re-tries — two requests with the same key cannot both take the create path.

HTTP Idempotency-Key

The pattern above is the database side of the Idempotency-Key header. Combine it with a stored response body and you have a complete idempotent endpoint: re-sends return the cached response, first-sends do the work and cache it.

---

Optimistic concurrency

Add a version column, gate the update on it, and bump it in the same statement:

const Doc = model('docs', {
  id:      f.id(),
  body:    f.string(),
  version: f.int().default(0),
});

async function saveDoc(id: string, body: string, expected: number) {
  const { count } = await db.doc.updateMany({
    where: { id, version: expected },
    data:  { body, version: { increment: 1 } },
  });
  if (count === 0) throw new ConflictError('stale version');
}

The where: { id, version: expected } and the version: { increment: 1 } are atomic relative to each other on every adapter — they are one UPDATE statement. A concurrent writer whose expected is older than the current row gets count === 0 and can refetch and retry.

This is the cheapest form of concurrency control: no locks, no serializable isolation, no advisory locks. It scales as well as the underlying row writes do.

---

Pessimistic locking

When you need to read a row, decide based on its current value, and write back without another writer interleaving, take a row lock inside a transaction:

await db.$transaction(async (tx) => {
  const [row] = await tx.$queryRaw<Account[]>`
    SELECT * FROM accounts WHERE id = ${id} FOR UPDATE
  `;
  if (row.balance < amount) throw new InsufficientFunds();

  await tx.account.update({
    where: { id },
    data:  { balance: { decrement: amount } },
  });
});

FOR UPDATE is Postgres / MySQL syntax. MSSQL uses WITH (UPDLOCK, ROWLOCK). SQLite serialises all writes through a single writer lock already — BEGIN IMMEDIATE in a $transaction is the closest equivalent. DuckDB is single-writer per process. Mongo does not have row-level pessimistic locking outside a transaction with { readConcern: 'snapshot', writeConcern: 'majority' }.

In practice, atomic ops + optimistic concurrency cover most cases without needing the explicit lock. Reach for FOR UPDATE when the decision logic between read and write is complex enough that you cannot encode it as a where clause on the update.

See docs/RAW-SQL.md for the $queryRaw shape.

---

Batched throughput

Three shapes for bulk writes, in order of throughput:

1. createMany — one statement, one round-trip. Best.
2. $transaction([() => …, () => …]) — N statements, one transaction, one commit. The array holds thunks as of 2.19.0; promises are refused.
3. N create calls — N statements, N round-trips, N commits. Worst.

Rough numbers, single client on a local Postgres, 200-byte rows:

Shape	Rows / second
createMany({ data: 10_000 rows })	~150,000
$transaction([10_000 creates])	~25,000
10,000 separate create calls	~3,000

The shape of the difference is the same on every adapter — exact numbers shift with network latency, row width, and the index count on the target table. The middle figure was measured before 2.19.0, when the array form was a Promise.all; the thunk form runs sequentially inside one transaction, so read it as an upper bound.

When createMany is not enough — sustained ingest above ~150k rows/s — drop to the dialect's native bulk path:

• Postgres: COPY … FROM STDIN. Use the pg-copy-streams package against the pool the adapter holds — (db.adapter as unknown as { pool: Pool }).pool — and pool.connect() for the client you pipe into. See BATCH.md for the bring-your-own-driver caveat.
• MySQL: LOAD DATA LOCAL INFILE from a tempfile. Requires local_infile=ON on the server.
• MSSQL: BULK INSERT from a file or the tedious bulkLoad API.
• SQLite: a single $transaction around a tight loop is already close to the disk's write ceiling; the next step is PRAGMA journal_mode = WAL and PRAGMA synchronous = NORMAL.
• DuckDB: COPY FROM a Parquet / CSV file is the fast path.
• Mongo: insertMany with ordered: false. The driver pipelines.

These paths bypass the typed model.* wrapper. Use them when the throughput is worth giving up the type-check.

---

Mutation events

Subscribe to write traffic via the same $on('query') channel reads use:

db.$on('query', (e) => {
  if (e.op === 'insert' || e.op === 'update' || e.op === 'delete') {
    log.info('write', { adapter: e.adapter, model: e.model, op: e.op, ms: e.duration_ms });
  }
});

The QueryEvent fields relevant to mutations:

Field	Meaning
adapter	'postgres' | 'mysql' | 'sqlite' | 'mongo' etc.
model	schema key ('user'), or '' for raw SQL
op	'insert' | 'update' | 'delete' | 'raw', plus Mongo verbs
sql	SQL text or Mongo op description ('users.insertOne')
params	bound params (SQL) or Mongo args object
duration_ms	wall clock between dispatch and result
rowCount	affected rows when the driver reports them, else -1
startedAt	server-local timestamp at dispatch
semanticOp	'softDelete' | 'softDeleteMany' | 'restore' | 'restoreMany' when the higher-level verb compiled down to a plain update

semanticOp is the field listeners use to tell a soft-delete from a real update without parsing SQL. It is set only for the four soft-delete verbs — create, update, upsert, delete are not re-labelled, because the op field already carries that.

For the full event-system reference including error events, OTel spans, and the slow-query alerting recipe, see docs/QUERIES.md.

---

Worked patterns

Eight problems that come up regularly, each solved with the verbs above.

(a) Idempotent webhook handler

A webhook delivers each event at-least-once. Drop duplicates with a composite unique on (provider, event_id):

const ProviderEvent = model('provider_events', {
  id:         f.id(),
  provider:   f.string(),
  event_id:   f.string(),
  payload:    f.json(),
  processed:  f.bool().default(false),
}, {
  uniques: [['provider', 'event_id']],
});

async function onWebhook(provider: string, body: WebhookBody) {
  const { processed } = await db.providerEvent.upsert({
    where:  { provider_event_id: { provider, event_id: body.id } },
    create: { provider, event_id: body.id, payload: body },
    update: {},      // no-op on duplicate
  });
  if (processed) return;

  await process(body);
  await db.providerEvent.update({
    where: { provider_event_id: { provider, event_id: body.id } },
    data:  { processed: true },
  });
}

The composite unique generates a unique index, which the upsert uses as its conflict target. Two duplicate webhooks hit the database in parallel and only one takes the create path — the other returns the existing row with processed: true and exits.

(b) Optimistic inventory decrement

Decrement stock only if there is enough, in one statement:

const { count } = await db.product.updateMany({
  where: { id: productId, stock: { gte: amount } },
  data:  { stock: { decrement: amount } },
});
if (count === 0) throw new OutOfStock();

No transaction, no lock, no race. The stock: { gte: amount } is part of the WHERE clause; the database evaluates it under the row lock the update takes anyway. If two concurrent calls fight over the last unit, exactly one gets count === 1 and the other gets count === 0.

(c) Atomic counter

The view-counter shape from earlier, this time noting the _at column:

async function recordView(postId: string) {
  await db.post.update({
    where: { id: postId },
    data:  {
      views:        { increment: 1 },
      last_view_at: new Date(),
    },
  });
}

One statement per call, scales linearly with whatever the row-lock throughput of the underlying database is. Postgres / MySQL / SQLite all run this comfortably at tens of thousands of calls per second per row when the row is hot in the buffer cache.

(d) Bulk ingest — 100,000 rows

Split into 100 batches of 1,000 inside one transaction. Forge does not chunk for you; the wrapper is a deliberate ten lines:

async function bulkIngest(rows: Row[]) {
  const CHUNK = 1000;
  await db.$transaction(async (tx) => {
    for (let i = 0; i < rows.length; i += CHUNK) {
      await tx.event.createMany({
        data: rows.slice(i, i + CHUNK),
        skipDuplicates: true,
      });
    }
  });
}

The transaction means it is all-or-nothing — partial loads do not leak. Above a few hundred thousand rows, switch to the dialect's native bulk path (see Batched throughput).

(e) Soft delete + restore

Declare the column with .softDeleteAt() and use softDelete / restore:

const Post = model('posts', {
  id:         f.id(),
  title:      f.string(),
  body:       f.string(),
  deleted_at: f.dateTime().optional().softDeleteAt(),
});

await db.post.softDelete({ where: { id: 'p1' } });
await db.post.restore({ where: { id: 'p1' } });

.softDeleteAt() is the part that matters — it is what the four verbs look for. On a plain optional dateTime with no marker, softDelete throws synchronously and names the model.

softDelete compiles to an update that sets deleted_at = now(), and emits a QueryEvent with semanticOp: 'softDelete' so listeners can treat it differently from a regular update (audit trail, replication filters, etc.). Reads filter deleted_at IS NULL by default; the opt-out is _withDeleted: true inside the where, underscore included, at the top level or inside an include at any depth:

await db.post.findMany({ where: { _withDeleted: true } });
await db.user.findFirst({ include: { posts: { where: { _withDeleted: true } } } });

The underscore is not cosmetic. _withDeleted is one of four keys the where-builder recognises and strips before compiling; withDeleted is not one of them, so it is treated as a field name. Under strict: true that throws [forge:strict] unknown where key 'withDeleted'; without strict it compiles to a filter on a column no table has and the database errors. Either way you do not get the opt-out. See docs/SOFT-DELETE.md and Soft delete for the full surface.

(f) Audit trail on every update

Tee writes to an audit table from a single event listener:

db.$on('query', async (e) => {
  if (e.op !== 'update' && e.op !== 'insert' && e.op !== 'delete') return;
  if (e.model === 'audit_log') return;   // do not audit the audit
  await db.auditLog.create({
    data: {
      at:       e.startedAt,
      model:    e.model,
      op:       e.semanticOp ?? e.op,
      rows:     e.rowCount,
      sql:      e.sql,
      duration: e.duration_ms,
    },
  });
});

The listener runs after the write commits, so a failed write does not produce an audit row. If you need the audit row to be in the same transaction as the write — strictly atomic audit — use the outbox pattern below instead, with the audit row written inside the same $transaction.

(g) Transactional outbox

Write a domain row and an outbox event in one transaction, then a worker drains the outbox to Kafka / SNS / whatever:

async function placeOrder(input: OrderInput) {
  return db.$transaction(async (tx) => {
    const order = await tx.order.create({ data: input });
    await tx.outbox.create({
      data: {
        topic:        'order.created',
        aggregate_id: order.id,
        payload:      order,
        status:       'pending',
      },
    });
    return order;
  });
}

// Worker, polled every 100 ms:
async function drainOutbox() {
  const batch = await db.outbox.findMany({
    where:   { status: 'pending' },
    orderBy: { id: 'asc' },
    take:    100,
  });
  for (const evt of batch) {
    await publish(evt.topic, evt.payload);
    await db.outbox.update({
      where: { id: evt.id },
      data:  { status: 'sent', sent_at: new Date() },
    });
  }
}

The $transaction is what makes this exactly-once-from-the-domain-side: either the order and the outbox row both commit, or neither does. The worker side is at-least-once delivery — publishers must dedup downstream or the outbox event must include an idempotency key that the consumer respects.

(h) Bulk update by computed value

Raise all prices by 10% — one statement, server-side arithmetic:

await db.product.updateMany({
  where: { category: 'electronics' },
  data:  { price: { multiply: 1.1 } },
});

No application round-trip per row, no risk of stale reads, no concurrency window. The same shape works for any monotonic transformation expressible as increment / decrement / multiply / divide. For non-monotonic transforms (e.g. price = price * tax_rate where tax_rate is per-row), drop to $executeRaw with an UPDATE … SET … FROM join — see docs/RAW-SQL.md.

---

For the next layer down — how the where tree compiles, how reads combine with these writes inside a transaction, and how the IR sees mutations — see docs/QUERIES.md and docs/MIGRATIONS.md.