PartiQL: writes & transactions

Part of the dialect series: change data. Fix one item, sweep a whole set, or move money across items atomically. Writes are deliberately the most guarded part of the dialect — complete-key rules, read-only stages, and a preview-first or two-step run before anything lands.

Clock functions

SET CreatedAt = CURRENT_TIMESTAMP        -- '2026-06-14T11:45:30Z' (UTC)SET Day       = CURRENT_DATE             -- '2026-06-14'SET ExpiresAt = unix_now() + 86400       -- epoch SECONDS, for TTL (a bare number)SET StampedMs = unix_now_ms()            -- epoch MILLISECONDS (JS-style)WHERE CreatedAt > CURRENT_DATE - 7       -- arithmetic folds before the wireWHERE CreatedAt > CURRENT_TIMESTAMP - INTERVAL '1' HOUR

DynamoDB has no clock — the studio substitutes a literal at run time, in UTC. That's a deliberate deviation from SQL's session-local clock: DynamoDB compares ISO strings lexicographically, so a local literal silently misses rows. They work in reads and writes; occurrences inside string literals are untouched.

• CURRENT_DATE / CURRENT_TIMESTAMP / utcnow() render ISO-8601 strings (a date, an instant, and an alias of the instant).
• unix_now() / CURRENT_EPOCH render epoch seconds as a bare number — the form TTL attributes store, which a string timestamp never matches.
• unix_now_ms() / CURRENT_EPOCH_MS render epoch milliseconds, for JS-origin timestamps.

