Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 72 ยท Databases

Relationships

Model one-to-many and many-to-many relationships in Flask-SQLAlchemy with back_populates, understand lazy loading, and fix the N+1 problem with selectinload.

Real data is connected. A blog post has comments, an author has many books, a book has several tags and each tag belongs to many books. In a relational database these links are stored as foreign keys: a column in one table that holds the primary key of a row in another table.

SQLAlchemy lets you work with those links as plain Python attributes. Instead of writing joins by hand, you write author.books or book.author. This lesson covers the two relationship shapes you will use constantly (one-to-many and many-to-many), how to keep both sides in sync, and the performance trap that catches almost every beginner: the N+1 query problem.

The shapes of relationships

erDiagram
    AUTHOR ||--o{ BOOK : writes
    POST ||--o{ POST_TAG : has
    TAG ||--o{ POST_TAG : has

One author has many books. Posts and tags are linked through a join table, which makes the link many-to-many.

  • One-to-many: one row on the "one" side owns many rows on the "many" side. The "many" table carries the foreign key (book.author_id).
  • Many-to-many: each side can have many of the other. A separate association table holds one row per pair; it has two foreign keys and nothing else.
  • One-to-one is a one-to-many with uselist=False on the relationship (and usually a unique foreign key). It is less common, so we skip it here.

One-to-many with back_populates

A relationship needs two things: a foreign key column on the many side, which the database enforces, and a relationship() attribute, which exists only in Python and tells SQLAlchemy how to follow the key. You normally declare a relationship() on both models and connect them with back_populates, so each side knows about the other.

Python Authors and books: one-to-many

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Here is what happened:

  • Mapped[list["Book"]] says author.books is a list of Book objects. The quotes are needed because Book is defined further down the file.
  • ForeignKey("author.id") uses the table name and column, not the class name. Flask-SQLAlchemy turns Author into the table author, BlogPost into blog_post.
  • back_populates links the two attributes. When you set extra.author = herbert, SQLAlchemy also adds extra to herbert.books, with no database access. You can see that in the fourth print: the list already holds three titles before commit().
  • The foreign key is filled in for you. You never assign author_id by hand. Appending to herbert.books or setting solaris.author is enough.
  • cascade="all, delete-orphan" makes the parent responsible for its children. Deleting the author deletes their books, and removing a book from author.books deletes that row instead of leaving it with a missing owner. Without a cascade, removing a book from the list would try to set author_id to NULL, which fails because the column is required.

You may see backref="author" in older code. It creates the other side automatically, but that attribute is invisible to type checkers and editors. Declaring both sides with back_populates is more explicit, and it is the style we use in this course.

Many-to-many with an association table

When posts can have many tags and tags belong to many posts, neither table can hold a single foreign key. Instead you create a third table with one row per (post, tag) pair, and point both relationships at it with secondary=.

For a plain join table with no extra data, declare it with db.Table instead of a model class. Making both columns the primary key together stops the same pair from being stored twice.

Python Posts and tags: many-to-many

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that you never touch post_tag directly. Appending to post.tags inserts a row into it, remove() deletes the row, and deleting a post cleans up its pairs. The tag itself survives when you remove it from a post, which is exactly what you want: a many-to-many link is not ownership.

Two query tools are worth remembering:

  • Post.tags.any(Tag.name == "sql") asks "does this post have at least one tag matching this condition?" and compiles to an EXISTS subquery, so it never duplicates rows.
  • .join(Post.tags) follows the relationship across the association table, which is handy when you want columns from both sides at once.

If the link itself needs data, such as the date a tag was added or a quantity on an order line, replace the plain table with a full model class holding two foreign keys plus the extra columns. That pattern is called an association object, and the bookshop project uses it for order items.

Lazy loading and the N+1 problem

By default, a relationship attribute is loaded lazily: SQLAlchemy runs a SELECT the first time you read it, and caches the result on the object. That feels magical and is perfect for one object. In a loop it becomes dangerous.

Imagine a page that lists 5 authors with their book counts. You run one query for the authors. Then, for each author, touching author.books fires another query. That is 1 + N queries, where N grows with your data. With 500 authors the page quietly makes 501 round trips.

flowchart LR
    A["1 query:<br/>SELECT authors"] --> B["author 1:<br/>SELECT books"]
    A --> C["author 2:<br/>SELECT books"]
    A --> D["author N:<br/>SELECT books"]

Lazy loading in a loop: one query for the parents, then one more for each parent.

The fix is to tell SQLAlchemy up front that you will need the related rows, using a loader option:

Option What it does Statements for authors + books
lazy (default) Loads on first access 1 + N
selectinload(Author.books) One extra SELECT ... WHERE author_id IN (...) 2
joinedload(Author.books) One LEFT JOIN; parents are repeated in the rows 1

The next example counts the SQL statements the engine really executes, using an event listener, so you can see the difference instead of taking it on trust.

Python Counting queries: lazy vs selectinload vs joinedload

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

All three runs return the same 15 books. Only the number of round trips changes. Which option should you choose?

  • selectinload is the best default for collections (the "many" side). It avoids duplicated parent rows and works well with LIMIT.
  • joinedload suits single related objects (a book's author), where the join adds no extra rows. When used on a collection, call .unique() on the result as the example does, because the parent repeats once per child.
  • Leave it lazy when you only touch the relationship for a few objects, or on code paths where most requests never read it.

You can also set the strategy on the relationship itself, for example relationship(lazy="selectin"). That changes the default everywhere, so many teams prefer to opt in per query with .options(...), where the cost is visible next to the code that needs the data.

If a page feels slow, count the statements it runs before you guess. Setting SQLALCHEMY_ECHO = True in the config prints every SQL statement to the log in development. A long run of near-identical SELECTs is the signature of N+1. Performance and Caching returns to this topic.

Relationships in views and templates

Once loaded, relationships are ordinary attributes, so templates use them directly:

HTML
{% for author in authors %}
  <h2>{{ author.name }}</h2>
  <ul>
    {% for book in author.books %}
      <li>{{ book.title }}</li>
    {% endfor %}
  </ul>
{% endfor %}

The view that renders this should load authors with selectinload(Author.books), otherwise the inner loop is where the hidden queries happen. Keep this in mind for the list pages you built in Queries and CRUD Views: the moment a list shows data from a related table, add a loader option.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In a one-to-many relationship between Author and Book, which table holds the foreign key?

    Each book belongs to exactly one author, so a single author_id column on the book table is enough. The author row needs no list of books.
  2. 2. What does back_populates do?

    back_populates pairs the two relationship() declarations. Setting book.author = a also adds the book to a.books, without any database round trip.
  3. 3. You show 100 authors and print len(author.books) for each, using default lazy loading. Roughly how many SQL statements run?

    One statement loads the authors, then each author's books triggers its own query on first access. That is the N+1 pattern; selectinload would reduce it to 2.
  4. 4. Which is the right way to declare a plain many-to-many link between Post and Tag?

    Neither side can hold a single key for a many-to-many link. The association table stores one row per pair, and secondary= tells SQLAlchemy to use it.
  5. 5. After orm.tags.remove(python) and a commit, what happens to the python tag row itself?

    In a many-to-many link neither side owns the other. Removing from the collection deletes the association row, not the tag.

Summary

  • A foreign key lives on the "many" side of a one-to-many link; relationship() with back_populates on both models gives you author.books and book.author.
  • SQLAlchemy fills in foreign keys for you when you append to a collection or set the other side, and keeps both sides in sync in memory.
  • Use cascade="all, delete-orphan" when the parent owns its children; leave it off for many-to-many links.
  • Many-to-many needs an association table (db.Table) and secondary=; use an association object when the link carries data.
  • Relationships load lazily by default, which causes N+1 queries in loops. Fix it per query with selectinload() for collections or joinedload() for single objects.
  • Count statements (an event listener or SQLALCHEMY_ECHO) instead of guessing about performance.

Next: Migrations with Flask-Migrate, changing your tables safely after the app is live.