DynamoDB PartiQL: Statements, Examples and API Limits

PartiQL is a SQL-compatible language for structured and nested data, and DynamoDB implements a subset for SELECT, INSERT, UPDATE and DELETE. Execute statements through the console, CLI or SDK using DynamoDB's PartiQL APIs. The same key design, capacity and consistency rules still apply: a SELECT without an appropriate partition-key condition can scan the table, and SQL JOIN is not supported.

How to use PartiQL in DynamoDB

Use the console's PartiQL editor, NoSQL Workbench, AWS CLI, or an SDK. Dynobase's PartiQL workspace also supports writes and transactions. The interface changes statement syntax; it does not remove the need to model access patterns or handle read pagination.

This page is the implementation reference. For choosing between operational statements, native APIs and an analytics engine, see SQL with DynamoDB. AWS documents the supported language subset in its PartiQL reference.

All examples use an existing users table with String partition key id and String sort key email. Every singleton write supplies both keys. Attributes such as name, isVerified and metadata are ordinary non-key fields.

API selection and limits

Every stored item must still fit DynamoDB's 400 KB limit. Transactions cannot operate on the same item twice, must stay in one account and Region, and are not supported on multi-Region strong consistency global tables. A batch is not a transaction: some statements can succeed while others fail.

The limits are documented in ExecuteStatement, BatchExecuteStatement, ExecuteTransaction and the DynamoDB constraints.

Querying the data

SELECT syntax and key conditions

The basic shape is:

SELECT * FROM "users" WHERE "id" = '123'
SELECT * FROM "users" WHERE "id" = '123'

This selects the item collection for id = '123'. To identify one item, add AND "email" = 'john@example.com'. String values use single quotes; quoted table and attribute identifiers use double quotes. Attribute names and table names are case-sensitive.

PartiQL also supports partition-key IN conditions:

SELECT * FROM "users" WHERE "id" IN ['123', '456']
SELECT * FROM "users" WHERE "id" IN ['123', '456']

DynamoDB PartiQL uses square brackets for this list. Do not copy the native expression API's parenthesized IN syntax into a PartiQL statement unchanged.

A projection can include document paths:

SELECT "id", "email", "metadata"."lastLoggedAt"
FROM "users"
WHERE "id" = '123'
SELECT "id", "email", "metadata"."lastLoggedAt"
FROM "users"
WHERE "id" = '123'

A WHERE clause on a non-key attribute can filter the candidates, but does not make them cheaper to read. For example, WHERE "isVerified" = true alone scans this table. Even a condition mentioning the partition key can still scan if another OR branch permits arbitrary items, such as "id" = '123' OR "isVerified" = true.

Use Query vs Scan for the access-pattern comparison. The SELECT reference lists key predicates that avoid full scans.

Index selection and sorting

Explicitly name a table and index, with both names quoted:

SELECT * FROM "users"."name-id-index" WHERE "name" = 'John'
SELECT * FROM "users"."name-id-index" WHERE "name" = 'John'

This assumes a GSI named name-id-index exists with name as its partition key and id as its sort key. A condition on name in a SELECT from users alone does not automatically select that GSI. Its results contain only projected attributes and use eventual consistency.

For the base-table example, reverse the email sort-key order within one ID:

SELECT * FROM "users" WHERE "id" = '123' ORDER BY "email" DESC
SELECT * FROM "users" WHERE "id" = '123' ORDER BY "email" DESC

ORDER BY is constrained to keys and supported key access patterns. It is not a way to sort a table by any arbitrary non-key field.

Parameters with ExecuteStatement

Use ? placeholders for values and supply Parameters in appearance order. Do not quote the question mark: '?' is a literal string, not a parameter. Placeholders represent values, not table names, index names or attribute identifiers. Choose those identifiers from trusted application configuration.

AWS CLI v2

aws dynamodb execute-statement \
  --statement 'SELECT * FROM "users" WHERE "id" = ? AND "email" = ?' \
  --parameters '[{"S":"123"},{"S":"john@example.com"}]' \
  --return-consumed-capacity TOTAL
