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 huge because of few reasons:

• It makes the learning curve a little less steep - You can use well-known language to start using DynamoDB and transition to the native DSL later on.
• It makes migrations a bit easier - no need to learn and implement DynamoDB query language from the very beginning.
• Choice - it gives you two options/languages to interact with DynamoDB. You don't like ExpressionAttributeValues and FilterExpressions? Fine, use PartiQL.

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 paradoxically a NoSQL database, and it is not changing the core principles of how it works. Data is still shared across partitions, GSIs and LSIs are still needed, scans and queries are not going anywhere. Your SELECT statements are going to be 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 will execute something like SELECT * from my-ddb-table;, it will be translated to a Scan.

While PartiQL might make DynamoDB more approachable, it is also more likely that you'll make a silly mistake like scanning a whole table. In PartiQL, a Scan and Query are both disguised as a SELECT statement while in DynamoDB's native language you have to explicilty state to use Scan(...). However, there's a way to guard yourself from 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

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
SET metadata={'lastLoggedAt':1234567890}
WHERE email='john@doe.com' AND id='123';
UPDATE users
SET isVerified=TRUE
SET metadata={'lastLoggedAt':1234567890}
WHERE email='john@doe.com' AND id='123';

Deleting data

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 big bulks is not possible. You can only delete one record in one 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 nice that it supports conditions, unlike regular DynamoDB's API.

Transactions

Just like with traditional DynamoDB query syntax, making transactions using PartiQL is possible. Keep in mind that inside one transactions block, you can only contain either read or write type. 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 using 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 I have mentioned previously, SELECT statements in PartiQL can convert into unwanted Scans. Luckily, IAM allows us from prohibiting 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