DynoTable
Beginner2 min read

PartiQL for DynamoDB by Example

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.

SELECT

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

Filter on the 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']

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.

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 — 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 autocomplete and see the read units each one consumes.

Updated