Query DynamoDB With PartiQL in AWS SDK v3 (ExecuteStatement)

Photo by Luca Bravo on Unsplash

This guide is for Node.js and TypeScript developers who want to read and write DynamoDB items with PartiQL instead of expression syntax, or who inherited code that does. You’ll get a typed module covering SELECT, INSERT, conditional UPDATE, DELETE, batches of up to 25 statements and transactions, plus the IAM policy that blocks accidental full scans and fixes for the errors you’ll meet.

Every sample was type-checked with strict tsc against @aws-sdk/client-dynamodb and @aws-sdk/lib-dynamodb 3.1142.0 in September 2026, and exercised with aws-sdk-client-mock: paging across two responses, duplicate inserts, failed conditions, a 30-key batch split into 25 and 5, and a transaction.

PartiQL or Query: which should you use?

PartiQL is another front end to the same storage engine. It doesn’t add joins or aggregates, and each statement still maps to a key lookup, a query, a scan or a single-item write. The PartiQL project defines the language; DynamoDB supports a subset of it.

Choose PartiQL when statements are easier to read than expressions, when a tool or team already speaks SQL, or for ad hoc work. Choose Query when you want the SDK paginator, ExclusiveStartKey control and no chance of a hidden scan; the guide to querying DynamoDB with AWS SDK v3 covers that path.

Prerequisites

• Node.js 18 or later with @aws-sdk/client-dynamodb, @aws-sdk/lib-dynamodb and, for the base-client example, @aws-sdk/util-dynamodb.
• Credentials the SDK can find; how AWS SDK v3 credential providers work explains fromIni, SSO and roles.
• A table. The examples use Orders (partition key CustomerId, sort key OrderId, a global secondary index OrderStatusIndex on OrderStatus) and Inventory (partition key ProductId).

How to query DynamoDB with PartiQL in AWS SDK v3, step by step

1. Wrap the clientDynamoDBDocumentClient.from(new DynamoDBClient(...)) lets you pass strings and numbers instead of { S: "..." } shapes.
2. Write the statement with placeholdersQuote table and index names with double quotes; use ? for every value. Never build statements by string concatenation with user input.
3. Put the key in WHEREEquality or IN on the partition key keeps a SELECT from scanning the table.
4. Send ExecuteStatementCommandRead Items, and loop while NextToken is set.
5. Handle statement errorsDuplicateItemException for inserts and ConditionalCheckFailedException for updates and deletes that match nothing.

Example: an orders module with PartiQL

// orders-partiql.ts: PartiQL reads and writes against an "Orders" table
// (partition key CustomerId, sort key OrderId, GSI "OrderStatusIndex" on OrderStatus)
// and an "Inventory" table (partition key ProductId), using the DynamoDB Document Client.
import { DynamoDBClient, DuplicateItemException, ConditionalCheckFailedException } from "@aws-sdk/client-dynamodb";
import {
  DynamoDBDocumentClient,
  ExecuteStatementCommand,
  BatchExecuteStatementCommand,
  ExecuteTransactionCommand,
} from "@aws-sdk/lib-dynamodb";

const ddb = DynamoDBDocumentClient.from(new DynamoDBClient({ region: process.env.AWS_REGION ?? "us-east-1" }), {
  marshallOptions: { removeUndefinedValues: true },
});

export interface Order {
  CustomerId: string;
  OrderId: string;
  OrderStatus: "PENDING" | "PAID" | "SHIPPED";
  OrderTotal: number;
  CreatedAt: string;
  ShippedAt?: string;
}

/** One item by its full primary key. Equality on the partition key means no table scan. */
export async function getOrder(customerId: string, orderId: string): Promise<Order | undefined> {
  const res = await ddb.send(
    new ExecuteStatementCommand({
      Statement: `SELECT * FROM "Orders" WHERE CustomerId = ? AND OrderId = ?`,
      Parameters: [customerId, orderId],
    }),
  );
  return res.Items?.[0] as Order | undefined;
}

/** All orders for a customer, newest first. ExecuteStatement has no paginator, so follow NextToken yourself. */
export async function listOrders(customerId: string): Promise<Order[]> {
  const orders: Order[] = [];
  let nextToken: string | undefined;
  do {
    const res = await ddb.send(
      new ExecuteStatementCommand({
        Statement: `SELECT CustomerId, OrderId, OrderStatus, OrderTotal, CreatedAt FROM "Orders" WHERE CustomerId = ? ORDER BY OrderId DESC`,
        Parameters: [customerId],
        NextToken: nextToken,
      }),
    );
    orders.push(...((res.Items ?? []) as Order[]));
    nextToken = res.NextToken;
  } while (nextToken);
  return orders;
}

