Introducing Inferred DynamoDB Foreign Keys in Commandeer
... About 3 min
# Introducing Inferred DynamoDB Foreign Keys in Commandeer
We have been using DynamoDB for the past 4-5 years both at Commandeer, and on previous projects we have built, including Tution.io and the Speed Queen Laundry App. It is a really great NoSQL system that allows for infinite scaleability, is easy to use, and provides a great API to allow developers to get and modify data easily.
But, one of the hangups we have had with NoSQL, is that often times, even though it is unstructured in nature, we typically build it in a structured way. This means we do things like having a Membership table that relates to a User table by a Global Secondary Index called userId. Anytime we need to get data from it however, we were having to pull in developers to code it up.
In our upcoming version of Commandeer, 1.7, we will be providing Foreign Key inference on tables. Let's dive into an example, to visually show you what I am talking about.
Download the Commandeer App - 15-day Free Trial - The #1 developer IDE to manage your serverless and container infrastructures, both locally and in the cloud. With support for 30+ of the best cloud services out there.
# Foreign Key Inference
For the initial version of foreign keys, if your columns are named things like userId or user_id or userID AND you have a User table, we will link the two.
In subsequent releases, we will be adding even more functionality for this via IaC. There will soon be plugins for Ansible, Terraform, and the Serverless Framework that allow you to specify the foreign keys in code, and we will pick them up. Under the hood, we will then have them as tags on the table, and Commandeer will automatically read from them.
Below you can see how our Membership table relates to our User and Team tables.
# ER Diagram
The first place you can easily see what I am talking about is within the DynamoDB ER Diagram. This tool allows you to visualize your DynamoDB tables. Here you can see that many of the tables have a userId column that points to the User table. And also that some of the tables have a foreign key of teamId pointing to the Team table.
# Foreign Key Information
If we drill down into the Membership table, you can see the information about not only the Primary and Secondary indexes, but now also the foreign keys and what tables they are linked to.
And zooming in further to the Membership table itself, you can see that the userId column maps to the User table, and the teamId column maps to the Team table.
You can also view this information from the side navigation.
# Legend
PK = Primary Key
SK = Sort Key
GSI = Global Secondary Index
FK = Foreign Key
# The Power of the Foreign Key
The real power of the Foreign Key is two-fold. First, as shown above, it gives you a great way to visualize your system with the ER Diagram. When you are explaining your system to people, you don't have to draw it out on a white board every time to visualize it The other great feature is that in Commandeer, we allow you to view your related data instantly when looking through a table.
Below you can see a few records in the Membership table, and if you have the 'Format Data' toggle turned on, you will see a new drop down link called 'View' on each record.
Clicking on the 'View' link then brings up the related record in a modal.
On the detail page, you also can view not only your current record, but also any data that might be associated with it. This is also a good example about it being an inference, as NoSQL doesn't really have foreign keys, meaning that there is not inferential integrity, so the record might not exist.
This is a great improvement over the old way of using DynamoDB, in which we would have the Membership table and the User table opened in separate tabs. And then copy the userId from the Membership table, and and it to the query on the User tab. Under the hood, it is doing a primary key query to retrieve the record, and it is only brought back on demand when you click the link.
# Conclusion
DynamoDB continues to be our go to NoSQL system, because it integrates with AppSync, Athena, and Lambda so well. Foreign keys add a whole new level of data accessibility that has been lacking in the ecosystem until now.
Happy Developing!
Download the Commandeer App - 15-day Free Trial - The #1 developer IDE to manage your serverless and container infrastructures, both locally and in the cloud. With support for 30+ of the best cloud services out there.