Many-to-Many Relationships with SQLAlchemy Association Tables

A many-to-many relationship allows multiple records on both sides to be associated. For example, many books can have multiple authors, and many authors can write multiple books. SQLAlchemy implements this using an association table (junction table) that holds foreign keys to both sides of the relationship.

Understanding Association Tables

At the database level, a many-to-many relationship requires a third table called an association table or junction table. For books and authors, it might look like:

authors (id, name)
books (id, title)
book_author (book_id, author_id)  <- association table

The book_author table holds pairs of book and author IDs. A single row in book_author means "this book has this author." Many rows can share the same book ID (one book, many authors) or the same author ID (one author, many books).

Simple Association with secondary Parameter

The simplest many-to-many relationship uses SQLAlchemy's secondary parameter, which specifies the association table. Define the table outside the models:

from sqlalchemy import Column, Integer, String, ForeignKey, Table, create_engine
from sqlalchemy.orm import declarative_base, relationship, Session

Base = declarative_base()

# Association table (no class, just metadata)
book_author = Table(
    "book_author",
    Base.metadata,
    Column("book_id", Integer, ForeignKey("books.id"), primary_key=True),
    Column("author_id", Integer, ForeignKey("authors.id"), primary_key=True),
)

class Author(Base):
    __tablename__ = "authors"
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    
    # Many authors can write many books
    books = relationship(
        "Book",
        secondary=book_author,
        back_populates="authors"
    )
    
    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)
    
    # Many books can have many authors
    authors = relationship(
        "Author",
        secondary=book_author,
        back_populates="books"
    )
    
    def __repr__(self):
        return f"<Book(id={self.id}, title={self.title})>"

The secondary parameter points to the association table. SQLAlchemy uses it to query the junction table and fetch related objects. No primary key is needed on the association table—just two foreign key columns with composite primary key constraints.

Working with Many-to-Many Relationships

Once defined, many-to-many relationships work like lists of objects:

engine = create_engine("sqlite:///library.db")
Base.metadata.create_all(engine)

with Session(engine) as session:
    # Create authors
    austen = Author(name="Jane Austen")
    shelley = Author(name="Mary Shelley")
    
    # Create books
    pride = Book(title="Pride and Prejudice")
    frankenstein = Book(title="Frankenstein")
    
    # Associate authors and books (both directions work)
    pride.authors = [austen]
    frankenstein.authors = [shelley]
    
    # A book can have multiple authors
    pride.authors.append(shelley)
    
    # An author can have multiple books
    austen.books.append(frankenstein)
    
    session.add_all([austen, shelley, pride, frankenstein])
    session.commit()
    
    # Query relationships
    print(pride.authors)  # [<Author(...)>, <Author(...)>]
    print(austen.books)   # [<Book(...)>, <Book(...)>]

Appending to book.authors or author.books inserts a row into the book_author junction table. SQLAlchemy handles the foreign key values automatically.

Association Objects: Many-to-Many with Extra Data

Sometimes you need to store extra information on the association. For example, storing the publication order of a book's authors or the royalty percentage each author receives. Use an association object—a class that represents the junction table:

from sqlalchemy import Column, Integer, String, ForeignKey, Float
from sqlalchemy.orm import relationship

class AuthorBook(Base):
    __tablename__ = "book_author"
    
    # Composite primary key: (book_id, author_id)
    book_id = Column(Integer, ForeignKey("books.id"), primary_key=True)
    author_id = Column(Integer, ForeignKey("authors.id"), primary_key=True)
    
    # Extra data on the association
    royalty_percentage = Column(Float, default=0.0)
    publication_order = Column(Integer)
    
    # Relationships to both sides
    book = relationship("Book", back_populates="author_links")
    author = relationship("Author", back_populates="book_links")
    
    def __repr__(self):
        return f"<AuthorBook(book_id={self.book_id}, author_id={self.author_id})>"

