# PartiQL: writes & transactions

Part of the [dialect series](https://dynostudio.dev/docs/dynostudio-partiql/): 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.

**You'll learn**

- Write single items with `INSERT` / `UPDATE` / `DELETE` under the complete-key rule
- Resolve a key collision with `INSERT … ON CONFLICT` — **skip**, **replace**, or **upsert** in one native write
- Sweep many items with a **preview-first** set-based write, and know it's per-item — not atomic
- Commit a fixed set of writes **all-or-nothing** with a transaction, and read a **consistent snapshot**
- Run reads and write transactions in a **success-gated `THEN` workflow** that stops on the first failure
- Stamp values with clock functions and turn a query into a reusable **parameterized** question

## Clock functions

```sql
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 wire
WHERE 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

```sql
INSERT INTO "stage.Users"
VALUE {'pk': 'User#9', 'sk': 'PROFILE', 'Name': 'Ada',
       'CreatedAt': CURRENT_TIMESTAMP};

UPDATE "stage.Users"
SET Role = 'admin'
SET LastSeen = CURRENT_TIMESTAMP
WHERE 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.

After a write, the results surface keeps a persistent **write receipt** instead of calling DynamoDB's empty response `0 rows`. It names the verb and table and shows billed WCU and elapsed time when available. A `RETURNING` item stays in the normal grid; without `RETURNING`, DynamoDB does not provide a reliable affected-item count, so the receipt says the count is unavailable rather than inventing one. If a write request is cancelled, the receipt marks the outcome **unknown** and tells you to verify the item before retrying.

## Upserts: INSERT … ON CONFLICT

```sql
-- 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 taken
INSERT 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 exists
INSERT 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.

> **Seed a counter with if_not_exists:** In `DO UPDATE`, a counter the `VALUE` also seeds wants the explicit idiom — `SET visits = if_not_exists(visits, 0) + 1`, not `SET visits = visits + 1`. On a freshly created row the bare `visits` doesn't exist yet, so there's nothing to add to; `if_not_exists` supplies the `0` floor, so the first upsert lands `1` and every later one increments.

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

```sql
UPDATE "stage.Users" SET status = 'archived' WHERE status = 'inactive';
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.

> **A sweep is per-item, not atomic:** A set-based write applies **one native `UpdateItem` / `DeleteItem` per row** — it is *not* a transaction. If one write fails partway, the run **stops, prior writes stand**, and the `applied / skipped / planned` counts say exactly where it stopped. When you need all-or-nothing across the set, that's a **transaction** (below), capped at 100 statements. Reach for a sweep when per-item progress is acceptable; reach for a transaction when partial application is not.

## 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.

When later work must depend on earlier success, use the standalone **`THEN` workflow gate** below. That is the deliberate distinction: a normal script continues after a failed statement; a `THEN` workflow stops.

## Success-gated workflows with THEN

```sql
SELECT pk, balance
FROM "stage.Users"
WHERE pk IN ('User#9', 'User#12') AND sk = 'PROFILE';

THEN

BEGIN TRANSACTION;
UPDATE "stage.Users"
SET balance = balance - 100
WHERE pk = 'User#9' AND sk = 'PROFILE' AND balance >= 100;
UPDATE "stage.Users"
SET balance = balance + 100
WHERE pk = 'User#12' AND sk = 'PROFILE';
COMMIT;

THEN

SELECT pk, balance
FROM "stage.Users"
WHERE pk IN ('User#9', 'User#12') AND sk = 'PROFILE'
ORDER BY pk;
```

`THEN` is a **Kanject workflow operator** that must occupy its own line between semicolon-closed units. It is closer to a fail-fast `&&` than SQL Server's `GO`: the next unit starts only when every earlier unit succeeded. It does not make the whole file one transaction.

- Each standalone `SELECT` is one independent request and uses the console's current eventual/strong consistency setting.
- Each `BEGIN TRANSACTION; …; COMMIT;` block is one independent **atomic boundary**. An earlier COMMIT is never rolled back by a later read or transaction failure.
- A leading read failure prevents later transactions from starting. A failure or cancellation after a COMMIT reports **partial completion** and skips every remaining step.
- A workflow may place standalone `SELECT`s and complete **write** transaction blocks in any order. Plain writes outside a transaction, read-only transaction blocks, variables, branching, and result piping are refused.
- `THEN` inside `CASE … WHEN … THEN … END` keeps its ordinary PartiQL expression meaning; only a standalone line separates workflow units.

A workflow containing writes follows the same safety ritual as a write transaction: the first Run validates and arms the **whole unchanged workflow**; the second button says **Execute workflow**. A read-only workflow runs immediately. Every step receives its own result tab, and the completion status names the committed transaction count, action summary, and read consistency.

> **Success-gated does not mean globally atomic:** Every transaction block is all-or-nothing **inside its own BEGIN / COMMIT boundary**. `THEN` controls whether the next unit starts; it cannot roll back a transaction that already committed. If step 3 fails after step 2 committed, DynoStudio reports partial completion and leaves the committed write standing.

## Transactions

```sql
BEGIN TRANSACTION;
UPDATE "stage.Users" SET balance = balance - 100
  WHERE pk = 'User#9' AND sk = 'PROFILE';
UPDATE "stage.Users" SET balance = balance + 100
  WHERE pk = 'User#12' AND sk = 'PROFILE';
INSERT INTO "stage.Outbox" VALUE {'pk': 'Outbox#transfers', 'sk': '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.

> **Arithmetic SET compiles to a native UpdateExpression:** DynamoDB's transactional PartiQL has no spelling for `SET balance = balance - 100`, so the studio compiles the whole block to the native write form (`TransactWriteItems`): each `SET` becomes an `UpdateExpression` (`SET #balance = #balance - :v`), and any extra `WHERE` predicates become the atomic `ConditionExpression`. Anything the native form can't express identically — `OR` / `NOT`, nested document paths, non-scalar `SET` values — is an **honest refusal** naming the fix, never a silent drop. Because it compiles to keyed writes, an arithmetic block needs a parsed schema for the table's keys.

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

```sql
-- one consistent snapshot across exact-key reads
BEGIN TRANSACTION;
SELECT * FROM "stage.Users" WHERE pk = 'User#9' AND sk = 'PROFILE';
SELECT * FROM "stage.Users" WHERE pk = 'User#12' AND sk = 'PROFILE';
COMMIT;
```

A transaction isn't only for writes. A block of `SELECT`s — 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).

**Try it · Transaction**

```sql
BEGIN TRANSACTION;
UPDATE "stage.Users" SET balance = balance - 100
  WHERE pk = 'User#9' AND sk = 'PROFILE';
UPDATE "stage.Users" SET balance = balance + 100
  WHERE pk = 'User#12' AND sk = 'PROFILE';
INSERT INTO "stage.Outbox" VALUE {'pk': 'Outbox#transfers', 'sk': 'TX#123', 'amount': 100};
COMMIT;
```

_Executes as:_ TransactWriteItems · 3 writes — All-or-nothing — if any item's condition fails, none of the three land. Two-step run: the first Run validates and previews the plan; an unchanged second Run commits.

_Result:_ ✓ Committed · 3 writes applied atomically · 3 WCU · idempotency token retained

_Run it live in DynoStudio:_ https://dynostudio.dev/dynostudio/

## Query parameters: reusable questions

```sql
-- author the query with named placeholders…
SELECT order_id, status, total
FROM "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.

> **Note:** Authoring parameters is a **Professional** capability; *running* an already-parameterized saved function is **free at every edition**. v1: named (and `?` positional) parameters work in single read statements and saved functions — not yet combined with subqueries, aggregates, scripts, or transactions.

**AWS background**

- [Amazon DynamoDB transactions](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/transaction-apis.html) — AWS docs: the TransactWriteItems / TransactGetItems semantics the transaction blocks compile to.
- [Expiring items with DynamoDB Time to Live (TTL)](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/TTL.html) — AWS docs: why TTL attributes store epoch seconds — the form `unix_now()` renders.

**Recap**

- Single-item `INSERT` / `UPDATE` / `DELETE` need the **complete primary key**; `RETURNING ALL OLD / NEW *` echoes the item.
- Successful writes keep a **write receipt**; without `RETURNING`, the affected count is unavailable rather than reported as `0 rows`.
- `INSERT … ON CONFLICT` resolves a key collision in one native write — `DO NOTHING` (skip), `DO REPLACE` (overwrite), or `DO UPDATE` (upsert, seeding new rows via `if_not_exists`).
- A **set-based** write is preview-first and **per-item** (not atomic); a **transaction** is **all-or-nothing**, and a read transaction gives one consistent snapshot.
- A standalone **`THEN`** composes reads and complete write transactions into a fail-fast workflow; every COMMIT remains its own atomic boundary.
- Arithmetic `SET` compiles to a native `UpdateExpression`; anything it can't express identically refuses with the fix named.
- Clock functions render **UTC** at run time (TTL wants `unix_now()`); named `{parameters}` turn a query into a reusable, injection-safe question.

**Try it yourself**

**1. Upsert a visit counter**

Record a visit for `User#9`: create the row with `visits = 1` when it doesn't exist yet, otherwise increment.

_Hint:_ On a freshly created row the bare `visits` doesn't exist yet — there's nothing to add to.

_Solution:_ `if_not_exists(visits, 0)` supplies the floor, so the first upsert lands `1` and every later one increments — one atomic native `UpdateItem` covers both create and update.

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

**2. Stamp a TTL**

Give the `Outbox#demo` event an `ExpiresAt` that DynamoDB's TTL sweeper will honour, seven days from now.

_Hint:_ TTL attributes store epoch **seconds** as a bare number — an ISO string never matches them.

_Solution:_ `unix_now()` renders epoch seconds, and the `+ 604800` offset (7 × 86,400) folds into the literal before the wire — the strip discloses the fold.

```sql
UPDATE "stage.Outbox"
SET ExpiresAt = unix_now() + 604800
WHERE pk = 'Outbox#demo' AND sk = 'EVT#0001'
```

**3. Move money atomically — with a guard**

Debit `User#9`'s balance by 100, credit `User#12`, and record an audit event in the outbox — all-or-nothing, and only if `User#9` can cover it.

_Hint:_ A script is per-statement; only a transaction block is atomic. Extra `WHERE` predicates compile to an atomic condition check.

_Solution:_ The block compiles to one `TransactWriteItems`; the extra `balance >= 100` predicate becomes a native `ConditionExpression`, so if `User#9` can't cover the debit, **none** of the three writes land. First Run previews, an unchanged second Run commits.

```sql
BEGIN TRANSACTION;
UPDATE "stage.Users" SET balance = balance - 100
  WHERE pk = 'User#9' AND sk = 'PROFILE' AND balance >= 100;
UPDATE "stage.Users" SET balance = balance + 100
  WHERE pk = 'User#12' AND sk = 'PROFILE';
INSERT INTO "stage.Outbox" VALUE {'pk': 'Outbox#transfers', 'sk': 'TX#124', 'amount': 100};
COMMIT;
```

> **Where next?:** Reads and writes, mastered — every lowering disclosed along the way. The control plane remains: [Tables, indexes & replicas](https://dynostudio.dev/docs/dynostudio-partiql-ddl/) creates, alters, and drops tables, GSIs, and analytics replicas. Kanject's model-driven access paths live in [Marker indexes](https://dynostudio.dev/docs/dynostudio-marker-indexes/). Or keep the [keyword reference](https://dynostudio.dev/docs/dynostudio-partiql-keywords/) open as you write.

---
_Source: https://dynostudio.dev/docs/dynostudio-partiql-writes/ · DynoStudio Docs_