Documentation menu

Getting Started

Usage

Reference

PartiQL

Dynoxide supports PartiQL across all three operations: ExecuteStatement, BatchExecuteStatement, and ExecuteTransaction. SELECT, INSERT, UPDATE and DELETE work with the grammar below. Dynoxide has its own complexity limits, so a statement AWS accepts can still be too complex locally.

aws dynamodb execute-statement \
  --endpoint-url http://localhost:8000 \
  --statement "SELECT * FROM Users WHERE pk = 'user#1'"

Parameters work in every position, including inside nested lists and maps:

aws dynamodb execute-statement \
  --endpoint-url http://localhost:8000 \
  --statement "SELECT * FROM Users WHERE pk = ?" \
  --parameters '[{"S": "user#1"}]'

Supported grammar

Category Support
Comparisons =, <>, <, >, <=, >=
Range and membership BETWEEN, NOT BETWEEN, IN, NOT IN
Functions attribute_exists, attribute_not_exists, attribute_type, size, begins_with, contains
Type tests IS / IS NOT with MISSING, NULL, BOOLEAN, BLOB, STRING, NUMERIC, STRUCT, TUPLE, LIST
Logical AND, OR, NOT, and parenthesised grouping
Projections Nested dot-notation paths
Pagination LIMIT and NextToken
Literals Set literals (<< >>), decimal and exponent numbers, escaped quotes
Ordering ORDER BY on key attributes, with the restrictions below
Comments -- and /* ... */

INSERT supports IF NOT EXISTS and rejects duplicates. UPDATE supports SET with expressions and REMOVE, including real list-index writes: SET tags[0] = :v updates that element (appending when the index is at or past the end) and REMOVE tags[0] deletes it and shifts the rest down.

From 1.4.0, UPDATE follows DynamoDB's update rules more closely. Every clause reads the item as it was before the update, so SET n = 7, s = n + 1 gives s the old n plus one. A path whose parent is missing or the wrong type is refused rather than created, so SET zz.y = 1 no longer makes zz. Overlapping paths are refused, REMOVE takes one path per keyword (REMOVE a, b is a syntax error), and arithmetic on a missing attribute is refused rather than read as zero.

size(path) counts UTF-16 code units in strings, bytes in binary values and members in collections. attribute_exists(path) and attribute_not_exists(path) test whether an attribute is present. EXISTS(path) is refused as an unrecognised function; use the attribute functions instead.

set_add and set_delete work in an UPDATE's SET clause. Quoted names preserve literal dots and brackets: "a.b" names a top-level attribute, while "a"."b" steps into a map. A statement may end with a semicolon, but a second statement after it is refused. Table aliases are not supported.

ORDER BY

From 1.3.0, ORDER BY controls the returned order; older builds accepted it and ignored it:

SELECT * FROM "Users" WHERE pk = 'user#1' ORDER BY sk DESC

Terms must name key attributes, not nested paths or other attributes. The WHERE clause must pin the partition key with = or IN. If it names more than one partition, ORDER BY must include the partition key too. These rules apply to reads on a table or index, and ORDER BY must be the last clause.

Partitions are read in partition-key order, following that term's direction when supplied. Each partition follows the sort-key term's direction; with only a partition-key term, its rows read in descending sort-key order. NULLS FIRST and NULLS LAST are accepted.

Limits

A statement must contain 1 to 8,192 characters. A WHERE clause allows up to 300 operators, grouping up to 64 levels deep and at most 100 operands in an IN list.

Dynoxide also limits the internal expansion of a WHERE clause to 256 alternatives, 4,096 condition slots in any one alternative and 16,384 across them. These are Dynoxide limits: DynamoDB evaluates the clause as a tree and can accept larger combinations.

PartiQL cannot read a GSI with multi-attribute partition or sort keys. The base table and its other indexes remain usable. See multi-attribute GSIs.

No COUNT(*)

Dynoxide used to accept SELECT COUNT(*). Real DynamoDB rejects it, so a statement that worked locally fell over in production, and 0.13.0 removed it. A COUNT(...) projection now gets DynamoDB's own ValidationException - the bare Unexpected path component message, with the 1-based position of the COUNT token - on all three operations, before the table is even looked up.

There's no replacement, which is what DynamoDB leaves you with too: count by paging the rows yourself, or use Query or Scan with Select: COUNT.

Pagination

SELECT pages the way Query and Scan do. Limit bounds the rows the engine evaluates, not the rows it returns, so a filtered statement can hand back fewer rows than the limit - or an empty page - while still carrying a NextToken. Follow the token rather than reading a short page as the end of the results.

Without ORDER BY, a SELECT bound to a partition key reads in ascending sort-key order, and its sort-key conditions are pushed into the read, so Limit paces against the rows in that key-condition range rather than the whole partition. A condition the key condition can't express falls back to the partition-wide read and filters afterwards, as before.

