Unique indexes in DynamoDB

DynamoDB doesn't have a mechanism to ensure value uniqueness for several columns, Let's me tell you how to handle it by ourselfs

Ihar Kryvanos

~6 min read · June 1, 2022 (Updated: May 6, 2024) · Free: No

About the the article

This article describes you how to guarantee uniqueness of some column or columns combination in a particular table.

Auditory

This article is for backend developers middle+ level. For juniors I recommend first to read articles from required knowledges

Required knowledges:

DynamoDB write transactions

Context

Let's imagine that we implementing application with authorization where users could login using: - email and password - phone and password - oauth

To handle all kind of authorization we would need to guarantee uniqueness of following user props: - id , unique user identifier - email , user's email - phone , user phone - combination of oauth_provider and external_user_id

Ok, let's start from the simplest one

Let's guarantee id uniqueness.

DynamoDB by itself guarantee only uniqueness of the primary key. Let's use it for our purposes. We just need to add check on creation that there is no other user with the same id, to not overwrite old user:

const { DynamoDB } = require('aws-sdk');
const { v4 } = require('uuid');
const createUser = async (
  email, 
  phone, 
  oauthProvider, 
  externalUserId
) => {
  const client = new DynamoDB.DocumentClient();
  
  await client
    .put({
      TableName: 'users',
      // check is no such id in table
      ConditionExpression: 'attribute_not_exists(id)',
      Item: {
        id: v4(),
        email: email,
        phone: phone,
        oauth_provider: oauthProvider,
        external_user_id: externalUserId,
      }
     })
     .promise();
};

Actually, this code will save record, if there is no record with the same id in the table or throw ConditionalCheckFailedException

Why we not use the same approach for other props?

Answer is pretty simple, you can't specify such condition expression to handle both cases. For example: - attribute_not_exists(id) and attribute_not_exists(email) , will throw exception only if both some other record has absolutely the same id and email - attribute_not_exists(id) or attribute_not_exists(email) , will allows to save record if at least one of id or email is unique

Also you could try really complicated conditions, with several or and and , and you could event success, but it would be impossible to handle uniqueness of properties combination

Robust and scalable solution

To implement really robust solution we would need to rebuild our table. We need to add following properties: - pk , it should be hash key - record_type , type of the record it allows us to deferential records with valuable payload and records to handle unique indexes

The idea is pretty simple for each user we would need to save several records. One to save user valuable payload and one to handle single unique index. We will use transactions to guarantee table consistency, in simple words that payload and uniqueness index won't be partially saved.

const { DynamoDB } = require('aws-sdk');
const { v4 } = require('uuid');
const createUser = async (
  email, 
  phone, 
  oauthProvider, 
  externalUserId
) => {
  const client = new DynamoDB.DocumentClient();
  
  const id = v4(); 
  await client
    .transactWrite({
      TransactItems: [{
        Put: {
          TableName: 'users',
          // check is no such id in table
          ConditionExpression: 'attribute_not_exists(pk)',
          Item: {
            pk: id,
            type: 'record',
            email: email,
            phone: phone,
            oauth_provider: oauthProvider,
            external_user_id: externalUserId,
          },
        },
      }, {
        Put: {
          TableName: 'users',
          // check is no such email in table
          ConditionExpression: 'attribute_not_exists(pk)',
          Item: {
            pk: `index#${email}`,
            type: 'unique-index',
          },  
        },
      }, {
        Put: {
          TableName: 'users',
          // check is no such phone in table
          ConditionExpression: 'attribute_not_exists(pk)',
          Item: {
            pk: `index#${phone}`,
            type: 'unique-index',
          },  
        },
      }, {
        Put: {
          TableName: 'users',
          // check is no such combination of oauth_provider and 
          // external_user_id in table
          ConditionExpression: 'attribute_not_exists(pk)',
          Item: {
            pk: `index#${oauthProvider}#${externalUserId}`,
            type: 'unique-index',
          },  
        },
      }],
    })
    .promise();
};

This solution is scale up to 25 unique indexes, because of DynamoDB write transaction limitation.

