SQLAlchemy Relationships: One-to-Many & Reverse Lookups
A one-to-many relationship connects two models where one parent record has multiple child records. For example, one author can write many books. SQLAlchemy uses ForeignKey to create the relationship at the database level and the relationship() function to enable Pythonic navigation between objects.
Understanding One-to-Many Relationships
In a one-to-many relationship, the "many" side holds the foreign key. A book references its author via author_id, but an author has no direct column storing book IDs. Instead, SQLAlchemy's relationship() function creates a Python attribute that dynamically queries related books:
from sqlalchemy import Column, Integer, String, ForeignKey, DateTime
from sqlalchemy.orm import declarative_base, relationship
from datetime import datetime
Base = declarative_base()
class Author(Base):
__tablename__ = "authors"
id = Column(Integer, primary_key=True)
name = Column(String(100), nullable=False)
# relationship() enables author.books (queries books with this author_id)
books = relationship("Book", back_populates="author")
def __repr__(self):
return f"<Author(id={self.id}, name={self.name})>"
class Book(Base):
__tablename__ = "books"
id = Column(Integer, primary_key=True)
title = Column(String(300), nullable=False)
# Foreign key: stores author_id in the database
author_id = Column(Integer, ForeignKey("authors.id"), nullable=False)
# relationship() provides book.author (the Author object)
author = relationship("Author", back_populates="books")
def __repr__(self):
return f"<Book(id={self.id}, title={self.title}, author_id={self.author_id})>"
The back_populates argument creates a two-way relationship. author.books returns all Book objects with that author, and book.author returns the Author object. Without back_populates, you'd need to query manually.
Accessing Related Objects
Once defined, relationships enable intuitive object access:
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
engine = create_engine("sqlite:///library.db")
Base.metadata.create_all(engine)
with Session(engine) as session:
# Create an author
author = Author(name="Jane Austen")
session.add(author)
session.commit()
# Create books and assign the author object (not just author_id)
book1 = Book(title="Pride and Prejudice", author=author)
book2 = Book(title="Emma", author=author)
session.add_all([book1, book2])
session.commit()
# Access related books via the relationship
print(author.books) # [<Book(...)>, <Book(...)>]
# Access the parent via the reverse relationship
print(book1.author.name) # "Jane Austen"
By assigning the author object directly to the book, SQLAlchemy automatically sets author_id to the author's ID. This is more Pythonic than managing foreign key IDs manually.
Cascade Operations
Cascade rules control what happens to child records when the parent is deleted or modified. By default, deleting an author does not delete associated books; instead, the foreign key constraint blocks the deletion. Use cascade to automate this behavior:
from sqlalchemy.orm import relationship
class Author(Base):
__tablename__ = "authors"
id = Column(Integer, primary_key=True)
name = Column(String(100), nullable=False)
# cascade="all, delete" deletes books when author is deleted
books = relationship(
"Book",
back_populates="author",
cascade="all, delete"
)
Common cascade options:
| Cascade | Behavior |
|---|---|
save-update | Add new related objects to the session when adding the parent (default). |
delete | Delete child objects when the parent is deleted. |
delete-orphan | Delete children if they are no longer referenced (stronger than delete). |
merge | Merge related objects when merging the parent. |
expunge | Remove child from session when parent is removed. |
all | Include save-update, merge, expunge, and refresh-expire. |
Use cascade="all, delete-orphan" for strong parent-child ownership (e.g., a user and their addresses). Use cascade="all, delete" when deleting a parent should clean up children. Use no cascade (the default) for loosely coupled relationships (e.g., an order and its items—items might belong to an inventory).
Lazy Loading Strategies
By default, accessing author.books triggers a query to the database (lazy loading). For performance-critical code, specify how relationships are loaded:
from sqlalchemy.orm import relationship
class Author(Base):
__tablename__ = "authors"
id = Column(Integer, primary_key=True)
name = Column(String(100), nullable=False)
# lazy="joined": load books in the same query as author
books = relationship(
"Book",
back_populates="author",
lazy="joined"
)
Lazy loading strategies:
| Strategy | Behavior |
|---|---|
select (default) | Load on access (separate query). |
joined | Load via JOIN in the same query. |
selectin | Load in a separate query with IN clause. |
raise | Raise an error if accessed outside the session. |
dynamic | Return a Query object for manual filtering. |
For now, use the default lazy="select". We'll revisit loading strategies in the eager loading article.
Many-to-One from the Child Side
From the child's perspective (Book), the relationship is many-to-one: many books reference one author. Define this relationship on the child:
class Book(Base):
__tablename__ = "books"
id = Column(Integer, primary_key=True)
title = Column(String(300), nullable=False)
author_id = Column(Integer, ForeignKey("authors.id"), nullable=False)
# Many-to-one: book.author is a single Author object
author = relationship("Author", back_populates="books")
With back_populates, you don't need to define the relationship twice. Define it once on the parent and once on the child, and SQLAlchemy connects them. Without back_populates, you'd use backref:
# Alternative: use backref instead of back_populates
author = relationship("Author", backref="books")
Both approaches work; back_populates is more explicit and is recommended in modern SQLAlchemy.
Querying Related Objects
Once a relationship is fetched, you can filter related objects:
with Session(engine) as session:
author = session.query(Author).filter_by(name="Jane Austen").first()
# author.books is a list; filter it with Python
austen_books = [b for b in author.books if "Pride" in b.title]
# Or query directly (more efficient)
pride = session.query(Book).filter_by(
author_id=author.id,
title=Book.title.contains("Pride")
).first()
Key Takeaways
- One-to-many: One parent has many children. The child holds the foreign key.
relationship()enables bidirectional object navigation (parent → children, child → parent).back_populatescreates two-way relationships; define it on both parent and child.- Cascade controls whether deleting a parent deletes children (use
cascade="all, delete-orphan"for owned relationships). - Lazy loading defaults to
select(on-demand queries); optimize later withjoinedorselectin.
Frequently Asked Questions
What's the difference between backref and back_populates?
Both create bidirectional relationships. backref defines it once on one side; back_populates requires explicit definition on both. back_populates is more explicit and recommended for clarity.
If I delete an author, what happens to their books?
By default, deletion is blocked (referential integrity). With cascade="delete", books are also deleted. With cascade="delete-orphan", books are deleted only if no other object references them.
How do I query an author with all their books in one query?
Set lazy="joined" on the relationship, or use .options(joinedload(Author.books)) in your query. See the eager loading article for details.
Can a book have multiple authors?
Not with a one-to-many relationship. Use a many-to-many relationship (covered in the next article) if books can have multiple authors.
What if I need to access the author ID without loading the Author object?
Just use book.author_id directly—no query needed. SQLAlchemy doesn't query the relationship unless you access book.author.