Bulk write operations (insertMany / updateMany / upsert / insertIgnore)

Four write APIs that go beyond single-row insert/update:

• insertMany(table, rows) — one-statement multi-row insert
• updateMany(table, patch, { where }) — one-statement bulk update
• upsert(table, row, options) — insert OR update on conflict
• insertIgnore(table, row, options) — insert OR keep existing

All four live on ctx.store alongside insert / update / delete.

#insertMany — one-statement multi-row insert

const inserted = await ctx.store.insertMany('todos', [
  { title: 'a', done: false },
  { title: 'b', done: false },
  { title: 'c', done: false },
])
// inserted: the rows as persisted, in input order (ids auto-injected)

One multi-row INSERT ... VALUES (...),(...) per dialect — a single round-trip regardless of row count, instead of N insert() calls. The auto-stamping middleware (id injection, audit/tenant columns, .computed() columns, .validate()) runs per row exactly as insert() does. An empty array is a no-op that returns [].

#Reactivity

insertMany emits ONE insert ChangeEvent per row, so reactive subscribers see each new row's delta — identical to N single inserts. Inside a transactional() the per-row events queue until commit and drop on a throw.

#Large arrays are chunked for you — and stay all-or-nothing

Every engine caps what ONE statement may carry, and the caps are far apart:

Dialect	Bind parameters per statement	Rows per VALUES
postgres	65 535	—
mysql / mariadb	65 535	—
mssql	2 098	1 000
sqlite / turso	32 766	—

INSERT … VALUES binds one parameter per column per row, so the row limit is floor(cap / columns) — a 12-column table caps at 5 461 rows on postgres and at 174 on mssql. Past that the driver refuses with its own error about a limit you never chose.

insertMany splits the array for you at that boundary. Two properties are guaranteed:

• A fitting array is still ONE statement. Nothing changes for the normal case — no extra round-trips, no behaviour difference.
• A chunked insert is still all-or-nothing. When the call is not already inside a transactional(), the chunks run in one transaction the framework opens, so a failure in the last chunk rolls back the earlier ones. Without that, chunking would quietly add partial-success-on-failure to a call that never had it.

There is nothing to configure. If a SINGLE row is wider than the cap (a 3 000-column table on mssql) the engine's own error is what you get — a row cannot be split.

#upsert — insert or update on conflict

The 90% case: "make this row exist with these values; if it already exists, update it":

await ctx.store.upsert('users', {
  email:       input.email,
  displayName: input.displayName,
}, {
  conflictColumns: ['email'],
})

conflictColumns declares which columns identify "the same row". Typically a single column with a UNIQUE constraint (['email']) or a composite UNIQUE (['orgId', 'slug']).

#Three update strategies

Default — overwrite every input column:

await ctx.store.upsert('users', input, {
  conflictColumns: ['email'],
  // update: omitted → every non-id, non-conflict column from `input`
})

Whitelist — only certain columns:

await ctx.store.upsert('users', input, {
  conflictColumns: ['email'],
  update: ['displayName'],         // overwrite ONLY displayName on conflict
})

Function — compute patch from existing row:

await ctx.store.upsert('users', input, {
  conflictColumns: ['email'],
  update: (existing) => ({ visits: (existing.visits ?? 0) + 1 }),
})
// Increment counter pattern — read then patch

#Composite conflict keys

await ctx.store.upsert('orgSlugs', {
  orgId: 'o1', slug: 'dashboard', active: true,
}, {
  conflictColumns: ['orgId', 'slug'],
})

Requires a composite UNIQUE constraint on the table — declare it via .unique([cols]) in the schema (see Indexes).

#The row you get back is the row that was there

When an upsert on a non-id key MATCHES, it updates the existing row — and that row keeps its own id. The id you passed is the one that would have been used had it inserted, so a fresh id on every call is the normal shape:

await ctx.store.upsert('projectHours', {
  teamId: 't-1', year: 2026, hours: 111,
}, {
  conflictColumns: ['teamId', 'year'],
  update: ['hours'],
})

The returned row carries the id of whichever row now holds those values. Compare values, not ids, if you need to know whether you inserted or updated.

On MySQL and MariaDB this costs one extra guarantee, because the dialect cannot express the question. Postgres names its target — ON CONFLICT (teamId, year) fires on that index and nothing else — while ON DUPLICATE KEY UPDATE fires on whichever unique key the incoming row violates, which may not be the one you named. The store therefore checks afterwards that the row it reached carries your conflict values, and refuses the write if it does not: that row would have absorbed your values while your row was never written. The refusal names the differing column; the values go to the server log rather than into a sentence that may reach a user.

A conflict column the database GENERATES cannot be checked this way — its value is never in the row you sent. The remaining columns are still compared; if a conflict key consists ENTIRELY of generated columns there is nothing to compare, and the upsert refuses rather than guessing.

#Generated columns in a conflict key

A partial-unique index on MySQL/MariaDB is usually a STORED generated column plus NULL-distinct semantics — the key constrains only the rows the expression marks. An upsert can conflict on such a key, but only on the path where the DATABASE evaluates the expression: the single-statement form, reached on MariaDB when the row is a complete INSERT row and update is a column list.

The other path looks the row up by value first — it has to, because a function update needs the existing row to compute its patch — and the value of a generated column is not in the row you passed. There is no substitute for it, so that combination refuses and names the column rather than guessing at one. Postgres has no such split: ON CONFLICT names its index and the server evaluates the column.

#insertIgnore — keep existing on conflict

