Many-to-many relationships in Flask

Introduction

Many-to-many relationships let you connect two things where each can have many of the other. For example, a student can join many classes, and each class can have many students.

You want to link users and roles where each user can have multiple roles and each role can belong to many users.

You have books and authors, where each book can have several authors and each author can write many books.

You manage tags and blog posts, where each post can have many tags and each tag can be on many posts.

Syntax

Flask

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

association_table = db.Table('association',
    db.Column('left_id', db.Integer, db.ForeignKey('left.id')),
    db.Column('right_id', db.Integer, db.ForeignKey('right.id'))
)

class Left(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    rights = db.relationship('Right', secondary=association_table, back_populates='lefts')

class Right(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    lefts = db.relationship('Left', secondary=association_table, back_populates='rights')

The db.Table creates a helper table to link two models.

The secondary argument tells SQLAlchemy which table connects the two sides.

Examples

This example links students and courses so each student can enroll in many courses and each course can have many students.

Flask

association_table = db.Table('student_course',
    db.Column('student_id', db.Integer, db.ForeignKey('student.id')),
    db.Column('course_id', db.Integer, db.ForeignKey('course.id'))
)

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    courses = db.relationship('Course', secondary=association_table, back_populates='students')

class Course(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    students = db.relationship('Student', secondary=association_table, back_populates='courses')

This example connects blog posts and tags so each post can have many tags and each tag can belong to many posts.

Flask

tags_posts = db.Table('tags_posts',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
    db.Column('post_id', db.Integer, db.ForeignKey('post.id'))
)

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    posts = db.relationship('Post', secondary=tags_posts, back_populates='tags')

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tags = db.relationship('Tag', secondary=tags_posts, back_populates='posts')

Sample Program

This Flask app creates authors and books linked many-to-many. It shows how to add books to authors and print their relationships.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

association_table = db.Table('association',
    db.Column('author_id', db.Integer, db.ForeignKey('author.id')),
    db.Column('book_id', db.Integer, db.ForeignKey('book.id'))
)

class Author(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    books = db.relationship('Book', secondary=association_table, back_populates='authors')

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    authors = db.relationship('Author', secondary=association_table, back_populates='books')

with app.app_context():
    db.create_all()

    a1 = Author(name='Alice')
    a2 = Author(name='Bob')
    b1 = Book(title='Flask Basics')
    b2 = Book(title='Advanced Flask')

    a1.books.append(b1)  # Alice wrote Flask Basics
    a2.books.extend([b1, b2])  # Bob wrote both books

    db.session.add_all([a1, a2, b1, b2])
    db.session.commit()

    # Print authors and their books
    for author in Author.query.order_by(Author.name):
        book_titles = ', '.join(book.title for book in author.books)
        print(f"{author.name} wrote: {book_titles}")

Important Notes

Always create the association table before defining relationships.

Use back_populates on both sides to keep relationships in sync.

Remember to commit your session to save changes to the database.

Summary

Many-to-many relationships connect two models where each can have many of the other.

Use an association table with db.Table to link the two models.

Define relationships with secondary and back_populates for easy access both ways.