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.

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

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']

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

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.

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.

Try DynoTable to run PartiQL statements with schema-aware autocomplete and browse the results in a sortable table view.