PartiQL

Execute SQL-like queries on DynamoDB using PartiQL syntax.

What is PartiQL?

PartiQL is a SQL-compatible query language created by AWS. It lets you use familiar SQL syntax (SELECT, INSERT, UPDATE, DELETE) to work with DynamoDB instead of the native API expressions.

DynamoDB added PartiQL support in 2020. It's useful when:

• You're coming from a SQL background
• You want simpler ad-hoc queries
• You prefer SQL syntax over DynamoDB expressions

Under the hood, PartiQL queries are converted to native DynamoDB operations. Performance is the same as using the native API.

Key features

• SQL-compatible syntax for DynamoDB
• Parameterized queries with ? placeholders
• Works with both Client and Model
• Async support
• Metrics (duration, RCU, item count)

Getting started

Basic select

Use execute_statement() to run PartiQL queries:

"""Basic PartiQL SELECT example."""

from pydynox import DynamoDBClient

client = DynamoDBClient()

# Select all items for a partition key
result = client.execute_statement(
    "SELECT * FROM users WHERE pk = ?",
    parameters=["USER#123"],
)

for item in result:
    print(item["name"])

# Access metrics
print(f"Duration: {result.metrics.duration_ms}ms")
print(f"Items: {result.metrics.items_count}")

The result is a list you can iterate over. It also has .metrics and .next_token attributes.

Select specific columns

Fetch only the columns you need:

"""PartiQL SELECT specific columns."""

from pydynox import DynamoDBClient

client = DynamoDBClient()

# Select only name and age columns
result = client.execute_statement(
    "SELECT name, age FROM users WHERE pk = ? AND sk = ?",
    parameters=["USER#1", "PROFILE"],
)

for item in result:
    print(f"{item['name']} is {item['age']} years old")

With Model (typed results)

Use Model.execute_statement() to get typed model instances:

"""PartiQL with Model - returns typed instances."""

from pydynox import Model, ModelConfig
from pydynox.attributes import NumberAttribute, StringAttribute


class User(Model):
    model_config = ModelConfig(table="users")
    pk = StringAttribute(hash_key=True)
    sk = StringAttribute(range_key=True)
    name = StringAttribute()
    age = NumberAttribute()


# Returns list of User instances (typed)
users = User.execute_statement(
    "SELECT * FROM users WHERE pk = ?",
    parameters=["USER#123"],
)

for user in users:
    print(user.name)  # IDE knows this is a string
    print(user.age)  # IDE knows this is a number

This gives you full IDE autocomplete and type checking.

Advanced

Async

Use async_execute_statement() for async code:

"""Async PartiQL example."""

import asyncio

from pydynox import DynamoDBClient


async def main():
    client = DynamoDBClient()

    result = await client.async_execute_statement(
        "SELECT * FROM users WHERE pk = ?",
        parameters=["USER#123"],
    )

    for item in result:
        print(item["name"])

    print(f"Duration: {result.metrics.duration_ms}ms")


asyncio.run(main())

Consistent reads

For strongly consistent reads:

result = client.execute_statement(
    "SELECT * FROM users WHERE pk = ?",
    parameters=["USER#123"],
    consistent_read=True,
)

Pagination

For large result sets, use next_token:

result = client.execute_statement("SELECT * FROM users")

# Process first page
for item in result:
    print(item)

# Fetch next page if available
if result.next_token:
    next_page = client.execute_statement(
        "SELECT * FROM users",
        next_token=result.next_token,
    )

Parameters

PartiQL vs Query

When should you use PartiQL vs the native Query API?

Use PartiQL when:

• You prefer SQL syntax
• You're migrating from SQL databases
• You want simpler ad-hoc queries
• You're doing one-off data exploration

Use Query when:

• You want type-safe conditions with model attributes
• You need automatic pagination
• You're building complex filter logic
• You want IDE autocomplete for conditions

Supported statements

PartiQL in DynamoDB supports:

Next steps

• Query - Type-safe queries with model attributes
• Conditions - Build filter conditions
• Async - Async operations