DEV Community: DynoTable

DynamoDB Condition Expressions

DynoTable — Sun, 26 Jul 2026 06:56:22 +0000

A condition expression is a predicate DynamoDB evaluates on the existing item
before it commits your write. If the predicate is false, the write is
rejected and nothing changes. It is the closest thing DynamoDB has to a
WHERE clause on a write — and the only safe way to enforce an invariant.

How do DynamoDB condition expressions work?

A condition expression is a predicate DynamoDB evaluates server-side against the current item before committing a write. If it's true, the write proceeds; if false, the write is rejected with ConditionalCheckFailedException and nothing changes. It folds the check and the mutation into one atomic operation, so concurrent callers can't race a stale read.

It's a guard, not a filter. ConditionExpression runs server-side on the current item; a false result fails the write with ConditionalCheckFailedException.
It replaces read-then-write. No SELECT then UPDATE round trip — the check and the mutation are one atomic operation, so two callers can't race.
It's free to reject, not free to run. A failed conditional write still consumes write capacity. A rejected write bills WCUs for the size of the existing item it was checked against (minimum 1) — a failed create-if-absent costs 1 WCU.

Coming from SQL, you'd read the row, check it in app code, then update. In
DynamoDB that gap between read and write is a data-corruption bug waiting for a
concurrent caller. The condition expression closes the gap.

Where they apply

You attach a ConditionExpression to PutItem, UpdateItem, DeleteItem, and
each action inside TransactWriteItems. It is not part of Query or Scan
— those use FilterExpression, which is a different thing on the read path.

That distinction trips people up, so be precise:

	`ConditionExpression`	`FilterExpression`
Path	Writes (`Put`/`Update`/`Delete`)	Reads (`Query`/`Scan`)
Effect on failure	Rejects the whole write	Drops the item from results
Sees	The current item, pre-write	Each candidate item, post-read
Cost	Failed write still bills	Filtered items are still billed for the read

Both run server-side. The difference is what "false" does: a condition aborts a
mutation; a filter just hides a row you already paid to read.
(AWS: Condition Expressions)

The functions you'll actually use

The condition language is small. The workhorses:

attribute_exists(path) / attribute_not_exists(path) — does this attribute exist on the item? The classic idiom for "create only if absent" / "update only if present".
Comparators — =, <>, <, <=, >, >= — against a value or another attribute.
attribute_type, begins_with, contains, size — type and string/set checks.
BETWEEN … AND …, IN (…) — range and membership.
AND, OR, NOT, parentheses — to combine the above.

attribute_not_exists on the partition key is the canonical way to make
PutItem behave like an insert that won't clobber an existing item — DynamoDB
has no separate "insert" op, so the condition is the insert semantics.
(AWS: Comparison Operator and Function Reference)

A worked example: guarding a ledger against overdraft

Take a banking ledger. Each account is one item:

PK = "ACCT#a7f3"
SK = "BALANCE"
clearedCents = 50000
holdCents    = 0

The invariant: a debit must never push the available balance below zero, and you
must never debit an account that doesn't exist. Two rules, both enforceable in
the write itself.

The wrong way (the footgun)

Example	Notes
`GetItem ACCT#a7f3 / BALANCE → clearedCents = 50000`
`if (50000 >= 30000) ... ← app-side check`
`UpdateItem SET clearedCents = 20000`

Between the GetItem and the UpdateItem, a second debit can read the same
50000, pass its own check, and write too. Both succeed; the account goes
negative. This is a read-modify-write race, and no amount of app-side validation
fixes it — the check and the write are separate operations.

The right way

Fold the check into the write. Debit 30000 cents, conditional on the account
existing and holding enough:

UpdateItem  ACCT#a7f3 / BALANCE
  SET clearedCents = clearedCents - :amt
  ConditionExpression:
    attribute_exists(PK) AND clearedCents >= :amt

with :amt = 30000. If the balance is too low, or the item was never created,
DynamoDB rejects the write with
ConditionalCheckFailedException
and the balance
is untouched. The concurrent debit either sees the original balance and is
checked against it, or sees the updated one — never a stale read it acted on.

You can build and copy the exact expression — names, values, and all — with the
DynamoDB expression builder instead of
hand-assembling the ExpressionAttributeValues map.

Try it right here — this builder is preset to a guarded PutItem
(attribute_not_exists) so you can read the generated ConditionExpression:

Warning

clearedCents >= :amt compares the stored value, not the value you read
earlier. That's the whole point — the comparison happens after any concurrent
write has landed, so the guard is always evaluated against current truth.

Inspecting the guard in DynoTable

When a conditional write fails, you want to see the item's real state, not guess
at it. Pull the account item up and read clearedCents directly.

Read the rejection, don't retry blindly

ConditionalCheckFailedException is not a transient error — retrying the same
write changes nothing. It means a business rule fired: insufficient funds,
duplicate create, stale version. Surface it as a domain outcome, not an infra
blip.

Two things make failures debuggable:

ReturnValuesOnConditionCheckFailure: ALL_OLD — DynamoDB returns the current item alongside the failure, so you can show "balance was 20000, you asked for 30000" without a second read. (AWS: Working with Items)
Distinguishing the two failure reasons. attribute_exists(PK) AND clearedCents >= :amt collapses "no account" and "no funds" into one exception. If callers need to tell them apart, split into two writes or inspect the returned item.

Optimistic locking is the same trick

The version-number pattern is just a condition expression wearing a different
hat. Store a version attribute; every write asserts the version you read and
bumps it:

UpdateItem  ACCT#a7f3 / BALANCE
  SET clearedCents = :new, version = :next
  ConditionExpression: version = :seen

If another writer moved first, version = :seen is false, the write is rejected,
and you re-read and retry. This is how DynamoDB does concurrency control without
locks — assert what you saw, fail if it moved. (AWS: Optimistic Locking with
Version Number)
DynoTable's staging area runs this pattern for you — a
concurrent edit surfaces as a conflict to resolve, not a lost write.

Pitfalls and next steps

