Many to many relationships in SQLAlchemy models (Flask)

Jul 28th, 2017 in  by Michael Cho

Building model definitions for many-to-many relationships via a secondary table.

Many-to-many relationships are fairly common in many (ahem) applications, and this is to remind myself how to create them - I always seem to forget! These are known as has_many :through associations in the Ruby on Rails world, and are needed whenever any Object A can have many Object B records, and any Object B can also be associated with many Object A records.

One real-world example would be an ecommerce site with database models for Users and Products - a User can have many Products through a secondary table (ie Orders), and a Product can be purchased by many Users through the same Orders table.

In this case, the concept of an "order" which associates users with products is fairly well known - but in situations where this does not apply, it is common to name this secondary table as a combination of both bodels (ie "UserProducts" if the concept of "Orders" is not commonly known). 

Here's how to define it in Flask / SQLAlchemy models.

1. Define your User model

This could look like:

from sqlalchemy.orm import relationship

class User(BaseModel):
    __tablename__ = 'users'

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

    # ... any other fields

    create_dttm = db.Column(db.DateTime, server_default=db.func.current_timestamp())

    products = relationship("Product", secondary="orders")

2. Define the Product model

from sqlalchemy.orm import relationship

class Product(BaseModel):
    __tablename__ = 'products'

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

    # ... any other fields

    create_dttm = db.Column(db.DateTime, default=datetime.utcnow)

    users = relationship("User", secondary="orders")

3. And finally, the association table

from sqlalchemy.orm import relationship, backref

class Order(BaseModel):
    __tablename__ = 'orders'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey(''))
    product_id = db.Column(db.Integer, db.ForeignKey(''))

    # ... any other fields

    dttm = db.Column(db.DateTime, default=datetime.utcnow)

    user = relationship(User, backref=backref("orders", cascade="all, delete-orphan"))
    product = relationship(Product, backref=backref("orders", cascade="all, delete-orphan"))

4. Usage

Once these models have been defined and imported (and presumably your database tables have been created), you can use them easily:

user = User.query.first()
user.products  # List all products, eg [<productA>, <productB> ]
user.orders    # List all orders, eg [<order1>, <order2>]
user.orders[0].products  # List products from the first order

p1 = Product.query.first()
p1.users  # List all users who have bought this product, eg [<user1>, <user2>]

Other articles you may like

SQLAlchemy commit(), flush(), expire(), refresh(), merge() - what's the difference?
Nov 2nd, 2017
Prioritized Code Review Checklist - what to look for first, second, and last
Sep 21st, 2017
Bash script to relink alembic migrations
Jun 12th, 2017
Using model callbacks in SQLAlchemy to generate slugs
Feb 7th, 2017
Overriding default Jinja dictionary attributes in Python view templates
Jan 29th, 2017