Flask-SQLAlchemy Many-to-Many Relationships: Association Tables and Association Objects

SQLAlchemy provides two tools for managing many-to-many relationships: Association Tables and Association Objects. This blog will review how to use association tables and association objects in a Flask application using the Flask-SQLAlchemy extension.

Association Tables

An association table, like a basic join table in SQLlite, is a database table that is used to keep track of the relationships between two other tables. An association tables does not contain any information besides what is necessary to manage those relationships.

Say we have two models, Teacher and Student, that have a many-to-many relationship. Each teacher has many students, and each student has many teachers. Their relationship can be managed with an association table, teacher_students. The only columns that teacher_students contains are the columns that refer to the tables for Teacher and Student. teacher_students cannot be used to manage any additional information.

The relationship between Teacher and Student is created by making columns in teacher_students that contain foreign keys referring to the id columns of the Teacher and Student tables.

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import MetaData
from sqlalchemy.ext.associationproxy import association_proxy

metadata = MetaData(naming_convention={
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
})

db = SQLAlchemy(metadata=metadata)

teacher_students = db.Table(
    'teacher_students',
    metadata,
    db.Column('teacher_id', db.ForeignKey('teachers.id'), 
        primary_key=True),
    db.Column('student_id', db.ForeignKey('students.id'), 
        primary_key=True)
)

It isn't necessary to specify the data type of the columns in teacher_students because the type of data in each column is inferred from the type of data in the columns of the Teacher table and Student table.

Regarding the model columns that should be used in association table:

It is also recommended, though not in any way required by SQLAlchemy, that the columns which refer to the two entity tables are established within either a unique constraint or more commonly as the primary key constraint; this ensures that duplicate rows won’t be persisted within the table regardless of issues on the application side.
https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#many-to-many

To create the many-to-many relationship between Teacher and Student, each model is given a relationship() attribute that links them to each other. Teacher is given a students attribute and Student is given a teachers attribute. The connection between the models and teacher_students is provided by the secondary parameter in each model's relationship attribute, which points to the association table.

class Teacher(db.Model):
    __tablename__ = 'teachers'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    students = db.relationship('Student', secondary=teacher_students,
        back_populates='teachers')

    def __repr__(self):
        return f'<Teacher {self.id}>'

class Student(db.Model):
    __tablename__ = 'students'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    teachers = db.relationship('Teacher', secondary=teacher_students,     
        back_populates='students')

    def __repr__(self):
        return f'<Student {self.id}>'

Additional details and information about the secondary parameter can be found here:
https://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.relationship.params.secondary

To see the students that a teacher has or the teachers that a student has, the students and teachers attributes can be called on instances of the Teacher and Student models:

>>> t1 = Teacher.query.first()
>>> t1
<Teacher 1>
>>> t1.students
[<Student 1>, <Student 3>, <Student 6>]

>>> s1 = Student.query.first()
>>> s1
<Student 1>
>>> s1.teachers
[<Teacher 1>, <Teacher 2>]

Association Objects

What if, instead of only keeping track of the relationships between Teacher and Student instances, we wanted the join table to also keep track of the exams that each teacher administers and each student takes. In that case, an association object would be used instead of an association table because it is necessary for the join table to hold extra information. In other words, when the join table needs to contain more columns than just the foreign keys from other tables, an association object should be used.

The association object pattern is a variant on many-to-many: it’s used when an association table contains additional columns beyond those which are foreign keys to the parent and child (or left and right) tables, columns which are most ideally mapped to their own ORM mapped class.
https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#association-object

In the example above, there are models for Teacher and Student. To create an association object that keeps track of exams, along with the relationships between Teacher and Student instances, there needs to be a third model, Exam, which acts as the association object. Like the association table used above, the Exam model's table is still given columns to keep track of the foreign keys of the many-to-many relationship models, but it can also contain columns for whatever other information is needed to be stored in the table.

class Exam(db.Model):
    __tablename__ = 'exams_table'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    teacher_id = db.Column(db.Integer, db.ForeignKey('teachers.id'))
    student_id = db.Column(db.Integer, db.ForeignKey('students.id'))

    teacher = db.relationship('Teacher', back_populates='exams')
    student = db.relationship('Student', back_populates='exams')

    def __repr__(self):
        return f'<Exam {self.id}>'

Instead of the Teacher and Student models having a direct, many-to-many relationship, they will now have one-to-many relationships with the Exam association object.

In the association object pattern, the relationship.secondary parameter is not used; instead, a class is mapped directly to the association table. Two individual relationship() constructs then link first the parent side to the mapped association class via one to many, and then the mapped association class to the child side via many-to-one, to form a uni-directional association object relationship from parent, to association, to child. For a bi-directional relationship, four relationship() constructs are used to link the mapped association class to both parent and child in both directions.
https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#association-object

Instead of Teacher having a students attribute, it has an exams attribute, while the Student model has its own exams attribute.

class Teacher(db.Model):
    __tablename__ = 'teachers'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    exams = db.relationship('Exam', back_populates='teacher')

    students = association_proxy('exams', 'student')

    def __repr__(self):
        return f'<Teacher {self.id}>'

class Student(db.Model):
    __tablename__ = 'students'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    exams = db.relationship('Exam', back_populates='student')

    teachers = association_proxy('exams', 'teacher')

    def __repr__(self):
        return f'<Student {self.id}>'

This means that, unlike with an association table, the relationship between Teacher and Student cannot be accessed directly from a relationship object in either model. To make it easier to view the relationship between Teacher and Student now that they are linked by an association object, SQLAlchemy provides the Association Proxy tool.

To enhance the association object pattern such that direct access to the Association object is optional, SQLAlchemy provides the Association Proxy extension. This extension allows the configuration of attributes which will access two “hops” with a single access, one “hop” to the associated object, and a second to a target attribute.
https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#association-object

The association proxies in the above example (students in the Teacher model and teachers in the Student model) are provided (1) the name of the immediate target that is being used to manage the many-to-many relationship and (2) an attribute that is available in an instance of that target. In this example the immediate target is the relationship attribute exams, which is the variable used to establish the one-to-many relationships between the Teacher and Student models and the Exam association object. The attribute given to association_proxy() is the corresponding relationship variable in the Exam association object.

Now, the Teacher and Student models each have way to view the one-to-many relationship with the Exam association object, as well as a way to directly view the many-to-many relationships through the association proxies. Teacher's association proxy provides the value of the students associated with it while concealing, or skipping over, the association object.

>>> t1 = Teacher.query.first()
>>> t1
<Teacher 1>
>>> t1.exams
[<Exam 1>, <Exam 2>, <Exam 3>]
>>> t1.students
[<Student 1>, <Student 3>, <Student 6>]

By default, calling a model's association proxy attribute will return instances of the target object. So, calling Teacher.students returns a list of all the instances of Student that have a relationship with a particular instance of Teacher.

Additional information about using association proxies can be found here:

https://docs.sqlalchemy.org/en/20/orm/extensions/associationproxy.html