Names that collide with reserved words. status, size, name, and ~570 others are reserved. Alias them with ExpressionAttributeNames (#s = status) or the request is rejected with a ValidationException ('Attribute name is a reserved keyword'). The reserved-words checker takes your attribute names and hands back the alias map ready to paste.
A condition can't reference another item. It only sees the item being written. Cross-item invariants need TransactWriteItems with a per-action ConditionExpression, or a ConditionCheck against a sentinel item.
Failed writes still cost WCUs. A guard that rejects 90% of the time still bills for those rejections. Cheap insurance, but not free.

For modeling the keys these guards run against, see
single-table design and
Query vs Scan. When you're ready to issue conditional
writes against real data, download DynoTable and run them against
your own tables.

Composite Sort Keys in DynamoDB

DynoTable — Sun, 26 Jul 2026 06:56:21 +0000

A composite primary key is a partition key plus a sort key. The trick that
makes it powerful is what you put in the sort key: encode a hierarchy as one
delimited string, and a single Query reads an entire subtree in sort order — no
joins, no recursion, no second round-trip.

How do composite sort keys work in DynamoDB?

A composite sort key packs a hierarchy into one delimited string — root/photos/2026/ — that DynamoDB stores in UTF-8 byte order. Because the layout already matches the tree, a single Query with begins_with(SK, "root/photos/") reads an entire subtree in path order. No joins, no recursion, no second round-trip — just a prefix scan over a contiguous slice.

The sort key is a sortable string, not just an ID. Pack a path into it — root/photos/2026/ — and DynamoDB stores the partition's items in UTF-8 byte order automatically.
A delimiter turns prefix matches into subtree reads. begins_with(SK, "root/photos/") returns every descendant of that folder in one query.
Sort keys support range conditions, not arbitrary filters. You get begins_with, between, >, < — design the key so the read you need is a prefix or a range, not a Scan.
The delimiter is load-bearing. Pick one that can't appear in a path segment, or two unrelated branches collide.

Why the sort key is the whole game

Coming from SQL, you'd model a folder tree with a parent_id self-join and walk
it recursively — one query per level. In DynamoDB that's an N+1 footgun against a
key-value store that has no joins.

DynamoDB stores every item under a partition key sorted by its sort key, in UTF-8
byte order for strings (AWS: Query key conditions).
So if your sort key is the path, the physical layout already matches the tree.
A read becomes a prefix scan over a contiguous slice — not a graph walk.

That's the shift: the sort key isn't an identifier you match exactly. It's a
sortable address. Design it and the query falls out for free.

Model a file-system tree

Say you're storing per-account file trees. One drive per account is the natural
partition; the path inside it is the sort key.

PK	SK	node_type	bytes
DRIVE#a91	root/	folder	-
DRIVE#a91	root/docs/	folder	-
DRIVE#a91	root/docs/taxes.pdf	file	88210
DRIVE#a91	root/photos/	folder	-
DRIVE#a91	root/photos/2026/	folder	-
DRIVE#a91	root/photos/2026/beach.jpg	file	284910
DRIVE#a91	root/photos/2026/sunset.jpg	file	512004

Two original conventions doing the work here:

PK = DRIVE#<account> keeps one account's whole tree in a single item collection, so any subtree read is a single-partition Query.
SK is the full path with a trailing / on folders. The trailing slash is deliberate — it makes a folder sort before its own children and keeps root/photos/ distinct from a sibling file named root/photos.

Read a subtree in one query

List everything under root/photos/ — folder, subfolders, and files, recursively:

Example	Notes
`Query`
`KeyConditionExpression = PK = :drive AND begins_with(SK, :prefix)`
`:drive = "DRIVE#a91"`
`:prefix = "root/photos/"`

That returns root/photos/, root/photos/2026/, beach.jpg, and sunset.jpg —
in path order, in one billed read. You pay only for the items in that slice, not
the whole drive.

In DynoTable, you run exactly this begins_with query against the path sort key and the
folder plus its descendants come back in path order — no placeholder syntax to hand-write.

Need the raw KeyConditionExpression (names, values, and begins_with) for your own
code? Build and copy it in the
DynamoDB Expression Builder.

List one level, not the whole subtree

begins_with gives you the recursive read. For a non-recursive directory
listing — the immediate children of root/photos/ and nothing deeper — store a
depth attribute and add a sort-key range plus a filter, or split the path into
a parent GSI. The simplest version: keep a parent attribute (root/photos/)
and a GSI keyed on it.

The point: a sort key answers prefix and range questions cheaply. "Direct
children only" is a different question — model it explicitly rather than hoping a
FilterExpression makes it efficient. A filter runs after the read and you pay
for every item it discards.

Pick the delimiter carefully

The delimiter is part of your data contract. Two rules:

It must never appear inside a path segment. If filenames can contain /, / is the wrong delimiter — a file named a/b is indistinguishable from a folder a holding b. Pick a reserved byte (some teams use # or a control char) and forbid it in segments.
Mind the sort order at boundaries. / (0x2F) sorts before digits and letters, which is usually what you want for tree order. Change the delimiter and you change the ordering — verify it against real data.

Warning

A composite sort key is one immutable string. To "move" a folder you must rewrite
the SK of every descendant — there's no rename-in-place. Heavy-move workloads
may want indirection (a stable node ID in the key, path in a GSI) instead.

Composite sort key vs. a separate sort attribute

	Composite sort key (`root/photos/2026/x`)	Plain ID sort key + `parent` attribute
Subtree read	One `begins_with` query	Recursive queries (N+1) or a GSI walk
Ordering	Path order, free	Must add an explicit sort attribute
Move / rename	Rewrite all descendants	Update one `parent` pointer
Direct-children list	Needs depth attr or GSI	Natural (`parent = x`)

Composite keys win when reads are subtree-shaped and ordering matters; the
flat-ID model wins when the tree mutates constantly. Most read-heavy hierarchies —
file trees, category trees, org charts — lean composite.

Pitfalls and next steps

Don't over-stuff the key. Everything you encode is immutable and indexed by prefix only. Attributes you query by equality belong in their own fields or a GSI, not jammed into the sort key.
A sort key can't do arbitrary WHERE. Only begins_with, between, and comparisons. If you find yourself reaching for a FilterExpression, you've probably modeled the key wrong — see Query vs. Scan.
Going deeper on key design lives in single-table design; for when a subtree read needs an index instead of the base table, see GSI vs. LSI.

Build the begins_with key condition with the
Expression Builder, then
download DynoTable to run these prefix queries against your own
tables and watch a subtree come back in path order. (And the self-JOIN you
left behind in SQL? DynoTable's SQL Workbench still runs
it when you need it.)

DynamoDB Composite Primary Key

DynoTable — Sun, 26 Jul 2026 06:47:39 +0000

A composite primary key is two attributes: a partition key and a
sort key. The partition key decides where an item lives; the sort key
orders items inside that partition.

Coming from SQL, think of it less as a unique id column and more as
GROUP BY partition, ORDER BY sort baked into the table itself.

What is a DynamoDB composite primary key?

A DynamoDB composite primary key combines two attributes: a partition key and a
sort key. The partition key decides which physical partition an item lives in;
the sort key orders items inside that partition. Together they form the item's
unique identity and let a single Query return a sorted range instead of one
item.

Two parts, two jobs. The partition key routes the item to a physical partition; the sort key orders every item that shares that partition key.
Uniqueness is the pair. Two items can share a partition key value as long as their sort keys differ — that's how one partition holds many rows.
The sort key is the whole point. It's what lets a Query return a range (>=, between, begins_with) instead of one item, with no Scan.
Keys must be scalars. Partition and sort keys can only be string, number, or binary — no maps, no lists (AWS docs).

Simple key vs composite key

A simple primary key is just a partition key. It uniquely identifies an
item, and you read it back with GetItem. That's it — no range reads, no
"give me the newest N".

A composite key adds the sort key, and that single addition is what makes
DynamoDB feel like a database instead of a hash map.

	Simple key	Composite key
Attributes	Partition key only	Partition key + sort key
Uniqueness	Partition key value	The pair of values
Multiple items per partition	No	Yes
`Query` a range	No (`GetItem` only)	Yes (`begins_with`, `between`, `>`)
Natural fit	Lookup by id	Time series, one-to-many, history

Model a sensor-readings table

Say you collect temperature samples from a fleet of field sensors. The access
pattern is "get the readings for one device, newest first, within a time
window". That's a textbook composite key.

Use the device id as the partition key and the reading timestamp as the
sort key:

deviceId	readingTs	tempC	humidity
DEV#a1b2	2026-06-23T08:00:00Z	21.4	48
DEV#a1b2	2026-06-23T08:05:00Z	21.7	47
DEV#a1b2	2026-06-23T08:10:00Z	22.1	46
DEV#c9d8	2026-06-23T08:00:00Z	19.8	55

All three DEV#a1b2 readings land in the same partition, physically stored
together and sorted by readingTs.

AWS calls the partition key the hash attribute and the sort key the range
attribute — the sort key is a range you can scan within
(AWS docs).

Here's how the items collapse into one item collection under each partition
key:

One Query against the partition key reads every reading for that device,
already in timestamp order — no sorting on the client, no second round trip.

Query the range, don't scan it

Because readingTs is an ISO-8601 string, it sorts lexicographically the same
way it sorts chronologically. So a time-window read is a key-condition range,
not a filter:

Example	Notes
`Query`
`deviceId = "DEV#a1b2"`
`readingTs BETWEEN "2026-06-23T08:00:00Z" AND "2026-06-23T08:10:00Z"`

That's a KeyConditionExpression — it narrows the read before DynamoDB
returns data, so you pay only for the items in the window. A FilterExpression
runs after the read and bills you for everything it scanned through; that's
the Scan footgun in miniature.

The expression itself, with placeholders and typed values, is fiddly to write
by hand. Build it visually with the
DynamoDB Expression Builder and copy the
exact KeyConditionExpression into your SDK call.

Warning

The partition key in a Query must be an exact equality (=). Only the sort
key accepts ranges. You can't ask for "all readings across every device after
8am" on the base table — that needs a different key design or a secondary index.

Design the sort key on purpose

The sort key isn't free metadata — it's the only lever for range reads, so
shape it to your queries.

Use a sortable timestamp. ISO-8601 strings or zero-padded epoch numbers sort correctly; raw localized dates don't.
Prefix it for one-to-many overloading. A sort key like READING#2026-06-23T08:00:00Z lets you mix entity types under one partition and slice them with begins_with. That's the seam into single-table design.
Put the high-cardinality dimension in the partition key. Sensor id has thousands of values, so it spreads writes evenly. A low-cardinality partition key (say, region) creates a hot partition.

When a composite key bites you

It's a commitment, not a convenience. The trap: you pick a partition key, ship,
then discover an access pattern that needs a different grouping — "all
readings above 30°C across the whole fleet".

The base table can't answer that; the partition key is fixed. Your options are a
global secondary index with a different key, or
restructuring.

Enumerate your reads before you commit the key schema. Changing a primary key
means a table migration, not an ALTER TABLE.

Next steps

Composite keys are the foundation under item collections, one-to-many
relationships, and most useful index designs — read
single-table design and
GSI vs LSI next to see where they lead.

Sketch your KeyConditionExpression in the
DynamoDB Expression Builder, then
try DynoTable to browse your real partitions and watch the sort
order line up against your own tables.

DynamoDB Batch Operations: BatchGetItem & BatchWriteItem

DynoTable — Sun, 26 Jul 2026 06:47:38 +0000

When you need to read or write many items at once, firing one GetItem or PutItem
per item means one network round trip per item — slow, and chatty. DynamoDB's batch
APIs fold many item operations into a single request: BatchGetItem for reads,
BatchWriteItem for writes.

They're a throughput-and-latency win, not a consistency guarantee — and that
distinction is where people get burned. A batch is not a transaction.

What are DynamoDB batch operations?

DynamoDB batch operations fold many item reads or writes into a single request: BatchGetItem fetches up to 100 items, BatchWriteItem puts or deletes up to 25, each capped at 16 MB. They save round trips, not capacity. Critically, a batch is not a transaction — items succeed or fail independently, with no rollback.

BatchGetItem — fetch up to 100 items (or 16 MB) across one or more tables in one call.
BatchWriteItem — up to 25 put/delete operations (or 16 MB) in one call. No updates — puts and deletes only.
Not atomic. Individual items can succeed while others fail. There's no rollback.
Partial failure is normal. Throttled items come back in UnprocessedItems / UnprocessedKeys — you must retry them yourself, with backoff.
Same capacity cost as the individual calls — batching saves round trips, not capacity units.

The problem: many items, one round trip

Say you run a support desk. A dashboard needs to load 50 tickets by ID to render a
queue; an overnight job archives 1,000 resolved tickets. Doing that one item at a time
is 50 (or 1,000) sequential round trips — latency stacks up and the job crawls.

Batching collapses those into a handful of calls. The 50-ticket read becomes a single
BatchGetItem; the archive job becomes a stream of BatchWriteItem calls of 25
deletes each. Far fewer round trips, the same data moved.

How the batch APIs work

BatchGetItem takes a set of primary keys (across one or more tables) and returns
the matching items. You can request strongly consistent reads per table. Anything it
couldn't read — usually because the request brushed a throughput limit — comes back in
UnprocessedKeys rather than failing the whole call.

BatchWriteItem takes a list of PutRequest / DeleteRequest operations. Note
what's missing: there is no update. A batch write either replaces a whole item
(put) or removes it (delete) — to modify specific attributes you still need
UpdateItem. Items it couldn't write come back in UnprocessedItems.

The key mental model: a batch is a bundle of independent operations, each
succeeding or failing on its own — not one all-or-nothing unit.

Batches are not transactions

This is the trap. If your archive job's batch hits a throughput limit halfway, some
tickets are deleted and some aren't — and DynamoDB does not undo the ones that
went through. There's no rollback, no isolation, no "all 25 or none."

If you need all-or-nothing semantics — "move the ticket to archived and decrement
the open-tickets counter, or do neither" — that's
TransactWriteItems, not a batch. Transactions cost
more (each operation is billed double) and cap at 100 items, but they give you the
atomicity batches deliberately don't.

Handling unprocessed items

A correct batch caller always checks the unprocessed set and retries it. DynamoDB
returns UnprocessedItems/UnprocessedKeys whenever the request as a whole was
accepted but some items couldn't be served — typically transient throttling.

Re-submit only the unprocessed items, with
exponential backoff and jitter.
Treating a batch as fire-and-forget silently drops writes — the kind of bug that
surfaces months later as missing data.

Batch writes in DynoTable

Estimate what a bulk job will cost first with the
DynamoDB pricing calculator — a batch consumes the
same capacity as the individual writes it bundles, just in fewer requests.

In DynoTable, you stage your edits locally and review them before committing them —
bulk changes across many rows go out as grouped requests rather than one API call
each. Bulk deletes go out as batched writes, with the unprocessed-item retry handled
for you.

Pitfalls + next steps

Always retry UnprocessedItems/UnprocessedKeys with backoff — they're expected, not exceptional.
No partial-failure rollback. Need atomicity? Use transactions.
No updates in a batch write — BatchWriteItem is put/delete only; reach for UpdateItem to change attributes.
Mind the per-call caps — 25 writes / 100 reads / 16 MB. Exceeding them fails the whole call with a ValidationException (too many items in BatchGetItem, in BatchWriteItem). Page through larger jobs; see pagination.

Want to run bulk reads and writes without scripting the retry loop?
Download DynoTable and edit your tables directly.

DynamoDB BatchGetItem in Python (boto3)

DynoTable — Mon, 20 Jul 2026 04:44:48 +0000

batch_get_item fetches up to 100 items by primary key in a single request (16 MB max). With the boto3 low-level client you pass a RequestItems map of table → keys — and you must loop on UnprocessedKeys, because a batch can legally return only part of what you asked for.

Code

import time

import boto3

client = boto3.client("dynamodb")

request_items = {
    "Music": {
        "Keys": [
            {"Artist": {"S": "Arturo Sandoval"}, "SongTitle": {"S": "Cubano Chant"}},
            {"Artist": {"S": "Arturo Sandoval"}, "SongTitle": {"S": "A Mis Abuelos"}},
            {"Artist": {"S": "Ella Fitzgerald"}, "SongTitle": {"S": "Misty"}},
        ]
    }
}

items = []
attempt = 0

while request_items:
    response = client.batch_get_item(RequestItems=request_items)
    items.extend(response["Responses"].get("Music", []))

    # A partial result is NOT an error: throttling, a >16 MB response, or an
    # internal failure returns the leftovers in UnprocessedKeys. Retry them
    # with exponential backoff.
    request_items = response["UnprocessedKeys"]
    if request_items:
        attempt += 1
        time.sleep(min(0.1 * 2**attempt, 5))

print(f"Fetched {len(items)} items")

Explanation

RequestItems — a map of table name → {"Keys": [...]}; one request can span multiple tables. Every key must be the full primary key (partition + sort for a composite key), in DynamoDB JSON ({"S": ...}, {"N": ...}). More than 100 keys, or the same key twice, raises a ValidationException.
Responses — a map of table name → the items found. Items come back in no particular order, and keys that don't exist simply don't appear — match results to requests by their key attributes.
UnprocessedKeys — the retry loop above is not optional. If the response passes 16 MB, throughput runs out, or a partition read passes 1 MB, DynamoDB returns the remainder here in RequestItems form, ready to feed straight back in (an empty map means done — falsy in Python, which is what ends the while). AWS strongly recommends exponential backoff between retries.
Consistency — batch reads are eventually consistent by default; set "ConsistentRead": True per table for strongly consistent reads.
Per table you can also pass ProjectionExpression (with ExpressionAttributeNames) to fetch only some attributes.
Prefer native Python types instead of DynamoDB JSON? The resource API offers dynamodb.batch_get_item(...) with plain values, but the retry contract is the same — UnprocessedKeys is yours to drain either way.

Do it visually

Prefer not to hand-assemble DynamoDB-JSON key maps? The DynamoDB Expression Builder builds typed key/value maps and copies runnable code, and the item size calculator shows how close a batch gets to the 16 MB ceiling.

To fetch and inspect items across tables in a GUI — no retry loops to write — download DynoTable.

Related examples

DynamoDB BatchGetItem in Node.js — the same batch read with AWS SDK v3.
DynamoDB BatchGetItem with the AWS CLI — the same batch read from the shell.
DynamoDB GetItem in Python — the single-item read this batches.
Batch operations in DynamoDB — limits, partial failure, and when batching pays off.
"Too many items requested for the BatchGetItem call" — more than 100 keys in one request.
"Provided list of item keys contains duplicates" — the same key twice in one batch.

References

Last verified 2026-07-13 against the official AWS documentation linked above.

DynamoDB BatchGetItem in Node.js (AWS SDK v3)

DynoTable — Mon, 20 Jul 2026 04:44:17 +0000

BatchGetItem fetches up to 100 items by primary key in a single request (16 MB max). In AWS SDK v3 you send a BatchGetItemCommand with a RequestItems map of table → keys — and you must loop on UnprocessedKeys, because a batch can legally return only part of what you asked for.

Code

import {BatchGetItemCommand, DynamoDBClient} from '@aws-sdk/client-dynamodb';

const client = new DynamoDBClient({region: 'us-east-1'});

const sleep = (ms) => new Promise((resolve) => setTimeout(resolve, ms));

let requestItems = {
  Music: {
    Keys: [
      {Artist: {S: 'Arturo Sandoval'}, SongTitle: {S: 'Cubano Chant'}},
      {Artist: {S: 'Arturo Sandoval'}, SongTitle: {S: 'A Mis Abuelos'}},
      {Artist: {S: 'Ella Fitzgerald'}, SongTitle: {S: 'Misty'}}
    ]
  }
};

const items = [];
let attempt = 0;

do {
  const response = await client.send(new BatchGetItemCommand({RequestItems: requestItems}));
  items.push(...(response.Responses?.Music ?? []));

  // A partial result is NOT an error: throttling, a >16 MB response, or an
  // internal failure returns the leftovers in UnprocessedKeys. Retry them
  // with exponential backoff.
  requestItems = response.UnprocessedKeys;
  if (requestItems && Object.keys(requestItems).length > 0) {
    attempt += 1;
    await sleep(Math.min(100 * 2 ** attempt, 5000));
  }
} while (requestItems && Object.keys(requestItems).length > 0);

console.log(`Fetched ${items.length} items`);

Explanation

RequestItems — a map of table name → { Keys: [...] }; one request can span multiple tables. Every key must be the full primary key (partition + sort for a composite key). More than 100 keys, or the same key twice, fails with a ValidationException.
Responses — a map of table name → the items found. Items come back in no particular order, and keys that don't exist simply don't appear — match results to requests by their key attributes.
UnprocessedKeys — the retry loop above is not optional. If the response passes 16 MB, throughput runs out, or a partition read passes 1 MB, DynamoDB returns the remainder here in RequestItems form, ready to feed straight back in. AWS strongly recommends exponential backoff between retries — an immediate retry tends to re-hit the same throttle.
Consistency — batch reads are eventually consistent by default; set ConsistentRead: true per table for strongly consistent reads.
Per table you can also pass ProjectionExpression (with ExpressionAttributeNames) to fetch only some attributes.
BatchGetItem may retrieve items in parallel server-side — it saves round trips, not read capacity: DynamoDB bills each item in the batch as an individual GetItem.

Do it visually

To fetch and inspect items across tables in a GUI — no retry loops to write — download DynoTable.

Related examples

DynamoDB BatchGetItem in Python — the same batch read with boto3.
DynamoDB BatchGetItem with the AWS CLI — the same batch read from the shell.
DynamoDB GetItem in Node.js — the single-item read this batches.
Batch operations in DynamoDB — limits, partial failure, and when batching pays off.
"Too many items requested for the BatchGetItem call" — more than 100 keys in one request.
"Provided list of item keys contains duplicates" — the same key twice in one batch.

References

Last verified 2026-07-13 against the official AWS documentation linked above.

DynamoDB BatchGetItem with the AWS CLI

DynoTable — Mon, 20 Jul 2026 04:44:16 +0000

aws dynamodb batch-get-item fetches up to 100 items by primary key in a single command (16 MB max). The keys go in --request-items as a map of table → keys in DynamoDB JSON — and you must check UnprocessedKeys in the output, because a batch can legally return only part of what you asked for.

Code

aws dynamodb batch-get-item \
  --request-items '{
    "Music": {
      "Keys": [
        {"Artist": {"S": "Arturo Sandoval"}, "SongTitle": {"S": "Cubano Chant"}},
        {"Artist": {"S": "Arturo Sandoval"}, "SongTitle": {"S": "A Mis Abuelos"}},
        {"Artist": {"S": "Ella Fitzgerald"}, "SongTitle": {"S": "Misty"}}
      ]
    }
  }'

The output maps each table to the items found, plus any leftovers:

{
    "Responses": {
        "Music": [
            {"Artist": {"S": "Arturo Sandoval"}, "SongTitle": {"S": "Cubano Chant"}, ...}
        ]
    },
    "UnprocessedKeys": {}
}

Explanation

--request-items — a map of table name → {"Keys": [...]}; one command can span multiple tables. Every key must be the full primary key (partition + sort for a composite key). More than 100 keys, or the same key twice, fails with a ValidationException.
Responses — items come back in no particular order, and keys that don't exist simply don't appear — match results to requests by their key attributes.
UnprocessedKeys — unlike query/scan, the CLI does not auto-retry a partial batch (it's not a pagination token). If the map is non-empty — throttling, a >16 MB response — re-run the command with that exact map as --request-items (it's already in the right form), backing off between attempts.
Per table you can add "ProjectionExpression": "..." (with "ExpressionAttributeNames") to fetch only some attributes, and "ConsistentRead": true for strongly consistent reads — batch reads are eventually consistent by default.
Tip: for more than a few keys, keep the map in a file and pass --request-items file://keys.json.

Do it visually

Hand-writing nested DynamoDB JSON in shell quotes is error-prone. The DynamoDB Expression Builder builds typed key/value maps and copies ready-to-run CLI commands.

To fetch and inspect items across tables in a GUI — no JSON escaping, no re-run bookkeeping — download DynoTable.

Related examples

DynamoDB BatchGetItem in Node.js — the same batch read with AWS SDK v3.
DynamoDB BatchGetItem in Python — the same batch read with boto3.
DynamoDB GetItem with the AWS CLI — the single-item read this batches.
Batch operations in DynamoDB — limits, partial failure, and when batching pays off.
"Too many items requested for the BatchGetItem call" — more than 100 keys in one request.
"Provided list of item keys contains duplicates" — the same key twice in one batch.

References

Last verified 2026-07-13 against the official AWS documentation linked above.

DynamoDB Backup & Point-in-Time Recovery

DynoTable — Mon, 20 Jul 2026 04:43:30 +0000

DynamoDB protects your data two ways. On-demand backups are full snapshots you
take and keep indefinitely. Point-in-time recovery (PITR) is continuous,
automatic backup that lets you restore the table to any second within a rolling
window. Both restore to a new table — they're recovery tools, not an undo button.

For the audit log this is non-negotiable. It's an immutable compliance record; a
bad migration that rewrites events, or an accidental bulk delete, has to be
recoverable to the moment before the mistake.

How does DynamoDB backup and point-in-time recovery work?

DynamoDB offers two backup types. Point-in-time recovery (PITR) takes continuous automatic backups, letting you restore to any second within a configurable 1-to-35-day window. On-demand backups are manual full snapshots kept indefinitely. Both restore to a new table, never over the original, so they are recovery tools rather than an in-place undo.

PITR = continuous backup, restore to any second within a configurable window of 1 to 35 days (it used to be a fixed 35).
On-demand backups = manual full snapshots kept as long as you want, independent of PITR's window.
Restores create a new table. You restore to a new name, then cut over — the original is untouched.
PITR is priced on table size, not on the number of restore points — estimate it with the DynamoDB Pricing Calculator.

The problem: a mistake you can't undo in place

DynamoDB has no transaction log you can roll back and no "undo" on a write. If a
migration script rewrites every event's action field, or someone runs a delete
that's wider than intended, the table is simply in the wrong state. Without
backups, the data is gone.

For an audit log — whose entire value is being a trustworthy record — "we can't get
last Tuesday's events back" is a compliance failure, not just an inconvenience.

How backup and PITR work

Point-in-time recovery, once enabled, takes continuous automatic backups. Per
the
AWS docs,
PITR gives you fully managed continuous backups of table data with per-second
restore granularity. The window is
configurable from 1 to 35 days via RecoveryPeriodInDays, and you can restore
to any second within it — up to roughly five minutes behind real time
(LatestRestorableDateTime) — including to a different region.

One important edge: decreasing the recovery period immediately reduces the
earliest restore point, and disabling then re-enabling PITR resets the
recoverable start time — you lose the prior continuous history.

PITR also gates the managed export to S3: the
export reads from the same continuous backups, so with PITR off,
ExportTableToPointInTime fails with
PointInTimeRecoveryUnavailableException.

On-demand backups are separate: manual, full-table snapshots you create
explicitly and retain indefinitely, useful for a pre-migration checkpoint or a
long-term compliance archive beyond the 35-day PITR window.

Both restore to a new table, not over the existing one:

A worked example: recovering from a bad migration

A migration meant to add an expiresAt attribute instead overwrote action on
every event with an empty string. PITR is on with a 35-day window, so you restore
to the second before the migration ran:

step	result
restore PITR to 09:59:00	new table audit-log-restored with correct actions
diff against live	confirm only the migration's rows differ
cut app over to restored	original left intact for forensics

The corrupted table stays untouched while you verify the restore — you compare the
restored events against the live ones, confirm the action values are back, then
repoint the app. Nothing is destroyed in the recovery itself.

If the loss were a handful of items rather than a whole-table corruption, you could
instead inspect the live data and the restored copy and copy just the affected rows
across — see copy a DynamoDB table.

Do it in DynoTable

A restore is only as good as your verification of it. After restoring to
audit-log-restored, you need to actually look at the recovered events and confirm
they match what they should have been before the mistake.

DynoTable connects to the restored table like any other, so you can query the
affected tenant's events, confirm the action values are correct, and compare
against the live table before you cut over — turning a restore from a leap of faith
into a verified recovery.

You can also export the recovered events for an offline compliance record — see
export DynamoDB to CSV.

Pitfalls and next steps

Enable PITR before you need it. It only protects from the moment it's on — there's no retroactive recovery. Turn it on for any table whose data you can't afford to lose.
Disabling PITR resets the window. Toggling it off and back on wipes the continuous history; the recoverable start time begins again from re-enablement.
Restores aren't instant or free. A restore provisions a whole new table and takes time proportional to size; budget for the duration and the extra table.
35 days isn't archival. For retention beyond the PITR window, take on-demand backups or export to S3 — PITR is a recovery window, not long-term storage.

That closes the audit-log operations loop: transactions for consistency, Streams
for reaction, TTL for expiry, the right capacity mode for cost, global tables for
region resilience, and PITR for data recovery. Revisit the
Operations & Cost overview to see how they fit together.

Download DynoTable to connect to a restored table and verify your
recovery before you trust it.

DynamoDB Atomic Counters

DynoTable — Mon, 20 Jul 2026 04:43:29 +0000

An atomic counter is a numeric attribute you bump in place with a single
UpdateItem call — no read first, no read-modify-write race. DynamoDB applies
each increment in arrival order and never lets two writers clobber each other's
count.

What is a DynamoDB atomic counter?

A DynamoDB atomic counter is a numeric attribute you increment in place with a single UpdateItem call using an ADD (or SET x = x + :n) update expression. DynamoDB reads, adds, and writes the value server-side, so concurrent writers serialize without lost updates — but it is not idempotent, so a retried call increments twice.

Use ADD (or SET x = x + :n) to increment in one call. DynamoDB reads, adds, and writes server-side — concurrent callers serialize, no lost updates.
No read first. Coming from SQL you'd SELECT then UPDATE; here you skip the read entirely and the operation is still safe under concurrency.
Atomic counters are not idempotent. A retried UpdateItem increments again. If you can't tolerate over- or undercounting, use a conditional update.
ADD on a missing attribute starts at 0, so the very first increment just works — no seed write needed.

The problem with read-modify-write

Say you track views on a video. The naive instinct, straight from SQL, is:
GetItem, add one in your app, PutItem the new total back.

Two viewers hit play at once. Both read views = 41. Both write 42. You
counted one view, not two. That's a lost update — the classic concurrency
footgun, and it doesn't show up until you have traffic.

In SQL you'd dodge it with UPDATE videos SET views = views + 1, pushing the
arithmetic into the database. DynamoDB has the same move, and it's the whole
point of an atomic counter.

Increment in one call

Model a per-video stats item. Partition key VID#<id>, sort key STATS#TOTAL,
with a numeric play_count:

PK	SK	play_count
"VID#9f3a"	"STATS#TOTAL"	41

To register a play, send one UpdateItem with an ADD clause:

Example	Notes
`# UpdateItem`
`Key PK = "VID#9f3a", SK = "STATS#TOTAL"`
`UpdateExpression ADD play_count :one`
`Values :one = 1`

DynamoDB reads play_count, adds 1, and writes the result inside a single
server-side operation. There's no window for another writer to slip in. Ten
concurrent plays produce +10, every time — that's what "atomic" buys you.

You can build and copy this exact expression — names, values, and all four
clause types — with the
DynamoDB Expression Builder.

ADD works even when play_count doesn't exist yet: DynamoDB treats a missing
numeric attribute as 0, so the first play creates it at 1. No separate seed
write. (AWS: Using update expressions)

ADD vs SET +: pick one

Two expressions do the same arithmetic. AWS recommends SET for general use
because it composes with other SET actions and reads more explicitly. (AWS:
Using update expressions)

	`ADD play_count :one`	`SET play_count = play_count + :one`
Missing attr	Creates it, starting at 0	Errors — needs `if_not_exists`
Data types	Numbers and sets only	Numbers (and more) via `SET`
Combine w/ `SET`	Separate clause	One `SET` clause, comma-separated
AWS guidance	Fine for counters	Recommended default

If the attribute might not exist and you want SET, guard it:
SET play_count = if_not_exists(play_count, :zero) + :one. With ADD you skip
that — it seeds from 0 for free.

Do it in DynoTable

Open the stats item to inspect the live counter, then roll up a sharded counter
with SUM and GROUP BY in the SQL Workbench to see the total across every
STATS#TOTAL#0..N row. To draft the increment itself, use the web
DynamoDB Expression Builder to compose the
ADD UpdateItem expression, names and values included.

The trap: counters aren't idempotent

Here's the part that bites teams in production. An atomic counter increments
every time UpdateItem runs. (AWS: Working with items)

Picture a network blip: you send the increment, the connection drops before the
response comes back, and you don't know whether it landed. You retry. If the
first call did succeed, you've now counted that play twice.

For video views that's fine — a few double-counts in a million plays won't hurt
anyone, and AWS calls this exact "track visitors" case the canonical use of
atomic counters. (AWS: Working with items)

It is not fine for anything that must be exact: inventory you can oversell,
credits you can double-spend, a balance you can corrupt. There, reach for a
conditional update.

Warning

Don't retry an atomic-counter UpdateItem blindly when exactness matters. A
"failed" request may have committed. Each retry risks another increment.

When you need exactness: conditional updates

A conditional update is idempotent if you condition on the same attribute you're
changing. Increment play_count to 42, but only if it's currently 41:

Example	Notes
`# UpdateItem`
`Key PK = "VID#9f3a", SK = "STATS#TOTAL"`
`UpdateExpression SET play_count = :next`
`ConditionExpression play_count = :current`
`Values :next = 42, :current = 41`

Now a retry is safe: if the first write already moved play_count to 42, the
condition play_count = 41 fails the second time and nothing changes. (AWS:
Working with items)

The cost is concurrency. Two writers racing on the same condition means one wins
and one gets a ConditionalCheckFailedException to retry — you've traded the
unconditional counter's throughput for correctness. For exact, contended
counters that's the right trade. For view counts it's overkill.

Pitfalls

One hot item. A single counter row is one partition key. A viral video hammering VID#9f3a / STATS#TOTAL can hit a per-partition write ceiling. Shard it: spread writes across STATS#TOTAL#0..N and sum on read.
No batch increment. BatchWriteItem is put/delete only — it can't run update expressions. Counters go through UpdateItem, one item per call. If you must bump several counters atomically, TransactWriteItems runs Update actions on up to 100 items in one request, at roughly double the write cost.
ADD is numbers and sets only. It won't touch strings or booleans; that's a SET. See DynamoDB data types for the full attribute model.

Next steps

Atomic counters are a write pattern; how you read aggregates back is a modeling
question — see single-table design for keeping
stats items beside their parent, and Query vs Scan so
rolling up a sharded counter stays a Query.

Draft and copy the increment in the
DynamoDB Expression Builder, then
try DynoTable to run atomic updates against your own tables and
watch the counts move.

How we made cheap LLMs reliable at DynamoDB: tool validators + evals

DynoTable — Fri, 17 Jul 2026 20:30:32 +0000

DynoTable's AI agent runs on your own
AWS Bedrock credentials, which means you pick the model — and
many people pick a cheap one. So we don't tune the agent against a frontier model. Our floor is
Amazon Nova Lite, a model that costs a rounding error per query and
fails in ways frontier models don't.

Here's the punchline of this whole post: on one Eval scenario — a join
that needs the composite-key table as the base — Nova Lite scored 0%. We
didn't switch models, didn't add few-shot examples, didn't fine-tune. We
rewrote one validator error message to say exactly what to do instead. It
went to 100%, flipping the join on the very next step.

The model was never the bottleneck. The message was.

This is the engineering story behind that lesson: the validators that sit
around every tool call, the things we deliberately do not validate, and
the eval harness that arbitrates every change. If you're building an
agent on a budget model — over DynamoDB or anything else —
most of it transfers.

Why cheap models fail at DynamoDB specifically

DynamoDB is a hostile target for a small model because the query surface
looks like SQL and isn't. PartiQL accepts
SELECT syntax but supports no JOIN, no GROUP BY, no DISTINCT, no
LIKE — and a model that learned SQL from the internet emits all of them
confidently.

The failures we actually observed, each from a real logged run:

Dropped limit arguments. Cheap models routinely omit the optional row limit on a query tool. The backend happily returns everything, a 200 KB tool result lands in the conversation, and the next model turn dies on the context ceiling — a ValidationException mid-loop, blamed on the wrong component.
Unsupported constructs. JOIN, GROUP BY, aggregates in PartiQL — statements DynamoDB will never run, emitted because they'd be correct on Postgres.
Identifier swaps. Nova Lite, given SELECT * FROM customers and the instruction "add a WHERE for Germany", would sometimes return SELECT * FROM orders WHERE … — a table the user never mentioned. The statement parses perfectly. Nothing syntactic catches it.
Tool misrouting. Asked about an attached file, Nova Lite called the open-tabs listing tool looking for files in tabs — it rarely used the tool-search mechanism at all, so anything not directly visible didn't exist.

None of these are hypothetical. Each one drove a specific guardrail below.

Validate at the tool boundary, and make the error a lesson

The single most important architectural decision: validation lives inside
the tool's execute step, not in the input schema. A schema rejection is a
dead end — the loop sees a type error and learns nothing. A tool that runs,
checks, and returns a structured error gives the model a normal tool result
it can react to on the next step.

And that error is written for the model, not for a human log. Our export
tool's errors read like this, verbatim:

startExport requires exactly one of {tabId} or {sql}.
FIX: call listOpenTabs and pass {tabId}, or pass {sql} with a single SELECT.

startExport {sql} must be a read-only single SELECT.
FIX: remove any INSERT/UPDATE/DELETE/DDL and pass one SELECT statement.

Query validation failures carry a validation: prefix —
validation: parse-error: …, validation: partiql-unsupported: JOIN is not supported by DynamoDB PartiQL. — which the system prompt establishes as
the signal to fix the statement rather than retry the same input. A
rejection message is a teaching signal. Before we adopted the FIX:
convention, the same failures ended with the model apologizing and telling
the user to click the export button themselves. After, it self-corrects and
completes the task.

The corollary took longer to learn: where the concrete example lives is a
design axis. If a mistake is caught by a validator at runtime, keep the
system prompt generic and let the dynamic error message carry the specific
tables and fix — the prompt stays small and the lesson arrives exactly when
it's needed. Only mistakes no validator can catch (like routing an
aggregate question to the right tool in the first place) earn a concrete
example in the prompt itself.

