SQL

Writing data

Insert, update, delete, upsert, and RETURNING.

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. The values then fill the table's columns in declaration order, and a row shorter than the table leaves the columns after it to their defaults, as PostgreSQL does:

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

Every row of one VALUES list has the same length, and no row may be longer than the table. 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

SET also takes PostgreSQL's row-value form, which assigns several columns at once, and the keyword DEFAULT, which assigns what an omitted INSERT slot would get — the column's default, or NULL when it has none:

UPDATE orders SET (status, discount) = ('refunded', 0) WHERE order_id = $1;
UPDATE orders SET status = DEFAULT WHERE order_id = $1;

= ROW(…) spells the row value too. Every value reads the row as it was before the statement, so SET (a, b) = (b, a) swaps two columns. A sub-SELECT source, SET (a, b) = (SELECT …), is not supported; give each column its own scalar subquery. DEFAULT is refused for an auto-increment column.

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.

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:

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

Changing a primary key

An UPDATE may set the primary key itself. The row moves to the new key, every declared reference to it follows in the same commit, and the old key keeps reading and writing the row for keyForwardRetentionMs (10 days by default):

UPDATE orders SET order_id = 900001 WHERE order_id = -1

A new key that another row holds, or that an earlier rekey retired, is refused with UniqueConstraintError, and nothing is written. UPDATE triggers fire for the move; INSERT and DELETE triggers do not. Offline sync and rekeying walks through every step.

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. In the other direction, a number or boolean written to a TEXT column stores its text form, '42' or 'true', PostgreSQL's assignment cast to text. A fractional number written to an INTEGER column is refused where PostgreSQL would round it; round it yourself with ROUND() or a cast. These are properties of SQL statements; the typed programmatic API still takes values of the column's JavaScript type.

The conflict target names a constraint by its columns, in any order: the primary key, or a UNIQUE constraint or unique index. A proposal that collides on a UNIQUE column updates the row that holds the value, under that row's own key:

CREATE TABLE accounts (
  account_id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  logins INTEGER NOT NULL DEFAULT 0
);

INSERT INTO accounts AS a (email) VALUES ($1)
ON CONFLICT (email) DO UPDATE SET logins = a.logins + 1
RETURNING account_id, logins

INSERT INTO accounts AS a names the target row for the assignments, the conflict predicate, and RETURNING; as in PostgreSQL, the AS is required there. A collision on a constraint other than the target is still an error. ON CONFLICT ON CONSTRAINT name is not supported; name the constraint's columns instead. The assignments cannot change the conflict target's own columns: ON CONFLICT (customer_id) DO UPDATE SET customer_id = … is refused at compile time, where PostgreSQL would move the row. To change a row's key, use UPDATE (see rekeying).

DO NOTHING is also available, with or without a conflict target — a bare ON CONFLICT DO NOTHING skips rows that collide on any unique constraint, the key or a UNIQUE column — and a later duplicate 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 row twice, so it cannot propose the same target value twice either. The conflict target's columns 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, and it reads the target's rows only: a column of an UPDATE … FROM or DELETE … USING source is refused, where PostgreSQL would return it.

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. A key retired by a rekey counts as taken while it still forwards to its 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.

1.0 cutover: foreign-key actions and trigger order

The current 0.x engine cascades child changes before a parent's BEFORE DELETE trigger. The 1.0 rebuild follows PostgreSQL: the parent's BEFORE trigger runs first, then the parent row changes. Referential actions run as system AFTER triggers, sorted by name alongside user AFTER triggers. Child changes run their own BEFORE triggers; child AFTER events join the pending queue and can run after the remaining parent AFTER triggers. Quoted user names that sort before RI_ConstraintTrigger_* can run before the action; ordinary lowercase names sort after that system prefix. UPDATE actions and rekeys follow the same scheduling model. BEFORE triggers also use name order, rather than creation order. Unquoted trigger names fold to lowercase; quoted names keep their spelling.

The 1.0 rebuild reserves the exact resolved-name prefix RI_ConstraintTrigger_ for those system triggers. Creating a user trigger with that prefix fails with SQLSTATE 42939 (reserved_name). The current 0.x engine accepts it, so rename such triggers before the cutover. For example, "RI_ConstraintTrigger_audit" is reserved; unquoted RI_ConstraintTrigger_audit folds to ri_constrainttrigger_audit and is outside that prefix. All other names follow PostgreSQL's trigger-name ordering.

For example, suppose children.parent_id references parents.id with ON DELETE CASCADE, and children.value has CHECK(value > 0):

CREATE TRIGGER validate_children BEFORE DELETE ON parents
BEGIN
  UPDATE children SET value = -1 WHERE parent_id = OLD.id;
END

In 0.x, deleting a parent can succeed because its children have already been deleted when this body runs. At the 1.0 cutover, the child still exists when BEFORE runs: its CHECK fails and the entire parent delete rolls back. This is an intentional behavior change. Trigger bodies and FK actions remain atomic with the originating statement.

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, [NOT] DEFERRABLE, and ISOLATION LEVEL below SERIALIZABLE, in PostgreSQL's grammar: modes are separated by spaces or commas. Each mode item is checked in written order; when all are valid, the last one of each kind applies. In a savepoint that starts READ WRITE, SET TRANSACTION READ ONLY, READ WRITE fails with 25001, while READ WRITE, READ ONLY succeeds. If the transaction is already READ ONLY, both lists fail. Every transaction reads one snapshot and commits atomically, which satisfies those levels, while SERIALIZABLE is refused rather than silently downgraded. A READ ONLY transaction refuses writes, as in PostgreSQL. INSERT, UPDATE, DELETE, MERGE, TRUNCATE, schema changes, nextval and row locks (SELECT … FOR UPDATE, FOR SHARE, and the KEY forms) fail with "cannot execute … in a read-only transaction". This holds however the statement arrives: through execute, query, queryCursor, a built query's run, or a live subscription's runs (which are not statements of the transaction, so they never count for READ WRITE). nextval is refused when it runs, as in PostgreSQL: currval works, and so does a nextval in a CASE branch not taken or under a false WHERE.

SET TRANSACTION READ ONLY makes the open transaction read-only at any point. Switching a read-only transaction back with SET TRANSACTION READ WRITE is refused once the transaction has run a statement (reads count, whichever method ran them), and always inside a savepoint. Setting READ WRITE on a transaction that is already read-write is allowed. As in PostgreSQL, READ ONLY set after SAVEPOINT lasts only until that savepoint ends: ROLLBACK TO and RELEASE both return to the mode the transaction had when the savepoint was set. Inserts and updates through the table methods (insert, update, …) are not SQL statements of the transaction. They wait for it to finish and then commit on their own.

Isolation-level changes are allowed only before the first query and outside a savepoint; setting the already-requested isolation level again is allowed at any time. The requested level starts at READ COMMITTED unless BEGIN supplies one. Both SET TRANSACTION DEFERRABLE and NOT DEFERRABLE are refused after the first query or inside a savepoint, even when the value is unchanged. These admission rules follow PostgreSQL; execution still uses the same fixed snapshot for every accepted isolation level. Reads and writes both count as queries for these rules.

Other session settings — SET name TO value, SET LOCAL …, 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.

On this page