# Writes and Transactions

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

Package: Kunii
Canonical: https://kuratchi.dev/docs/kunii/writes-and-transactions
Markdown: https://kuratchi.dev/docs/kunii/writes-and-transactions.md

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

```ts
// 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:

```ts
// 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`.

```ts
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.

## Read next

- [Querying](/docs/kunii/querying)
- [Aggregates and Reporting](/docs/kunii/aggregates-and-reporting)
- [Koze Integration](/docs/kunii/koze-integration)