Each clock takes an integer offset, folded into the literal before the wire (DynamoDB can't do arithmetic on the wire). A bare integer on a date counts whole days — CURRENT_DATE - 7; INTERVAL 'n' unit is explicit, with DAY / HOUR / MINUTE / SECOND (the number may be quoted or bare). A sub-day unit promotes CURRENT_DATE to a timestamp off midnight UTC. Epoch arithmetic counts in the rendered base unit — unix_now() - 3600 is an hour ago in seconds, unix_now_ms() - 500 is 500 ms ago. The strip discloses every fold (CURRENT_DATE - 7 → '2026-06-07').

DML: INSERT, UPDATE, DELETE

INSERT INTO "stage.Users"VALUE {'pk': 'User#9', 'sk': 'PROFILE', 'Name': 'Ada',       'CreatedAt': CURRENT_TIMESTAMP}​UPDATE "stage.Users"SET Role = 'admin'SET LastSeen = CURRENT_TIMESTAMPWHERE pk = 'User#9' AND sk = 'PROFILE'RETURNING ALL NEW *​DELETE FROM "stage.Users"WHERE pk = 'User#9' AND sk = 'PROFILE'RETURNING ALL OLD *

Writes are native, single-item operations — the statement you write is the statement DynamoDB runs:

• INSERT INTO … VALUE {…} — one item, written as a PartiQL object literal (string keys, typed values; nested maps and lists follow the same literal syntax). Inserting over an existing primary key is rejected by DynamoDB itself — INSERT never silently overwrites; to say what to do instead when the key is taken, reach for ON CONFLICT (next).
• UPDATE … SET … WHERE … — the WHERE must name the complete primary key (partition key, plus the sort key when the table has one); the editor flags anything looser before it runs. DynamoDB's native clause forms pass through as-is — repeated SET assignments, and REMOVE to drop an attribute.
• DELETE FROM … WHERE … — same complete-key rule.
• RETURNING ALL OLD * / ALL NEW * — echo the item as it was before, or stands after, the write. Available on single-statement writes (not inside transactions).
• Clock functions render in writes — 'CreatedAt': CURRENT_TIMESTAMP stamps the UTC instant at run time.
• ? positional parameters work in single read statements and saved functions — not yet combined with subqueries, aggregates, scripts, or transactions (named parameters are below).
• Read-only stages refuse writes at the dispatch layer, regardless of which surface issued them — the console, the item editor, a saved function, or a transaction.

Upserts: INSERT … ON CONFLICT

-- skip if the key already exists (a counted skip, not an error)INSERT INTO "stage.Users"VALUE {'pk': 'User#9', 'sk': 'PROFILE', 'Name': 'Ada'}ON CONFLICT DO NOTHING​-- overwrite the whole item if the key is takenINSERT INTO "stage.Users"VALUE {'pk': 'User#9', 'sk': 'PROFILE', 'Name': 'Ada L.'}ON CONFLICT DO REPLACE​-- upsert: create the row, or apply this update when it already existsINSERT INTO "stage.Users"VALUE {'pk': 'User#9', 'sk': 'PROFILE', 'visits': 1}ON CONFLICT DO UPDATE SET visits = if_not_exists(visits, 0) + 1

Plain INSERT refuses to overwrite an existing item — DynamoDB's rule. An ON CONFLICT clause says what to do instead when the primary key is already taken, and each form lowers to the one native write it maps onto (disclosed in the "executes as" strip). Like the other single-item writes it runs on the first press — no preview ritual — and a read-only stage refuses it like any write.

• DO NOTHING — a PutItem guarded by attribute_not_exists; an existing key is a counted skip, not an error. The idempotent "insert only if absent".
• DO REPLACE — an unguarded PutItem; it overwrites the whole item when the key exists (the effect a plain INSERT refuses to have).
• DO UPDATE SET … — a native UpdateItem upsert: the SET applies when the row exists, and the other VALUE attributes seed a freshly created row (each lowered to if_not_exists), so one atomic write covers both create and update. The SET speaks the same grammar as UPDATE — literals and attr ± number arithmetic.

The conflict target is always the primary key: a bare ON CONFLICT means "on the key", and an explicit ON CONFLICT (pk) / ON CONFLICT (pk, sk) must name exactly the key. DynamoDB has no uniqueness anywhere outside the primary key, so a non-key target is refused before the wire, with the reason named.

Two ways to change more than one item: a set-based write that sweeps every row matching a predicate (next), or a transaction that lands a fixed set of writes all-or-nothing (further down). They're different guarantees — a sweep is per-item, a transaction is atomic.

Set-based writes

UPDATE "stage.Accounts" SET status = 'archived' WHERE status = 'dormant'DELETE FROM "stage.Outbox"  WHERE CreatedAt < CURRENT_TIMESTAMP - 30

An UPDATE / DELETE whose WHERE doesn't name one full primary key — exactly the shape DynamoDB itself refuses — runs as a bulk write in the PartiQL console, and the contract is preview-first:

• The first Run writes nothing. It finds the matching items (capped at 1,000 — past that it refuses and asks for a narrower WHERE) and lands them in the grid: the preview is the write set, row for row.
• An unchanged second Run writes exactly those items — one native UpdateItem / DeleteItem per row, keyed by primary key. Any edit disarms and re-previews.
• Every write re-checks the WHERE atomically as a native ConditionExpression, so a row that changed since the preview is skipped and counted, never blindly overwritten. The status line reports applied / skipped / planned plus the billed WCU.

SET supports the same shapes as transactions — literals, attr ± number arithmetic, REMOVE, both spellings. The WHERE re-check supports = <> != < <= > >=, BETWEEN, begins_with, literal IN lists, and IS [NOT] MISSING (compiled to attribute_not_exists / attribute_exists) — combined with AND / OR / NOT and parentheses, compiled into one boolean condition. Only RETURNING and a predicate with no native spelling refuse, with the reason named — a bulk write never silently drops a predicate it can't re-check.

Scripts

The PartiQL console runs ;-separated statements in order, as independent requests, each landing in its own result tab. A failed statement marks its tab and the run continues — partial results beat losing everything. Semicolons inside string literals never split. Diagnostics and the strip are script-aware, per statement.

Transactions

BEGIN TRANSACTION;UPDATE "stage.Accounts" SET balance = balance - 100 WHERE accountId = 'A1';UPDATE "stage.Accounts" SET balance = balance + 100 WHERE accountId = 'B2';INSERT INTO "stage.Ledger" VALUE {'ledgerId': 'TX#123', 'amount': 100};COMMIT;

BEGIN TRANSACTION; …; COMMIT; turns the script into one all-or-nothing native transaction. The editor validates every rule in realtime — where DynamoDB would report an opaque cancellation after the run, the studio catches violations at the keystroke: writes only, each UPDATE/DELETE must target exactly one item by its full key, each item at most once, up to 100 statements. Extra WHERE conditions compile to an atomic condition check.

Execution is a deliberate two-step run: the first Run validates and shows the plan, an unchanged second Run executes — any edit re-arms the preview. The request carries an idempotency token (a retry is a no-op, not a double-apply), clock functions share one instant across the block, and a cancellation reports per-statement reasons instead of DynamoDB's opaque error. Every run reports the billed capacity in the status line (· 3 WCU, or · 4 RCU across 2 tables for a read transaction).

Read transactions

-- one consistent snapshot across exact-key readsBEGIN TRANSACTION;SELECT * FROM "stage.Accounts" WHERE accountId = 'A1';SELECT * FROM "stage.Accounts" WHERE accountId = 'B2';COMMIT;

A transaction isn't only for writes. A block of SELECTs — each an exact key lookup (full primary key, no extra predicates, no LIMIT / ORDER BY / aggregates) — runs as one native read transaction: every item served from a single consistent snapshot, which no sequence of individual reads can guarantee. Reads skip the two-step ritual and execute on the first press — there's nothing to double-apply — and run fine against a read-only stage, since the lock guards writes only. A key that matches nothing is an empty result, not an error; a block can't mix reads and writes (DynamoDB's rule, caught in the editor).