Diagram: view on dynotable.com

Parse the things you're going to reject

Rejecting JOIN with a generic syntax error at offset 27 teaches
nothing. So our hand-written PartiQL parser does something slightly
perverse: it deliberately parses constructs DynamoDB doesn't support
into a valid syntax tree — just so a walker can emit a specific, teachable
diagnostic. All verbatim, all model-facing:

JOIN is not supported by DynamoDB PartiQL.
GROUP BY is not supported by DynamoDB PartiQL.
TOP N is not supported. Use the API limit parameter.
IN list has 120 items. DynamoDB PartiQL caps IN at 50 values (PK column) or 100 values (non-key column).
IS NOT NULL is not supported in DynamoDB PartiQL. Use attribute_exists.
lower(path) is not a DynamoDB PartiQL function. Use … instead. — with the replacement resolved per function.

Each message names the construct and the escape hatch. Paired with the
prompt rule "if PartiQL rejects an unsupported construct, switch to the
SQL workbench immediately", a cheap model recovers from its Postgres
instincts in one step instead of thrashing.

Know what NOT to validate

The schema-aware tier of our SQL validator resolves table and column
references against the real table descriptions the app already holds. Early
on it also flagged references to attributes DynamoDB didn't declare — which
sounds right and is exactly wrong. DynamoDB's attributeDefinitions only
lists key attributes, so the check rejected WHERE <non-key> = … — the
single most common legitimate query shape in existence. That check is now a
warning that never gates.

