TopolearnTags, roles, memberships and enrollments are all many-to-many, and choosing the right pattern up front avoids painful refactors.
A many-to-many link needs an association table holding two foreign keys. If the link carries no extra data, define it as a plain Core Table and pass it as secondary:
tags: Mapped[list["Tag"]] = relationship(secondary=post_tags, back_populates="posts")
The ORM then manages the association rows for you: post.tags.append(tag) inserts a row, removing the tag deletes it. You never map the association table as a class.
When the link has its own columns, like a membership's role or joined_at, use the association object pattern instead: map the association table as a class (Membership) with a many-to-one relationship to each side, and give each side a one-to-many relationship to Membership. You then create Membership(user=u, group=g, role="admin") explicitly. To keep a convenient user.groups view as well, the association_proxy extension can expose the far side through the association objects.
Make the association table's primary key the pair of foreign keys so the same link can't be stored twice. Deleting a Post removes its rows from a secondary table automatically; deleting the tags themselves is up to you.
Starting with the simple secondary form and later needing link data is a common migration, so ask early whether the relationship itself has attributes.
Example
from sqlalchemy import Column, ForeignKey, Table, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session
class Base(DeclarativeBase): pass
post_tags = Table("post_tags", Base.metadata,
Column("post_id", ForeignKey("posts.id"), primary_key=True),
Column("tag_id", ForeignKey("tags.id"), primary_key=True))
class Post(Base):
__tablename__ = "posts"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str]
tags: Mapped[list["Tag"]] = relationship(secondary=post_tags, back_populates="posts")
class Tag(Base):
__tablename__ = "tags"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
posts: Mapped[list[Post]] = relationship(secondary=post_tags, back_populates="tags")
engine = create_engine("sqlite://"); Base.metadata.create_all(engine)
with Session(engine) as s:
py, db = Tag(name="python"), Tag(name="db")
s.add_all([Post(title="ORM", tags=[py, db]), Post(title="Pandas", tags=[py])]); s.commit()
print(sorted(p.title for p in py.posts)) # ['ORM', 'Pandas']
print(s.scalars(select(Post.title).join(Post.tags).where(Tag.name == "db")).all()) # ['ORM']Remember
- secondary=<Table> for plain many-to-many links
- The ORM inserts and deletes association rows as you edit the lists
- Use an association object class when the link has its own columns
- Make the two foreign keys the association table's primary key
Watch out for
- Adding a column to a secondary table and finding there is no way to set it through the relationship.