BEGIN TRANSACTION;
UPDATE "stage.Accounts" SET balance = balance - 100 WHERE accountId = 'A1';
UPDATE "stage.Accounts" SET balance = balance + 100 WHERE accountId = 'B2';
INSERT INTO "stage.Ledger" VALUE {'ledgerId': 'TX#123', 'amount': 100};
COMMIT;

Query parameters: reusable questions

-- author the query with named placeholders…SELECT order_id, status, totalFROM "orders"."GSI_status"WHERE status = {Status} AND created_at >= {Since:DateTimeOffset}​-- …it lowers to positional ? with values bound alongside (no injection surface)WHERE status = ?  AND  created_at >= ?     -- ? <- 'shipped'   ? <- '2026-06-01'

Lift a literal into a named {parameter} and a query becomes a reusable question — type a fresh value and re-run instead of editing the statement. Named parameters use the same placeholder grammar as key templates: {name}, {name:format}, {name|transform}. At run time each {name} lowers to a positional ? and the value binds alongside — it never enters the statement text, so there's no injection surface. A :format shapes the value's text (a date's layout, say), not its wire type.

In the PartiQL console a Parameters panel lists every {name} with a typed value box, and Extract as parameter lifts a selected literal into a fresh one. A saved query that carries parameters becomes a function — runnable from the function form, ⌘K, and history.

INSERT INTO "stage.Users"VALUE {'pk': 'User#9', 'sk': 'PROFILE', 'visits': 1}ON CONFLICT DO UPDATE SET visits = if_not_exists(visits, 0) + 1

UPDATE "stage.Sessions"SET ExpiresAt = unix_now() + 604800WHERE pk = 'Session#42' AND sk = 'META'

BEGIN TRANSACTION;UPDATE "stage.Accounts" SET balance = balance - 100  WHERE accountId = 'A1' AND balance >= 100;UPDATE "stage.Accounts" SET balance = balance + 100  WHERE accountId = 'B2';INSERT INTO "stage.Ledger" VALUE {'ledgerId': 'TX#124', 'amount': 100};COMMIT;