TopolearnRelationships let you work with user.posts instead of hand-joining ids, and their cascade settings decide what happens on save and delete.
A database link needs a foreign key column on the "many" side: user_id: Mapped[int] = mapped_column(ForeignKey("users.id")). The ORM adds `relationship()` attributes on top so you can navigate between objects:
- On
User:posts: Mapped[list["Post"]] = relationship(back_populates="author"). - On
Post:author: Mapped["User"] = relationship(back_populates="posts").
The Mapped[list[...]] annotation makes it a collection (one-to-many); a plain class makes it a single object (many-to-one). back_populates names the attribute on the other side and keeps both sides in sync in Python: appending a post to user.posts sets post.author, before anything is flushed.
When you add the user to the session, related posts are added automatically through the default save-update cascade, and the foreign key values are filled in at flush time. You never set post.user_id by hand.
Deletion needs a decision. By default, deleting a user sets user_id to NULL on its posts (which fails if the column is NOT NULL). Use cascade="all, delete-orphan" on the parent's collection when children can't exist without their parent: deleting the user deletes its posts, and removing a post from user.posts deletes it.
Accessing user.posts on a loaded object runs a SELECT the first time: relationships are lazy-loaded by default. Loading strategies are covered in their own lesson.
Example
from sqlalchemy import ForeignKey, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session
class Base(DeclarativeBase): pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
posts: Mapped[list["Post"]] = relationship(back_populates="author",
cascade="all, delete-orphan")
class Post(Base):
__tablename__ = "posts"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str]
user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
author: Mapped[User] = relationship(back_populates="posts")
engine = create_engine("sqlite://"); Base.metadata.create_all(engine)
with Session(engine) as s:
ada = User(name="ada", posts=[Post(title="Engines"), Post(title="Notes")])
s.add(ada) # posts cascade in automatically
s.commit()
print(ada.posts[0].author.name, ada.posts[0].user_id) # ada 1
s.delete(ada); s.commit()
print(s.scalars(select(Post)).all()) # []Remember
- ForeignKey column on the many side, relationship() on both sides
- back_populates keeps both directions in sync in memory
- Related objects are added via save-update cascade; FK values are set at flush
- cascade='all, delete-orphan' when children belong to the parent
Watch out for
- Setting the foreign key id by hand and the relationship as well, then getting confused when they disagree.