SQLAlchemy Relationships

Reviewed & published by Brayan K

A SQLAlchemy relationship is a Python-side link between two models that lets you navigate from one object to its related rows — like reading a user's posts — without writing SQL joins by hand.

Part of the free Flask course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

In this lesson you'll build one-to-many links with ForeignKey and relationship, model many-to-many with an association table, and learn when to load related data lazily versus eagerly.

What You'll Learn in This Lesson

• Defining a ForeignKey column
• Adding relationship() with back_populates
• Navigating one-to-many in both directions
• Building a many-to-many association table
• Understanding lazy vs eager loading (N+1)
• The Flask-SQLAlchemy db.Model equivalent

1 One-to-Many: A User Has Many Posts

The most common relationship is one-to-many: one user writes many posts; each post belongs to one user. You model it with two pieces. The "many" side (Post) gets a ForeignKey column pointing at the "one" side. Then both sides get a relationship() linked by back_populates so you can navigate either way.

The runnable example uses plain SQLAlchemy 2.x with an in-memory SQLite database, so it runs anywhere. After saving a user with two posts, you can read user.posts and post.author.

# models.py — one-to-many with plain SQLAlchemy 2.x (runnable, in-memory sqlite)
from sqlalchemy import create_engine, ForeignKey, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    # the "one" side: a list of posts, kept in sync with Post.author
    posts: Mapped[list["Post"]] = relationship(back_populates="author")

class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(100))
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))   # the FK column
    # the "many" side: each post points back to one user
    author: Mapped["User"] = relationship(back_populates="posts")

engine = create_engine("sqlite://")     # in-memory database
Base.metadata.create_all(engine)

with Session(engine) as s:
    ada = User(name="Ada")
    ada.posts.append(Post(title="Hello"))   # setting one side updates the other
    ada.posts.append(Post(title="World"))
    s.add(ada)
    s.commit()

    user = s.scalars(select(User).where(User.name == "Ada")).first()
    print("Ada's posts:", [p.title for p in user.posts])           # navigate one -> many
    hello = s.scalars(select(Post).where(Post.title == "Hello")).first()
    print("Author of 'Hello':", hello.author.name)                 # navigate many -> one

# ✅ Expected output:
# Ada's posts: ['Hello', 'World']
# Author of 'Hello': Ada

Output: Ada's posts: ['Hello', 'World'] and Author of 'Hello': Ada — the same relationship read from each end.

2 Many-to-Many: Posts and Tags

When each side can have many of the other — a post has many tags, and each tag labels many posts — you need a many-to-many relationship. The database can't store that directly, so you add a third association table holding two foreign keys. You then pass it to relationship(secondary=...) on both sides.

The runnable example wires up a post_tag association table and demonstrates adding tags to a post and listing the posts for a tag.

# models.py — many-to-many through an association table (runnable)
from sqlalchemy import create_engine, ForeignKey, String, Column, Table, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session

class Base(DeclarativeBase):
    pass

# the association table: two foreign keys, one to each side
post_tag = Table(
    "post_tag", Base.metadata,
    Column("post_id", ForeignKey("posts.id"), primary_key=True),
    Column("tag_id", ForeignKey("tags.id"), primary_key=True),
)

class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(100))
    tags: Mapped[list["Tag"]] = relationship(secondary=post_tag, back_populates="posts")

class Tag(Base):
    __tablename__ = "tags"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    posts: Mapped[list["Post"]] = relationship(secondary=post_tag, back_populates="tags")

engine = create_engine("sqlite://")
Base.metadata.create_all(engine)

with Session(engine) as s:
    py = Tag(name="python")
    flask_tag = Tag(name="flask")
    post = Post(title="Flask + SQLAlchemy", tags=[py, flask_tag])
    other = Post(title="Pure Python", tags=[py])
    s.add_all([post, other, py, flask_tag])
    s.commit()

    p = s.scalars(select(Post).where(Post.title == "Flask + SQLAlchemy")).first()
    # sorted() because a relationship has no order of its own: the database
    # returns the rows however it likes. Add order_by to the relationship, or
    # sort them here, whenever the order matters to you.
    print("Tags on the post:", sorted(t.name for t in p.tags))
    t = s.scalars(select(Tag).where(Tag.name == "python")).first()
    print("Posts tagged python:", sorted(pp.title for pp in t.posts))

# ✅ Expected output:
# Tags on the post: ['flask', 'python']
# Posts tagged python: ['Flask + SQLAlchemy', 'Pure Python']

Output: Tags on the post: ['python', 'flask'], and the python tag lists both posts ('Flask + SQLAlchemy' and 'Pure Python') — many on each side.

3 Lazy vs Eager, and the Flask-SQLAlchemy Form

By default, relationships load lazily: SQLAlchemy fetches related rows only the moment you touch the attribute. That is convenient, but looping over 100 users and reading user.posts each time fires 100 extra queries — the infamous N+1 problem. To avoid it, load eagerly with selectinload() or joinedload(), which fetch everything up front.

# eager loading avoids N+1 (runnable)
from sqlalchemy import create_engine, ForeignKey, String, select
from sqlalchemy.orm import (
    DeclarativeBase, Mapped, mapped_column, relationship, Session, selectinload,
)

class Base(DeclarativeBase): pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    posts: Mapped[list["Post"]] = relationship(back_populates="author")

class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(100))
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    author: Mapped["User"] = relationship(back_populates="posts")

