DynamoDB PartiQL

PartiQL is a SQL-compatible query language that works with DynamoDB. Developers who already know SQL can read and change DynamoDB data with familiar statements such as SELECT, INSERT, UPDATE, and DELETE. PartiQL translates each statement into native DynamoDB operations.

Why PartiQL Exists

Native DynamoDB calls use JSON structures with expression attributes and placeholders. Newcomers often find that style heavy. PartiQL offers a readable alternative. A statement that looks like a sentence replaces several lines of request JSON.

Running PartiQL Statements

You can run PartiQL in three places: the PartiQL editor in the DynamoDB console, the AWS CLI, and the SDKs. Three API calls carry statements:

Reading Data with SELECT

SELECT * FROM Students WHERE StudentID = 'S001'
SELECT Name, City FROM Students WHERE StudentID = 'S001'

The first statement returns the whole item with that key. The second statement returns only two attributes. A SELECT that supplies the partition key runs as an efficient Query or GetItem.

Writing Data with INSERT

INSERT adds one item per statement. The item appears as a map after the keyword VALUE.

INSERT INTO Students VALUE {'StudentID': 'S020', 'Name': 'Kavya', 'City': 'Pune'}

INSERT fails when an item with the same key already exists.

Changing Data with UPDATE

UPDATE Students SET City = 'Delhi' WHERE StudentID = 'S020'
UPDATE Students REMOVE Phone WHERE StudentID = 'S020'

An UPDATE statement must include the full primary key in the WHERE clause. DynamoDB updates one item per statement.

Removing Data with DELETE

DELETE FROM Students WHERE StudentID = 'S020'

Using Parameters

Question marks serve as parameters. The application passes the actual values separately. Parameters keep statements clean and prevent mistakes caused by joining strings.

Statement:   SELECT * FROM Students WHERE StudentID = ?
Parameters:  [ { "S": "S001" } ]

Querying an Index

Write the table name and the index name in double quotes, separated by a dot, to query a secondary index.

SELECT * FROM "Students"."CityIndex" WHERE City = 'Pune'

Danger: Hidden Table Scans

A SELECT without a key condition reads the entire table. DynamoDB performs a full Scan, and the cost grows with table size.

EFFICIENT (partition key supplied)
SELECT * FROM Orders WHERE CustomerID = 'C100'
→ runs as Query, reads only matching items

COSTLY (no key condition)
SELECT * FROM Orders WHERE Status = 'SHIPPED'
→ runs as Scan, reads every item, then filters

Always include the partition key in the WHERE clause of read statements. An IAM policy can deny full table scans with the dynamodb:FullTableScan condition key.

Differences from Standard SQL

• PartiQL in DynamoDB has no JOIN operation.
• No GROUP BY or aggregate functions such as SUM exist.
• Each INSERT adds a single item.
• Updates and deletes need the full primary key.
• The language does not change how DynamoDB stores data. Key design still decides performance.

PartiQL and Capacity

PartiQL statements consume the same capacity as the native operations they become. A key-based SELECT costs the same as GetItem. A scan-style SELECT costs the same as Scan.

When to Choose PartiQL

PartiQL shines during exploration. A developer can inspect data in the console editor, test a quick fix, or run a one-time correction without writing application code. Teams that come from relational databases adapt faster because the statements look familiar. Production code that needs fine control over expressions, conditions, and return values often stays with the native API. Both styles work on the same tables, so a team can mix them freely.

Summary

• PartiQL lets you use SQL-style statements with DynamoDB.
• SELECT, INSERT, UPDATE, and DELETE map to native DynamoDB operations.
• Parameters with question marks keep statements safe and tidy.
• A SELECT without the partition key triggers a full table scan.
• No joins or aggregate functions exist, so access patterns still shape table design.