Every validator is a bet that the rejected shape is more likely wrong than
right. When the data model can't support the check, don't make the bet.

Result caps as behavior, not just safety

The fix for the dropped-limit blow-up wasn't "add the limit to the
prompt" (cheap models drop it anyway). Query tool results are capped hard —
10 rows or 5 KB, whichever comes first, always keeping at least the
first row — and the truncation notice is itself prescriptive:

Showing 10 of 4,200. To filter: re-run with WHERE.
To view in UI: emit proposeOpenTable.
To aggregate: use runWorkbenchSql GROUP BY.

The caps are intentionally far below what the context could hold. The goal
is behavioral: force the model to hand the user a real table view or an
exact aggregate, never to enumerate rows into chat. The recovery hint
names the exact tools, so the cap teaches the routing at the moment the
model needs it. The same envelope is mirrored byte-for-byte in the eval
harness, and a regression scenario pins truncated: true against a
10,000-item table so the cap can never silently disappear.

Evals are the arbiter

None of the above was designed on intuition and kept on faith. Every
prompt clause, validator message, and cap is gated by an eval suite that
runs real agent loops against a seeded DynamoDB Local — real tools,
real query execution, the production prompt builders and validators
imported directly rather than reimplemented — scored by binary checks on
the tool trace, the final state of the database, and the text.