For idempotent write patterns where you want to ensure a row exists but DON'T want to overwrite it:

await ctx.store.insertIgnore('audit_dedup', {
  eventId, ts: new Date(),
}, {
  conflictColumns: ['eventId'],
})
// First call: row inserted, returned
// Second call (same eventId): existing row returned, no write happens

Common uses:

• Dedup events by external ID (Stripe webhooks, Shopify orders)
• Idempotent "ensure tenant exists" patterns
• Audit logs where re-running a sync shouldn't double-write

Returns the FINAL row in both cases (newly-inserted or pre-existing).

#One conflict target, and only conflicts

conflictColumns names ONE constraint. A duplicate on a different unique index is not something insertIgnore can resolve — it cannot know which existing row you meant — so it throws, naming the constraint that actually fired.

It also throws when the insert was rejected rather than skipped. This matters most on MariaDB, where the statement lowers to INSERT IGNORE: that downgrades every error to a warning — foreign key, NOT NULL, CHECK, truncation — not just the unique violation the API models. So "no row was inserted" does not imply "a conflict happened", and reporting one as the other would turn a rejected write into a silent no-op: the row is not there and the caller is told it already was.

MysqlStore.insertIgnore: the insert into 'docs' was REJECTED, not skipped as a
conflict. INSERT IGNORE downgrades every error to a warning, and the warning was:
[1452] Cannot add or update a child row: a foreign key constraint fails …
Nothing was written and nothing conflicted — fix the cause above.

The real cause comes from SHOW WARNINGS on the same connection, which is only attributable inside a transaction — so outside one the message says the constraint is unknown rather than guessing at it. Framework mutations are auto-transactional, so the common path has the cause.

#updateMany — one-statement bulk update

import { inSubquery, eq, queryFor } from '@voltro/database'

// Hide every post by a banned user
const count = await ctx.store.updateMany('posts', { hidden: true }, {
  where: inSubquery('userId',
    queryFor(database.users).where(eq('banned', true)).select('id'),
  ),
})
// count: number of rows actually updated

The where predicate is a regular Predicate AST — same shape .where() uses. Sub-queries via inSubquery / exists are supported.

#Typed: updateManyRow

updateMany takes a string table name and an untyped patch, so a misspelled column or a wrongly-typed value is only found by the database — or not at all, if the column happens to exist. updateManyRow takes the TABLE OBJECT instead and checks the patch against the row type:

import { updateManyRow, eq } from '@voltro/database'

await updateManyRow(ctx.store, posts, { hidden: true }, {
  where: eq('userId', bannedId),
})

await updateManyRow(ctx.store, posts, { hiddne: true }, { where: … })
//                                      ^^^^^^^ compile error: not a column

It is worth using rather than the string form, and the evidence is concrete: one app migrating 29 store.upsert call sites to the typed upsertRow got 15 tsc errors across 8 distinct defects that no test had caught — including seven per-user mutations with no authentication check at all, which wrote ctx.request.subject.id (typed string | null) into a NOT NULL column, so an anonymous caller reached the database and got a raw statement failure instead of a typed refusal.

The subtlest one is the most persuasive: a value spread from a plain object literal widens to string, and a column's .oneOf() union rejects it even though the value IS one of the members. Neither a reviewer nor a test would plausibly find that; only the row type asks the question. (as const fixes it.)

#Reactivity

updateMany emits ONE ChangeEvent per affected row, so reactive subscribers see deltas just like a row-by-row update path. The event fan-out is preserved on every dialect — via RETURNING * (postgres/sqlite), OUTPUT INSERTED.* (MSSQL), or a post-image SELECT (mysql/mariadb — neither ships UPDATE … RETURNING).

#Cross-dialect

The framework picks the optimal SQL form per dialect — every operation is O(1) statements regardless of row count:

Dialect	upsert / insertIgnore	updateMany
postgres	INSERT ... ON CONFLICT (cols) DO UPDATE/DO NOTHING RETURNING *	UPDATE ... WHERE ... RETURNING *
sqlite 3.35+	ON CONFLICT (cols) DO UPDATE/DO NOTHING + RETURNING	UPDATE ... WHERE ... RETURNING *
mariadb 10.5+	native INSERT ... ON DUPLICATE KEY UPDATE ... RETURNING * / INSERT IGNORE ... RETURNING *	3 statements (SELECT ids → UPDATE → SELECT post-images) — no UPDATE … RETURNING in any MariaDB version
mysql 8.x	same fallback	3 statements (SELECT ids → UPDATE → SELECT post-images)
mssql	same fallback	UPDATE ... SET ... OUTPUT INSERTED.* WHERE

The user-facing API is identical across all of them — the per-dialect work is hidden in the store implementations.

#Atomicity

All three operations participate in the surrounding transactional() wrapper. ChangeEvents queue until commit; a throw rolls them back.

For upsert on the fallback dialects (mysql/mssql), the SELECT-then-INSERT/UPDATE pair is atomic INSIDE the transaction. Without a surrounding transaction, a concurrent writer could race the SELECT — caller is responsible for wrapping when atomicity matters.

#When NOT to use these

• Loop of single-row writes — use updateMany (for updates) or insertMany (for inserts) instead. One round-trip beats N every time.

#See also

• Aggregations — count() / aggregate() for read-side bulk reads
• Sub-queries — inSubquery / exists in updateMany where: clauses
• Composite UNIQUE — for the constraint that backs upsert's conflictColumns: ['a', 'b']