PartiQL for DynamoDB by Example

PartiQL is a SQL-compatible query language for DynamoDB. It's friendlier than the raw API for ad-hoc work — but it runs on the same engine, so the same key rules (and the same costs) apply underneath the familiar syntax.

How do you write PartiQL queries for DynamoDB?

PartiQL gives DynamoDB four SQL-shaped statements — SELECT, INSERT, UPDATE, and DELETE — run through ExecuteStatement. Each one compiles to a native operation underneath, so filtering on the partition key stays a Query while omitting it becomes a full-table Scan. Writes still target one item by its primary key; there's no relational JOIN, GROUP BY, or aggregate.

Every statement runs through the same ExecuteStatement API (or its batch/transaction variants). Table and index names must be double-quoted when they contain special characters; attribute names in expressions follow the same quoting rules as the low-level API. The PartiQL docs in DynoTable mirror these examples with schema-aware autocomplete and a searchable history of past runs.

SELECT

SELECT * FROM "AppData"
WHERE "PK" = 'CUSTOMER#42' AND begins_with("SK", 'ORDER#')

Filter on the partition key and this is a Query. Omit the partition key and PartiQL silently runs a full-table Scan — same footgun, just hidden behind SELECT *.

Put numbers on the Scan path: a table with 500,000 items averaging 1 KB each meters ~62,500 read request units on on-demand billing for a full SELECT * (500,000 KB ÷ 4 KB per unit, eventually consistent at 0.5 RCU each). Add WHERE status = 'OPEN' and the bill is identical — filtering happens after the read is metered. The keyed SELECT in the example above touches only the CUSTOMER#42 partition; if that collection holds 40 orders at 1 KB each, the same eventually-consistent read costs 5 units (40 KB total), not 62,500.

ExecuteStatement paginates like the native API: a non-empty NextToken means more rows remain. Loop until the token is absent (pagination guide).

INSERT

INSERT INTO "AppData" VALUE {'PK': 'CUSTOMER#42', 'SK': 'PROFILE', 'plan': 'pro'}

UPDATE

UPDATE "AppData" SET "plan" = 'enterprise'
WHERE "PK" = 'CUSTOMER#42' AND "SK" = 'PROFILE'

DELETE

DELETE FROM "AppData"
WHERE "PK" = 'CUSTOMER#42' AND "SK" = 'ORDER#2026-001'

Querying an index

Use the index name in the FROM clause:

SELECT * FROM "AppData"."GSI1" WHERE "GSI1PK" = 'STATUS#OPEN'

WHERE also supports IN, contains() and begins_with():

SELECT * FROM "AppData"
WHERE "PK" = 'CUSTOMER#42' AND "SK" IN ['ORDER#1', 'ORDER#2']

Parameterized statements

Use ? placeholders instead of inlining values — it sidesteps quoting/injection issues and lets the SDK marshal types for you:

SELECT * FROM "AppData" WHERE "PK" = ? AND begins_with("SK", ?)

Pass Parameters: [{ S: 'CUSTOMER#42' }, { S: 'ORDER#' }] to ExecuteStatement.

Prefer parameters in application code and reserve string interpolation for ad-hoc console sessions. The SDK marshals types (N for numbers, BOOL for booleans) so you avoid the classic "42" string where DynamoDB expected a number attribute. Reserved attribute names still need aliases in PartiQL exactly as in FilterExpression — run your names through the reserved-words checker before baking them into a stored statement.

Batch and transactions

• BatchExecuteStatement — up to 25 statements in one round trip. Faster, but no cross-item atomicity (each succeeds or fails on its own).
• ExecuteTransaction — up to 100 statements, all-or-nothing. Use it when several writes must commit together.

Batch and transaction ceilings are fixed service limits, not tuning knobs. A cart checkout that touches 30 line items still needs chunking: two BatchExecuteStatement calls (25 + 5) or one transaction if you need atomicity across all 30 (and each statement still targets a single item by full primary key).

PartiQL vs Workbench SQL

PartiQL executes on DynamoDB — one keyed access pattern per statement. DynoTable's SQL Workbench executes in the client over rows you've already pulled, which unlocks JOIN, GROUP BY, and aggregates PartiQL deliberately omits. The split is operational, not cosmetic:

Workbench still respects DynamoDB's access rules: you fetch partitions with keyed reads (or bounded Scans you accept consciously), then SQL shapes the result set locally. See PartiQL vs SQL for the full comparison.

The PartiQL footgun

PartiQL looks like SQL but runs on the DynamoDB engine, so SQL habits backfire:

• A single UPDATE/DELETE must target one item by its full primary key — there's no UPDATE … WHERE status = 'x' mass update (loop with a batch instead).
• No JOIN, no GROUP BY, no aggregates (COUNT/SUM/AVG). See PartiQL vs SQL.
• Omitting the partition key turns any SELECT into a full-table Scan — bounded only by your bill.

When you genuinely need a JOIN, a GROUP BY, or an aggregate, DynoTable's SQL Workbench runs them client-side over the rows you've pulled — the SQL PartiQL can't speak, inside DynamoDB's access-pattern rules.

PartiQL doesn't change the underlying data types — values still go over the wire as DynamoDB-JSON, which you can inspect with the converter.

For UPDATE and DELETE statements, the WHERE clause must resolve to exactly one item. Partial-key WHERE clauses that match many rows fail at compile time or runtime rather than mass-updating — loop with a keyed Query, then issue per-item DML inside BatchExecuteStatement or a transaction when you need atomicity.

Complex KeyConditionExpression shapes (nested begins_with, mixed AND/OR) are easier to prototype in the DynamoDB Expression Builder and translate into PartiQL's WHERE syntax once the condition is correct.

Try DynoTable to run PartiQL statements with schema-aware autocomplete and browse the results in a sortable table view — then pivot the same dataset into Workbench when you need a join or aggregate the engine cannot push down.