Amazon DynamoDB is a popular choice amongst developers needing a NoSQL database solution on AWS. Especially popular in AWS serverless community, the service has proven to be ridiculously scalable and reliable.

In 2020 AWS launched PartiQL support for Amazon DynamoDB as another way to interact with the service. The announcement was fascinating as introducing PartiQL support created a divergence in functionality compared to the "vanilla" DynamoDB API.

This blog post will explore how the PartiQL syntax differs from the "vanilla" DynamoDB API and how some operations are only possible using the newly introduced API.

Let us dive in.

Anatomy of DynamoDB PartiQL statement

An example DynamoDB PartiQL statement oddly resembles an SQL statement. In fact, SQL compatibility is one of PartiQL design tenents.

import {
  DynamoDBClient,
  ExecuteStatementCommand
} from "@aws-sdk/client-dynamodb";

const client = new DynamoDBClient({});

client.send(
  new ExecuteStatementCommand({
    Statement: 'SELECT * FROM "MyDataTable" WHERE OrderID = 123'
  })
);

Compared to the "vanilla" DynamoDB API, you might, depending on how used you are to the syntax, think that the PartiQL for DynamoDB is the best thing ever.

import { DynamoDBClient, GetItemCommand } from "@aws-sdk/client-dynamodb";

const client = new DynamoDBClient({});

client.send(
  new GetItemCommand({
    TableName: "MyDataTable",
    Key: {
      OrderId: {
        N: 123
      }
    }
  })
);

The SQL-like PartiQL syntax for DynamoDB might be tempting, especially for developers already familiar with SQL language. While utilizing PartiQL for interacting with Amazon DynamoDB is, in my opinion, completely fine, one has to be aware of the pitfalls that are associated with it

With great power comes great responsibility

As good as the PartiQL syntax looks and feels, if you are not careful, you might unknowingly quickly run out of allocated RCUs (if you are using Provisioned Capacity) or be faced with a higher than usual AWS bill at the end of the month.

The problem with PartiQL is the following: the SQL-like syntax makes it relatively hard to know what kind of "vanilla" DynamoDB API operation your statement translates to. For example, the following PartiQL statement would roughly translate to GetItem DynamoDB API call if the OrderID attribute is your table primary key.

SELECT * FROM "MyDataTable" WHERE OrderID = 123

If not, DynamoDB would perform a Scan operation to retrieve the data you asked for. Yikes!

If you are unfamiliar with what Scan operation is and why, in most cases, it is not the best idea to use it, refer to this great article by Alex DebRie.

It is not all lost, though. One might prevent such situations by utilizing AWS IAM and denying the dynamodb:Scan operations in the context of identity you currently operate in.

I've touched on a similar topic in the context of AWS Amplify. You can read more about it here.

Possible only with PartiQL

As I eluded at the beginning of this blog post, some operations (or parameters) are only available in the world of DynamoDB PartiQL statements.

Conditions support for `batchWrite`-like operations

To my best knowledge, the batchWrite API is one of the most efficient ways to perform multiple, non-transactional, write operations in the context of DynamoDB.

const client = new DynamoDBClient({});

const result = await client.send(
  new BatchWriteItemCommand({
    RequestItems: {
      MyDataTable: [
        {
          PutRequest: {
            Item: {
              OrderID: {
                N: "200"
              }
            }
          }
        },
        {
          DeleteRequest: {
            Key: {
              OrderID: {
                N: "123"
              }
            }
          }
        }
      ]
    }
  })
);

If I want to delete the OrderID conditionally, let us say only when the Status is fulfilled I'm not able to do so. The DeleteRequest object does not contain the ConditionExpression parameter.

Switching to PartiQL, I can add such condition to the SQL-like statement.

const client = new DynamoDBClient({});

const result = await client.send(
  new BatchExecuteStatementCommand({
    Statements: [
      {
        Statement:
          "INSERT INTO \"MyDataTable\" value {'OrderID': 200, 'Status': 'pending'}"
      },
      {
        Statement:
          'DELETE FROM "MyDataTable" WHERE "OrderID" = 123 AND "Status" = \'fulfilled\''
      }
    ]
  })
);

Word of caution - before you refactor all your batchWrite calls into DynamoDB PartiQL statements, know that the WHERE clause must include equality checks on all key attributes (primary/sort key). If it does not, the Responses property on the result object will contain the following error message: "Where clause does not contain a mandatory equality on all key attributes."

WORM data models

This section is inspired by the following Tweet and the subsequent answer.

The nature of DynamoDB PartiQL INSERT statement allows for creating Write Once Read Many data models on top of the DynamoDB.

As per INSERT statement documentation

If the DynamoDB table already has an item with the same primary key as the primary key of the item being inserted, DuplicateItemException is returned.

To achieve an actual WORM data access model, you would also employ some help from the AWS IAM service – denying the update and delete operations (both "vanilla" and PartiQL). Here is an example IAM policy that you might leverage to do so.

What about a condition and a `putItem` API?

Suppose you are familiar with how the "vanilla" putItem API operates. In that case, you might wonder how come the WORM data access model implementation is only possible with PartiQL statements – we could utilize ConditionExpression and the putItem API, right?

const client = new DynamoDBClient({});

const result = await client.send(
  new PutItemCommand({
    TableName: "MyDataTable",
    Item: {
      OrderID: {
        N: "123"
      }
    },
    ConditionExpression: "attribute_not_exists(#OrderID)",
    ExpressionAttributeNames: {
      "#OrderID": "OrderID"
    }
  })
);

The problem with this approach is that nothing stops us from removing the condition. To my best knowledge it is impossible to ensure the ConditionExpression is there and performs the proper check (apart from code review, maybe).

For the reason mentioned above, the putItem API is not the best suited for creating such data models.

Closing words

Did you find any other significant difference between "vanilla" DynamoDB API and the PartiQL syntax? I'm curious to know!
For more AWS serverless content, consider following me on Twitter - @wm_matuszewski.

Thank you for your valuable time.

DynamoDB showdown: vanilla API vs PartiQL