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.
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.
sqlalchemy imports may look different depending on the needs of your model, but
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 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 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
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_library. We do the same exact thing for the
Game class instance relationship.
The last step to establishing our many-to-many relationship join table is defining a
user_library attribute in both our
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.
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!