What the evals caught that intuition missed:

A "cleaner" prompt genericization regressed a scenario from 100% to 0%. It read better to us. The floor model disagreed. Only the eval noticed.
Frontier-model prompt advice is actively wrong for cheap models. Published guidance says to soften CRITICAL/MUST language because big models over-trigger on it. Cheap models under-comply and need the firm version. We now eval-gate any borrowed advice instead of applying it on faith.
Strict tool assertions punished correct behavior. A scorer requiring the model to call the ask tool for clarification passed 19% across the model ladder — almost every "failure" was a model that had correctly asked for clarification in text. The scorer now accepts either. Score the user-facing intent, not the mechanism.
The model ladder earned its narrowing. We started broad — Mistral, AI21, GLM, Qwen, Nova Pro, more — and cut it to two rungs (Nova Lite, Claude Haiku) for reasons only real runs surface: one family cost ~10× per call because Bedrock offered it no prompt caching; Nova Pro hallucinated concatenated attribute names like customers.customerName on open-ended joins. And one family had to be blocklisted outright — Bedrock's unified API happily accepts tool definitions for models whose adapter then emits function calls as plain text, something the API metadata won't tell you. We found it with a paid smoke probe, not a spec.

Two disciplines keep the suite honest. Example tables never equal test
tables: prompt examples use neutral names that don't exist in the seed
data, and the evals run on different tables — so a pass proves the model
generalized the pattern, not that it memorized our example. And the $0
watch mode refuses recorded fixtures: replayed model outputs would
green-pass assertions that depend on real tool activity, so the free tier
validates wiring only, and anything that claims a score had to spend
real tokens on a real model.

