Writes and Transactions | Kunii | Primitives Docs
Primitives

Writes and Transactions

Use returning, upsert, and batch writes without leaving the chain API

returning()

Chain returning() before any write terminal to emit a SQL RETURNING clause and surface the affected rows in result.data. This halves the number of D1 round-trips for the common create-then-read pattern.

// Insert and get the row back (including auto-assigned id).
const res = await db.users.returning().insert({ email: 'a@b.com', name: 'Ada' });
// res.data = [{ id: 42, email: 'a@b.com', name: 'Ada', ... }]

// Narrow the projection.
const res = await db.users.returning(['id']).insert({ email: 'a@b.com' });
// res.data = [{ id: 42 }]

// Update and read back the new state.
const res = await db.users
  .where({ id: 1 })
  .returning()
  .update({ tier: 'pro' });

// Delete and capture what was removed.
const res = await db.users
  .where({ id: 1 })
  .returning()
  .delete();

returning() works on insert, update, and delete. JSON columns are parsed back to objects on the returned rows just like a normal read.

upsert()

upsert() compiles to SQLite's INSERT ... ON CONFLICT(...) DO UPDATE / DO NOTHING. Three common conflict-resolution modes:

// Refresh every non-target column from the new values.
await db.users.upsert({
  values: { id: 1, email: 'a@b.com', tier: 'pro' },
  onConflict: 'id',
  update: 'all',
});

// Leave existing rows alone if conflict.
await db.users.upsert({
  values: { id: 1, email: 'a@b.com' },
  onConflict: 'id',
  update: 'ignore',
});

// Explicit set values (supports JSON-path keys).
await db.users.upsert({
  values: { id: 1, email: 'a@b.com', meta: { plan: 'free' } },
  onConflict: 'id',
  update: { tier: 'pro', 'meta.plan': 'pro' },
});

// Read back the result in the same call.
const { data } = await db.users
  .returning()
  .upsert({ values: {...}, onConflict: 'id', update: 'all' });

onConflict accepts a single column name or an array for composite unique keys. values accepts a single row or an array.

db.batch()

Wrap a set of writes in db.batch(async tx => ...) to commit them atomically. On D1 this is one HTTP round-trip via the native db.batch([...]) API. On Durable Objects it's wrapped in transactionSync when autoMigrate(ctx.storage, schema) was passed ctx.storage rather than ctx.storage.sql.

const result = await db.batch(async (tx) => {
  await tx.users.where({ id: 1 }).update({ tier: 'pro' });
  await tx.audit.insert({ event: 'tier_change', userId: 1 });
});
// result.success: boolean
// result.results: per-statement QueryResult[]

Inside the callback, write terminals (insert, update, delete) stage statements instead of running them. The actual commit happens after the callback resolves.

Read terminals (many, first, one, count, exists, distinct) are not supported inside batch. Run them before or after. The result data is not available until commit, so reads inside a batch cannot make decisions.

The tx argument has the same fluent shape as db, so chains (where, orderBy, select, returning, and so on) compose normally.