Writing data

Prefer a typed builder to SQL strings? The Kysely adapter supports inserts, updates, deletes, transactions, and RETURNING.

Insert

INSERT INTO customers (customer_id, name, city, signed_up_on)
VALUES ($1, $2, $3, $4)

You can add several rows in one statement. INSERT … SELECT also works: Minnow reads from the current transaction, including rows written earlier in it, then adds the result:

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'

Any query expression can feed the insert — a WITH, a UNION, a parenthesized member — and ON CONFLICT applies to the rows it produces exactly as to a VALUES list. A VALUES row may carry scalar subqueries, evaluated against the statement snapshot and any earlier staged writes in its transaction. Autocommit retries recompute them after a concurrent commit:

INSERT INTO invoices (invoice_no, customer_id)
VALUES ((SELECT MAX(invoice_no) + 1 FROM invoices), $1)

The column list may be omitted when every table column is supplied in declaration order:

INSERT INTO shipments VALUES ('A-1', 1, CURRENT_TIMESTAMP)

The value count must then equal the table's full column count. Prefer an explicit list in application code so a later schema change cannot silently change the positional meaning. Statement-time and volatile values are evaluated when the statement runs, not when its cached plan is compiled: CURRENT_DATE, CURRENT_TIMESTAMP (also spelled NOW(), transaction_timestamp(), statement_timestamp(), or clock_timestamp()), and LOCALTIME are stable across all rows of one statement, while random(), gen_random_uuid(), and nextval(...) run for each written expression.

Columns you leave out use their catalog default, or NULL when they are nullable. A NOT NULL column without a value or a default is an error. PostgreSQL's explicit forms work too:

INSERT INTO preferences DEFAULT VALUES;
INSERT INTO events (event_id, kind, noted_at) VALUES ($1, $2, DEFAULT);

DEFAULT VALUES inserts one row. DEFAULT can occupy any slot in a VALUES list, including several rows at once. Both run the same stored defaults as an omitted column; there is no hidden adapter-only behavior. An explicit NULL never invokes a default.

Update and delete

UPDATE orders SET status = 'refunded', total = total - $1 WHERE order_id = $2
DELETE FROM orders WHERE placed_at < $1

Any WHERE clause works. Minnow finds the matching rows and records the change. SET expressions can read the row's current values, as total - $1 does above, and a scalar subquery — correlated or not — reads the rows as they were before the statement:

UPDATE customers AS c
SET order_count = (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id)
WHERE c.signed_up_on < $1

Both statements take PostgreSQL's table alias (UPDATE customers AS c, DELETE FROM orders o), which the assignments, predicates, and RETURNING may qualify by. Subquery predicates work the same way they do in SELECT, including IN (SELECT ...), NOT IN (SELECT ...), and correlated EXISTS / NOT EXISTS. UPDATE … FROM and DELETE … USING join extra sources — tables, views, or derived tables — to the target, as PostgreSQL does; the assignments and predicates may read them, and a target row matched by several source rows is touched once:

UPDATE customers c
SET last_order_at = o.placed_at
FROM (SELECT customer_id, MAX(placed_at) AS placed_at FROM orders GROUP BY customer_id) o
WHERE o.customer_id = c.customer_id

TRUNCATE [TABLE] name removes every row of one table, the same as an unfiltered DELETE; it reports the rows removed, takes one table at a time, and refuses CASCADE.

UPDATE requires a table with a unique key: logs

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

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

Upsert

ON CONFLICT is Minnow's PostgreSQL-compatible upsert form.

INSERT INTO customers (customer_id, name, city, signed_up_on)
VALUES ($1, $2, $3, $4)
ON CONFLICT (customer_id) DO UPDATE SET name = EXCLUDED.name, city = EXCLUDED.city

