# Mutations

> Typed inserts, updates, deletes, and returning — and which engine path each takes.

These are the builders. For the equivalent SQL statements — and for the batch APIs underneath —
see [Writing data](/docs/sql/dml.md).

The mutation builders mirror the select builder: immutable, nothing runs until `execute()`, and the
row types come from your schema.

```ts
await db.insertInto("customers").values({ customer_id: 1, name: "Ada", city: "London" }).execute();
```

## Insert

`values()` takes one row or an array, and accepts the table's **insert** shape — nullable columns
and columns the engine can fill may be omitted:

```ts
await db
  .insertInto("orders")
  .values([
    { order_id: 1, customer_id: 1, status: "new", total: 24.5 },
    { order_id: 2, customer_id: 1, status: "new", total: 88.0 }, // note omitted, pads to null
  ])
  .execute();
```

Repeated `values()` calls accumulate, so a batch can be assembled in pieces.

`orReplace()` routes the same rows through the upsert path, replacing any row with the same unique
key instead of throwing:

```ts
await db.insertInto("customers").values(row).orReplace().execute();
```

Inserts go through the engine's batch API rather than the SQL parser — the same path
[bulk loading](/docs/sql/dml.md#bulk-loading) uses — so a large `values([...])` is a columnar write,
not thousands of parsed statements.

## Update and delete

Both address rows through a predicate and compile to the same mutation statements SQL produces:

```ts
await db.updateTable("orders").set({ status: "shipped" }).where("order_id", "=", 1).execute();

await db
  .updateTable("orders")
  .set((eb) => ({ total: eb("total", "*", 2) })) // expressions, not just literals
  .where("status", "=", "pending")
  .execute();

await db.deleteFrom("orders").where("status", "=", "cancelled").execute();
```

`set()` also takes a single column and value — `set("status", "shipped")` — and accepts the table's
**update** shape, which excludes the unique key. An `undefined` entry means "leave this column
alone", so a spread-patch built from optional fields is safe:

```ts
const patch: { status?: string; note?: string } = { status: "shipped" };
await db.updateTable("orders").set(patch).where("order_id", "=", 1).execute();
```

Writes are rejected against a [view](/docs/schema.md#views) at compile time — a view has no insert
shape, so `db.insertInto("active_customers")` does not typecheck.

## Getting rows back

`returning()` projects named columns; `returningAll()` gives the whole row. Both change what
`execute()` resolves to:

```ts
const created = await db
  .insertInto("notes")
  .values({ body: "hello" })
  .returningAll()
  .executeTakeFirstOrThrow();
// { id: 1, slug: "…", status: "draft", created: Date, body: "hello" }

const [updated] = await db
  .updateTable("orders")
  .set({ status: "shipped" })
  .where("order_id", "=", 1)
  .returning(["order_id", "status"])
  .execute();
```

This is how you read back values the engine generated — auto-increment keys and defaults — without
a second query. Inserts echo the written values overlaid with generated columns; updates return
post-update values; deletes return the rows as they were read.

## Running a mutation

| Call                        | Returns                                                            |
| --------------------------- | ------------------------------------------------------------------ |
| `execute()`                 | An array: the `returning` rows, or one result object with a count. |
| `executeTakeFirst()`        | The first element, or `undefined`.                                 |
| `executeTakeFirstOrThrow()` | The first element, or throws `NoResultError`.                      |
| `compile()`                 | The compiled statement, without running it.                        |

Without `returning`, `execute()` resolves to a single-element array carrying the count —
`numInsertedRows`, `numUpdatedRows`, or `numDeletedRows` — so `executeTakeFirstOrThrow()` is the
idiomatic call either way.

## Atomicity

One builder call is one commit. To make several land together, run them inside a
[write scope](/docs/engine/transactions.md#atomic-writes) on the driver — every staged mutation
publishes as one atomic commit, and a throw aborts the scope with nothing published.

Constraints declared in your schema are enforced on every one of these paths: a
[foreign key](/docs/schema.md#relations-and-row-conditions) with no matching parent row, or a `CHECK`
a row fails, rejects the write rather than being applied.

---

Minnow 0.1.0 · this page on the site: /docs/client/writes/