I recently created a CLI using Python and SQLAlchemy that required the use of a join table. For those of you that have experience on this topic, creating a normal model with SQLAlchemy is a pretty simple task. However, when you start to join those models in many-to-many relationships things can get complex pretty quickly. Implementing foreign keys tied back to all your original models, and then interacting with the join table in the database can quickly lead to many confusing error messages if not set up correctly. During my experience creating a CLI, this was by far one of the more frustrating and confusing tasks. I find writing Python functions to be pretty straight-forward in comparison. In this blog post, I am going to provide a quick run-down of how to create a simple join table using SQLAlchemy and explain the logistics behind it.
Getting Started
Before getting started with creating models, there are a few imports we will need to ensure we can format our models the way we want and define what data a table record should have.
Your sqlalchemy imports may look different depending on the needs of your model, but relationship and declarative_base are necessities to establish base models and table relationships. SQLAlchemy's declarative_base has some very powerful features that simplify the object relational mapping necessary for any database framework. The declarative base serves as a base class for database models, and combines table definitions and class definitions. It simplifies class-table mapping, provides access to database queries, and integrates sessions to perform CRUD operations on our database.
Building Base Models
Building the base models is a relatively simple task, as they look a lot like vanilla Python classes. The following are the models I created:
Table names are defined using the SQLAlchemy attribute __tablename__ accessible through Base. We then can define all of our columns for the database table. This also serves as a model for the creation of class instances, which will inherit attributes from the Base. We lastly define a magic method __repr__ (representation), that formats instances of created classes into human readable strings. This is extremely helpful when debugging code with ipdb or in a situation where you need to print() an instance of a class to the console for any reason.
Building the user_library Join Table
Building the join table is quite a bit different from creating simple models. As the join table will serve as an "in between" for our many-to-many relationships between the users and games table, we will need to implement the use of foreign keys. This is how I have modeled my join table:
In my join table, I have defined foreign keys as user_id linking back to the users table, and game_id linking back to the games table. We then need to establish a relationship between a User class instance and a Game class instance through our join table. We do this by using the relationship() function built into sqlalchemy.orm. We define user as a relationship to a User class instance that back populates to the user_library table. The back_populates argument is used to establish a bidirectional relationship between a User and user_library. We do the same exact thing for the Game class instance relationship.
Defining Relationships to user_library
The last step to establishing our many-to-many relationship join table is defining a user_library attribute in both our Game and User models. It is very important to pay attention to syntax here, as defining these relationships incorrectly can lead to unexpected bugs or interactions with your database. This is how I have updated my models:
Because we are linking foreign keys for both Game and User instances, we need to define a user_library connection in both models. It's important that the name definition for the class attribute matches the name of the join table. In my example I defined my class attribute as user_library which is the same as the connecting join table. Looking at the User model as the example, we use the relationship function to establish a relationship to instances of User_library and then use back_populates to establish a bidirectional relationship to the user attribute we defined in the User_library model. I've drawn arrows in the below image to more clearly exhibit the relationships we established.
Conclusion
That is a basic tutorial on how to build a join table and establish table relationships. Trust me, this process can get extremely complex when working with more than 3-4 tables and/or multiple join tables. I created multiple Entity Relationship Diagrams during the process of creating my CLI that looked like jumbled up spaghetti, but it was extremely rewarding once I figured it out. Hopefully I've been able to help guide you through the basics of the process and help explain the why's behind it all. I'll include a link to my GitHub repo for the project that inspired this tutorial below. Happy coding everyone!
Top comments (2)
Thanks
No problem :) Hoping to do more tutorials like this in the future!