A Quick Guide: Creating Join Tables with SQLAlchemy
Tags: beginners, python, sql, tutorial
Published: 2023-08-27 01:46:17

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.



![sqalchemy imports](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1ovgo6i56fqh9r312xvz.png)

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:


![base game/user models](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/70dum5twn5gv1fczebz3.png)

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:

 
![user_library join table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/78sdxcgyqju0d5r3gd8t.png)

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:


![refactored game/user models](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sx14iktchwslpf6xuswe.png)

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.


![relationship connections](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sgtetnkv7cj6zhw3rcs0.png)


## 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!

[Vapor Game Store CLI Repo](https://github.com/BryTheWiseGuy/vapor-game-store-cli)