DynamoDB PartiQL - The Ultimate Guide (with Examples)

AWS recently announced that DynamoDB will now support PartiQL - an SQL-like language for querying, modifying, and inserting data. This is significant for several reasons:

• It makes the learning curve less steep - You can use a well-known language to start using DynamoDB and transition to the native DSL later on.
• It simplifies migrations - No need to learn and implement DynamoDB's native query language from the very beginning.
• It offers choice - It gives you two options/languages to interact with DynamoDB. If you don't like ExpressionAttributeValues and FilterExpressions, you can use PartiQL instead.

How to use PartiQL in DynamoDB:

• Using Dynobase
• The DynamoDB console
• The AWS Command Line Interface (AWS CLI)
• The DynamoDB APIs

Querying the data

Syntax:

SELECT expression
FROM table[.index-name]
WHERE condition
ORDER BY key [DESC|ASC], ...;
SELECT expression
FROM table[.index-name]
WHERE condition
ORDER BY key [DESC|ASC], ...;

Example:

SELECT * FROM users WHERE id = 777;
SELECT * FROM users WHERE id = 777;

Keep in mind that even if DynamoDB supports this SQL-like language, it is still fundamentally a NoSQL database, and this does not change its core principles. Data is still distributed across partitions, and Global Secondary Indexes (GSIs) and Local Secondary Indexes (LSIs) are still needed. Scans and queries are not going anywhere. Your SELECT statements are converted into Scan or Query operations depending on the WHERE clause. If you add a condition on an indexed field, the PartiQL query will be translated to a Query. In other cases, if you execute something like SELECT * from my-ddb-table;, it will be translated to a Scan.

While PartiQL might make DynamoDB more approachable, it also increases the likelihood of making mistakes like scanning an entire table. In PartiQL, a Scan and Query are both disguised as a SELECT statement, whereas in DynamoDB's native language, you have to explicitly state to use Scan(...). However, there's a way to guard yourself against that behavior, more on that in the IAM section.

Inserting data

Syntax:

INSERT INTO table VALUE item;
INSERT INTO table VALUE item;

Example:

INSERT INTO users VALUE {'id': '123', 'name': 'John'};
INSERT INTO users VALUE {'id': '123', 'name': 'John'};

Updating data

Syntax:

UPDATE table
[SET | REMOVE] path [= data]
WHERE condition [RETURNING returnvalues];
UPDATE table
[SET | REMOVE] path [= data]
WHERE condition [RETURNING returnvalues];

Example:

UPDATE users
SET isVerified=TRUE,
    metadata={'lastLoggedAt':1234567890}
WHERE email='john@doe.com' AND id='123';
UPDATE users
SET isVerified=TRUE,
    metadata={'lastLoggedAt':1234567890}
WHERE email='john@doe.com' AND id='123';

Deleting data

Syntax:

DELETE FROM table WHERE condition;
DELETE FROM table WHERE condition;

Example:

DELETE FROM "users" WHERE "id" = '123';
DELETE FROM "users" WHERE "id" = '123';

Similar to updates, deleting records in bulk is not possible. You can only delete one record per operation. If you need to remove multiple records or truncate the whole table, consider using Dynobase's Terminal or Truncate Table option.

However, if you'd like to delete or update items in batches of 25, you can do so using the batchExecuteStatement API. It is also beneficial that it supports conditions, unlike the regular DynamoDB API.

Transactions

Just like with traditional DynamoDB query syntax, making transactions using PartiQL is possible. Keep in mind that within one transaction block, you can only include either read or write operations. Mixing these two in one block is not possible.

Executing transactions is possible via SDK or CLI by passing an array of objects with Statements and Parameters:

[
  {
    "Statement": "EXISTS(SELECT * FROM users WHERE lastName='Doe' AND deletedAt IS MISSING)"
  },
  {
    "Statement": "INSERT INTO Users VALUE {'id':'?','firstName':'?'}",
    "Parameters": [{ "S": "123" }, { "S": "John" }]
  }
]
[
  {
    "Statement": "EXISTS(SELECT * FROM users WHERE lastName='Doe' AND deletedAt IS MISSING)"
  },
  {
    "Statement": "INSERT INTO Users VALUE {'id':'?','firstName':'?'}",
    "Parameters": [{ "S": "123" }, { "S": "John" }]
  }
]

Save this JSON as a file and reference it using the command:

aws dynamodb execute-transaction --transact-statements file://my-partiql-transaction.json
aws dynamodb execute-transaction --transact-statements file://my-partiql-transaction.json

IAM

PartiQL DynamoDB update adds four new IAM permissions:

• dynamodb:PartiQLSelect - needed to perform read operations on the table
• dynamodb:PartiQLInsert - needed to perform put operations on the table
• dynamodb:PartiQLUpdate - needed to perform update operations on the table
• dynamodb:PartiQLDelete - needed to perform delete operations on the table

If you want to allow all operations on your DynamoDB table, attach the following policy to your role:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["dynamodb:PartiQL*"],
      "Resource": ["arn:aws:dynamodb:us-east-1:123456789012:table/MyTable"]
    }
  ]
}
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["dynamodb:PartiQL*"],
      "Resource": ["arn:aws:dynamodb:us-east-1:123456789012:table/MyTable"]
    }
  ]
}

Deny Full Table Scans

As mentioned previously, SELECT statements in PartiQL can convert into unwanted Scans. Luckily, IAM allows us to prohibit that behavior. You can create a Deny statement with a condition dynamodb:FullTableScan = true and include it in your policy:

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

Caveats

• Not suited for analytics, e.g., SELECT COUNT or complex operations. If you need it, use Athena or write your own aggregation functions based on DynamoDB Streams.
• Amazon DynamoDB supports only a subset of the PartiQL language.
• You cannot use it for UPDATE X WHERE Y logic. If you need to conditionally update multiple items at once, head to our guide on how to conditionally update collections in DynamoDB.
• Is DynamoDB with SQL support still a NoSQL database? 🙃
• If you want to use a true relational and SQL database, consider Aurora and read our comparison here.

Best Practices

When using PartiQL with DynamoDB, it's essential to follow best practices to ensure optimal performance and cost-efficiency. Always use indexed fields in your WHERE clauses to avoid full table scans. Regularly monitor your DynamoDB usage and set up alarms for unexpected spikes in read/write capacity. Additionally, consider using DynamoDB Streams to trigger real-time processing of data changes, which can be particularly useful for maintaining derived data or triggering downstream processes.