VoltroDocumentation

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

Insert many rows in one statement, update many rows in one statement, upsert with ON CONFLICT semantics, insert-or-ignore for idempotent writes.

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 [].

MySQL reads named and composite primary keys declared with .primaryKey([...]) back in input order, including database defaults. Supply every explicit key component on every row; an ordinary non-key id column does not replace that declaration. Raw stores need the registered table definition, as normal framework boot provides. The same explicit-key recovery applies to single-row insert calls: an earlier AUTO_INCREMENT write on the connection cannot replace the declared row identity, and nullable non-key values are not used to identify the inserted row. Insert chunks and key-based readback run in one transaction, and a failed chunk leaves no inserted rows or change events. MariaDB uses its native RETURNING path.

Typed: insertManyRows

insertMany takes a string table name and untyped rows, so a missing required column, a misspelled one, or a value of the wrong type is only found by the database — somewhere inside the batch. insertManyRows takes the TABLE OBJECT instead and checks every element against the insert row type:

import { insertManyRows } from '@voltro/database'

const inserted = await insertManyRows(ctx.store, todos, rows)

await insertManyRows(ctx.store, todos, [{ title: 'a' }, {}])
//                                                      ^^ compile error: `title` is required

It forwards to insertMany unchanged — same single statement, same chunking, same per-row middleware and events. The only difference is where a wrong column is found.

It is a separate helper rather than a loop over insertRow because of where the mismatches come from. A batch is usually MAPPED — from a parsed import, an API page, a CSV — and the element type a loop body infers is only as good as whatever produced the array. A Date where the column takes a number survives review in exactly that shape, and only the row type asks the question.

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

SQL callback upserts address the existing row through all conflictColumns, including composite and alternate keys. This also applies to MySQL/MariaDB partial-insert fallbacks and SQL Server recovery after a competing insert. They do not require a text id column. Numeric IDs and schema-encoded key values preserve the same single-row scope. This does not turn the callback read/compute path into an atomic SQL expression.

Values that apply only on conflict

updateValues adds assignments to the UPDATE branch without adding them to the INSERT row. This is useful when a reconnect changes a timestamp while preserving the original registration fields:

await ctx.store.upsert('screens', input, {
  conflictColumns: ['id'],
  update: ['status'],
  updateValues: { updatedAt: new Date() },
})

On INSERT, only input and the ordinary insert defaults apply. On conflict, defined updateValues override the default, column-list or callback projection; update: [] writes only these additional values. undefined is omitted; explicit null, false, 0 and '' are values. id and conflict columns cannot be rewritten through this option, and invalid assignments are refused before either branch writes. The input must still satisfy the insert contract.

Column-list/default upserts retain the dialect's existing execution path: Postgres/SQLite use ON CONFLICT, SQL Server uses MERGE, and MariaDB uses its native statement for complete insert rows. Constants are bound parameters in the same statement, not a follow-up UPDATE. MySQL and the existing partial-row or callback fallbacks remain read/compute paths; this option does not introduce new concurrency guarantees for them.

For raw SQLite-family stores, register the table definitions just as framework boot does: JSON and array columns use schema-driven encoding on every row-write verb, including bulk writes and callback upserts. This applies to SQLite and both Turso clients; unregistered tables carry no column metadata for a codec.

Use logical column values for row keys and conflict values too. For example, a timestamp key remains a Date; do not pre-encode it as SQLite storage text. Keyed update, patchJson and delete, and the existing-row lookup after an insertIgnore conflict, apply the same column codecs as inserts. Returned rows contain decoded values.

The option also flows through transactions, EffectStore, replicated stores and the typed upsertRow helper. Request and boot codecs encode conflict values (including encrypted columns) before storage. The existing encrypted-upsert destination-lookup race remains: a concurrent insert between that lookup and the write can invalidate the resolved row binding. This option is not a fix for that race. The request-scoped store also evaluates column .onUpdate() for omitted conflict assignments; explicit selected values and updateValues take precedence. Function-form updates receive a decoded existing row; their returned patch is validated and encoded for that row before storage.

Composite conflict keys

The conflict columns can also be a natural or composite primary key declared with .primaryKey([...]); no synthetic id column is required. The in-memory store used by makeTestContext updates the matched row through that column key, including inside transactions. Column-list, callback and updateValues updates preserve independent fields; reads see pending writes, rollback discards them, and update events are published only after commit.

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.

An .encrypted() column in an upsert

An encrypted value is bound to the ROW that holds it, and on the conflict path that row is the existing one — not the one you passed. The framework resolves which row the upsert will land on before it encrypts, so the value stays readable; you write the column exactly as you would anywhere else.

Two consequences worth knowing:

  • The resolve costs one extra read, and only for an upsert that actually writes an encrypted column. Everything else is unchanged.
  • A conflict column cannot itself be .encrypted(). Each write gets a fresh IV, so two encryptions of the same plaintext never compare equal and the conflict could not be detected. Key on a hash of the value instead, and keep the encrypted original beside it.

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.

Did it insert or update? — upsertRowOutcome

upsert returns the row and not what happened to it, so an app that has to report "created" reads first and then upserts. That is a round trip per row, and a window: another writer between the read and the write makes the answer wrong.

import { upsertRowOutcome } from '@voltro/database'

const { row, outcome } = await upsertRowOutcome(ctx.store, players, player, {
  conflictColumns: ['tenantId', 'externalId'],
  update: ['name', 'score'],
})
if (outcome === 'inserted') await notifyNewPlayer(row)

The same answer on the Effect surface: upsertWithOutcome is on yield* EffectStore and on the tx view transactional hands out, so an ingest that runs inside a transaction asks the store instead of reading first:

const store = yield* EffectStore
const { row, outcome } = yield* store.upsertWithOutcome('players', player, {
  conflictColumns: ['tenantId', 'externalId'],
  update: ['name', 'score'],
})

It costs nothing. The store mints the row's id before the statement, and on the conflict path every dialect hands back the EXISTING row's id — two ids that differ are the answer.

'inserted' is never a stand-in for "could not tell". Where the id cannot decide it — you supplied the id yourself, or the table's id is assigned by the database — the store reads the destination first and answers exactly. Only a call that asks for the outcome ever pays for that read.

There is no upsertMany. For a batch of conflict writes, put the loop inside ONE store.transactional and cap the input: the round trips stay, the partial state does not.

Did it insert, or was it there? — insertIgnoreRowOutcome

insertIgnore returns the existing row on the ignore path, and that row looks exactly like one it just wrote. Use the native outcome to count newly inserted rows; a read before the insert cannot decide which concurrent caller wins.

import { insertIgnoreRowOutcome } from '@voltro/database'

const { row, outcome } = await insertIgnoreRowOutcome(ctx.store, actors, actor, {
  conflictColumns: ['id'],
})
if (outcome === 'inserted') planted++

The same answer on the Effect surface, on yield* EffectStore and on the tx view: insertIgnoreWithOutcome.

const store = yield* EffectStore
const { row, outcome } = yield* store.insertIgnoreWithOutcome('actors', actor, {
  conflictColumns: ['id'],
})

The native store decides at the insert, including when you supply the ID or the database generates it. Every DataStore implements insertIgnoreWithOutcome, including raw startup stores, namespace views and transaction views. The typed helper above works on all of them, without a middleware wrapper. Inside a transaction, the result becomes durable only after commit; rollback discards it. Custom store adapters must return { row, outcome: 'inserted' | 'ignored' } from their actual insert decision, never from an ID comparison or a pre-read. Plain insertIgnore returns only the row from the same operation.

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']

Reviewed conflict loops

insertIgnore preserves the existing row for the declared conflict key and refuses other constraint failures. There is no insertManyIgnore. Keep a bounded conflict loop inside a mutation transaction or an explicit store.transactional callback; Doctor accepts that transaction-owned loop. A deliberately bounded write loop may also be an intentional product choice; record its reason in voltro-doctor-reviews.json rather than introducing a batch wrapper solely to reduce findings. Plain insert loops still use insertMany.