Difficulty: Intermediate
Database relationships model how different entities connect to each other. In an ORM like SQLAlchemy, relationships let you navigate between related objects using Python attributes instead of writing JOIN queries. Understanding one-to-many, many-to-many, and one-to-one relationships is essential for data modeling.
A one-to-many relationship means one record in table A relates to multiple records in table B. For example, one user has many posts. In SQLAlchemy, you define this with a ForeignKey column on the 'many' side and a relationship() on the 'one' side. The ForeignKey column stores the ID of the related record. The relationship() provides attribute access to related objects.
The db.relationship() function creates a property that loads related objects. On the User model, posts = db.relationship('Post', backref='author') creates two things: user.posts returns a list of the user's posts, and post.author returns the post's author user. The backref parameter automatically creates the reverse relationship.
A many-to-many relationship means records in table A relate to multiple records in table B, and vice versa. For example, posts and tags: a post can have many tags, and a tag can be on many posts. This requires an association table (also called a junction table) that stores pairs of foreign keys. SQLAlchemy uses db.Table() for this.
Relationship loading strategies affect performance. lazy='select' (default) loads related objects when you access them (lazy loading). lazy='joined' loads them with a JOIN in the initial query (eager loading). lazy='subquery' uses a subquery. lazy='dynamic' returns a query object you can filter further. Choose based on your access patterns.
The cascade parameter controls what happens to related objects when the parent is deleted. cascade='all, delete-orphan' deletes child records when the parent is deleted. This is important for maintaining referential integrity. Without it, deleting a user would leave orphaned posts with invalid foreign keys.
One-to-one relationships are a special case of one-to-many with uselist=False. This tells SQLAlchemy that the relationship returns a single object, not a list. For example, a user might have one profile: profile = db.relationship('Profile', backref='user', uselist=False).
from app.extensions import db
from datetime import datetime
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False)
# One user has many posts
posts = db.relationship('Post', backref='author', lazy='dynamic',
cascade='all, delete-orphan')
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(200), nullable=False)
body = db.Column(db.Text)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
# Foreign key to users table
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
# Usage:
# user = User(username='alice')
# post = Post(title='Hello', body='World', author=user)
# db.session.add(user)
# db.session.commit()
# user.posts.all() # [<Post Hello>]
# post.author # <User alice>The ForeignKey on Post.user_id links posts to users. The relationship on User.posts lets you access all posts by a user. backref='author' creates Post.author automatically. cascade ensures deleting a user deletes their posts.
from app.extensions import db
# Association table (no model class needed)
post_tags = db.Table('post_tags',
db.Column('post_id', db.Integer, db.ForeignKey('post.id'), primary_key=True),
db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True)
)
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(200), nullable=False)
# Many posts can have many tags
tags = db.relationship('Tag', secondary=post_tags, lazy='subquery',
backref=db.backref('posts', lazy=True))
class Tag(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), unique=True, nullable=False)
# Usage:
# python_tag = Tag(name='Python')
# post = Post(title='Flask Tutorial')
# post.tags.append(python_tag)
# db.session.add(post)
# db.session.commit()
# post.tags # [<Tag Python>]
# python_tag.posts # [<Post Flask Tutorial>]The association table post_tags stores (post_id, tag_id) pairs. The secondary parameter on db.relationship() points to this table. Both sides can navigate the relationship: post.tags and tag.posts.
from app.extensions import db
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
# One user has one profile
profile = db.relationship('Profile', backref='user', uselist=False,
cascade='all, delete-orphan')
class Profile(db.Model):
id = db.Column(db.Integer, primary_key=True)
bio = db.Column(db.Text)
avatar_url = db.Column(db.String(300))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), unique=True)
# Usage:
# user = User(username='alice')
# user.profile = Profile(bio='Developer', avatar_url='/img/alice.png')
# db.session.add(user)
# db.session.commit()
# user.profile # <Profile object>
# user.profile.bio # 'Developer'uselist=False makes the relationship return a single object instead of a list. The unique=True on the foreign key column enforces the one-to-one constraint at the database level.
from app.extensions import db
from datetime import datetime
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
def to_dict(self):
"""Serialize the user to a dictionary."""
return {
'id': self.id,
'username': self.username,
'email': self.email,
'created_at': self.created_at.isoformat()
}
@staticmethod
def find_by_email(email):
return User.query.filter_by(email=email).first()
def __repr__(self):
return f'<User {self.username}>'Models can have custom methods. to_dict() serializes the object for JSON API responses. Static methods like find_by_email encapsulate common queries. __repr__ gives a useful string representation for debugging.
One-to-Many, Many-to-Many, ForeignKey, db.relationship, backref, Association Table