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.

#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).

#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).

#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.

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