SQLAlchemy Relationships

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.

Learn SQLAlchemy Relationships in our free Flask course — a beginner-friendly interactive lesson with worked examples, a practice exercise and a quick…

Part of the free Flask course at LearnCodingFast — hands-on lessons with examples you run in your browser, 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.

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 .

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

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.

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

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.

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:

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

❌ 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.

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() .

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

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.

🚀 Up next: Querying with SQLAlchemy — filter, sort, join, and aggregate the data you've just connected.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary