# Writing data

> Insert, update, delete, upsert, and RETURNING.

Prefer a typed builder to SQL strings? [Mutations](/docs/client/writes.md) covers the same ground
through `@minnowdb/client`.

## Insert

```sql
INSERT INTO customers (customer_id, name, city, signed_up_on)
VALUES (?, ?, ?, ?)
```

Multiple row tuples in one statement are fine, and so is `INSERT … SELECT`, which materializes
the query at one version before writing:

```sql
INSERT INTO archived_orders (order_id, customer_id, total, placed_at)
SELECT order_id, customer_id, total, placed_at
FROM orders
WHERE placed_at < TIMESTAMP '2024-01-01'
```

Columns you leave out are written as `NULL`, so a nullable column can simply be omitted. A
`NOT NULL` column without a value or a default is an error.

## Update and delete

```sql
UPDATE orders SET status = 'refunded', total = total - ? WHERE order_id = ?
DELETE FROM orders WHERE placed_at < ?
```

Any `WHERE` clause works — the engine resolves it to the affected rows and writes a mutation
segment addressing them. `SET` expressions can read the row's current values, as `total - ?` does
above.

> **The table needs a unique key**
>
> `UPDATE` and `DELETE` are rejected on a table with no `PRIMARY KEY`:
>
> ```
> UPDATE requires a table with a unique key: logs
> ```
>
> Mutation segments identify rows by unique key, so a table without one can only be appended to.
> This holds for every write path, not just SQL. Give a table a key if it will ever be edited.

When you already hold the keys, the batch APIs skip the parser and the lookup:

```ts
await db.deleteBatch("orders", { keys: [1001, 1002, 1003] });
```

## Upsert

```sql
INSERT INTO customers (customer_id, name, city, signed_up_on)
VALUES (?, ?, ?, ?)
ON CONFLICT (customer_id) DO UPDATE SET name = EXCLUDED.name, city = EXCLUDED.city
```

`DO NOTHING` is also available. `EXCLUDED` refers to the row that would have been inserted, which
is how you write "keep the newer value" without reading first.

## RETURNING

Any of the four statements can return the rows it touched — post-update values for `UPDATE`, and
the removed rows for `DELETE`:

```sql
UPDATE products SET list_price = list_price * 1.05
WHERE product_id = ?
RETURNING product_id, name, list_price
```

```ts
const result = await db.execute(sql, [productId]);
result.returnedRows; // [{ product_id: 42, name: "…", list_price: 18.85 }]
```

This is one round trip instead of a write followed by a read, and it observes exactly the rows the
statement wrote — no window in which something else changes them.

## Unique keys

A `PRIMARY KEY` column is enforced: inserting a key that already exists throws
`UniqueConstraintError` rather than duplicating the row. Membership is tracked separately from the
data blocks, so the check does not scan the table.

```ts
import { UniqueConstraintError } from "@minnowdb/core";

try {
  await db.execute("INSERT INTO customers (customer_id, name) VALUES (?, ?)", [1, "Ada"]);
} catch (error) {
  if (error instanceof UniqueConstraintError) {
    // error.tableName, error.keys
  }
}
```

## Triggers

`AFTER` and `BEFORE` triggers fire inside the same commit as the write that caused them, so a row
and everything derived from it publish together or not at all.

```sql
CREATE TRIGGER log_refunds AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO audit (order_id, old_status, new_status, at)
  VALUES (OLD.order_id, OLD.status, NEW.status, CURRENT_TIMESTAMP);
END
```

`DROP TRIGGER log_refunds` removes it.

## Atomicity

One statement is one commit. Several statements that must land together belong in a
[write scope](/docs/engine/transactions.md):

```ts
const { version } = await db.write(async (tx) => {
  await tx.execute("UPDATE stock SET on_hand = on_hand - ? WHERE sku = ?", [qty, sku]);
  await tx.execute("INSERT INTO shipments (sku, qty, at) VALUES (?, ?, ?)", [sku, qty, new Date()]);
});
```

Either both are visible or neither is — including to another tab, which never sees the stock
decremented without the shipment.

The same scope is reachable from SQL, for a console or a client that only speaks statements:

```sql
BEGIN;
UPDATE stock SET on_hand = on_hand - 1 WHERE sku = 'A-1';
INSERT INTO shipments (sku, qty, at) VALUES ('A-1', 1, CURRENT_TIMESTAMP);
COMMIT;
```

Statements inside see each other — a `SELECT` after the `UPDATE` reads the new value — and
`ROLLBACK` discards the lot. Two rules keep an open transaction from becoming a leak: schema
changes are refused inside one, because the catalog commits outside the scope and a rollback could
not take them back, and a transaction left untouched for 30 seconds rolls itself back. A callback
scope has no such bound, which is why it stays the better form when you have one.

## Merging

`MERGE` writes one source's rows into a table, deciding per row what to do:

```sql
MERGE INTO stock s
USING (SELECT sku, qty FROM delivery) d ON s.sku = d.sku
WHEN MATCHED AND d.qty = 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET on_hand = s.on_hand + d.qty
WHEN NOT MATCHED THEN INSERT (sku, on_hand) VALUES (d.sku, d.qty)
```

The branches are tried in order for each source row, the whole statement is one commit, and it
fires the same triggers the equivalent `INSERT`, `UPDATE`, and `DELETE` would. The `ON` condition
has to equate the target's unique key with a source value: that is how rows are addressed, and it
is also why one source row can never match two target rows.

## Bulk loading

Parsing a statement per row is the wrong shape for loading a lot of data. The batch APIs take
rows or columns directly:

```ts
await db.insertBatch("orders", rows); // an array of plain objects
```

See [bulk writes](/docs/engine.md#bulk-writes) for the columnar form and the buffered writer.

---

Minnow 0.1.0 · this page on the site: /docs/sql/dml/