aws dynamodb execute-statement \
  --statement 'SELECT * FROM "users" WHERE "id" = ? AND "email" = ?' \
  --parameters '[{"S":"123"},{"S":"john@example.com"}]' \
  --return-consumed-capacity TOTAL

The CLI uses typed DynamoDB JSON values. A Number parameter would be {"N":"10"}, while a Boolean parameter is {"BOOL":true}. AWS's parameterized statement examples explain this interface.

JavaScript SDK v3 with the document client

Install @aws-sdk/client-dynamodb and @aws-sdk/lib-dynamodb, configure credentials and a Region, and run this as an ES module:

import { DynamoDBClient } from "@aws-sdk/client-dynamodb"
import { DynamoDBDocumentClient, ExecuteStatementCommand } from "@aws-sdk/lib-dynamodb"

const client = DynamoDBDocumentClient.from(new DynamoDBClient({}))
const result = await client.send(new ExecuteStatementCommand({
  Statement: 'SELECT * FROM "users" WHERE "id" = ? AND "email" = ?',
  Parameters: ["123", "john@example.com"],
  ReturnConsumedCapacity: "TOTAL",
}))
console.log(result.Items, result.ConsumedCapacity)
import { DynamoDBClient } from "@aws-sdk/client-dynamodb"
import { DynamoDBDocumentClient, ExecuteStatementCommand } from "@aws-sdk/lib-dynamodb"

const client = DynamoDBDocumentClient.from(new DynamoDBClient({}))
const result = await client.send(new ExecuteStatementCommand({
  Statement: 'SELECT * FROM "users" WHERE "id" = ? AND "email" = ?',
  Parameters: ["123", "john@example.com"],
  ReturnConsumedCapacity: "TOTAL",
}))
console.log(result.Items, result.ConsumedCapacity)

The document client accepts ordinary JavaScript values and returns ordinary objects. If you import ExecuteStatementCommand from the low-level @aws-sdk/client-dynamodb package instead, use typed attribute values for parameters and decode the response yourself. Do not mix the two representations.

Python with boto3

import boto3
from boto3.dynamodb.types import TypeDeserializer

client = boto3.client("dynamodb")
deserializer = TypeDeserializer()
result = client.execute_statement(
    Statement='SELECT * FROM "users" WHERE "id" = ? AND "email" = ?',
    Parameters=[{"S": "123"}, {"S": "john@example.com"}],
    ReturnConsumedCapacity="TOTAL",
)

for item in result.get("Items", []):
    print({key: deserializer.deserialize(value) for key, value in item.items()})
import boto3
from boto3.dynamodb.types import TypeDeserializer

client = boto3.client("dynamodb")
deserializer = TypeDeserializer()
result = client.execute_statement(
    Statement='SELECT * FROM "users" WHERE "id" = ? AND "email" = ?',
    Parameters=[{"S": "123"}, {"S": "john@example.com"}],
    ReturnConsumedCapacity="TOTAL",
)

for item in result.get("Items", []):
    print({key: deserializer.deserialize(value) for key, value in item.items()})

boto3's client uses typed values; the deserializer converts them into Python values, with Numbers represented as Decimal.

Pagination with NextToken

For a SELECT that returns multiple items, pass the service's NextToken into the next ExecuteStatement request. Keep the statement, parameters and read options the same. Limit counts evaluated items, not only returned matches, so continue through empty pages while a token is returned.

Using the JavaScript client above:

const request = {
  Statement: 'SELECT * FROM "users" WHERE "id" = ?',
  Parameters: ["123"],
  Limit: 100,
}
let nextToken

do {
  const page = await client.send(new ExecuteStatementCommand({
    ...request,
    ...(nextToken ? { NextToken: nextToken } : {}),
  }))
  for (const item of page.Items ?? []) console.log(item)
  nextToken = page.NextToken
} while (nextToken)
const request = {
  Statement: 'SELECT * FROM "users" WHERE "id" = ?',
  Parameters: ["123"],
  Limit: 100,
}
let nextToken

