DynamoDB PartiQL: SQL for DynamoDB (Tutorial)

PartiQL is a SQL-compatible query language that AWS layers on top of DynamoDB. It lets you SELECT, INSERT, UPDATE, and DELETE items using familiar syntax instead of the classic Query/PutItem/UpdateItem API. It does not turn DynamoDB into a relational database — there are no joins, and the same partition-key rules still govern performance.

This guide covers the four statement types, batch and transactional execution, and the cases where PartiQL genuinely helps versus where it quietly hurts.

What PartiQL actually is

PartiQL is a thin language layer, not a new engine. Every statement compiles down to one of the operations you already know:

• A SELECT that pins the partition key becomes a Query (or a GetItem for a single key).
• A SELECT without a partition-key predicate becomes a Scan — and a Scan reads every item in the table.
• INSERT, UPDATE, and DELETE map to PutItem, UpdateItem, and DeleteItem.

That last point is the one to internalize: SQL-looking syntax does not change the cost model. The same access-pattern discipline from query vs scan applies verbatim.

You run PartiQL through three API calls:

SELECT: reading items

A SELECT is efficient only when the WHERE clause includes an equality on the partition key. Quote string values with single quotes.

-- Efficient: resolves to a Query on the partition
SELECT * FROM "Orders"
WHERE "PK" = 'USER#123' AND begins_with("SK", 'ORDER#')

To target a Global Secondary Index, name it after the table:

SELECT "orderId", "total"
FROM "Orders"."GSI1"
WHERE "GSI1PK" = 'STATUS#SHIPPED'

The dangerous pattern is a predicate on a non-key attribute:

-- Full table Scan — reads every item, filters after
SELECT * FROM "Orders" WHERE "status" = 'SHIPPED'

This compiles to a Scan with a filter. It returns the right answer but reads (and bills for) the entire table. If you find yourself writing these, you need an index, not a different query language.

Parameters

Use ? placeholders to avoid string concatenation and injection-style mistakes:

{
  "Statement": "SELECT * FROM \"Orders\" WHERE \"PK\" = ?",
  "Parameters": [{ "S": "USER#123" }]
}

INSERT, UPDATE, DELETE

INSERT writes a new item. It uses a map literal and fails if an item with the same key already exists — it behaves like a conditional PutItem, not an upsert.

INSERT INTO "Orders" VALUE {
  'PK': 'USER#123',
  'SK': 'ORDER#2026-06-16#A1',
  'total': 49.90,
  'status': 'PENDING'
}

UPDATE modifies an existing item and must target a full primary key. You can SET or REMOVE attributes, and add a condition with WHERE:

UPDATE "Orders"
SET "status" = 'SHIPPED'
WHERE "PK" = 'USER#123' AND "SK" = 'ORDER#2026-06-16#A1'

To support atomic counters and list operations, PartiQL UPDATE supports arithmetic and the same functions you’d use in update expressions:

UPDATE "Inventory"
SET "qty" = "qty" - 1
WHERE "PK" = 'SKU#9'

DELETE removes one item by full key and also accepts a condition:

DELETE FROM "Orders"
WHERE "PK" = 'USER#123' AND "SK" = 'ORDER#2026-06-16#A1'

Note that PartiQL writes operate on one item per statement — there is no UPDATE ... WHERE status = 'X' that touches many rows. To affect multiple items you issue multiple statements (via batch) or run a Query/Scan first and write each key.

Batch execution

BatchExecuteStatement sends up to 25 statements in a single request. They run independently — there’s no atomicity, and individual statements can fail while others succeed.

{
  "Statements": [
    { "Statement": "INSERT INTO \"Orders\" VALUE {'PK':'USER#1','SK':'ORDER#1'}" },
    { "Statement": "INSERT INTO \"Orders\" VALUE {'PK':'USER#2','SK':'ORDER#2'}" }
  ]
}

Each entry in the response carries its own Error, so you must inspect results per statement and retry the failures yourself. This is the PartiQL face of batch operations, with the same need to handle partial success.

Transactions

ExecuteTransaction runs up to 100 write statements (INSERT/UPDATE/DELETE) atomically — all succeed or all roll back. It’s the PartiQL equivalent of TransactWriteItems.

{
  "TransactStatements": [
    { "Statement": "UPDATE \"Accounts\" SET \"bal\" = \"bal\" - 10 WHERE \"PK\" = 'A'" },
    { "Statement": "UPDATE \"Accounts\" SET \"bal\" = \"bal\" + 10 WHERE \"PK\" = 'B'" }
  ]
}

The same constraints from transactions apply: no two statements may target the same item, the whole transaction is bounded at 4 MB, and conditional checks that fail abort everything.

When PartiQL helps — and when it doesn’t

PartiQL is a convenience layer, so weigh it against the classic API honestly.

Good fits:

• Ad-hoc exploration in the console or CLI when you want to read a few items fast.
• Teams fluent in SQL writing simple, key-driven reads and writes.
• Tooling and dashboards that already speak SQL and want one syntax across data stores.

Poor fits:

• High-throughput application code. The classic API is more explicit about what each call costs, and it’s harder to accidentally write a Scan.
• Complex conditional writes. Native UpdateItem with ConditionExpression, ExpressionAttributeNames, and return values gives you finer control.
• Anything where you might forget the partition key. A missing key predicate silently becomes a Scan; the classic Query simply won’t let you omit it.

A practical rule: use PartiQL for exploration and glue code, and the typed API for the hot path where you want the cost of every call to be obvious.

Exploring PartiQL safely

The fastest way to build intuition is to run statements against a real table and watch which ones turn into Scans. A DynamoDB GUI like Tablyne lets you write PartiQL, see the consumed capacity and whether a statement hit an index or scanned the table, and inspect the returned items inline — which makes the “this innocent-looking SELECT just read my whole table” lesson land before it shows up on your bill.

PartiQL doesn’t change DynamoDB’s fundamentals; it just gives you a SQL-shaped door into them. Keep the partition key in your WHERE clause, treat keyless SELECTs as Scans, and reach for the typed API when you need precision.