Mutations

These are the builders. For the equivalent SQL statements — and for the batch APIs underneath — see Writing data.

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

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:

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:

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 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:

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:

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 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:

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

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 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 with no matching parent row, or a CHECK a row fails, rejects the write rather than being applied.