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 < $1Any 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 < $1SET 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_idTRUNCATE [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: logsMutation 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 = -1A 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.cityA 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, loginsINSERT 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.receivedWhen 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 REPLACEIt 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 > 0The 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_priceThe 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);
ENDDROP 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;
ENDIn 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 objectsSee bulk writes for the columnar form and the buffered writer.