We handle user creation, now we need to be creative and find a way how to handle user updates. To do this we would need to be able to change payload record and unique-index record. Moreover, during this operation we need to be sure that payload record won't change. To understand why let me give an example: 1. request A needs to update email (from some@email.com to another@email.com) 2. request A finds the record and get old email value 3. request A prepare a query to remove old email unique-index and create new one 4. request B updates user's email to third-one@email.com and saves changes in DB 5. request C creates user with some@email.com and saves it to DB 6. request A execute it's query and removes unique-index of user created by request C and creates new one for user from request A

As a result we got a situation when user from request C has not unique email. To avoid this situation we need somehow be sure that user from request A won't be changed since we take it to saving changes to DB.

There are a lot of options how to do this, let's use the most reliable one - transaction pattern. To get more details you could read this article.

Here the example of pessimistic blocking usage. Here the client example for our specific case

const { DynamoDB } = require('aws-sdk');
const { v4 } = require('uuid');
class DynamoDBTransactionalClient {
  constructor() {
    this._client = new DynamoDB.DocumentClient();
  }

  async findAndLockByPk(table, pk) {
    // checking is record exists, to not spend time for locking
    const findRes = await this._client
      .get({
        TableName: table,
        Key: { pk: pk },
      })
      .promise();

    const isExist = Boolean(findRes.Item);
    if (!isExist) {
      return null;
    }

    // locking record, we need retries to seamlessly wait, 
    // in case record locked by another transaction
    for (let i = 0; i < 10; i++) {
      const res = await this._client
        .update({
          TableName: table,
          Key: { id: id },
          // check is record isn't locked
          ConditionExpression: 'lockedTill < :now',
          // locks record and set timeout
          UpdateExpression: 
             'SET lockedBy = :trId, lockedTill = :lockedTill',
          ExpressionAttributeValues: {
            ':now': Date.now(),
            ':trId': v4(),
            // set 5 minute transaction timeout
            ':lockedTill': Date.now() + 5 * 60 * 1000,
          },
          ReturnValues: 'ALL_NEW',
        })
        .promise()
        .catch((ex) => {
          if (ex.code === 'ConditionalCheckFailedException') {
            // case, when record is already locked
            return null;
          }

          throw ex;
        });

      if (!res || !res.Attributes) {
        // wait for a wait till record would be unlocked
        await new Promise((resolve) => setTimeout(resolve, 500));
        continue;
      }

      return res.Attributes;
    }

    // throwing exception, if record was locked for too long
    throw new Error('Record locked for too long');
  }

  async saveAndUnlock(table, record) {
    const transactionId = record.lockedBy;
    // removing lock
    record.lockedBy = null;
    record.lockedTill = 0;

    await this._client
      .put({
        TableName: table,
        Item: record,
        ConditionExpression: 'lockedBy = :trId',
        ExpressionAttributeValues: {
          ':trId': transactionId,
        },
      })
      .promise()
      .catch((ex) => {
        if (ex.code === 'ConditionalCheckFailedException') {
          // case, record locked by another transaction
          throw new Error('Record already locked by other transaction');
        }

        throw ex;
      });
  }
}

And here code to perform user update

// to reduce amount of code here the example of email update,
// but you could easily extend it to handle all other properties
const userEmailUpdate = (userId, newEmail) => {
  const trClient = new DynamoDBTransactionalClient();
  const user = await trClient.findAndLockByPk('users', userId);
  const oldEmail = user.oldEmail;
  const client = new DynamoDB.DocumentClient();
  await client.transactWrite({
    TransactItems: [
      {
        Put: {
          TableName: 'users',
          // creating unique index record for new email. 
          // To be sure that nobody will take this.
          ConditionExpression: 'attribute_not_exists(pk)',
          Item: {
            pk: `index#${newEmail}`,
            type: 'unique-index',
          },
        },
      },
      {
        Delete: {
          TableName: 'users',
          // checking is unique index for old email still exists
          ConditionExpression: 'attribute_exists(pk)',
          Key: {
            pk: `index#${oldEmail}`,
          },
        },
      },
    ],
  }).promise();
  // currently unique index records already updated,
  // and we pretty sure that no any other request takes new email.
  // Also we are sure that no any other request update current user.    
  // We could update email and unlock it in DB
  user.email = newEmail;
  await trClient.saveAndUnlock('users', user);
};

Conclusion

DynamoDB has no build in unique indexes, but if you understand how they works you able to implement them by yourself. Be creative and study from others how you could use ordinary features in unordinary cases.

#dynamodb #nodejs #aws #dbs