Mutations

@dbsp/core provides four mutation builders — insert, update, delete, and upsert. All require an explicit .execute() call; none run implicitly. Every builder also supports .dump() for SQL inspection without hitting the database.

---

Insert

// Insert a single row
orm.insert('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .dump();

Bulk Insert

Pass an array to .values() to insert multiple rows in a single statement:

orm.insert('users')
  .values([
    { name: 'Alice', email: 'alice@example.com' },
    { name: 'Bob',   email: 'bob@example.com'   },
  ])
  .dump();

RETURNING

Use .returning() to get back specific columns from the inserted rows:

const { sql: newUserSql } = orm.insert('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .returning(['id', 'name', 'createdAt'])
  .dump();

---

Update

const userId = 1;

import { eq } from '@dbsp/core';

orm.update('users')
  .set({ name: 'Alice Smith' })
  .where(eq('id', userId))
  .dump();

update() requires a .where() clause. Omitting it throws UnsafeOperationError — this is a safety guard against accidental full-table updates.

Update All Rows (Intentional Full-Table)

When you genuinely need to update every row, use updateAll():

orm.updateAll('users')
  .set({ active: false })
  .dump();

Update with RETURNING

import { eq } from '@dbsp/core';

const { sql: updatedSql } = orm.update('users')
  .set({ active: true })
  .where(eq('email', 'alice@example.com'))
  .returning(['id', 'name', 'active'])
  .dump();

---

Delete

import { eq } from '@dbsp/core';

orm.delete('posts')
  .where(eq('published', false))
  .dump();

Like update(), delete() requires a .where() clause. Use deleteAll() when you intend to remove every row:

orm.deleteAll('users').dump();

Delete with RETURNING

import { eq } from '@dbsp/core';

const { sql: removedSql } = orm.delete('posts')
  .where(eq('published', false))
  .returning(['id', 'title'])
  .dump();

---

Upsert

Insert a row, or update it on conflict:

// Auto-update all non-conflict columns
orm.upsert('users')
  .values({ name: 'Alice', email: 'alice@example.com', active: true })
  .onConflict(['email'])
  .doUpdate()
  .dump();

// Update only specific columns on conflict
orm.upsert('users')
  .values({ name: 'Alice', email: 'alice@example.com', active: true })
  .onConflict(['email'])
  .doUpdate({ name: 'Alice Updated', active: true })
  .dump();

// Skip the row silently on conflict
orm.upsert('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .onConflict(['email'])
  .doNothing()
  .dump();

// Conflict by constraint name instead of columns
orm.upsert('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .onConflictConstraint('users_email_unique')
  .doNothing()
  .dump();

// With RETURNING
const { sql: resultSql } = orm.upsert('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .onConflict(['email'])
  .doUpdate()
  .returning(['id', 'name'])
  .dump();

Advanced ON CONFLICT patterns

The basic upsert patterns above cover most use cases. The following patterns handle edge cases you will encounter in production schemas.

Selecting a specific constraint

When a table has multiple unique constraints, specify which one governs conflict detection. Use .onConflictConstraint(name) to target a named constraint instead of listing columns:

// doctest: skip — constraint names are DB-specific
orm.upsert('users')
  .values({ name: 'Alice', email: 'alice@example.com', externalId: 'ext-001' })
  .onConflictConstraint('users_email_unique')
  .doUpdate({ name: 'Alice Updated' })
  .dump();
// SQL: INSERT INTO "users" (...) VALUES ($1, $2, $3)
// ON CONFLICT ON CONSTRAINT "users_email_unique"
// DO UPDATE SET "name" = $4

Source: packages/core/src/dx/mutation-builders.ts:870 — onConflictConstraint(constraintName: string).

Partial-index conflict targets with a WHERE clause

PostgreSQL partial indexes restrict conflict detection to rows that satisfy a condition. When your UNIQUE index has a WHERE clause (a partial index), pass a matching condition to .onConflict() via the where option:

// doctest: skip — partial-index conflict; index must exist in the DB
import { eq } from '@dbsp/core';

// Partial unique index: CREATE UNIQUE INDEX ON "products" ("sku") WHERE "active" = true
orm.upsert('products')
  .values({ sku: 'ABC', price: 99.99, active: true })
  .onConflict({ columns: ['sku'], where: [eq('active', true)] })
  .doUpdate({ price: 99.99 })
  .dump();
// SQL: INSERT INTO "products" ("sku", "price", "active") VALUES ($1, $2, $3)
// ON CONFLICT ("sku") WHERE "active" = $4
// DO UPDATE SET "price" = $5

Note: The WHERE here is on the conflict target (the partial index predicate), not on the UPDATE action. It tells PostgreSQL which index to use for conflict detection.

Not yet supported: The second argument to doUpdate(set, where) is accepted by the TypeScript API but is silently ignored by the PostgreSQL compiler — the WHERE is not emitted in DO UPDATE SET ... WHERE .... Do not use doUpdate(set, whereCondition) to conditionally apply updates; the condition will have no effect.

Source: packages/core/src/dx/mutation-builders.ts:887 — doUpdate(set?, where?); packages/adapter-pgsql/src/mutations/upsert.ts:116 — partial-index WHERE on conflict target.

Multi-column conflict targets

List all columns that compose the unique constraint when the conflict target spans multiple columns:

orm.upsert('user_roles')
  .values({ userId: 1, roleId: 3, grantedAt: new Date() })
  .onConflict(['userId', 'roleId'])
  .doUpdate({ grantedAt: new Date() })
  .dump();
// SQL: INSERT INTO "user_roles" (...) VALUES ($1, $2, $3)
// ON CONFLICT ("userId", "roleId")
// DO UPDATE SET "grantedAt" = $4

DO NOTHING vs DO UPDATE — when to use each

Need	Use
Idempotent insert: ignore if already exists	.doNothing()
Upsert: create if new, update if exists	.doUpdate() (no args = auto-update all non-conflict columns)
Selective upsert: update only specific fields	.doUpdate({ col: value })
Conditional upsert: only update if a condition holds	Not yet supported (see note above)

.doNothing() compiles to ON CONFLICT DO NOTHING — the entire row is left unchanged on a conflict. It is the correct choice for "insert if not exists" patterns where updating the existing row would be incorrect (e.g., first-write-wins semantics).

---

Safety Rules

Builder	Safety rule
orm.update()	Requires .where() — throws UnsafeOperationError without it
orm.delete()	Requires .where() — throws UnsafeOperationError without it
orm.updateAll()	No WHERE required — explicit opt-in for full-table update
orm.deleteAll()	No WHERE required — explicit opt-in for full-table delete

These rules prevent silent data loss from forgotten filter conditions.

---

Observability with dump()

All mutation builders support .dump() — inspect the SQL and parameters without executing:

const { sql, parameters } = orm.insert('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .dump();

console.log(sql);
// INSERT INTO "users" ("name", "email") VALUES ($1, $2)

console.log(parameters);
// ['Alice', 'alice@example.com']

This works even without a database connection (compile-only mode). See Observability for more on dump().