Search for a command to run...
Understanding DynamoDB single table design, one-to-many and many-to-many relationships.
An architect by trade, practicing architecture in the Cloud. 7x AWS Certified, AWS Community Builder, Serverless advocate, Vue enthusiast.
I wrote a npm package, dyna-record, which abstracts these design concepts. I’m interested to know what you think of it. I’m apparently not allowed to share links here but I wrote a blog title Unlock Relational Data Modeling in DynamoDB with dyna-record on medium
Great to learn something! I'm new to DynamoDB and still confused why you prepended project# and monitor# to some GSIs, this would increase the redundancy and also unintentionally increase storage space.
Also, while querying, why do you query both, the primary key and the GSI, even though the schema says they contain the same data.
I might be totally getting the concept of GSIs wrong. Pardon me for that.
Thanks for the awesome and in-depth article 🎉
Hey Mohit Yadav, good question.
It's called index overloading. The table can hold many different kinds of data items at one time, hence we use generic names for primary key (PK), sort key (SK), and global secondary index (GSI).
Prefixing allows to identify the entity to be queried, e.g. user-related items will always have PK starting with user#, so on.
The GSIs help with certain access patterns, let's say we have items like: PK:project#1234|SK:project#1234|GSI:org#1234|attrs PK:project#4321|SK:project#4321|GSI:org#1234|attrs
We can get the project details using GetItem, but to Query all projects of an org we'll have to use the overloaded GSI like GSI=org#1234 AND begins_with(SK, project#).
Thanks for the in-depth answer, Stefan!
Honestly speaking, I understand most of this, but some concepts are still a bit rusty... Explaining them may be out of the scope of this article, so I'll definitely learn them when I get into NoSQL DBs. 😃
Hey :)
Good article explaining the how, but I am left curious about the why :/
Why would you use a dynamoDB for relational data in lieu of just a relational database?
Hey Pablo,
I picked DynamoDB because of the flexible schema and scalability, and it's also very Serverless-friendly. As a plus, the on-demand pricing worked better for my use case.
When a relational database makes more sense, use that by all means.
Ok, thanks for answering :)
A clean serverless pattern for streaming real-time AI workflow responses with Server-Sent Events and AWS Lambda
Emails not reaching the inbox due to template Rendering Failure
A secure, efficient, and hassle-free way to run data analytics queries
How to run your AWS Lambda functions with a fixed IP up to 10x cheaper.
Using the API Gateway - SQS integration to create scalable webhook listeners and validate the requests at the API Gateway level
I'm building monithor.co - an open-source web monitoring service built with Serverless on top of AWS. It's using DynamoDB as the database, a highly performant NoSQL database that's been increasingly popular for serverless applications over the past years.
After more than a decade of dealing mostly with relational databases, using DynamoDB requires looking at things from a different angle, unlike traditional SQL databases, it does not use table joins and other relational database constructs. However, we can model many common relational designs in a single DynamoDB table but the process is different.
Before getting deeper into things let's summarize the DynamoDB table basic concepts:
There are three ways of reading data from DynamoDB:
You shouldn't start designing the DynamoDB schema until you know the questions it will need to answer. Understanding the business problems and the application use cases upfront is essential. Write down all the access patterns, here's what the access pattern list looks like for Monithor.
Our table can grow quite big so we want to avoid Scan at all costs so we'll create our data model so we can extract what we need using Query and GetItem.
You should maintain as few tables as possible in a DynamoDB application, we'll go with a single table design.
Yes, having all the data in a single table might look confusing and hard to read, but it offers a rich set of flexible and efficient design patterns, better utilization of resources, and easier configuration, capacity planning, and monitoring.
When using a single table, use generic attributes for the primary key like PK (partition key), SK (sort key), GS1PK (first global secondary index partition key), GS1SK (first global secondary index sort key).
Also, identify the type of the item by prefixing keys with type. e.g. project#1234 (project = type, 1234 = id)
The DynamoDB single-table design requires a different mindset than relational database table design, I recommend using NoSQL workbench for DynamoDB for data modeling and visualization. https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/workbench.html
Here's the CloudFormation template for our DynamoDB single table.
Resources:
# DynamoDB
ddbTable:
Type: AWS::DynamoDB::Table
# enable table retention on production use
# DeletionPolicy: Retain
Properties:
TableName: ${self:custom.resourceName}
AttributeDefinitions:
- AttributeName: PK
AttributeType: S
- AttributeName: SK
AttributeType: S
- AttributeName: created_at
AttributeType: N
- AttributeName: nextrun_at
AttributeType: N
KeySchema:
- AttributeName: PK
KeyType: HASH
- AttributeName: SK
KeyType: RANGE
LocalSecondaryIndexes:
- IndexName: pk_created
KeySchema:
- AttributeName: PK
KeyType: HASH
- AttributeName: created_at
KeyType: RANGE
Projection:
ProjectionType: ALL
- IndexName: pk_run
KeySchema:
- AttributeName: PK
KeyType: HASH
- AttributeName: nextrun_at
KeyType: RANGE
Projection:
ProjectionType: ALL
GlobalSecondaryIndexes:
- IndexName: GSI
KeySchema:
- AttributeName: GSI
KeyType: HASH
- AttributeName: SK
KeyType: RANGE
Projection:
ProjectionType: ALL
# use the On Demand pricing
BillingMode: PAY_PER_REQUEST
# autodelete logs after specified time
TimeToLiveSpecification:
AttributeName: expires_at
Enabled: TRUE
Modeling relational data in DynamoDB requires reusing or overloading keys and secondary indexes for different purposes. Overly-simplified, the data model for Monithor looks as in the diagram below.
Let's take aside the relationship between projects and monitors, a project can have multiple monitors. In the UI we'll have to display all the monitors of a project, so we'll have to create an API endpoint that will query all monitors by project ID.
We rely on the GSI global secondary index, for each monitor entry we'll populate the GSI with the project ID prefixed with the entity type project#.
Then, when we query on the GSI index the key conditions will be GSI=project#1234 AND SK begins_with(monitor#) which will return the information we're looking for.
The query code would look like this:
const monitors = await ddb
.query({
TableName: process.env.DDB_TABLE,
IndexName: "GSI",
KeyConditionExpression: "#GSI = :gsi AND begins_with(#SK, :prefix)",
ExpressionAttributeNames: {
"#GSI": "GSI",
"#SK": "SK"
},
ExpressionAttributeValues: AWS.DynamoDB.Converter.marshall({
":gsi": "project#1234",
":prefix": "monitor#"
}),
});
And here's yet another example of a one-to-many relationship. We need to show a list of all projects within the organization dashboard UI. In this case, we'll fill the organization ID in the GSI of the project item.
Querying the GSI with the key conditions GSI=org#1234 AND SK begins_with(project#) will return all the projects of that organization.
The many-to-many relationships may seem a bit difficult at first, in a relational database we sort it out with a relationship table and two JOIN statements, but in DynamoDB this is not possible.
On Monithor, the core many-to-many relationship is between projects & users. We want to have granular access control for every project, so a user can have different access levels on multiple projects. A project can have multiple users.
We rely on the table sort key to store the association between entities, we store a concatenated value of user ID & project ID plus making use of GSI.
In the app UI, once the user logs in we'll want to display a list of the projects they have access to. We know the user ID from the login session so we'll have the query the table index with the following key conditions PK=user#1234 AND SK begins_with user#1234|project#
This will return all the project IDs the user#1234 has access to.
The code will look as follows.
const monitors = await ddb
.query({
TableName: process.env.DDB_TABLE,
KeyConditionExpression: "#PK = :pk AND begins_with(#SK, :prefix)",
ExpressionAttributeNames: {
"#PK": "PK",
"#SK": "SK"
},
ExpressionAttributeValues: AWS.DynamoDB.Converter.marshall({
":pk": "user#1234",
":prefix": "user#1234|project#"
}),
});
There's another UI screen where we need to display a list of users that have access to a project. This time we'll query the GSI* with the following key conditions GSI=project#1234 AND SK begins_with user#
This will return all the user IDs that have access to project#1234.
If you want to get deeper into DynamoDB I can't recommend enough Alex DeBrie's The DynamoDB Book.
Also, have a look at the Best Practices for Designing and Architecting with DynamoDB.
Monithor is a web service monitoring tool that helps you check websites or APIs' uptime and functionality and be alerted when they don't work as expected. It's built on AWS using the Serverless framework and it's open-source.