The checklist, if you're building one of these

Pick your floor model first and eval against it always; frontier models hide your bugs.
Put validation in the tool's execute step; return errors the loop can act on, never schema dead-ends.
Write every rejection as what's wrong + FIX: exact next action, and interpolate the user's actual tables into it.
Parse what you intend to reject, so the diagnostic can be specific.
Audit each validator against your data model — delete the ones that reject legitimate shapes.
Cap tool results below what fits, and make the truncation notice route the model to the right next tool.
Run evals as real loops against a real (local) backend, reusing production code paths; keep example data disjoint from test data; make the eval — not taste — the arbiter of every prompt and message change.

Does any of this matter if you use a frontier model?

Less often, but yes. The validators are load-bearing at every tier — a
frontier model still can't run JOIN on DynamoDB, and prescriptive errors
just get consumed faster. On our own runs the chip-emission behavior of a
frontier-class model once dropped to 0% under a prompt phrasing that
smaller models handled — the eval harness caught that too. Reliability
engineering isn't a small-model tax; small models just make the invoice
arrive sooner.

Where this runs

All of this ships inside DynoTable's agent and its
gated tool catalog: schema-aware
natural-language querying on your own
Bedrock credentials, exact whole-table aggregates, and writes that only
ever land in a reviewable staging area. External agents get the same
validated toolkit through the
MCP server — how we built that safely is
its own story, from OAuth to credential isolation.