A string constant or bound parameter written into a datetime, number, or boolean column is read in the column's type, the way PostgreSQL types an unknown literal by its target: INSERT INTO orders (placed_at) VALUES ('2026-04-01T00:00:00Z'), SET quantity = '7', and a string bound to $1 all store the typed value, and RETURNING echoes it. Text that does not parse in that type is refused with the column's type error. This is a property of SQL statements; the typed programmatic API still takes values of the column's JavaScript type.

DO NOTHING is also available, with or without a conflict target — a bare ON CONFLICT DO NOTHING skips rows that collide on the table's unique key — and a later duplicate key in the same VALUES list is skipped after the first proposal is retained. EXCLUDED refers to the row that would have been inserted, including its defaults; a bare column refers to the stored row. Assignments can combine both with parameters, constants, CASE, arithmetic, and scalar functions:

INSERT INTO inventory (sku, received)
VALUES ($1, $2)
ON CONFLICT (sku) DO UPDATE SET on_hand = on_hand + EXCLUDED.received

When every non-key column should be replaced, Minnow also accepts a concise whole-row form:

INSERT INTO customers (customer_id, name, city, signed_up_on)
VALUES ($1, $2, $3, $4)
ON CONFLICT (customer_id) DO REPLACE

It is not PostgreSQL syntax; it is a Minnow extension with the same result as assigning every non-key column from EXCLUDED, and it remains well-defined for a table whose key is its only column.

Conflict updates and fresh inserts from one statement publish atomically. One statement cannot update the same existing key twice. The conflict key itself cannot be reassigned, and aggregates, windows, and subqueries are not allowed in the assignment. A predicate after the assignment list can turn a conflict into a no-op:

INSERT INTO inventory (sku, received)
VALUES ($1, $2)
ON CONFLICT (sku) DO UPDATE
SET on_hand = on_hand + EXCLUDED.received
WHERE EXCLUDED.received > 0

The predicate can read the stored target row and EXCLUDED, with the same scalar-expression boundary as assignments.

RETURNING

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

UPDATE products SET list_price = list_price * 1.05
WHERE product_id = $1
RETURNING product_id, name, list_price

The target qualifier is optional for columns and *, so PostgreSQL-builder output such as RETURNING products.product_id and RETURNING products.* is equivalent. Any scalar expression works too, with the semantics it has in a SELECT: RETURNING product_id, list_price * 1.05 AS next_price, UPPER(name) AS label reads the post-update row for UPDATE, the written row for INSERT, and the removed row for DELETE. Aggregates are not allowed in RETURNING.

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.

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

try {
  await db.execute("INSERT INTO customers (customer_id, name) VALUES ($1, $2)", [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.

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.

Trigger names are global within a database, and creation is atomic: two tabs cannot both create the same name, and an old DROP TRIGGER cannot delete a later same-name replacement. The whole body — every NEW/OLD binding, target column, and default — is resolved before the trigger is admitted, and views cannot own or be targeted by triggers. A later column or default change that would invalidate a stored body is refused; a write already staged under the old trigger catalog gets a schema conflict instead of publishing stale effects.

Atomicity

One statement is one commit. Several statements that must land together belong in a write scope:

const { version } = await db.write(async (tx) => {
  await tx.updateBatch("stock", { keys: [sku], changes: { on_hand: [nextOnHand] } });
  await tx.insertBatch("shipments", [{ sku, qty, at: 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:

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 (or ABORT) discards the lot (END commits, as COMMIT does). BEGIN and SET TRANSACTION accept READ ONLY, READ WRITE, and ISOLATION LEVEL below SERIALIZABLE: every transaction reads one snapshot and commits atomically, which satisfies those levels, while SERIALIZABLE is refused rather than silently downgraded. Session settings — SET name TO value, SET LOCAL …, SET TRANSACTION …, RESET name — are accepted and ignored, since an embedded single-session engine has nothing to configure by them; SET TIME ZONE accepts only UTC. SHOW setting answers the questions drivers ask on connection (server_version, search_path, timezone, transaction_isolation, …) with the engine's fixed values. 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:

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:

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

See bulk writes for the columnar form and the buffered writer.