/** Query a global secondary index: quote both the table and the index name. */
export async function ordersWithStatus(status: Order["OrderStatus"], limit = 50): Promise<Order[]> {
  const res = await ddb.send(
    new ExecuteStatementCommand({
      Statement: `SELECT * FROM "Orders"."OrderStatusIndex" WHERE OrderStatus = ?`,
      Parameters: [status],
      Limit: limit, // items evaluated per call, not items returned
    }),
  );
  return (res.Items ?? []) as Order[];
}

/** INSERT fails with DuplicateItemException if the key already exists, so it never overwrites. */
export async function createOrder(order: Order): Promise<boolean> {
  try {
    await ddb.send(
      new ExecuteStatementCommand({
        Statement: `INSERT INTO "Orders" VALUE {'CustomerId': ?, 'OrderId': ?, 'OrderStatus': ?, 'OrderTotal': ?, 'CreatedAt': ?}`,
        Parameters: [order.CustomerId, order.OrderId, order.OrderStatus, order.OrderTotal, order.CreatedAt],
      }),
    );
    return true;
  } catch (err) {
    if (err instanceof DuplicateItemException) return false;
    throw err;
  }
}

/** Conditional update: the extra WHERE term acts as a condition. No match means ConditionalCheckFailedException. */
export async function markShipped(customerId: string, orderId: string, when: string): Promise<Order | undefined> {
  try {
    const res = await ddb.send(
      new ExecuteStatementCommand({
        Statement: `UPDATE "Orders" SET OrderStatus = 'SHIPPED' SET ShippedAt = ? WHERE CustomerId = ? AND OrderId = ? AND OrderStatus = 'PAID' RETURNING ALL NEW *`,
        Parameters: [when, customerId, orderId],
      }),
    );
    return res.Items?.[0] as Order | undefined;
  } catch (err) {
    if (err instanceof ConditionalCheckFailedException) return undefined; // missing, or not PAID
    throw err;
  }
}

/** DELETE one item by key; RETURNING ALL OLD * gives back what was removed. */
export async function deleteOrder(customerId: string, orderId: string): Promise<Order | undefined> {
  const res = await ddb.send(
    new ExecuteStatementCommand({
      Statement: `DELETE FROM "Orders" WHERE CustomerId = ? AND OrderId = ? RETURNING ALL OLD *`,
      Parameters: [customerId, orderId],
    }),
  );
  return res.Items?.[0] as Order | undefined;
}

/** Up to 25 statements per BatchExecuteStatement. HTTP 200 doesn't mean every statement worked. */
export async function getOrders(keys: { customerId: string; orderId: string }[]): Promise<{ found: Order[]; failed: string[] }> {
  const found: Order[] = [];
  const failed: string[] = [];
  for (let i = 0; i < keys.length; i += 25) {
    const chunk = keys.slice(i, i + 25);
    const res = await ddb.send(
      new BatchExecuteStatementCommand({
        Statements: chunk.map((k) => ({
          Statement: `SELECT * FROM "Orders" WHERE CustomerId = ? AND OrderId = ?`,
          Parameters: [k.customerId, k.orderId],
        })),
      }),
    );
    (res.Responses ?? []).forEach((r, j) => {
      const key = `${chunk[j].customerId}/${chunk[j].orderId}`;
      if (r.Error) failed.push(`${key}: ${r.Error.Code}`); // e.g. ThrottlingError, ResourceNotFound
      else if (r.Item) found.push(r.Item as Order);
    });
  }
  return { found, failed };
}

/** All-or-nothing: create the order and take stock in one transaction (up to 100 statements). */
export async function placeOrder(order: Order, productId: string, quantity: number, requestId: string): Promise<void> {
  await ddb.send(
    new ExecuteTransactionCommand({
      ClientRequestToken: requestId, // retries with the same token and statements are idempotent
      TransactStatements: [
        {
          Statement: `INSERT INTO "Orders" VALUE {'CustomerId': ?, 'OrderId': ?, 'OrderStatus': ?, 'OrderTotal': ?, 'CreatedAt': ?}`,
          Parameters: [order.CustomerId, order.OrderId, order.OrderStatus, order.OrderTotal, order.CreatedAt],
        },
        {
          Statement: `UPDATE "Inventory" SET InStock = InStock - ? WHERE ProductId = ? AND InStock >= ?`,
          Parameters: [quantity, productId, quantity],
        },
      ],
    }),
  );
}

A few things in this module are easy to get wrong:

