Define Relationships Between SQLAlchemy Data Models
Tags: sqlalchemy, python, software, sql
Published: 2022-05-15 17:51:24

---
title: Define Relationships Between SQLAlchemy Data Models
published: true
date: 2019-07-11 05:50:00 UTC
tags: SQLAlchemy,Python,Software,SQL
series: Mastering SQLAlchemy
canonical_url: https://hackersandslackers.com/sqlalchemy-data-models/
---

![Define Relationships Between SQLAlchemy Data Models](https://hackersandslackers-cdn.storage.googleapis.com/2019/07/sqlalchemy-relationships.jpg)

All Python developers have something to gain from SQLAlchemy. Whether you're looking for a better way to[manage database connections](https://hackersandslackers.com/python-database-management-sqlalchemy/), or [build out an ORM](https://hackersandslackers.com/implement-sqlalchemy-orm) data layer for your application, there's no reason for any of us to omit "`pip install sqlalchemy`" from our Python vocabulary.  

Engineers who work on large-scale applications overwhelmingly prefer handling data via ORMs over raw SQL. For those with heavy data backgrounds (like myself), the abstraction hiding SQL behind Python objects can be off-putting. Why do we _need_ foreign keys to execute JOINs between two tables? Why do engineers working on large-scale software seem to overuse terminology such as " **one-to-many**" versus " **many-to-many**" relationships, when SQL itself has no such terminology? If you've felt this sentiment, you're in [good company](https://wozniak.ca/blog/2014/08/03/1/index.html).

After a couple years, I've come find that ORMs _do_ result in less work in the context of building applications, and aren't just a crutch for people who are "afraid of SQL." We save significant time by handling sensitive data transactions as reproducible code patterns, but this benefit pales in comparison to what we gain in security and integrity. ORMs don't write destructive SQL queries; people do.

So, yes. It _is_ annoying that engineers who write ORMs use different lingo than those who understand the underlying SQL, and it _is_ annoying how much upfront work it takes to set up an ORM, but it _is_ worth it. Today we're getting the hardest part of ORM development out of the way by learning how to define table relationships in SQLAlchemy.

## Set up Some Data Models

We already covered SQLAlchemy data models in our [last post](https://hackersandslackers.com/sqlalchemy-orm-implementation/), so I'll skip the finer details. If you arrived here by frantically Googling questions about SQLAlchemy, you should probably catch up on what models are and how to define them.

We're going to create a few models to demonstrate how to create a SQL relationship between them. In the spirit of blogging, we'll be creating models for `User`, `Post`, and `Comment`:

```python
"""Declare models and relationships."""
from sqlalchemy import Boolean, Column, DateTime, ForeignKey, Integer, String, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func

from database import engine

Base = declarative_base()

class User(Base):
    """User account."""

    __tablename__ = "user"

    id = Column(Integer, primary_key=True, autoincrement="auto")
    username = Column(String(255), unique=True, nullable=False)
    password = Column(Text, nullable=False)
    email = Column(String(255), unique=True, nullable=False)
    first_name = Column(String(255))
    last_name = Column(String(255))
    bio = Column(Text)
    avatar_url = Column(Text)
    role = Column(String(255))
    last_seen = Column(DateTime)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

    def __repr__ (self):
        return "<User %r>" % self.username


class Comment(Base):
    """User-generated comment on a blog post."""

    __tablename__ = "comment"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer)
    post_id = Column(Integer, index=True)
    body = Column(Text)
    upvotes = Column(Integer, default=1)
    removed = Column(Boolean, default=False)
    created_at = Column(DateTime, server_default=func.now())
    
    def __repr__ (self):
        return "<Comment %r>" % self.id


class Post(Base):
    """Blog post/article."""

    __tablename__ = "post"

    id = Column(Integer, primary_key=True, index=True)
    author_id = Column(Integer)
    slug = Column(String(255), nullable=False, unique=True)
    title = Column(String(255), nullable=False)
    summary = Column(String(400))
    feature_image = Column(String(300))
    body = Column(Text)
    status = Column(String(255), nullable=False, default="unpublished")
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now())

    def __repr__ (self):
        return "<Post %r>" % self.slug

Base.metadata.create_all(engine)
```
<figcaption>models.py</figcaption>
## One-to-Many Relationships

One-to-many (or many-to-one) relationships are the most common type of database relationships. A timeless example of how such a relationship is applied is a business' relationship between **_customers & orders._** Single customers have multiple orders, but orders don't have multiple customers, hence the term

Using our blog example, let's look at what a one-to-many relationship might look like for authors who have multiple posts, or posts which have multiple comments:

```python
...

from sqlalchemy.orm import relationship

class User(Base):
    """User account."""

    __tablename__ = "user"

    id = Column(Integer, primary_key=True, autoincrement="auto")
    username = Column(String(255), unique=True, nullable=False)
    password = Column(Text, nullable=False)
    email = Column(String(255), unique=True, nullable=False)
    first_name = Column(String(255))
    last_name = Column(String(255))
    bio = Column(Text)
    avatar_url = Column(Text)
    role = Column(String(255))
    last_seen = Column(DateTime)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

    def __repr__ (self):
        return "<User %r>" % self.username


class Comment(Base):
    """User-generated comment on a blog post."""

    __tablename__ = "comment"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("user.id")) # Foreign key added
    post_id = Column(Integer, ForeignKey("post.id"), index=True) # Foreign key added
    body = Column(Text)
    upvotes = Column(Integer, default=1)
    removed = Column(Boolean, default=False)
    created_at = Column(DateTime, server_default=func.now())

    # Relationships
    user = relationship("User")

    def __repr__ (self):
        return "<Comment %r>" % self.id


class Post(Base):
    """Blog post/article."""

    __tablename__ = "post"

    id = Column(Integer, primary_key=True, index=True)
    author_id = Column(Integer, ForeignKey("user.id")) # Foreign key added
    slug = Column(String(255), nullable=False, unique=True)
    title = Column(String(255), nullable=False)
    summary = Column(String(400))
    feature_image = Column(String(300))
    body = Column(Text)
    status = Column(String(255), nullable=False, default="unpublished")
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now())

    # Relationships
    author = relationship("User")
    comments = relationship("Comment")

    def __repr__ (self):
        return "<Post %r>" % self.slug
```
<figcaption>models.py</figcaption>

We added two critical additionals to our models which might be hard to spot at first glance. First, we set some attributes (columns) as **Foreign keys** (if you're familiar with SQL, you should be good-to-go here).A foreign key is a property of a column; when a foreign key is present, we're saying that this particular column denotes a _relationship_ between tables: most common items of one table "belong" to items of another table, like when _customers_ "own" _orders,_ or when _users_ "own" _posts._ In our example, we're saying that each _post_ has an _author (user)_ as specified by the `author_id` attribute, seen here:

```python
author_id = Column(Integer, ForeignKey("user.id"))
```

We can marry data between our _users_ table and our _posts_ table, so that fetching one will allow us to get information about the other.

The other new concept here is **relationships.** Relationships complement foreign keys, and are a way of telling our application (not our database) that we're building relationships between two models. Notice how the value of our foreign key is `'user.id'`. `user` is table name for our `User` table. Compare this to the value we pass to our **relationship** , which is `"User"`: the class name of the target data model (not the table name!).

```python
author = relationship("User")
```

Foreign keys tell _SQL_ which relationships we're building, and relationships tell our _app_ which relationships we're building. We need to do both.

The point of all this is the ability to easily perform JOINs in our app. When using an ORM, we wouldn't be able to say "join this model with that model", because our app would have no idea which columns to join _on_. When our relationships are specified in our models, we can do things like join two tables together without specifying any further detail: SQLAlchemy will know how to join tables/models by looking at what we set in our data models (as enforced by the foreign keys & relationships we set). We're really just saving ourselves the burden of dealing with data-related logic while creating our app's business logic by defining relationships upfront.

<!--kg-card-begin: html-->
SQLAlchemy only creates tables from data models if the tables don't already exist. In other words, if we have faulty relationships the first time we run our app, the error messages will persist the second time we run our app, even if we think we've fixed the problem. To deal with strange error messages, try deleting your SQL tables before running your app again whenever making changes to a model.
<!--kg-card-end: html-->
### Back References

Specifying relationships on a data model allows us to access properties of the joined model via a property on the original model. If we were to join our **Comment** model with our **User** model, we'd be able to access properties of a comment's author via `Comment.user.username`, where `user` is the name of our relationship, and `username` is a property of the associated model.

Relationships created in this way are one-directional, in that we can access team details through a player, but _can't_ access player details from a team. We can solve this easily by setting a **back reference**.

When creating a relationship, we can pass an attribute called **backref** to make a relationship bi-directional. Here's how we'd modify the relationship we set previously:

```python
 # Relationships
author = relationship("User", backref="posts")
```
<figcaption>models.py</figcaption>

With a **backref** present, we can now access user details of a post by calling `Post.author`.

### Creating Related Data

Time to create some data. We're going to need a user to serve as our blog's author. While we're at it, let's give them some blog posts:

```python
from .models import Post, User

admin_user = User(
    username="toddthebod",
    password="Password123lmao",
    email="todd@example.com",
    first_name="Todd",
    last_name="Birchard",
    bio="I write tutorials on the internet.",
    avatar_url="https://storage.googleapis.com/hackersandslackers-cdn/authors/todd_small@2x.jpg",
    role="admin",
)

post_1 = Post(
    author_id=admin_user.id,
    slug="fake-post-slug",
    title="Fake Post Title",
    status="published",
    summary="A fake post to have some fake comments.",
    feature_image="https://hackersandslackers-cdn.storage.googleapis.com/2021/01/logo-smaller@2x.png",
    body="Cheese slices monterey jack cauliflower cheese dolcelatte cheese and wine fromage frais rubber cheese gouda. Rubber cheese cheese and wine cheeseburger cheesy grin paneer paneer taleggio caerphilly. Edam mozzarella.",
)

post_2 = Post(
    author_id=admin_user.id,
    slug="an-additional-post",
    title="Yet Another Post Title",
    status="published",
    summary="An in-depth exploration into writing your second blog post.",
    feature_image="https://hackersandslackers-cdn.storage.googleapis.com/2021/01/logo-smaller@2x.png",
    body="Smelly cheese cheese slices fromage. Pepper jack taleggio monterey jack cheeseburger pepper jack swiss everyone loves. Cheeseburger say cheese brie fromage frais swiss when the cheese comes out everybody's happy babybel cheddar. Cheese and wine cheesy grin",
)
```
<figcaption>objects.py</figcaption>

We created our objects, but we haven't saved them to our database yet. I'm going to throw together a couple functions for this; `create_user()` will handle user creation, and `create_post()` will create... well, you know:

```python
"""Create records related to one another via SQLAlchemy's ORM."""
from typing import Tuple

from sqlalchemy.exc import IntegrityError, SQLAlchemyError
from sqlalchemy.orm import Session

from logger import LOGGER
from sqlalchemy_tutorial.part3_relationships.models import Post, User

def create_user(session: Session, user: User) -> User:
    """
    Create a new user if username isn't already taken.

    :param session: SQLAlchemy database session.
    :type session: Session
    :param user: New user record to create.
    :type user: User

    :return: Optional[User]
    """
    try:
        existing_user = session.query(User).filter(User.username == user.username).first()
        if existing_user is None:
            session.add(user) # Add the user
            session.commit() # Commit the change
            LOGGER.success(f"Created user: {user}")
        else:
            LOGGER.warning(f"Users already exists in database: {existing_user}")
        return session.query(User).filter(User.username == user.username).first()
    except IntegrityError as e:
        LOGGER.error(e.orig)
        raise e.orig
    except SQLAlchemyError as e:
        LOGGER.error(f"Unexpected error when creating user: {e}")
        raise e

def create_post(session: Session, post: Post) -> Post:
    """
    Create a post.

    :param session: SQLAlchemy database session.
    :type session: Session
    :param post: Blog post to be created.
    :type post: Post

    :return: Post
    """
    try:
        existing_post = session.query(Post).filter(Post.slug == post.slug).first()
        if existing_post is None:
            session.add(post) # Add the post
            session.commit() # Commit the change
            LOGGER.success(
                f"Created post {post} published by user {post.author.username}"
            )
            return session.query(Post).filter(Post.slug == post.slug).first()
        else:
            LOGGER.warning(f"Post already exists in database: {post}")
            return existing_post
    except IntegrityError as e:
        LOGGER.error(e.orig)
        raise e.orig
    except SQLAlchemyError as e:
        LOGGER.error(f"Unexpected error when creating user: {e}")
        raise e
```
<figcaption>orm.py</figcaption>

If you're caught off-guard by the complexity of these functions, know that everything besides `session.add()` and `session.commit()` exists for the purpose of error handling and avoiding duplicates. We don't want duplicate posts or users, hence why we check for `existing_user` and `existing_post` before continuing with their respective functions.

Let's created these records:

```python
from .orm import create_user, create_post

# Create admin user & two posts
admin_user = create_user(session, admin_user)
post_1 = create_post(session, post_1)
post_2 = create_post(session, post_2)
```
<figcaption>Creating records</figcaption>

We now have **one** user, and **many** (ish) posts! If you were to check your database, you would see these records created there. The stage is set for a **one-to-many** join query.

### Performing a One-to-Many JOIN

When we perform a JOIN on SQLAlchemy models, we can utilize the _relationship_ attribute of each record we fetch as though the attribute were an entire record in itself. It's probably easier just to show you what I mean.

In the query below, we fetch all posts in our database belonging to **user 1**. We then JOIN posts belonging to that user by extending our query with `.join(User, Post.author_id == User.id)`:

```python
"""Perform JOIN queries on models with relationships."""
from sqlalchemy.orm import Session

from logger import LOGGER
from sqlalchemy_tutorial.part3_relationships.models import Post, User

def get_all_posts(session: Session, admin_user: User):
    """
    Fetch all posts belonging to an author user.

    :param session: SQLAlchemy database session.
    :type session: Session
    :param admin_user: Author of blog posts.
    :type admin_user: User

    :return: None
    """
    posts = (
        session.query(Post)
        .join(User, Post.author_id == User.id)
        .filter_by(username=admin_user.username)
        .all()
    )
    for post in posts:
        post_record = {
            "post_id": post.id,
            "title": post.title,
            "summary": post.summary,
            "status": post.status,
            "feature_image": post.feature_image,
            "author": {
                "id": post.author_id,
                "username": post.author.username,
                "first_name": post.author.first_name,
                "last_name": post.author.last_name,
                "role": post.author.role,
            },
        }
        LOGGER.info(post_record)
```

After executing the query, we loop over each record and print a JSON object representing the posts we've fetched along with their author's data:

```json
{
  "post_id": 1,
  "title": "Fake Post Title",
  "summary": "A fake post to have some fake comments.",
  "status": "published",
  "feature_image": "https://hackersandslackers-cdn.storage.googleapis.com/2021/01/logo-smaller@2x.png",
  "author": {
    "id": 2,
    "username": "toddthebod",
    "first_name": "Todd",
    "last_name": "Birchard",
    "role": "admin"
  }
}, {
  "post_id": 2,
  "title": "Yet Another Post Title",
  "summary": "An in-depth exploration into writing your second blog post.",
  "status": "published",
  "feature_image": "https://hackersandslackers-cdn.storage.googleapis.com/2021/01/logo-smaller@2x.png",
  "author": {
    "id": 2,
    "username": "toddthebod",
    "first_name": "Todd",
    "last_name": "Birchard",
    "role": "admin"
  }
}
```
<figcaption>Output of <code>get_all_posts()</code></figcaption>
## Many-to-Many Relationships

Setting foreign key relationships serve us well when we're expecting a table in our relationship to only have a single record per multiple records in another table (ie: one player per team). What if players could belong to _multiple_ teams? This is where things get complicated.

As you might've guessed, many-to-many relationships happen between tables where n number of records from table 1 could be associated with n number of records from table 2. SQLAlchemy achieves relationships like these via _association tables._ An association table is a SQL table created for the sole purpose of explaining these relationships, and we're going to build one.

Check out how we define the **association\_table** variable below:

```python
from sqlalchemy import Column, Integer, String, ForeignKey, Table
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

association_table = Table('association', Base.metadata,
    Column('team_id', Integer, ForeignKey('example.sqlalchemy_tutorial_players.team_id')),
    Column('id', Integer, ForeignKey('example.sqlalchemy_tutorial_teams.id'))
)

class Player(Base):
    """Individual player belonging to a team."""

    __tablename__ = "player"

    id = Column(Integer, primary_key=True, autoincrement="auto")
    team_id = Column(Integer, ForeignKey("team.id"), nullable=False)
    first_name = Column(String(255), nullable=False)
    last_name = Column(String(255), nullable=False)
    position = Column(String(100), nullable=False)
    injured = Column(Boolean)
    description = Column(Text, nullable=True)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

    # Relationships
    team = relationship("Team")

    def __repr__ (self):
        return "<Player {}>".format(self.id)


class Team(Base):
    """Team consisting of many players."""

    __tablename__ = "team"

    id = Column(Integer, primary_key=True, autoincrement="auto")
    name = Column(String(255), nullable=False)
    city = Column(String(255), nullable=False)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

    def __repr__ (self):
        return "<Team {}>".format(self.id)
```
<figcaption>models.py</figcaption>

We're using a new data type **Table** to define a table which builds a many-to-many association. The first parameter we pass is the name of the resulting table, which we name `association`. Next we pass `Base.metadata` to associate our table with the same declarative base that our data models extend. Lastly, we create two columns which serve as foreign keys to each of the tables we're associating: we're linking **Player**'s **team\_id** column with **Team**'s **id** column.

The essence of we're really doing here is creating a third table which associates our two tables. We could also achieve this by creating a third data model, but creating an association table is a bit more straightforward. From here on out, we can now query **association\_table** directly to get records from our players and teams table.

The final step of implementing an association table is to set a relationship on our data model. Notice how we set a **relationship** on **Player** like we did previously, but this time we set the **secondary** attribute equal to the name of our association table.

```json
[
  {
    "comment_id": 1,
    "body_summary": "This post about SQLAlchemy is awful. You didnt ev...",
    "upvotes": 2,
    "comment_author_id": 3,
    "post": {
      "slug": "fake-post-slug",
      "title": "Fake Post Title",
      "post_author": "toddthebod"
    }
  },
  {
    "comment_id": 2,
    "body_summary": "By the way, you SUCK!!! I HATE you!!!! I have a pr...",
    "upvotes": 5,
    "comment_author_id": 3,
    "post": {
      "slug": "fake-post-slug",
      "title": "Fake Post Title",
      "post_author": "toddthebod"
    }
  },
  {
    "comment_id": 3,
    "body_summary": "YOU RUINED MY LIFE!!!!...",
    "upvotes": 5,
    "comment_author_id": 3,
    "post": {
      "slug": "fake-post-slug",
      "title": "Fake Post Title",
      "post_author": "toddthebod"
    }
  }
]
```
<figcaption>Record output</figcaption>