# PartiQL: writes & transactions

Part of the [dialect series](https://www.kanject.com/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**
- 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.

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

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

## Transactions

```sql
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.

> **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.Accounts" WHERE accountId = 'A1';
SELECT * FROM "stage.Accounts" WHERE accountId = 'B2';
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.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;
```

_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://www.kanject.com/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.
- `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.
- 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 a session item 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.Sessions"
SET ExpiresAt = unix_now() + 604800
WHERE pk = 'Session#42' AND sk = 'META'
```

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

Debit account `A1` by 100, credit `B2`, and record a ledger row — all-or-nothing, and only if `A1` 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 `A1` can't cover the debit, **none** of the three writes land. First Run previews, an unchanged second Run commits.

```sql
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;
```

> **Where next?:** Reads and writes, mastered — every lowering disclosed along the way. The control plane remains: [Tables, indexes & replicas](https://www.kanject.com/docs/dynostudio-partiql-ddl/) creates, alters, and drops the tables, GSIs, and Kanject markers your queries run against. Or keep the [keyword reference](https://www.kanject.com/docs/dynostudio-partiql-keywords/) open as you write.

---
_Source: https://www.kanject.com/docs/dynostudio-partiql-writes/ · Kanject Docs_