DynamoDB showdown: vanilla API vs PartiQL
Tags: aws, serverless, dynamodb, cdk
Published: 2022-02-06 04:25:57

---
title: DynamoDB showdown: vanilla API vs PartiQL
published: true
description: What are the differences between the "vanilla" API and PartiQL
tags: aws, serverless, dynamodb, cdk
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u0kcmumfphs2pt5ey8nx.jpeg
---
[Amazon DynamoDB](https://aws.amazon.com/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](https://twitter.com/awscloud/status/1322257181467619331) and reliable.

In 2020 AWS launched [PartiQL support for Amazon DynamoDB](https://aws.amazon.com/about-aws/whats-new/2020/11/you-now-can-use-a-sql-compatible-query-language-to-query-insert-update-and-delete-table-data-in-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](https://aws.amazon.com/blogs/opensource/announcing-partiql-one-query-language-for-all-your-data/).

```js
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.

```js
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_](https://aws.amazon.com/dynamodb/pricing/provisioned/)) 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_**.

```shell
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](https://www.dynamodbguide.com/scans) by [Alex DebRie](https://twitter.com/alexbdebrie).

It is not all lost, though. One might prevent such situations by utilizing [AWS IAM](https://aws.amazon.com/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](https://aws.amazon.com/amplify/). You can read more about it [here](https://dev.to/aws-builders/prevent-costly-amazon-dynamodb-operations-in-aws-amplify-cli-projects-45k).

## 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](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/WorkingWithItems.html#WorkingWithItems.BatchOperations) is one of the most efficient ways to perform multiple, **non-transactional**, write operations in the context of DynamoDB.

```js
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.

```js
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](https://twitter.com/heitor_lessa/status/1458124109112545282) and the subsequent [answer](https://twitter.com/NoSQLKnowHow/status/1458139547527647239).

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](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.insert.html)

> 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](https://github.com/aws-samples/aws-dynamodb-examples/blob/master/DynamoDBIAMPolicies/AmazonDynamoDBAppendOnlyAccess.json).

#### 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?

```js
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](https://twitter.com/wm_matuszewski).

Thank you for your valuable time.