do {
  const page = await client.send(new ExecuteStatementCommand({
    ...request,
    ...(nextToken ? { NextToken: nextToken } : {}),
  }))
  for (const item of page.Items ?? []) console.log(item)
  nextToken = page.NextToken
} while (nextToken)

The same loop in boto3:

request = {
    "Statement": 'SELECT * FROM "users" WHERE "id" = ?',
    "Parameters": [{"S": "123"}],
    "Limit": 100,
}

while True:
    page = client.execute_statement(**request)
    for item in page.get("Items", []):
        print(item)
    token = page.get("NextToken")
    if not token:
        break
    request["NextToken"] = token
request = {
    "Statement": 'SELECT * FROM "users" WHERE "id" = ?',
    "Parameters": [{"S": "123"}],
    "Limit": 100,
}

while True:
    page = client.execute_statement(**request)
    for item in page.get("Items", []):
        print(item)
    token = page.get("NextToken")
    if not token:
        break
    request["NextToken"] = token

In the CLI, pass this service token with --next-token. It is distinct from --starting-token used by CLI-managed Query and Scan pagination. ExecuteStatement has no ExclusiveStartKey input; do not copy the native Query continuation-key loop into it. The pagination guide explains the other token and key formats.

Inserting data

PartiQL uses INSERT INTO ... VALUE, with a single item tuple:

INSERT INTO "users" VALUE {
  'id': '123',
  'email': 'john@example.com',
  'name': 'John',
  'isVerified': false,
  'metadata': {'lastLoggedAt': 0}
}
INSERT INTO "users" VALUE {
  'id': '123',
  'email': 'john@example.com',
  'name': 'John',
  'isVerified': false,
  'metadata': {'lastLoggedAt': 0}
}

The table must already exist and both key attributes must be supplied. Unlike an unconditional PutItem, PartiQL INSERT does not overwrite an existing item with the same key: it returns DuplicateItemException. See the INSERT reference.

For application writes, parameterize the values:

await client.send(new ExecuteStatementCommand({
  Statement: `INSERT INTO "users" VALUE {'id': ?, 'email': ?, 'name': ?}`,
  Parameters: ["456", "alex@example.com", "Alex"],
}))
await client.send(new ExecuteStatementCommand({
  Statement: `INSERT INTO "users" VALUE {'id': ?, 'email': ?, 'name': ?}`,
  Parameters: ["456", "alex@example.com", "Alex"],
}))

Updating data

An UPDATE must resolve to one complete primary key. Use repeated SET clauses for multiple assignments, not a comma-separated SQL assignment list:

UPDATE "users"
SET "isVerified" = true
SET "metadata" = {'lastLoggedAt': 1792713600}
WHERE "id" = '123' AND "email" = 'john@example.com'
RETURNING ALL NEW *
UPDATE "users"
SET "isVerified" = true
SET "metadata" = {'lastLoggedAt': 1792713600}
WHERE "id" = '123' AND "email" = 'john@example.com'
RETURNING ALL NEW *

Here the entire metadata map is replaced. To change one field of an existing map, use a nested path such as SET "metadata"."lastLoggedAt" = ?. Supported update operations also include REMOVE and the documented list/set functions; use the UPDATE reference for their syntax.

Add a non-key condition to enforce an expected state:

const result = await client.send(new ExecuteStatementCommand({
  Statement: 'UPDATE "users" SET "isVerified" = ? WHERE "id" = ? AND "email" = ? AND "isVerified" = ?',
  Parameters: [true, "123", "john@example.com", false],
  ReturnValuesOnConditionCheckFailure: "ALL_OLD",
}))
console.log(result)
const result = await client.send(new ExecuteStatementCommand({
  Statement: 'UPDATE "users" SET "isVerified" = ? WHERE "id" = ? AND "email" = ? AND "isVerified" = ?',
  Parameters: [true, "123", "john@example.com", false],
  ReturnValuesOnConditionCheckFailure: "ALL_OLD",
}))
console.log(result)

