Flask Relationships

A relationship links two database tables together. Flask-SQLAlchemy defines relationships in Python using db.relationship() and foreign keys. Once set up, you navigate between related objects using dot notation instead of writing JOIN queries.

Types of Relationships

One-to-Many: Author and Posts

class Author(db.Model):
    id    = db.Column(db.Integer, primary_key=True)
    name  = db.Column(db.String(100), nullable=False)
    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), nullable=False)
    author_id = db.Column(db.Integer, db.ForeignKey('author.id'), nullable=False)

Author table:              Post table:
┌────┬───────┐             ┌────┬────────────────┬───────────┐
│ id │ name  │             │ id │ title          │ author_id │
├────┼───────┤             ├────┼────────────────┼───────────┤
│  1 │ Alice │             │  1 │ Flask Basics   │     1     │
│  2 │ Bob   │             │  2 │ SQLAlchemy     │     1     │
└────┴───────┘             │  3 │ Python Tips    │     2     │
                           └────┴────────────────┴───────────┘

Key Parts Explained

• db.ForeignKey('author.id') — stores the linked Author's ID in the Post table
• db.relationship('Post', backref='author') — tells SQLAlchemy to load related posts; backref='author' automatically adds post.author on the Post side
• lazy=True — loads posts from the database only when you first access author.posts

Using the Relationship

# Create linked records
alice = Author(name='Alice')
db.session.add(alice)
db.session.commit()

post1 = Post(title='Flask Basics', author_id=alice.id)
post2 = Post(title='SQLAlchemy',   author_id=alice.id)
db.session.add_all([post1, post2])
db.session.commit()

# Access posts through the author object
author = Author.query.get(1)
for post in author.posts:
    print(post.title)

# Access author through a post
post = Post.query.get(1)
print(post.author.name)   # 'Alice'

Many-to-Many: Students and Courses

Many-to-many relationships need an association table — a middle table that holds pairs of IDs from both sides.

enrollment = db.Table('enrollment',
    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)
    name    = db.Column(db.String(100))
    courses = db.relationship('Course', secondary=enrollment, backref='students')

class Course(db.Model):
    id    = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200))

student.courses  → list of Course objects
course.students  → list of Student objects (from backref)

# Enroll a student in courses
alice = Student(name='Alice')
flask_course  = Course(title='Flask')
python_course = Course(title='Python')

alice.courses.append(flask_course)
alice.courses.append(python_course)
db.session.add(alice)
db.session.commit()

# List Alice's courses
for c in alice.courses:
    print(c.title)

One-to-One: User and Profile

class User(db.Model):
    id      = db.Column(db.Integer, primary_key=True)
    email   = db.Column(db.String(120), unique=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'), unique=True)

uselist=False tells SQLAlchemy to return a single object instead of a list. Without it, user.profile returns a list — with it, it returns one Profile object or None.

Lazy Loading Options

Summary

SQLAlchemy relationships link tables using foreign keys and db.relationship(). One-to-many is the most common — define a foreign key on the "many" side and a relationship on the "one" side. Many-to-many needs an association table. One-to-one uses uselist=False. Backref automatically adds the reverse relationship so both sides navigate to each other using dot notation.