From 1.3.0, a NextToken is bound to the statement text exactly as sent, including whitespace, keyword case and map literal key order. Keep that text unchanged on subsequent pages. Limit may change; Limit: 0 is refused. A mismatch returns NextToken does not match request, and a token that won't decode returns Invalid NextToken.

Parameters must match by value. Number parameters must also have the same decimal scale: 1.5E1 and 15 resume, but 100 and 1E2, or 0 and 0.0, don't. Set parameters compare without member order, and number-set members compare by value regardless of scale.

A statement with a list or map parameter cannot resume, even with the same parameter sent again. Its first page can return a token, but the next page is refused. This matches the captured DynamoDB behaviour; plan around it before paging a statement with collection parameters.

Evaluated-row limits arrived in 0.13.0. The stricter token matching above arrives in 1.3.0; older builds accepted some statements that parsed alike even when their text differed.

RETURNING

New in 0.12.0. Before that Dynoxide parsed the clause and silently dropped it, so a statement that should have returned an item returned nothing.

UPDATE Users SET status = 'active' WHERE pk = 'user#1' RETURNING ALL NEW *
DELETE FROM Users WHERE pk = 'user#1' AND sk = 'profile' RETURNING ALL OLD *

What each operation accepts:

Statement Accepted Rejected
UPDATE All four of ALL OLD, ALL NEW, MODIFIED OLD, MODIFIED NEW -
DELETE ALL OLD * only MODIFIED OLD *, ALL NEW *, MODIFIED NEW *

The variants DynamoDB does not allow on DELETE are rejected with its exact validation message rather than ignored.

The MODIFIED projections return only the paths that changed, and exclude the primary key. A nested SET a.b returns just the changed leaf rather than the whole a attribute, and when nothing was projected you get an empty Items array rather than no field. Over list-index paths the changed elements come back packed into a dense list in ascending index order, so SET a[0], a[2] yields {a: [v0, v2]}, matching DynamoDB.

DELETE ... RETURNING ALL OLD * against a key that isn't there returns a present but empty Items array. That differs from the classic DeleteItem path, and it matches real DynamoDB.

BatchExecuteStatement honours RETURNING on each member. ExecuteTransaction rejects it outright with a top-level ValidationException.

A successful ExecuteStatement write without RETURNING returns Items: []. An ExecuteTransaction of writes returns Responses: [], rather than an empty response entry for each statement.

Items on condition failure

From 1.3.0, ReturnValuesOnConditionCheckFailure: ALL_OLD returns the whole existing item when an UPDATE or DELETE fails its WHERE condition:

Operation Where the item appears
ExecuteStatement Item on the ConditionalCheckFailedException
BatchExecuteStatement Error.Item on that statement's response
ExecuteTransaction Item on that statement's cancellation reason

Use the request-level option on ExecuteStatement and the per-statement option on batches and transactions. NONE, omitting it, a missing item, a duplicate INSERT or a statement without a condition returns no failure item. Values other than ALL_OLD and NONE are refused. For transaction retries, this option is part of the ClientRequestToken match; omitted and NONE count as the same mode.

Where PartiQL differs from the classic API

Three behaviours catch people out, and all three match real DynamoDB:

  • UPDATE is not an upsert. The target has to exist already. Updating a key that isn't there fails with ConditionalCheckFailedException ("The conditional request failed") and creates nothing.
  • DELETE requires the full key. On a table with a composite key you must supply the sort key as well as the partition key.
  • SELECT without a key condition scans. The same cost model as the classic API applies, so a WHERE clause on a non-key attribute reads the whole table and filters afterwards.

Errors

Parse failures use DynamoDB's wording: Statement wasn't well formed, can't be processed: <detail>. A statement outside the supported forms is refused according to what it parses as: a lone name, for example, reports Unsupported operation: Id, while trailing text reports Unexpected token after expression.

On BatchExecuteStatement, a member that fails to parse carries the short-form ValidationError code, matching a per-statement execution error rather than a request-level one. Each successful member echoes its TableName.

A request must supply a parameter for every ? placeholder. Too few gives Number of parameters in request and statement don't match. Extra parameters are accepted but still validated; an explicit empty Parameters list is refused.

A cancelled ExecuteTransaction reports every statement that failed. A key of the wrong schema type is reported alone, with None for the other statements. Both batch and transaction requests reject mixed reads/writes and duplicate item targets; batch and transaction SELECTs must name the table's primary key rather than add non-key filters.

Over MCP

The MCP server exposes all three operations as tools: execute_partiql, batch_execute_partiql, and execute_transaction_partiql. See the MCP docs.

In the browser

From 0.13.0 the WebAssembly build runs all three operations too, matching the native build statement for statement - RETURNING projections and ClientRequestToken idempotency included. One corner is unreachable rather than supported: a PartiQL write records a stream event when the table has a stream enabled, and the browser build can't enable a stream.