This statement is an alternative transition example: it succeeds only while the flag is false. If the preceding update already changed the flag, the condition fails as intended. A failed condition is a business-state outcome, not a transient error to retry unchanged forever.

Deleting data

DELETE FROM "users"
WHERE "id" = '123' AND "email" = 'john@example.com'
RETURNING ALL OLD *
DELETE FROM "users"
WHERE "id" = '123' AND "email" = 'john@example.com'
RETURNING ALL OLD *

This removes one item and optionally returns its old attributes. You cannot delete every matching non-key value with one DELETE statement. Discover the keys, then issue bounded singleton or batch operations. The DELETE reference describes missing-item and condition-failure behavior.

BatchExecuteStatement: independent operations

A batch can contain up to 25 statements, all reads or all writes. Every SELECT in a batch must use equality on all primary-key attributes and return at most one item. A batch is therefore not a container for several open-ended partition queries.

For example, save this as batch-statements.json:

[
  {
    "Statement": "UPDATE \"users\" SET \"isVerified\" = ? WHERE \"id\" = ? AND \"email\" = ?",
    "Parameters": [{"BOOL":true},{"S":"123"},{"S":"john@example.com"}]
  },
  {
    "Statement": "UPDATE \"users\" SET \"isVerified\" = ? WHERE \"id\" = ? AND \"email\" = ?",
    "Parameters": [{"BOOL":true},{"S":"456"},{"S":"alex@example.com"}]
  }
]
[
  {
    "Statement": "UPDATE \"users\" SET \"isVerified\" = ? WHERE \"id\" = ? AND \"email\" = ?",
    "Parameters": [{"BOOL":true},{"S":"123"},{"S":"john@example.com"}]
  },
  {
    "Statement": "UPDATE \"users\" SET \"isVerified\" = ? WHERE \"id\" = ? AND \"email\" = ?",
    "Parameters": [{"BOOL":true},{"S":"456"},{"S":"alex@example.com"}]
  }
]

These are independent examples for existing items; do not delete those items before trying the batch.

aws dynamodb batch-execute-statement \
  --statements file://batch-statements.json \
  --return-consumed-capacity TOTAL
aws dynamodb batch-execute-statement \
  --statements file://batch-statements.json \
  --return-consumed-capacity TOTAL

A successful HTTP response does not prove every statement succeeded. Inspect each entry of Responses for Error. In SDK v3, the document-client equivalent is BatchExecuteStatementCommand from @aws-sdk/lib-dynamodb, with native values in each statement's Parameters.

Retry only retryable failed statements with backoff, preserve successful results, and surface conditional failures separately. Replaying a successful increment could apply it twice. PartiQL batches support UPDATE and conditions, while native BatchWriteItem supports puts and deletes without per-item conditions; this distinction does not mean other native DynamoDB APIs lack conditional writes.

ExecuteTransaction: atomic statements

Use a transaction when a group of item operations must succeed or fail together. Up to 100 actions fit in one transaction, subject to the 4 MB data ceiling and normal item limits. Read transactions and write transactions are separate; an EXISTS statement can act as a condition check alongside writes.

This example requires an existing administrative user id = 'admin', email = 'admin@example.com', with isVerified = true. It checks that item and inserts a different user atomically. Save it as transaction-statements.json:

[
  {
    "Statement": "EXISTS(SELECT * FROM \"users\" WHERE \"id\" = ? AND \"email\" = ? AND \"isVerified\" = ?)",
    "Parameters": [{"S":"admin"},{"S":"admin@example.com"},{"BOOL":true}]
  },
  {
    "Statement": "INSERT INTO \"users\" VALUE {'id': ?, 'email': ?, 'name': ?}",
    "Parameters": [{"S":"789"},{"S":"sam@example.com"},{"S":"Sam"}]
  }
]
[
  {
    "Statement": "EXISTS(SELECT * FROM \"users\" WHERE \"id\" = ? AND \"email\" = ? AND \"isVerified\" = ?)",
    "Parameters": [{"S":"admin"},{"S":"admin@example.com"},{"BOOL":true}]
  },
  {
    "Statement": "INSERT INTO \"users\" VALUE {'id': ?, 'email': ?, 'name': ?}",
    "Parameters": [{"S":"789"},{"S":"sam@example.com"},{"S":"Sam"}]
  }
]