class Author(Base):
    __tablename__ = "authors"
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    
    # Link to the association table
    book_links = relationship("AuthorBook", back_populates="author")
    
    # Convenient access to Book objects via association
    books = relationship(
        "Book",
        secondary="book_author",
        back_populates="authors",
        viewonly=True  # Read-only; use book_links to modify
    )

class Book(Base):
    __tablename__ = "books"
    
    id = Column(Integer, primary_key=True)
    title = Column(String(300), nullable=False)
    
    # Link to the association table
    author_links = relationship("AuthorBook", back_populates="book")
    
    # Convenient access to Author objects via association
    authors = relationship(
        "Author",
        secondary="book_author",
        back_populates="books",
        viewonly=True  # Read-only; use author_links to modify
    )

With an association object, you access the junction table directly:

with Session(engine) as session:
    austen = Author(name="Jane Austen")
    pride = Book(title="Pride and Prejudice")
    
    # Create the association with extra data
    link = AuthorBook(
        book=pride,
        author=austen,
        royalty_percentage=15.0,
        publication_order=1
    )
    
    session.add_all([austen, pride, link])
    session.commit()
    
    # Access the association object
    for link in pride.author_links:
        print(f"{link.author.name} gets {link.royalty_percentage}% royalty")

The viewonly=True parameter prevents modifying the relationship via book.authors.append(); instead, create new AuthorBook objects explicitly. This ensures extra data (royalty_percentage, publication_order) is always present.

Querying Many-to-Many Relationships

Query many-to-many relationships using filters and joins:

from sqlalchemy import and_

with Session(engine) as session:
    # Books by a specific author
    austen_books = session.query(Book).join(Book.authors).filter(
        Author.name == "Jane Austen"
    ).all()
    
    # Authors of a specific book
    pride_authors = session.query(Author).join(Author.books).filter(
        Book.title == "Pride and Prejudice"
    ).all()
    
    # Books with multiple authors
    books_with_many_authors = session.query(Book).join(Book.authors).group_by(
        Book.id
    ).having(func.count(Author.id) > 1).all()

When querying through a many-to-many relationship, use .join() to connect through the association table.

Cascading Many-to-Many

Cascade rules on many-to-many relationships control whether deleting one side deletes the association (but usually not the other side):

class Book(Base):
    __tablename__ = "books"
    
    id = Column(Integer, primary_key=True)
    title = Column(String(300), nullable=False)
    
    # cascade="all, delete" deletes associations but not authors
    authors = relationship(
        "Author",
        secondary=book_author,
        cascade="all, delete",
        back_populates="books"
    )

With cascade="all, delete", deleting a book deletes its associations in the junction table but leaves authors intact. Rarely use delete-orphan on many-to-many relationships.

Key Takeaways

• Many-to-many requires an association table (junction table) with foreign keys to both sides.
• Simple association: Use secondary=junction_table parameter when no extra data is needed.
• Association objects: Create a class representing the junction table to store extra data (timestamps, percentages, order).
• viewonly=True: Mark convenience relationships as read-only if you use association objects.
• Querying: Use .join() to navigate through the association table.

Frequently Asked Questions

How do I add extra data to a many-to-many relationship?

Use an association object—a model class representing the junction table with its own relationships and extra columns. Access it via book.author_links instead of book.authors.

Can I use both simple secondary and association objects in the same relationship?

Not cleanly. Choose one or the other. If you need extra data, use association objects. For simple many-to-many (no metadata), use secondary.

What happens if I delete an author—do their books get deleted?

No. By default, the association entries are deleted, but the books remain. If you want to delete books too, use cascade="delete" on the Book.authors relationship, but this is unusual.

How do I query books with a specific number of authors?

Use group_by() with having() to filter on aggregates:

session.query(Book).join(Book.authors).group_by(Book.id).having(
    func.count(Author.id) == 2
).all()

Can a book be associated with the same author twice?

With a composite primary key on the association table (book_id, author_id), no—the database prevents duplicate rows. This is the desired behavior.

Further Reading

• SQLAlchemy Many-to-Many Relationships
• Association Objects
• Relationship Configuration Options
• Cascades and Relationship Behavior