Amazon DynamoDB: pointer strategy
Tags: serverless, dynamodb
Published: 2021-03-20 12:36:06

The transition from relational databases to the world of NoSQL is not easy. Literally, everything is different. Sometimes this can be so overwhelming that you don't know how to solve a problem so trivial that virtually nonexistent in the world of SQL databases. 

Today, I want to tell you about such a problem. 

Imagine that in your database you store `Order` records in the table. And you need information about the last (newest) order number. (Why? It's not relevant now.)

### Last Order number in SQL
When using SQL this problem is trivial. All you need to do is write:
```SQL
SELECT orderId FROM Orders ORDER BY orderId DESC LIMIT 1;
```
The query is simple and very efficient (especially when we have indexes). Well done SQL 👏

### Last Order number in DynamoDB

And this is where it starts to be problematic. I won't give you a solution right away because to fully understand it some knowledge about DynamoDB is required.

## A bit of theory about DynamoDB
Let's start by saying that the equivalent of SQL's `SELECT` in DynamoDB are two commands `scan` and `query`. Both are used to retrieve information from a table (in DynamoDB we only have the concept of a table, not the entire database). However, they are significantly different from each other. The `scan` method scans the contents of the **whole** table and returns it to us as a collection of elements.

For the following table `scan` returns `6` items because there are six orders in the whole table.
![Table 1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s0lcm3y3611kpu77az1t.png)

When using `scan` we could retrieve information about all orders and process them in the *code*, e.g. in the Lambda function, filter the data and return the last order number.

This approach is obviously the ***worst possible***, as we are retrieving much more data from the database than we want (collection of Orders vs single Order number). This will affect the running time of our application, but also the cost because **in DynamoDB we pay for each query / amount of data returned**. To make matters worse, when dealing with large amounts of data, we have to page through the results.

So let us consider other options.

The `query` method is used to retrieve data from a *local collection*, that is, elements in the database that share a common (same value) *Partition Key*.

> I'm trying to make this article concise and simple, so I'm not going to discuss how Amazon DynamoDB is internally designed. However, I strongly encourage you to learn it on your own, because it is simply a good architecture school and it will help you understand why this database is used the way it is.

In our order table (above), each order has a different *Partition Key* value, so using the `query` method simply doesn't make sense here since we don't have local collections. 

On a side note, a local collection for an Order could be a list of goods in the single order. In that case, each item's *Primary Key* would consist of two values (a composite key):
1. Partition Key
1. Sort Key

The table would change to the state presented below. Then calling `query` with the parameter `order#2` would return us two items because in this order someone bought two products: DataLake Training and Consulting.

![Table 2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uv7wqqodnk8errepfqb3.png)

However, this is a different data access pattern in DynamoDB, and I've presented it to you as a side note.

Let's move back to the main problem.

What can we do since neither `scan` nor `query` are suitable to return the latest Order number?

### The GetItem method
There is also `GetItem`, a method that returns us a single item from the database when given a specific *Primary Key* value. It's kind of like `getById(id)` or in SQL:
```SQL
SELECT * FROM Orders WHERE orderId = 'orderNumber';
```
That's cool, but how could we get this `orderNumber` if this is what we want to pull from the database in the first place? 🤔

## Pointer strategy
This is where the pointer strategy comes in. 

We can replace an unknown value with something known, some constant, and refer to the database through that constant. This element with the constant *Partition Key* will be **only one in the entire table**. Thus we can use it as a pointer to store the value of the most recent order.

To our first table, we add another element with a *Partition Key* of **always** equal to `LAST_ORDER` (our *constant* string). Such an element has one attribute named `OrderId` with the value of the most recent order. Each time we add a new Order to the table, we also update the value of the `LAST_ORDER` element to the value of the new `orderId`.

![Table 3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lvkwuwvqc9c2rq7tz2n1.png)

Now, all we need to do is call a simple `getItem('LAST_ORDER')` method that returns the last order number for us in an **optimal** way.

### Advanced techniques
Taking into account that the DynamoDB table is usually part of a larger system, let's consider what happens when multiple processes write to the database in parallel (directly or through the SQS queue - doesn't matter). Certainly, at some point it will happen that the last item written to the database, will not be the last order. Then the `LAST_ORDER` pointer will wrongly point to an older Order.

We could solve this problem by using the *SQS FIFO* queue, but there is a much simpler and cheaper solution for this.

We just need to apply a `ConditionExpression` when saving a new version of the `LAST_ORDER` element, which will check if the new `orderId` value is greater than the one currently stored in the database. If it is it will update it, if not, it will not.

Thanks to this, using one write to the database, we can update the value without downloading and checking it *on the code side*. In addition, this method is ***idempotent*** (in case we get the same event multiple times, it will not change the database state on subsequent calls).

#### How to do it?
It's time for some code. My sample implementation in JavaScript:
```JavaScript
  async createPointer(orderId) {
    const pointer = new Pointer({ orderId })
    const params = {
      Item: pointer.toItem(),
      ReturnConsumedCapacity: 'TOTAL',
      TableName: process.env.ordersTableName,
      ConditionExpression: 'attribute_not_exists(#orderId) OR #orderId < :newId',
      ExpressionAttributeNames: {
        '#orderId': 'orderId'
      },
      ExpressionAttributeValues: {
        ':newId': { N: `${orderId}` }
      }
    }
    log('createPointer params', params)
    try {
      await this.dynamoDbAdapter.create(params)
    } catch (error) {
      if (error.code === 'ConditionalCheckFailedException') {
        log(`LAST_ORDER pointer already exists and is greater than ${orderId}. Skipping update.`)
      } else {
        log('Error', error)
        throw error
      }
    }
    return pointer
  }
```

Code listing explanation:
* **Line 4** - the Pointer class implements a method that converts a `Pointer` object into the JSON that the DynamoDB API expects. Below is the implementation of this method.
* **Line 7** - conditional expression: when to write to the database and when not to? The `attribute_not_exists(#orderId)` is needed so that when the database is empty, the code also executes and writes the item for the first time. From this point on, only the second part  `#orderId < :newId` of the condition will have meaning.
* **Line 19** - if the condition is not met, the DynamoDB API returns a `ConditionalCheckFailedException`, which in our case is expected sooner or later.

And here is a `Pointer` class implementation.
```JavaScript
class Pointer {
  constructor({ orderId, createdAt = new Date() }) {
    this.orderId = parseInt(orderId)
    this.createdAt = createdAt instanceof Date ? createdAt : new Date(createdAt)
  }

  key() {
    return {
      PK: { S: 'LAST_ORDER' }
    }
  }

  static fromItem(item) {
    return new Pointer({
      orderId: item.orderId.N,
      createdAt: item.createdAt.S
    })
  }

  toItem() {
    return {
      ...this.key(),
      orderId: { N: this.orderId.toString() },
      createdAt: { S: this.createdAt.toISOString() },
    }
  }
}
```
Please note, the style of this class is *ripped off* from Alex DeBrie 😃 

He is an author of *The DynamoDB Book* which is the best thing to teach yourself how to use this database. Since I read that book I stared using many advanced techniques that he described. I highly recommend his book. 

## Summary
I hope this simplified, but taken from my real system, example helped you better understand how to use DynamoDB. I've only covered a small area of knowledge, but at the same time I've shown you how to solve a specific problem you may encounter in your projects.

I'm aware that DynamoDB has a steep learning curve - there's no denying that - but honestly, **I can't imagine serverless systems without Amazon DynamoDB database**. In 9 out of 10 cases, I would choose DynamoDB over AWS RDS (including Aurora Serverless).