aws dynamodb execute-transaction \
  --transact-statements file://transaction-statements.json \
  --return-consumed-capacity TOTAL
aws dynamodb execute-transaction \
  --transact-statements file://transaction-statements.json \
  --return-consumed-capacity TOTAL

The question marks are unquoted, table casing is consistent, and EXISTS identifies a full primary key plus a condition. The checked item is different from the inserted item because two actions cannot target the same item in one transaction. See transaction syntax and the EXISTS reference.

Use ExecuteTransactionCommand from @aws-sdk/lib-dynamodb for native JavaScript parameters, or boto3's execute_transaction(TransactStatements=...) with typed parameters. A transaction failure cancels the group; inspect cancellation reasons before deciding to retry. Do not split an oversized transaction into batches and assume the original atomicity still holds.

IAM

Grant the specific PartiQL operations needed on the intended resources:

• dynamodb:PartiQLSelect for reads.
• dynamodb:PartiQLInsert for inserts.
• dynamodb:PartiQLUpdate for updates.
• dynamodb:PartiQLDelete for deletes.

For example, this read-only policy allows key-based SELECT statements and denies full scans on the example table and its indexes. Replace the account and Region with your own deployment values:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "dynamodb:PartiQLSelect",
      "Resource": [
        "arn:aws:dynamodb:us-east-1:123456789012:table/users",
        "arn:aws:dynamodb:us-east-1:123456789012:table/users/index/*"
      ]
    },
    {
      "Effect": "Deny",
      "Action": "dynamodb:PartiQLSelect",
      "Resource": [
        "arn:aws:dynamodb:us-east-1:123456789012:table/users",
        "arn:aws:dynamodb:us-east-1:123456789012:table/users/index/*"
      ],
      "Condition": {"Bool":{"dynamodb:FullTableScan":"true"}}
    }
  ]
}
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "dynamodb:PartiQLSelect",
      "Resource": [
        "arn:aws:dynamodb:us-east-1:123456789012:table/users",
        "arn:aws:dynamodb:us-east-1:123456789012:table/users/index/*"
      ]
    },
    {
      "Effect": "Deny",
      "Action": "dynamodb:PartiQLSelect",
      "Resource": [
        "arn:aws:dynamodb:us-east-1:123456789012:table/users",
        "arn:aws:dynamodb:us-east-1:123456789012:table/users/index/*"
      ],
      "Condition": {"Bool":{"dynamodb:FullTableScan":"true"}}
    }
  ]
}

Add write actions only for roles that need them. For transaction-only access, AWS also documents the dynamodb:EnclosingOperation condition. See the PartiQL IAM policy examples. A denied scan is preferable to discovering an accidental table-wide read through a cost spike, but an allowed key query can still read a large item collection.

Caveats and production checks

DynamoDB supports a subset of PartiQL, not every feature in the language specification. There is no SQL JOIN, general GROUP BY, or arbitrary COUNT aggregation. AWS's function list is the authority for supported functions. Native vector indexes use their dedicated search API rather than PartiQL.

Request consumed capacity while evaluating an access pattern. Paginate every intended read, check batch-level and statement-level failures separately, and use parameters for values. Do not assume a small returned payload means a selective read. Keep transactional workloads bounded and decide which operations are safe to retry after an uncertain network result.

PartiQL can make queries familiar to an SQL user, but migration still involves keys, indexes, consistency and application-level relationship handling. For ad hoc joins or analytics over large datasets, choose the external analytics paths described in the SQL guide rather than repeatedly scanning the live table.