Relationships

Relationships

Data in the real world isn't flat — it's connected. Users write posts, posts have comments, comments belong to users. SQLAlchemy lets you define these relationships cleanly so you can navigate between related data without writing join queries by hand.

One-to-Many

The most common relationship. One user has many posts. You set this up with a ForeignKey on the "many" side and a relationship() on the "one" side.

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    posts = db.relationship('Post', backref='author', lazy=True)

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200))
    content = db.Column(db.Text)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

Now you can do user.posts to get all of a user's posts, and post.author to get the user who wrote a post. The backref='author' creates that reverse access automatically.

Many-to-Many

Sometimes both sides have many of the other. Think tags on a post — a post has many tags, and a tag belongs to many posts. You need a helper table (association table) to make this work.

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

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

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True)

The secondary=post_tags parameter tells SQLAlchemy to use that association table to connect posts and tags. Now post.tags and tag.posts both work seamlessly.

One-to-One

Sometimes you need a one-to-one connection — like a user profile that belongs to exactly one user. Set it up like a one-to-many but add uselist=False.

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    profile = db.relationship('Profile', backref='user', uselist=False)

class Profile(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    bio = db.Column(db.Text)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

With uselist=False, accessing user.profile returns a single object instead of a list.

Eager vs Lazy Loading

By default, relationships use lazy loading — related data is fetched only when you access it. This can cause the N+1 query problem where accessing a list of items triggers a separate query for each one's relationships.

# Lazy loading (default) - triggers a query per user
users = User.query.all()
for user in users:
    print(user.posts)  # Each access runs a new query

# Eager loading - fetches everything in one go
users = User.query.options(db.joinedload(User.posts)).all()
for user in users:
    print(user.posts)  # No extra queries

Use eager loading when you know you'll need related data. It's a simple optimization that can make a huge difference.

Joining Queries

When you need to filter based on related data, joins are your friend.

# Get all posts by active users
active_posts = db.session.query(Post).join(User).filter(User.is_active == True).all()

# Get users who have posted
users_with_posts = User.query.join(Post).distinct().all()