# Getting Attached to SQLAlchemy Using Relationships

By Yonatan Vega · September 24, 2025

> Source: https://fullstack.rocks/article/getting-attached-to-sqlalchemy-using-relationships

![A diagram showing relationships between alchemist tables with potions and ingredients.](https://storage.googleapis.com/fullstack-rocks-media/getting-attached-with-relationships-960-minified-dff3024b9ddd.webp)

When working with relational databases, the most important concept is undoubtedly the relationships between tables. SQLAlchemy, being an ORM, provides us with several ways to define and work with these relationships. In this section, we'll explore the different types of relationships and how to implement them in SQLAlchemy.

A relationship in SQLAlchemy is created using the `relationship()` function, which creates a link between two mapped classes. The relationship can be one-to-many, one-to-one, many-to-one, or many-to-many. Let's dive into each type.

## One-to-many relationships

A one-to-many relationship is perhaps the most common type of relationship in database design. It describes a relationship where one record in a table can be associated with multiple records in another table.

For example, think of an alchemist who can have multiple labs. One alchemist can own many labs, but each lab belongs to only one alchemist.

Let's define this relationship in SQLAlchemy:

```python
from sqlalchemy import create_engine, ForeignKey, String, Integer
from sqlalchemy.orm import DeclarativeBase, mapped_column, relationship, Session, Mapped
from typing import List

# Define our base class
class Base(DeclarativeBase):
  pass

# Define our models with the relationship
class Alchemist(Base):
  __tablename__ = 'alchemists'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50), nullable=False)
  age: Mapped[int] = mapped_column(Integer)
  
  # This establishes the one-to-many relationship
  # One alchemist can have many labs
  labs: Mapped[List["Lab"]] = relationship(back_populates="alchemist")

class Lab(Base):
  __tablename__ = 'labs'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50), nullable=False)
  location: Mapped[str] = mapped_column(String(100))
  
  # Foreign key to link to the alchemist
  # Many labs can belong to one alchemist
  alchemist_id: Mapped[int] = mapped_column(ForeignKey("alchemists.id"))
  
  # This establishes the many-to-one side of the relationship
  alchemist: Mapped["Alchemist"] = relationship(back_populates="labs")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/getting-attached-to-sqlalchemy-using-relationships#code-6ac165385e218264632868fb)_

Looking at the code, we've created two models, `Alchemist` and `Lab`. The `Alchemist` model has a `labs` relationship that points to a list of `Lab` objects. The `Lab` model has an `alchemist_id` foreign key column that references the primary key of the `alchemists` table, and an `alchemist` relationship that points back to the associated `Alchemist` object.

The `back_populates` parameter is used to specify the name of the attribute on the related class that will "back populate" to this relationship. This parameter enables SQLAlchemy to establish a bidirectional relationship, which means we can access the labs from an alchemist and also access the alchemist from a lab.

Let's see how we can use this relationship in practice:

```python
# Create an engine and create the tables
engine = create_engine("sqlite:///:memory:", echo=True)
Base.metadata.create_all(engine)

# Create a session
with Session(engine) as session:
  # Create an alchemist
  flamel = Alchemist(name="Nicolas Flamel", age=665)
  
  # Create some labs for the alchemist
  paris_lab = Lab(name="Paris Laboratory", location="Paris, France", alchemist=flamel)
  london_lab = Lab(name="London Workshop", location="London, England", alchemist=flamel)
  
  # Add and commit
  session.add_all([flamel, paris_lab, london_lab])
  session.commit()
  
  # Query an alchemist and access their labs
  alchemist = session.query(Alchemist).filter_by(name="Nicolas Flamel").first()
  print(f"Alchemist: {alchemist.name}")
  for lab in alchemist.labs:
      print(f"  Lab: {lab.name} in {lab.location}")
  
  # Query a lab and access its alchemist
  lab = session.query(Lab).filter_by(name="Paris Laboratory").first()
  print(f"Lab: {lab.name}")
  print(f"  Owned by: {lab.alchemist.name}")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/getting-attached-to-sqlalchemy-using-relationships#code-6ac165385e218264632868fc)_

In this example, we created an alchemist, Nicolas Flamel, and two labs that belong to him. We set the relationship by assigning `flamel` to the `alchemist` attribute of each lab.

When we query the alchemist, we can access their labs through the `labs` attribute. Likewise, when we query a lab, we can access its alchemist through the `alchemist` attribute. This is the power of bidirectional relationships in SQLAlchemy.

Let's also notice that when defining the relationship, we've used the string `"Lab"` in the type annotation for `labs`. This is called a "forward reference" and it's necessary because the `Lab` class isn't fully defined yet when we're defining the `Alchemist` class. Similarly, we used the string `"Alchemist"` in the type annotation for `alchemist` in the `Lab` class.

## One-to-one Relationships

A one-to-one relationship is a special case of a one-to-many relationship where each record in the first table has exactly one matching record in the second table, and vice versa. This can be useful when you want to split a table into two parts, for example, to keep some data separate for performance or security reasons.

Let's say each alchemist has exactly one familiar (a magical companion), and each familiar belongs to exactly one alchemist. We can model this relationship as follows:

```python
from sqlalchemy import create_engine, ForeignKey, String, Integer
from sqlalchemy.orm import DeclarativeBase, mapped_column, relationship, Session, Mapped
from typing import Optional

class Base(DeclarativeBase):
  pass

class Alchemist(Base):
  __tablename__ = 'alchemists'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50), nullable=False)
  age: Mapped[int] = mapped_column(Integer)
  
  # One-to-one relationship: one alchemist has one familiar
  # The uselist=False parameter is what makes this a one-to-one relationship
  familiar: Mapped[Optional["Familiar"]] = relationship(back_populates="alchemist", uselist=False)

class Familiar(Base):
  __tablename__ = 'familiars'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50), nullable=False)
  species: Mapped[str] = mapped_column(String(50))
  
  # Foreign key to link to the alchemist
  # The unique=True constraint ensures one-to-one relationship
  alchemist_id: Mapped[int] = mapped_column(ForeignKey("alchemists.id"), unique=True)
  
  # One-to-one relationship: one familiar belongs to one alchemist
  alchemist: Mapped["Alchemist"] = relationship(back_populates="familiar")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/getting-attached-to-sqlalchemy-using-relationships#code-6ac165385e218264632868fd)_

There are a few key elements that make this a one-to-one relationship:

- The `uselist=False` parameter in the `relationship()` function on the `Alchemist` class means that the `familiar` attribute will be a single object, not a list of objects.
- The `unique=True` constraint on the `alchemist_id` foreign key in the `Familiar` class ensures that each alchemist ID can only appear once in the `familiars` table, meaning each alchemist can have at most one familiar.

The `Optional` type annotation for `familiar` indicates that an alchemist might not have a familiar (the attribute can be `None`).

Let's see how we can use this one-to-one relationship:

```python
# Create an engine and create the tables
engine = create_engine("sqlite:///:memory:", echo=True)
Base.metadata.create_all(engine)

# Create a session
with Session(engine) as session:
  # Create an alchemist
  paracelsus = Alchemist(name="Paracelsus", age=49)
  
  # Create a familiar for the alchemist
  phoenix = Familiar(name="Fawkes", species="Phoenix", alchemist=paracelsus)
  
  # Add and commit
  session.add_all([paracelsus, phoenix])
  session.commit()
  
  # Query an alchemist and access their familiar
  alchemist = session.query(Alchemist).filter_by(name="Paracelsus").first()
  print(f"Alchemist: {alchemist.name}")
  print(f"  Familiar: {alchemist.familiar.name} ({alchemist.familiar.species})")
  
  # Query a familiar and access its alchemist
  familiar = session.query(Familiar).filter_by(name="Fawkes").first()
  print(f"Familiar: {familiar.name}")
  print(f"  Belongs to: {familiar.alchemist.name}")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/getting-attached-to-sqlalchemy-using-relationships#code-6ac165385e218264632868fe)_

In this example, we created an alchemist, Paracelsus, and a familiar, Fawkes, that belongs to him. We set the relationship by assigning `paracelsus` to the `alchemist` attribute of the familiar.

Because this is a one-to-one relationship, we can access the familiar directly as `alchemist.familiar` (not as a list), and similarly, we can access the alchemist as `familiar.alchemist`.

## Many-to-many Relationships

A many-to-many relationship occurs when multiple records in a table can be associated with multiple records in another table. For example, an alchemist can create many potions, and a potion can be created by many alchemists.

To implement a many-to-many relationship in a relational database, we need an association table that serves as a bridge between the two tables. In SQLAlchemy, we can define this association table and set up the many-to-many relationship as follows:

```python
from sqlalchemy import create_engine, ForeignKey, String, Table, Column
from sqlalchemy.orm import DeclarativeBase, mapped_column, relationship, Session, Mapped
from typing import List

class Base(DeclarativeBase):
  pass

# Association table for the many-to-many relationship
# This is a simple table with just the foreign keys
alchemist_potion = Table(
  'alchemist_potion',
  Base.metadata,
  Column('alchemist_id', ForeignKey('alchemists.id'), primary_key=True),
  Column('potion_id', ForeignKey('potions.id'), primary_key=True)
)

class Alchemist(Base):
  __tablename__ = 'alchemists'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50), nullable=False)
  age: Mapped[int]
  
  # Many-to-many relationship: many alchemists can create many potions
  # The secondary parameter specifies the association table
  potions: Mapped[List["Potion"]] = relationship(
      secondary=alchemist_potion,
      back_populates="alchemists"
  )

class Potion(Base):
  __tablename__ = 'potions'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50), nullable=False)
  effect: Mapped[str] = mapped_column(String(100))
  
  # Many-to-many relationship: many potions can be created by many alchemists
  alchemists: Mapped[List["Alchemist"]] = relationship(
      secondary=alchemist_potion,
      back_populates="potions"
  )
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/getting-attached-to-sqlalchemy-using-relationships#code-6ac165385e218264632868ff)_

The key element here is the `alchemist_potion` association table, which has two foreign keys: one referencing the `alchemists` table and one referencing the `potions` table. These foreign keys are also set as primary keys in the association table, creating a composite primary key.

In the relationship definition, we specify the association table using the `secondary` parameter. This tells SQLAlchemy to use this table to establish the many-to-many relationship.

Now, let's see how we can use this many-to-many relationship:

```python
# Create an engine and create the tables
engine = create_engine("sqlite:///:memory:", echo=True)
Base.metadata.create_all(engine)

# Create a session
with Session(engine) as session:
  # Create some alchemists
  flamel = Alchemist(name="Nicolas Flamel", age=665)
  paracelsus = Alchemist(name="Paracelsus", age=49)
  
  # Create some potions
  elixir = Potion(name="Elixir of Life", effect="Immortality")
  felix = Potion(name="Felix Felicis", effect="Luck")
  veritaserum = Potion(name="Veritaserum", effect="Truth telling")
  
  # Establish the many-to-many relationships
  flamel.potions = [elixir, felix]  # Flamel created the Elixir of Life and Felix Felicis
  paracelsus.potions = [felix, veritaserum]  # Paracelsus created Felix Felicis and Veritaserum
  
  # Add and commit
  session.add_all([flamel, paracelsus, elixir, felix, veritaserum])
  session.commit()
  
  # Query an alchemist and access their potions
  alchemist = session.query(Alchemist).filter_by(name="Nicolas Flamel").first()
  print(f"Alchemist: {alchemist.name}")
  for potion in alchemist.potions:
      print(f"  Created: {potion.name} ({potion.effect})")
  
  # Query a potion and access its alchemists
  potion = session.query(Potion).filter_by(name="Felix Felicis").first()
  print(f"Potion: {potion.name}")
  for alchemist in potion.alchemists:
      print(f"  Created by: {alchemist.name}")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/getting-attached-to-sqlalchemy-using-relationships#code-6ac165385e21826463286900)_

In this example, we created two alchemists and three potions. We established the many-to-many relationships by assigning lists of potions to the `potions` attribute of each alchemist.

When we query an alchemist, we can access their potions through the `potions` attribute. Similarly, when we query a potion, we can access its alchemists through the `alchemists` attribute.

Notice that we're now dealing with collections on both sides of the relationship. An alchemist has a list of potions, and a potion has a list of alchemists. This is the essence of a many-to-many relationship.

## Association Tables

While the simple many-to-many relationship we defined earlier works well for basic cases, sometimes we need to store additional information about the relationship. For instance, we might want to track when an alchemist created a potion, or how successful the creation was.

In such cases, we can define a full model for the association table instead of just a Table object. Let's see how to do this:

```python
from sqlalchemy import create_engine, ForeignKey, String, Integer, DateTime
from sqlalchemy.orm import DeclarativeBase, mapped_column, relationship, Session, Mapped
from typing import List
from datetime import datetime

class Base(DeclarativeBase):
  pass

class AlchemistPotion(Base):
  __tablename__ = 'alchemist_potion'
  
  # Composite primary key made up of the two foreign keys
  alchemist_id: Mapped[int] = mapped_column(ForeignKey("alchemists.id"), primary_key=True)
  potion_id: Mapped[int] = mapped_column(ForeignKey("potions.id"), primary_key=True)
  
  # Additional data about the relationship
  created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
  success_rating: Mapped[int] = mapped_column(Integer, default=5)  # Rating out of 10
  
  # Relationships
  alchemist: Mapped["Alchemist"] = relationship(back_populates="potion_associations")
  potion: Mapped["Potion"] = relationship(back_populates="alchemist_associations")

class Alchemist(Base):
  __tablename__ = 'alchemists'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50), nullable=False)
  age: Mapped[int]
  
  # Relationship to the association table
  potion_associations: Mapped[List["AlchemistPotion"]] = relationship(back_populates="alchemist")
  
  # Convenience property to get potions directly
  @property
  def potions(self):
      return [assoc.potion for assoc in self.potion_associations]

class Potion(Base):
  __tablename__ = 'potions'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50), nullable=False)
  effect: Mapped[str] = mapped_column(String(100))
  
  # Relationship to the association table
  alchemist_associations: Mapped[List["AlchemistPotion"]] = relationship(back_populates="potion")
  
  # Convenience property to get alchemists directly
  @property
  def alchemists(self):
      return [assoc.alchemist for assoc in self.alchemist_associations]
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/getting-attached-to-sqlalchemy-using-relationships#code-6ac165385e21826463286901)_

Now our association table is a full model, `AlchemistPotion`, with its own attributes and relationships. The `alchemist_id` and `potion_id` foreign keys are still the primary keys, but we've added two more columns: `created_at` to track when the relationship was established, and `success_rating` to rate how successful the potion creation was.

We've also added properties to the `Alchemist` and `Potion` classes to make it easier to access the related objects. The `potions` property on `Alchemist` returns a list of potions, and the `alchemists` property on `Potion` returns a list of alchemists.

Let's see how we can use this more complex many-to-many relationship:

```python
# Create an engine and create the tables
engine = create_engine("sqlite:///:memory:", echo=True)
Base.metadata.create_all(engine)

# Create a session
with Session(engine) as session:
  # Create some alchemists
  flamel = Alchemist(name="Nicolas Flamel", age=665)
  paracelsus = Alchemist(name="Paracelsus", age=49)
  
  # Create some potions
  elixir = Potion(name="Elixir of Life", effect="Immortality")
  felix = Potion(name="Felix Felicis", effect="Luck")
  
  # Create the association objects
  flamel_elixir = AlchemistPotion(
      alchemist=flamel,
      potion=elixir,
      success_rating=10  # Perfect creation
  )
  
  flamel_felix = AlchemistPotion(
      alchemist=flamel,
      potion=felix,
      success_rating=7  # Good but not perfect
  )
  
  paracelsus_felix = AlchemistPotion(
      alchemist=paracelsus,
      potion=felix,
      success_rating=9  # Almost perfect
  )
  
  # Add and commit
  session.add_all([flamel, paracelsus, elixir, felix, flamel_elixir, flamel_felix, paracelsus_felix])
  session.commit()
  
  # Query an alchemist and access their potions
  alchemist = session.query(Alchemist).filter_by(name="Nicolas Flamel").first()
  print(f"Alchemist: {alchemist.name}")
  for assoc in alchemist.potion_associations:
      print(f"  Created: {assoc.potion.name} on {assoc.created_at} with rating {assoc.success_rating}/10")
  
  # We can also use our convenience property
  print(f"Potions created by {alchemist.name}: {', '.join(potion.name for potion in alchemist.potions)}")
  
  # Query a potion and access its alchemists
  potion = session.query(Potion).filter_by(name="Felix Felicis").first()
  print(f"Potion: {potion.name}")
  for assoc in potion.alchemist_associations:
      print(f"  Created by: {assoc.alchemist.name} with rating {assoc.success_rating}/10")
  
  # We can also use our convenience property
  print(f"Alchemists who created {potion.name}: {', '.join(alchemist.name for alchemist in potion.alchemists)}")
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/getting-attached-to-sqlalchemy-using-relationships#code-6ac165385e21826463286902)_

In this example, we created two alchemists and two potions, and established the relationships through `AlchemistPotion` objects, which include additional data about the relationship.

When querying, we can now access the association objects and get details about the relationship, not just the related objects. We can also use our convenience properties to directly access the related objects without going through the association table.

This approach gives us much more flexibility in how we define and use many-to-many relationships. It allows us to store additional data about the relationship, and to define methods on the association class for manipulating this data.

## Summary

In this section, we've learned about the four main types of relationships in SQLAlchemy: one-to-many, one-to-one, many-to-one, and many-to-many. We've seen how to define each type of relationship, and how to use the relationships to access related objects.

Here's a quick summary:

- **One-to-many**: One record in table A can be associated with multiple records in table B. Example: One alchemist can have many labs.
- **One-to-one**: One record in table A can be associated with exactly one record in table B, and vice versa. Example: One alchemist has one familiar.
- **Many-to-one**: Multiple records in table A can be associated with one record in table B. Example: Many ingredients can belong to one category.
- **Many-to-many**: Multiple records in table A can be associated with multiple records in table B. Example: Many alchemists can create many potions.
- **Association tables**: Used to establish many-to-many relationships. Can be simple tables with just foreign keys, or full models with additional attributes.

> **Note:** Understanding these relationship types and how to define them is crucial for building effective data models with SQLAlchemy. The ability to express these relationships in code, and to easily navigate between related objects, is one of the key benefits of using an ORM like SQLAlchemy.