And when you want determinism instead of generation — the query you'll
commit to code — skip the model entirely: the
Expression Builder assembles the
exact request by hand.

How we built a local SQL engine for DynamoDB (a database with no JOINs)

DynoTable — Fri, 17 Jul 2026 20:30:00 +0000

DynamoDB can export a 100 GB table but it cannot aggregate one. There is no
JOIN, no GROUP BY, no COUNT(*) — not slow versions of them, none —
and even PartiQL, AWS's own SQL-ish surface, doesn't add
them. So every team ends up writing the same
throwaway script: paginate a Scan, fold rows into a map, print the
answer, delete the script.

DynoTable's Workbench is our answer to that script: a tab
where you write one real SELECT — multi-table JOIN … ON, WHERE,
GROUP BY, HAVING, DISTINCT, CASE, aggregates — against tables that
support none of it. This post is how it works under the hood, the parser
that silently lied to us, and why we later tore out the engine's memory
model.

Don't build a query engine

The core decision was refusing to write a relational executor. DynamoDB
pages stream into an embedded SQLite database, and SQLite — one of the most
tested query engines on earth — does the relational work:

Diagram: view on dynotable.com

Each DynamoDB item lands in SQLite as its raw typed envelope
({platform: {S: "ios"}}) in a single item column. A small user-defined
function unwraps attributes at query time, so your SELECT platform
compiles to a attr(item, 'platform', …) call. One detail in that
function is load-bearing: it wraps its JSON parsing in a catch, because the
SQLite binding turns one malformed row's parse error into an abort of the
entire prepared statement. Without the catch, one corrupt row would kill
the query.

The honest constraint we kept: DynamoDB's access-pattern physics still
apply. A JOIN's to-side must be a primary key or a GSI
partition key — the engine resolves joins by key lookup against the
stream, never by cross-product scan. If you need arbitrary joins on
non-key attributes, that's a modeling conversation,
not a query-engine feature.

The parser that lied to us

The first version used a popular off-the-shelf SQL parser. Over a few
weeks, four independent correctness bugs traced back to it:

SELECT DISTINCT was silently dropped. The parser accepted it and the emitter ignored it — every duplicate row came back. A user's bug report read: "shows a table of all the values, not counters."
Anything unhandled became the literal SQL NULL. CASE, CAST, scalar subqueries — the emitter's fallback for an unknown syntax node was return 'NULL'. Queries ran, returned confident wrong answers, and raised no error.
Identifier case didn't behave like SQL. SELECT PLATFORM FROM t returned nulls when the attribute was platform — the opposite of what every SQL database trains you to expect.
Autocomplete disagreed with the resolver, suggesting names the compiler would then fail to resolve.

The replacement, sql-parser-cst, won on one primitive the survey found
nowhere else: its tree preserves both the raw text and the
normalized name of every identifier — so the compiler can tell
platform from "PLATFORM". That single distinction lets one grammar
carry proper ANSI semantics: unquoted identifiers resolve
case-insensitively, quoted ones are the case-sensitive escape hatch,
exactly like Postgres or SQLite. It also carries a source range on every
node, so a diagnostic underlines the offending construct instead of the
whole statement.

And we replaced the return 'NULL' fallback with a rule we now treat as
policy: no silent NULL, ever. The emitter's default arm is a
precise-ranged error. Window functions get a message that says why, not
just no:

Window functions are not supported in Workbench.
They have undefined semantics on the joined-row materialization.

The trade we accepted: a pre-1.0 dependency, pinned to the exact version,
upgraded only by hand with the test suite as the gate. We evaluated
patching the old parser (it needed a parallel pre-tokenizer — parsing
every input twice), using the editor's syntax tree (token-level, no clause
structure), and hand-rolling (most of a SQLite parser, unbounded
maintenance). A four-way correctness failure beats all of those concerns.

Knowing what not to block

One diagnostic deliberately does not stop you. When a filter references
an attribute the schema can't confirm the casing of, we can't know the
canonical case — DynamoDB's server-side filters are case-sensitive and its
schema only declares key attributes. That emits a warning, and warnings
never disable Run. We learned this class of lesson the hard way in
our validator work: a gate that
rejects the most common legitimate query shape is worse than no gate.

The cap was the wrong shape

The first aggregation engine buffered scanned rows into an in-memory
SQLite with a size cap — 64 MB here, 250 MB there, and one path with no
cap that could OOM the app on a big table. Hit the cap and you got a
partial answer with an overflow flag.

The redesign started with a reframing: the competitor is the throwaway
script a developer would write instead. And a competent script doesn't
buffer-then-cap — it streams, folding each page into a running total
with bounded memory. The in-memory cap wasn't too small; it was the wrong
shape. Worse, a partial aggregate isn't "incomplete" — it's wrong. The
moment that made this concrete: our own AI demo confidently narrated a
partial "top spenders" ranking as fact.

The rebuilt Compute engine works the way the script does, plus everything
the script never bothers with:

