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, DELETE), run through ExecuteStatement. Filter on the partition key and you get a keyed read; omit it and you get a full-table Scan.

Writes target one item by its full primary key, and there's no relational JOIN, GROUP BY or aggregate.

Quoting follows SQL: single quotes for string values, double quotes for identifiers. Double-quote a table name that contains special characters, and always double-quote both names when you query an index ("AppData"."GSI1").

SELECT

SELECT * FROM "AppData"
WHERE "PK" = 'ACCOUNT#acme' AND begins_with("SK", 'TICKET#')

Pin the partition key with = or IN and DynamoDB reads only those partitions. Omit it and PartiQL silently runs a full-table Scan: the same footgun, hidden behind SELECT *.

`WHERE` clause shape	What DynamoDB does	Typical cost driver
`PK = ?` (exact partition)	`Query`	Items in that partition (plus filter waste)
`PK = ? AND begins_with(SK, ?)`	`Query`	Sort-key range within one partition
`PK IN [?, ?]` or `PK = ? OR PK = ?`	Keyed reads, no scan	Items under each listed key
`GSI1PK = ?` via `FROM "Table"."GSI1"`	`Query` on index	Items in the index partition
`PK > ?`, or `PK = ? OR attr = ?` (non-key)	`Scan`	Every item in the target, before filters
No partition-key equality on table or index	`Scan`	Every item in the target, before filters

The rule comes from the AWS SELECT reference: an equality or IN on the partition key avoids the full scan. A range on the partition key, or an OR whose other branch is a non-key attribute, scans.

Put numbers on the Scan path. A table with 500,000 items averaging 1 KB meters about 62,500 read request units on on-demand for a full SELECT * (500,000 KB ÷ 4 KB × 0.5 for an eventually consistent read).

Add WHERE status = 'OPEN' and the bill is identical: the filter runs after the read is metered. The keyed SELECT above reads only ACCOUNT#acme. At 40 tickets of 1 KB, it costs 5 units (40 KB ÷ 4 KB × 0.5), not 62,500.

ExecuteStatement paginates like the native API: while a response carries a NextToken, call again with it. Don't stop on an empty page, since a filtered read can return zero items plus a token (pagination guide).

INSERT

INSERT INTO "AppData" VALUE {'PK': 'ACCOUNT#acme', 'SK': 'PROFILE', 'plan': 'team'}

INSERT never overwrites. If an item with that key already exists, the statement fails with DuplicateItemException. To replace an item, use the native PutItem (aws dynamodb put-item), which overwrites by default.

UPDATE

UPDATE "AppData" SET "plan" = 'enterprise'
WHERE "PK" = 'ACCOUNT#acme' AND "SK" = 'PROFILE'

UPDATE never creates. The WHERE must name the full primary key, and if no item has that key the statement fails with ConditionalCheckFailedException.

For an upsert, use the native UpdateItem (aws dynamodb update-item), which creates the item when the key is missing.

DELETE

DELETE FROM "AppData"
WHERE "PK" = 'ACCOUNT#acme' AND "SK" = 'TICKET#1001'

DELETE also needs the full key. Deleting a key that doesn't exist succeeds with zero items deleted. Append RETURNING ALL OLD * when you need to know what was removed: you get the deleted item back, or nothing if there was none.

Querying an index

Name the index in the FROM clause, with both names double-quoted:

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

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

SELECT * FROM "AppData"
WHERE "PK" = 'ACCOUNT#acme' AND "SK" IN ['TICKET#1001', 'TICKET#1002']

Parameterized statements

Use ? placeholders instead of inlining values. They sidestep quoting and injection issues:

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

With the low-level client you pass each value with its type: Parameters: [{ S: 'ACCOUNT#acme' }, { S: 'TICKET#' }]. Nothing converts the types for you, so state them carefully.

Send a number as { S: '100' } and a key comparison fails with Key attribute's data type should match its data type in table's schema. A non-key comparison just matches nothing.

Prefer parameters in application code and keep string interpolation for ad-hoc console sessions.

PartiQL has no ExpressionAttributeNames aliases. If an attribute name collides with a PartiQL keyword, double-quote it: count = 1 fails with Statement wasn't well formed, can't be processed, while "count" = 1 runs.

The reserved-words checker tests the expression-API list (FilterExpression and friends), which is not PartiQL's: status is on it yet runs unquoted here. Quoting every identifier is always safe.

The opposite mistake is quieter. WHERE "PK" = "ACCOUNT#acme" doesn't error: DynamoDB reads "ACCOUNT#acme" as an attribute name, compares two attributes, and returns zero rows. String values always take single quotes.

Batch and transactions

BatchExecuteStatement: up to 25 statements in one round trip. Each succeeds or fails on its own, so check every entry in Responses. A batch is all reads or all writes, and each batch SELECT must name a full primary key.
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. Closing 30 tickets at once still needs chunking: two BatchExecuteStatement calls (25 + 5), or one transaction if you need atomicity across all 30.

PartiQL vs Workbench SQL

PartiQL executes on DynamoDB, one table per statement. DynoTable's SQL Workbench executes in the client over rows it reads through DynamoDB's own operations (Query/Scan for the base table, key lookups for the joined side), adding JOIN, GROUP BY and aggregates. Pick by job:

Need	Reach for
Single-partition read/write by primary key	PartiQL `SELECT` / DML
Cross-partition filter you modeled a GSI for	PartiQL against the index
Ad-hoc join across two entity types	Keyed reads merged in code, the Athena connector, or Workbench SQL
Monthly support minutes rolled up by account	A pre-computed aggregate, Athena, or Workbench `GROUP BY`
Production hot-path microservice	Native `Query` / `GetItem` APIs

The Workbench still reads through DynamoDB's access rules: keyed reads, or Scans you accept knowingly. Its JOIN rules are in SQL for DynamoDB, and PartiQL vs SQL has the feature-by-feature comparison.

The PartiQL footgun

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

UPDATE/DELETE change one item, by full primary key. A partial key fails with a ValidationException: the WHERE clause has to pin every key attribute with an equality. For a mass update, query the table or a GSI for the keys, then send per-item statements in BatchExecuteStatement or, for atomicity, ExecuteTransaction.
No JOIN, no GROUP BY, no aggregates (COUNT/SUM/AVG). Join by querying each side by key and merging in code (JOIN guide). Keep totals current on write (GROUP BY, COUNT and SUM), or run ad-hoc SQL through Athena (SQL for DynamoDB).
A keyless SELECT is a full-table Scan. To make it an error instead, deny dynamodb:PartiQLSelect when dynamodb:FullTableScan is true in the caller's IAM policy.

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

Writing the same access with the native API instead? The DynamoDB Expression Builder builds the KeyConditionExpression (one partition-key equality plus at most one sort-key condition, no OR) and any FilterExpression for you.

Try DynoTable to run PartiQL with schema-aware autocomplete, quick-fixes for double-quoted values, LIKE and IN (…), searchable run history and a read estimate before you run.

Open a Workbench tab when you need a join or aggregate; the PartiQL docs cover the editor.