There are many benefits to using SQLAlchemy, including simplified database connection management and seamless integration with libraries like Pandas. If you are in the business of building applications, I would venture to guess that using an ORM to manage your app's data is a top priority and a great use case for SQLAlchemy.
As someone with a strong background in data, it's understandable to find database model management easier than SQL queries as a software engineer. However, the use of foreign keys and distinguishing between one-to-many and many-to-many relationships may seem like unnecessary abstractions. It's important to remember that these elements are not limitations of SQL and serve a specific purpose in executing JOINs between tables.
The purpose of utilizing an ORM is to reduce the workload of application developers by converting database concepts into code that can be easily replicated within our application. In this session, we will be examining how to define SQLAlchemy data models, with a focus on effectively managing table relationships.
We will be discussing vanilla SQLAlchemy. If you want to learn about implementing SQLAlchemy data models in Flask, check out this post after.

Definition of a Basic Model

Before explaining how to define relationships, let's review the process of creating a basic database model.

To create SQLAlchemy database models, we need to create classes that extend an SQLAlchemy base, which is usually a declarative_base(). We import this base from the sqlalchemy package. The database models are made up of Columns, which are specific data types used in SQLAlchemy.

The code above creates a model that makes use of all available column types in SQLAlchemy. Each model we create corresponds with a table, where each Column object represents a column in the resulting table. When our app initializes SQLAlchemy, tables will be created in our database to match each model, provided they don't already exist.

We set optional built-in variables in our model such as tablename and table_args. The former determines the name of the resulting database table, while the latter allows us to set which Postgres schema our table will belong to.

Creating Tables From Our Models

After creating our model, we must create the corresponding table in our database. To achieve this, we can execute the create_all() method on the Base object, which our model inherits from. Below is a script that automates this task:

I have saved the ExampleModel class to a file named models.py, from where we import Base. After creating the engine, the line Base.metadata.create_all(engine) generates all the tables related to our models.

You can also take a look at the SQL query that has been produced by our example model:

One-to-Many & Many-to-One Relationships

One-to-many or many-to-one relationships are widely used in databases. For instance, a customer can have multiple orders, or a sports player can belong to a single team. To illustrate the latter, we'll create some models.

Below is a code snippet that may appear confusing initially, but don't worry, it's not easy for anyone to understand at first glance. We'll work together to make sense of it.

Right away, we can identify a couple of key elements. We have two distinct models with their own Columns: one for players and another for teams. There are also two new features to take note of.

Firstly, we've introduced the concept of Foreign keys on the PlayerModel's team_id column. If you're familiar with SQL, you'll be well-versed in this area. If not, consider it like this: a foreign key is a characteristic of a column that signifies a relationship between tables. Usually, items from one table are closely associated with those in another table, such as when customers "own" orders or when teams "own" players. In our scenario, we're stating that each player is associated with a team, as depicted by their team_id. This enables us to combine data from both our players and team tables.

A new concept that we're introducing here is relationships. These relationships work alongside foreign keys and enable us to build connections between two models in our application (not the database). It's worth noting that the foreign key value we use is 'example.sqlalchemy_tutorial_teams.id'. In this context, 'example' refers to our Postgres schema and 'sqlalchemy_tutorial_teams' is the table name for our teams table. When it comes to the relationship value we pass, we use "TeamModel" which is the class name of the target data model, not the table name. Essentially, foreign keys convey the relationships we're building to SQL, while relationships inform our application of the same. Both need to be done.

The purpose of this is to make it simple to perform JOINs in our app. If we were to use an ORM, we wouldn't be able to specify which columns to join on by simply saying "join this model with that model". However, by defining our relationships in our models, we can easily join two tables together without the need for any additional details. SQLAlchemy is able to do this by examining the data models we've set up, which are enforced by the foreign keys and relationships we've defined. This saves us the hassle of handling data-related logic while creating our app's business logic, as we're able to define these relationships upfront.

If the tables already exist, SQLAlchemy will not create new ones from the data models. This means that if there are any issues with relationships during the first run of the app, those errors will still persist on the second run, even if we believe we have corrected them. To address unexpected error messages, it may be helpful to remove the SQL tables before running the app again after making changes to the model.

*Back References *
When we specify relationships on a data model, it enables us to access properties of the joined model by using a property on the original model. For instance, if we join our PlayerModel with our TeamModel, we can retrieve properties of a player's team by using PlayerModel.team.name. Here, team refers to the name of our relationship and name is a property of the associated model.

One-directional relationships are created where team details can be accessed through a player, but not the other way around. Adding a back reference can solve this issue.

When creating a relationship, we can set the backref attribute to make it bi-directional. Here's how we modify the previous relationship:

Now, with a backref, we can access a team's player details by calling TeamModel.player.

JOIN operation.

After successfully establishing a connection between two data models, the most effective way to verify your work is by performing a JOIN on these models. We won't delve into the creation of complex SQLAlchemy ORM queries here, but we can use this method to confirm our progress.

When we JOIN the TeamModel and PlayerModel, we reference everything as a property of PlayerModel. Here is the sample data output:

Many-to-Many Relationships

Establishing foreign key relationships is beneficial when we anticipate a table in our relationship to have only one record per multiple records in another table (for example, one player per team). However, when players can belong to several teams, things become intricate.

You may have already noticed that many-to-many relationships occur between tables where any number of records from table 1 can be linked to any number of records from table 2. SQLAlchemy makes use of association tables to establish these relationships. An association table is a SQL table that is specifically designed to clarify these connections and we will create one now.

Take a look at how the association_table variable is defined below:

We have implemented a new data type, called Table, to create a many-to-many association between two tables. To define this association, we provide the name of the resulting table as the first parameter, which we have named "association". Additionally, we associate this new table with the same declarative base as our data models, by passing Base.metadata. Finally, we create two columns as foreign keys, linking the team_id column of PlayerModel with the id column of TeamModel.

What we're doing is creating a third table that links our two existing tables. Instead of creating a whole new data model, we're opting for the simpler solution of an association table. Moving forward, we can now directly access the association table to retrieve information from both our players and teams tables.

To implement an association table, the final step is to establish a relationship on the data model. It's worth noting that we set a relationship on the PlayerModel as we did before, but this time we assigned the secondary attribute to the name of the association table.