engine = create_engine("sqlite://")
Base.metadata.create_all(engine)

with Session(engine) as s:
    for n in ("Ada", "Bob"):
        s.add(User(name=n, posts=[Post(title=f"{n}-1"), Post(title=f"{n}-2")]))
    s.commit()

    # selectinload pulls all posts in ONE extra query instead of one-per-user
    stmt = select(User).options(selectinload(User.posts)).order_by(User.name)
    for u in s.scalars(stmt):
        print(u.name, "->", [p.title for p in u.posts])

# ✅ Expected output:
# Ada -> ['Ada-1', 'Ada-2']
# Bob -> ['Bob-1', 'Bob-2']

Output: Ada -> ['Ada-1', 'Ada-2'] and Bob -> ['Bob-1', 'Bob-2'], fetched without firing a separate query per user.

In a Flask app you usually use Flask-SQLAlchemy, where models inherit from db.Model and you use db.relationship. The mapping is direct — here is the same one-to-many in that style:

🎯 Your Turn: Fill in the Blanks

Complete the one-to-many between an Author and their Books. Replace each ___.

# 🎯 YOUR TURN: replace each ___ to wire up the relationship
from sqlalchemy import create_engine, ForeignKey, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session

class Base(DeclarativeBase): pass

class Author(Base):
    __tablename__ = "authors"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    books: Mapped[list["Book"]] = relationship(back_populates="___")   # name the attr on Book

class Book(Base):
    __tablename__ = "books"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(100))
    author_id: Mapped[int] = mapped_column(___("authors.id"))          # which column type links rows?
    author: Mapped["Author"] = relationship(back_populates="books")

engine = create_engine("sqlite://")
Base.metadata.create_all(engine)

with Session(engine) as s:
    a = Author(name="Orwell", books=[Book(title="1984"), Book(title="Animal Farm")])
    s.add(a); s.commit()
    found = s.scalars(select(Author)).first()
    print(found.name, "wrote", [b.title for b in found.___])           # navigate one -> many

Common Errors & Quick Reference

❌ NoForeignKeysError / could not determine join condition

You added a relationship() but forgot the ForeignKey column. SQLAlchemy needs the FK to know how the two tables connect.

❌ back_populates names don't match

The string in back_populates must be the attribute name on the other class. A typo leaves the two sides out of sync or raises a mapper error.

❌ DetachedInstanceError when reading related data

You closed the session before touching a lazily-loaded attribute. Either keep the session open or load eagerly with selectinload().

🏆 Mini Challenge: Students and Courses

Model a many-to-many between students and the courses they enroll in.

• Create an enrollment association table
• Give Student a courses relationship and Course a students one
• Enroll one student in two courses and list them

# 🏆 Mini Challenge: many-to-many enrollment
from sqlalchemy import create_engine, ForeignKey, String, Column, Table, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session

class Base(DeclarativeBase): pass

enrollment = Table(
    "enrollment", Base.metadata,
    Column("student_id", ForeignKey("students.id"), primary_key=True),
    Column("course_id", ForeignKey("courses.id"), primary_key=True),
)

class Student(Base):
    __tablename__ = "students"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    courses: Mapped[list["Course"]] = relationship(secondary=enrollment, back_populates="students")

class Course(Base):
    __tablename__ = "courses"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(50))
    students: Mapped[list["Student"]] = relationship(secondary=enrollment, back_populates="courses")

engine = create_engine("sqlite://")
Base.metadata.create_all(engine)

with Session(engine) as s:
    math = Course(title="Math")
    cs = Course(title="CS")
    ada = Student(name="Ada", courses=[math, cs])
    s.add_all([ada, math, cs]); s.commit()
    found = s.scalars(select(Student).where(Student.name == "Ada")).first()
    print("Ada is enrolled in:", [c.title for c in found.courses])
    print("Math roster:", [st.name for st in math.students])

❓ Frequently Asked Questions

Lesson complete — your data is connected!

You can model one-to-many with ForeignKey and relationship, build many-to-many with an association table, and choose lazy or eager loading to keep queries efficient.

Practice quiz

Continue this course

• Previous: Form Validation with Flask-WTF
• Next: Querying with SQLAlchemy — Filter, order, join, and aggregate with the modern select() API
• Quick reference: Flask cheat sheet

Frequently asked questions

What is the difference between a ForeignKey and a relationship?

A ForeignKey is the actual database column that stores the id of a related row — for example posts.user_id pointing at users.id. relationship() is the Python-side convenience SQLAlchemy adds on top: it lets you write post.author or user.posts to navigate between objects without writing the join yourself. You usually need both.

What does back_populates do?

back_populates links the two sides of a relationship so they stay in sync. If you set post.author = user, SQLAlchemy automatically adds that post to user.posts, and vice versa. You declare back_populates on both relationships, naming the attribute on the other class.

How do I model a many-to-many relationship?

You create a third 'association' table with two foreign keys — one to each side — and pass it to relationship(secondary=...). For example a Post and a Tag connect through a post_tag table, letting one post have many tags and one tag belong to many posts.

What is the difference between lazy and eager loading?

Lazy loading (the default) fetches related rows only when you first touch the attribute, which can cause many small queries (the N+1 problem). Eager loading with selectinload() or joinedload() fetches the related rows up front in one or two queries, which is far more efficient when you know you will need them.

Related lessons

• Defining Models with SQLAlchemy