How to use Auto Increment for Primary Key Id in Dynamodb

AWS provides the NoSQL database DynamoDB, which is meant for high performance and scalability, one common pattern in designing databases leverages an auto-incremented primary key that uniquely identifies each record. While most traditional relational databases like MySQL and PostgreSQL support auto-incrementing primary keys out of the box, DynamoDB does not, since it is a distributed database. There are ways around this limitation to simulate an auto-increment in DynamoDB.

The following article will walk through various ways of implementing an auto-increment primary key in DynamoDB and give best practices around how to avoid common pitfalls. Examples with step-by-step explanations and diagrams are included for further clarity.

Primary Terminologies

• DynamoDB: This is a fully managed NoSQL database that allows for rapid and predictable performance with seamless scalability.
• Primary Key: The DynamoDB primary key serves as the unique identifier for each record in the table. It can either be a simple partition key or a composite key consisting of both the partition key and sort key.
• Auto-Increment: This feature in conventional databases auto-increments a field, usually the primary key, when a new record is added.
• Atomic Counter: An atomic counter signifies a count as one single, uninterruptible operation, maintaining consistency across distributed systems.
• Partition Key: The attribute of the primary key that DynamoDB uses to partition data across multiple partitions.
• Expression Attribute Values: Used in operations like update-item, expression attribute values define the actual value for placeholders in expressions.
• ReturnValues: An option in DynamoDB commands that determines whether the updated item values should be returned. For example, in an update-item command, you would use --return-values "UPDATED_NEW" to get the updated counter value.

Step-by-Step Process to Auto-Increment Primary Key in DynamoDB

Step 1: Create a Counter Table

First, create a DynamoDB table to store the current counter value. This table will be responsible for managing the sequence number.

Run the following command to create the CounterTable:

aws dynamodb create-table \

--table-name CounterTable \

--attribute-definitions AttributeName=counter_id,AttributeType=S \

--key-schema AttributeName=counter_id,KeyType=HASH \

--provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5

• counter_id: Partition key (string), which will hold the identifier for the counter.
• counter_value: This will store the current value of the counter.

Step 2: Initialize the Counter

• Next, insert the first item into the CounterTable with an initial counter value. This will serve as the starting point of your auto-increment logic.

aws dynamodb put-item \

--table-name CounterTable \

--item '{"counter_id": {"S": "global_counter"}, "counter_value": {"N": "0"}}'

• This will create an item in CounterTable with a counter_id set to "global_counter" and an initial counter_value of 0.

Step 3: Create a Main Table for Storing Items

Create another table where you will store the actual data, using the auto-incremented ID as the primary key.

aws dynamodb create-table \

--table-name MainTable \

--attribute-definitions AttributeName=id,AttributeType=N \

--key-schema AttributeName=id,KeyType=HASH \

--provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5

• id: This will be the primary key, and you will auto-increment this value by using the counter in the CounterTable.

Step 4: Increment the Counter in CounterTable

• To generate a new auto-incremented ID, you need to update the counter in CounterTable before inserting a new item into MainTable.

Use the update-item command to increment the counter value:

aws dynamodb update-item \

--table-name CounterTable \

--key '{"counter_id": {"S": "global_counter"}}' \

--update-expression "SET counter_value = counter_value + :incr" \

--expression-attribute-values '{":incr": {"N": "1"}}' \

--return-values "UPDATED_NEW"

• This command will increment the counter_value by 1 and return the updated value.

• We can check in DynamoDB console as well

We can increase again number by using the update-item command

aws dynamodb update-item \

--table-name CounterTable \

--key '{"counter_id": {"S": "global_counter"}}' \

--update-expression "SET counter_value = counter_value + :incr" \

--expression-attribute-values '{":incr": {"N": "1"}}' \

--return-values "UPDATED_NEW"

• This command will increment the counter_value by 2 and return the updated value.

Here we can value was updated

Step 5: Insert a New Item in MainTable with the Auto-Incremented ID

Now that you have the auto-incremented ID, insert a new item into the MainTable with that ID.

This will insert a new item into MainTable with the following structure:

• id = 1 (auto-incremented)
• name = "Item 1"
• description = "First item description"

Now we can verify in console

By following these steps we can setup and use auto increment for primary key id in dynamodb

Step 6: Repeat Steps 4 to 5 for Subsequent Inserts

For each new item, follow these steps:

• Increment the counter in CounterTable (Step 4).
• Extract the updated counter_value.
• Insert the new item into MainTable using the updated counter value as the primary key.

Conclusion

DynamoDB will implement auto-increment since it does not support auto-incrementing keys like traditional relational databases. Using a separate counter table to manage and increment a numeric value allows us to simulate this behavior, this approach makes it possible to assign a unique, sequential ID to each new item in your primary table.

The CounterTable maintains a continuously incrementing value, which is used as the ID for creating new entries in the main table. This solution is scalable, customizable, and suitable for various use cases that require unique identifiers in DynamoDB tables