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, updateMany, upsert, delete, 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
• delete vs deleteMany
• Atomic number ops
• 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.
• Sequential / auto-increment columns (declared with f.int().autoincrement()) 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	Returns	Notes
update	unique selector	the updated row	throws if no row matched
updateMany	any filter	{ 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 type-narrowed to the model's unique fields (the @id, plus anything marked .unique() or part of a unique compound). Passing a non-unique field there is a TypeScript error. If you genuinely want a single-row update against a non-unique filter, use updateMany and either rely on the count or guard with take: 1 in a preceding findFirst.

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

A typed update against a unique key is asserting the row exists. Letting that silently no-op masks application bugs: the handler thinks it wrote, the database disagrees, and the next read shows stale data. The updateMany path is the right verb when "match or not" is a valid outcome.

---

upsert

upsert takes three blocks: where (must be a unique selector), 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.

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.

---

delete vs deleteMany

Same asymmetry as update.

Verb	where	Returns	Notes
delete	unique selector	the deleted row	throws if no row matched
deleteMany	any filter	{ count }	zero matches is a no-op

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.

---

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 },
  },
});

Supported ops: increment, decrement, multiply, divide. Each takes a number. Mixing an op with a set on the same column is rejected at compile time.

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 }, divide N is $mul: { col: 1 / N }. All SQL dialects compile decrement and divide to the obvious - N / / N.

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.

---

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)
updateMany	count	count	count	count	count	count
delete	row	row*	row	row	row	row (findOneAndDelete)
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().nullable(),
});

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 round-trip, one commit.
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.

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 and forge's connection via db.$raw().connection.
• 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.boolean().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 a deleted_at column and use softDelete / restore:

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

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

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 unless you opt in with withDeleted: true. See 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.