How to Use relationship in Flask-SQLAlchemy: Syntax and Examples

In Flask-SQLAlchemy, use the relationship() function inside a model class to define how it connects to another model, usually paired with ForeignKey. This creates a link that lets you access related objects easily, like accessing all posts of a user with user.posts.

Syntax

The relationship() function is used inside a model class to define a link to another model. It usually pairs with a ForeignKey in the related model to establish the connection.

• relationship('ModelName'): The name of the related model as a string.
• backref='name': Creates a shortcut to access the original model from the related model.
• lazy='select': Controls when related items are loaded (default is 'select').

python

class Parent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    children = db.relationship('Child', backref='parent', lazy=True)

class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('parent.id'))

Example

This example shows two models, User and Post, where each post belongs to one user, and each user can have many posts. The relationship() allows accessing all posts of a user easily.

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, 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(120), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

with app.app_context():
    db.create_all()
    user = User(username='alice')
    db.session.add(user)
    db.session.commit()
    post1 = Post(title='First Post', author=user)
    post2 = Post(title='Second Post', author=user)
    db.session.add_all([post1, post2])
    db.session.commit()
    # Access posts from user
    user_posts = [post.title for post in user.posts]
    print(user_posts)

Output

['First Post', 'Second Post']

Common Pitfalls

Common mistakes include:

• Not defining ForeignKey in the related model, which breaks the link.
• Using wrong table or column names in ForeignKey (it must match the target table and column exactly).
• Forgetting to use backref or back_populates to create two-way access.
• Confusing lazy loading options, which can cause unexpected database queries.

python

class Parent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # Missing ForeignKey in Child causes error
    children = db.relationship('Child', backref='parent')

class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # Wrong foreign key name (should be 'parent.id')
    parent_id = db.Column(db.Integer, db.ForeignKey('wrongtable.id'))

# Correct way:
class Parent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    children = db.relationship('Child', backref='parent')

class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('parent.id'))

Quick Reference

Tips for using relationship() in Flask-SQLAlchemy:

• Always pair relationship() with a matching ForeignKey.
• Use backref or back_populates for two-way access between models.
• Choose lazy loading based on your performance needs: lazy='select' loads on access, lazy='joined' loads immediately with a join.
• Access related objects like normal Python attributes (e.g., user.posts).

Key Takeaways

Use relationship() in one model and ForeignKey in the related model to link tables.

The backref parameter creates easy two-way access between related models.

Access related objects as attributes, like user.posts to get all posts of a user.

Ensure ForeignKey references the correct table and column name.

Choose the right lazy loading option to control when related data loads.