• Attribute names. Names like Status, Total and Timestamp are DynamoDB reserved words, which is why the table uses OrderStatus and OrderTotal. If you’re stuck with a reserved name, wrap it in double quotes.
• Strings versus identifiers. Single quotes are string literals ('PAID'); double quotes are identifiers ("Orders").
• UPDATE conditions. The WHERE clause has to resolve to one primary key, and extra terms like AND OrderStatus = 'PAID' act as a condition. The PartiQL reference says that if WHERE doesn’t evaluate to true for any item, DynamoDB returns ConditionalCheckFailedException.
• INSERT never overwrites. If the key exists, you get DuplicateItemException. Use UPDATE or PutCommand for upserts.
• One item per write. INSERT, UPDATE and DELETE each touch a single item. Multi-item changes go in a batch or a transaction.

The RETURNING clause is PartiQL’s version of ReturnValues: ALL OLD *, MODIFIED OLD *, ALL NEW * or MODIFIED NEW *. The DynamoDB UpdateItem guide for SDK v3 goes deeper on counters and optimistic locking, which work the same way in either syntax.

Document client or base client: how are parameters sent?

The document client in @aws-sdk/lib-dynamodb converts Parameters into DynamoDB AttributeValue shapes on the way out and converts Items back into plain objects; the lib-dynamodb README lists its marshalling options. We captured the request body the document client produced for an insert with ["c-42", 49.9]:

{"Statement":"INSERT INTO \"Orders\" VALUE {'CustomerId': ?, 'OrderTotal': ?}","Parameters":[{"S":"c-42"},{"N":"49.9"}]}

With the base client you build those shapes yourself and unmarshall the result:

// partiql-low-level.ts: the same SELECT with the base client. Parameters and Items use AttributeValue shapes.
import { DynamoDBClient, ExecuteStatementCommand } from "@aws-sdk/client-dynamodb";
import { unmarshall } from "@aws-sdk/util-dynamodb";

const client = new DynamoDBClient({ region: process.env.AWS_REGION ?? "us-east-1" });

export async function getOrderRaw(customerId: string, orderId: string): Promise<Record<string, unknown> | undefined> {
  const res = await client.send(
    new ExecuteStatementCommand({
      Statement: `SELECT * FROM "Orders" WHERE CustomerId = ? AND OrderId = ?`,
      Parameters: [{ S: customerId }, { S: orderId }], // typed values: S, N (as a string), BOOL, M, L ...
      ConsistentRead: true,
      ReturnConsumedCapacity: "TOTAL",
    }),
  );
  console.log("read capacity used:", res.ConsumedCapacity?.CapacityUnits);
  return res.Items?.[0] ? unmarshall(res.Items[0]) : undefined;
}

The two ExecuteStatementCommand classes share a name, so import from one package per file. Mixing them up sends a raw string where an AttributeValue was expected, or the reverse, and DynamoDB rejects the request as invalid.

How does PartiQL pagination work with NextToken?

A single SELECT reads at most 1 MB of data before filtering. When there’s more, the response includes NextToken; pass it back with the same statement and parameters until it’s absent. The SDK has paginators for Query and Scan but none for ExecuteStatement, which is why listOrders has its own do...while loop. The guide to AWS SDK v3 paginators shows what the generated ones do for other APIs.

Limit caps how many items DynamoDB evaluates per call, not how many match. A Limit of 50 with a filter on a non-key attribute can return 3 items and a NextToken. If you need “the first 50 matches”, keep reading pages until you have them.

Which SELECT statements scan the whole table?

The DynamoDB PartiQL reference is specific: a SELECT runs as a full table scan unless the WHERE clause has an equality or IN condition on the partition key.

• Key lookups or queries: WHERE CustomerId = ?, WHERE CustomerId = ? AND OrderId = ?, WHERE CustomerId IN [?, ?], WHERE CustomerId = ? OR CustomerId = ?.
• Full scans: WHERE CustomerId > ?, WHERE OrderTotal > 500, WHERE CustomerId = ? OR OrderStatus = 'PAID', or no WHERE at all.

A scan reads every item and can use up a table’s provisioned throughput in one go. On an index, the same rule applies to the index’s partition key: "Orders"."OrderStatusIndex" WHERE OrderStatus = ? is a query. The IAM policy below turns accidental scans into access denied errors. If a table only ever gets scanned, the report to find unused DynamoDB tables with no reads or writes is a useful sanity check on whether it’s needed.

Batches and transactions: BatchExecuteStatement or ExecuteTransaction?