A planner classifies every query into one of three lanes: stream-fold (plain algebraic aggregates — COUNT/SUM/AVG/ MIN/MAX — folded page-by-page off the main process; size never gates a foldable query, it just takes longer), materialize (sorts, key-bounded joins — spilled to disk-backed SQLite, no memory cap), or an honest refusal that recommends the right heavyweight tool (DynamoDB's S3 export plus Athena) for the tail we can't serve.
The fold classification is deliberately narrow. A HAVING, an ORDER BY, a bare column next to the aggregate — any of them forces the materialize lane. A fold that ignored a clause and still reported "exact" would be precisely the wrong-but-confident bug this planner exists to prevent.
Exactness is part of the result. Aggregate columns show a partial badge while pages are still streaming, and drop it only when the scan has drained. Even arithmetic honesty is tracked: integer sums stay exact past 2⁵³ (stored as 64-bit integers), while a fractional sum that exceeds the float mantissa flags itself partial: precision rather than round quietly.
Fuses replace popups for machines. The interactive app asks before a five-million-item scan; the AI agent and MCP callers get scan budgets and page-boundary aborts instead. And the result envelope the model sees is deliberately trimmed — cost class and size estimates are withheld, because surfacing a stale guess invites the model to narrate it as fact.

One cost note that surprised people internally: for a one-off aggregate, a
live scan is roughly 6× cheaper than generating a DynamoDB→S3 export —
which is why the script-shaped lane is the default and the warehouse is
the recommendation for repeated heavy analytics, not the reflex. (The
pricing calculator will price a full
pass of your own table.)

Is it safe to point SQL at production?

The Workbench is structurally read-only. The final SQL that reaches the
local engine passes a defense-in-depth gate that tokenizes and rejects
anything that isn't a single SELECT — as a contract violation, not a
user error, because the compiler should never have produced it. The SQLite
substrate runs with dangerous built-ins disabled, and writes to DynamoDB
have exactly one path in the entire app: the
reviewable staging area, which SQL cannot reach.

What transfers if you're building one

Don't write a query engine; write a faithful compiler onto one that already exists. Your bugs will live in the seams (identifier case, type envelopes, corrupt rows), not the join algorithm.
Demand quote-provenance from your parser. If it can't tell name from "NAME", you cannot implement SQL's case semantics, and your users will find out before you do.
Make "unhandled syntax" a loud error, never a default value. Silent NULL is how confident wrong answers ship.
Benchmark your design against the throwaway script your user would write. If the script streams and you buffer, you built the wrong shape.
Treat partial aggregates as wrong answers that need labeling, not partial credit.

The Workbench docs cover the day-to-day feature surface,
and SQL for DynamoDB maps what works across the
whole ecosystem. Or just download DynoTable, open a Workbench
tab with ⌘⌥Q, and run a JOIN against the database that
doesn't have one.

Why a database GUI should never write directly

DynoTable — Fri, 17 Jul 2026 20:29:59 +0000

Every database GUI has a moment where a click turns into a write against
production. We decided that moment shouldn't exist. In DynoTable, nothing
you do — not an item edit, not a bulk delete, not an
AI agent's change — touches DynamoDB directly. Every write
lands in a staging area as a reviewable per-attribute
diff, and ships only when a human commits it.

The mental model we built toward is git: a staging area for review, and a
commit that behaves like git push --force-with-lease — it succeeds only
if the remote still looks the way you last saw it. This post is the
engineering underneath: the cross-tab bug that reshaped the whole design,
the refactor whose main output was deleted code, and why the arrival of
AI agents turned a UX nicety into the load-bearing safety wall.

Writes are diffs

Edits accumulate in a local SQLite-backed stage and render as diff cards —
old value, new value, per attribute — in a side panel. Rows tint in the
grid so staged state is visible from the data, not just the panel. Commit
ships the staged set as DynamoDB transactions, chunked to
the service's limits (100 items per transaction, with byte budgets per
operation and per request), sequentially, with a 30-second timeout per
chunk.

The staging docs cover the daily workflow. What they don't
cover is what the stage is keyed by — which turned out to be the most
consequential decision in the system.

Keyed to the table, not the tab

The first version scoped each stage to the tab that created it. That
design produced a real bug: the AI's staging tool derived its target from
the active tab, and its guard skipped tabs that aren't table views. So
if you asked the assistant to fix a row while a SQL Workbench tab was
focused, the edit was staged into the Workbench's stage — and the "reveal
staged change" chip opened the wrong tab.

Worse, the commit lock was also per-tab. Two tabs viewing the same table
held two independent locks — meaning both could commit the same staged
rows to real DynamoDB concurrently. A double-commit race against
production, built into the data model.

The fix was to re-key everything by table identity —
{profile, region, tableName} — instead of by tab:

One stage per table, visible from every view of it, surviving tab close and reopen. The AI can stage with no table tab open at all.
One commit lock per table. The double-commit race is not "handled" — it's unrepresentable.
The wrong-tab bug class is gone by construction: there is no tab in the key.

And re-keying deleted more code than it added. The startup sweep that
garbage-collected stages for closed tabs? A reviewer flagged it as
feature-destroying under the new model — stages no longer die with tabs —
so it was removed outright. Discard-on-tab-close and its confirmation
dialog: removed. The only true orphan left is a deleted connection
profile, which cascades explicitly.

The migration itself was the repo's first row-mutating one: a
hand-written SQLite table rebuild that de-duplicated legacy per-tab rows
with a ROW_NUMBER() OVER (PARTITION BY …) window (newest edit wins) and
backfilled the new key with char(0) separators, byte-identical to the
runtime's key builder. Row-mutating migrations got their own
seed-then-migrate test harness that day.

`--force-with-lease`, for DynamoDB

A review UI is worthless if the thing you reviewed isn't the thing that
gets written. Between staging and committing, someone else may have
changed the row. So every commit operation carries
condition expressions that pin the write to the
exact snapshot you reviewed — optimistic locking,
attribute by attribute:

A create asserts the item doesn't already exist.
An update asserts every attribute you're changing still holds the value you saw when you staged it.
Even a removal asserts the attribute still equals its old value — if a teammate changed note from "old" to "new" while your removal of note sat staged, the commit must not silently delete their edit.

When a condition fails, DynamoDB cancels the whole transaction and tells
us which item drifted. That row gets a conflict banner — rebase against
the live value or abort — rather than a silent overwrite in either
direction.

And on any chunk failure, the committer halts. Committed chunks stay
committed, the failed chunk rolls back atomically, nothing further is
attempted. We deliberately rejected best-effort continuation: a review
tool that keeps writing past a conflict is applying a change set nobody
reviewed.

Diagram: view on dynotable.com

The refactor that deleted a subsystem

Commits are long-running: the app registers each one in a replay registry
so a renderer reload — or a second window — can re-attach and watch it
finish. Over time, three different code paths each attached two
observers per commit, and an entire arbitration mechanism grew up around
one question: which observer is allowed to release the commit lock? It had
its own scary name (ownsLockOnBeforeRegistration), a comment block
explaining an IPC ordering race, and a 230-line toast tracker.

The fix was a single owner: a Commit Session that attaches exactly
once per commit, owns the lock exclusively, projects progress into the
store, and guarantees its start call settles on every exit path — never
hangs, never rejects. The arbitration machinery and the tracker weren't
relocated; they were deleted. The memory benchmark that thrashes the
staging panel dropped about a third of its heap. The best code review
comment we got that month: "this PR is mostly red."

Two behaviors fall out of that ownership model that we consider
table-stakes for any tool that writes to production: an in-flight commit
survives a renderer reload (the session re-attaches and completes),
and it even survives your license flipping to read-only mid-commit — new
commits are blocked, but a write already in flight is observed to its end,
never abandoned half-applied.

Why the wire format is deliberately ugly

Staged values are stored as raw DynamoDB typed envelopes —
{N: "42"}, {S: "42"} — not as friendly unmarshalled JSON. Two reasons.
The diff must be type-aware: {N: "1"} and {S: "1"} are different
values, and a primary-key hash built on unmarshalled values would collide
them. And the round-trip must be lossless: the SDK's unmarshalled number
wrappers don't survive JSON serialization into SQLite and back, and
deep-equality against user input breaks. The commit path uses the raw
client for the same reason — what you reviewed is byte-for-byte what gets
conditioned on and written.

Then the agents arrived

Staging predates our AI features, but it's the reason they could ship at
all. The assistant's write tool stages; its own description tells the
model — verbatim:

The user reviews + commits from the staging panel —
this tool never writes to DynamoDB directly.

There is no commit tool. Not permission-gated, not audited — absent.
The MCP server exposes the same
boundary structurally: its middle consent scope is literally named
"read + stage". A prompt-injected agent at that scope
can propose garbage, and the blast radius is a diff card a human reads.
And because commits carry per-attribute optimistic locks, even a stale
agent edit can't silently clobber a concurrent human one — it surfaces as
a conflict like everything else.

We wrote about making the agent's queries reliable;
staging is the other half — making its writes boring.

What to demand from any tool that writes to your database

A review step between intent and write — diffs, not confirmation dialogs.
Optimistic concurrency on the reviewed snapshot, including deletes and removals — never last-writer-wins.
Atomic batches with halt-on-failure, not best-effort continuation past a conflict.
A write path that survives a crash or reload without leaving a half-applied set.
For AI: staging as the only write primitive an agent has — a missing capability beats a guarded one.

The staging docs show the workflow, and
editing DynamoDB data covers the basics
it protects. Or download DynoTable, stage a few edits with
⌘S, and watch a bulk change become something you can actually
read before it happens.