BatchExecuteStatement takes up to 25 statements that must be all reads or all writes, and each read must specify equality on every key attribute, so it returns at most one item. The important part is the response: an HTTP 200 doesn’t mean every statement worked. Each entry in Responses is in request order and may carry an Error with a Code such as ThrottlingError, ConditionalCheckFailed or DuplicateItem. getOrders above checks every entry; with 30 keys, the mocked run made two calls (25 and 5) and reported the failed keys separately.

ExecuteTransaction takes up to 100 statements, all-or-nothing, and they also can’t mix reads and writes (the EXISTS function is the exception, for condition checks). If any statement fails its condition, the whole call fails with TransactionCanceledException, whose CancellationReasons list is in statement order. The API reference lists IdempotentParameterMismatchException for a ClientRequestToken reused with a different payload, so keep the token stable across retries of the same order. For reading those reasons and sizing transactions, see DynamoDB transactions with TransactWriteItems in SDK v3; for bulk loads without all-or-nothing, BatchWriteItem with AWS SDK v3 handles retries of unprocessed items.

Which IAM permissions does PartiQL need?

PartiQL has its own IAM actions, one per statement type, on the table or index ARN. dynamodb:Query or dynamodb:PutItem alone won’t authorize a PartiQL statement. The second statement uses the dynamodb:FullTableScan condition key to deny any SELECT that would scan:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "PartiQLOnOrdersAndInventory",
      "Effect": "Allow",
      "Action": [
        "dynamodb:PartiQLSelect",
        "dynamodb:PartiQLInsert",
        "dynamodb:PartiQLUpdate",
        "dynamodb:PartiQLDelete"
      ],
      "Resource": [
        "arn:aws:dynamodb:us-east-1:123456789012:table/Orders",
        "arn:aws:dynamodb:us-east-1:123456789012:table/Orders/index/OrderStatusIndex",
        "arn:aws:dynamodb:us-east-1:123456789012:table/Inventory"
      ]
    },
    {
      "Sid": "NoFullTableScans",
      "Effect": "Deny",
      "Action": "dynamodb:PartiQLSelect",
      "Resource": "arn:aws:dynamodb:us-east-1:123456789012:table/*",
      "Condition": { "Bool": { "dynamodb:FullTableScan": "true" } }
    }
  ]
}

To restrict statements to transactions only, AWS documents a dynamodb:EnclosingOperation condition with the value ExecuteTransaction. For checking which actions a piece of SDK code needs, see finding the IAM actions your AWS SDK for JavaScript code needs, or paste the module into the IAM policy generator for TypeScript and review what it drafts.

Troubleshooting and common mistakes

• Access denied on a query that works in the console. The role has dynamodb:Query but not dynamodb:PartiQLSelect, or the deny on full scans matched because the WHERE clause lacks partition key equality. Then work through troubleshooting IAM access denied errors.
• ValidationException on the statement. Usually a reserved word used as an attribute name without double quotes, double quotes used for a string value, or a mismatch between placeholders and Parameters.
• Empty results with a NextToken. Normal for filtered reads. Keep paging.
• Unexpected cost or throttling on reads. A SELECT is scanning. Set ReturnConsumedCapacity to TOTAL and compare capacity per call before and after fixing the WHERE clause.
• Batch looks successful but items are missing. Check each Responses[i].Error.
• Testing. Mock DynamoDBDocumentClient with aws-sdk-client-mock; the guide to mocking AWS SDK v3 clients in unit tests shows the setup we used.

Limits: what PartiQL for DynamoDB can’t do

• No joins, no GROUP BY, no aggregates such as COUNT or SUM. Compute them in code or with an export to an analytics service.
• No multi-item UPDATE or DELETE: one item per statement.
• No SDK paginator; you loop on NextToken.
• Statements are limited to 8,192 characters, batches to 25 statements, transactions to 100.
• It doesn’t make DynamoDB relational. Access patterns still have to match your keys and indexes.

If you’re moving older code, the free AWS SDK v2 to v3 converter drafts the change from v2 executeStatement calls, and migrating a Node.js app from AWS SDK v2 to v3 covers the rest of the project. For DynamoDB PartiQL in AWS SDK v3 inside Lambda, keep the imports to the two packages you use; reducing AWS SDK v3 bundle size in Lambda explains why.

Frequently asked questions

Related guides

• Query DynamoDB with AWS SDK v3Keys, indexes and the built-in paginator.
• DynamoDB UpdateItem with AWS SDK v3Conditions, counters and optimistic locking.
• DynamoDB BatchWriteItem with AWS SDK v3Bulk writes with retries for unprocessed items.
• DynamoDB transactions with SDK v3All-or-nothing writes and cancellation reasons.
• Process DynamoDB Streams in LambdaReact to the items your statements change.
• Mock AWS SDK v3 clients in unit testsTest PartiQL code without a table.