Lesson 5.4 · Databases

Relationships

Connect tables: users and their to-dos (one-to-many with a foreign key) and to-dos and tags (many-to-many with a link table). Move between them in Python, then meet the real traps: SQLite does not check foreign keys by default, deleting a user fails without cascade_delete, and a list of users with their to-dos sent 23 queries instead of 2.

Intermediate40 min

What you will be able to do

Link two tables with a foreign key and a Relationship
Build a many-to-many relationship with a link table
Move between related objects in Python
Switch on foreign key checks in SQLite
Delete a parent with its children, and recover from a failed commit
Return nested data and avoid the N+1 query problem

The idea, in plain English

Real data is connected. A to-do belongs to one user; a user has many to-dos. That is one-to-many: the todo table gets an owner_id column - a foreign key that points to a row in the user table. Tags are different: a to-do can have many tags and a tag many to-dos. That is many-to-many, and it needs a third table with one row per link.

In SQLModel you write both levels. Field(foreign_key=...) makes the column in the database. Relationship(...) adds a Python shortcut on top: todo.owner gives a User object, user.todos gives a list - SQLModel writes the joins for you.

We build users, to-dos and tags, move between them, and then test the edges: wrong foreign keys, deleting a user who has to-dos, and how many SQL queries an API answer really costs. SQLModel 0.0.48, SQLAlchemy 2.1.4, FastAPI 0.143.0.

Worked example: Users, their to-dos and tags in one SQLite file - filled from a script, then served by an API that returns each user with their to-dos.

workflowThree tables and a linkstep 1 / 3

user

ravi, anu

todo

owner_id -> user.id

todotaglink

todo_id, tag_id

tag

home, work

React Flow

1 - One-to-many

Each to-do has an owner_id. user.todos and todo.owner are the Python shortcuts.

ravi.todos

['Buy milk', 'Pay rent']

flights.owner

anu (owner_id = 2)

How the rows point to each other. Real results from our users, to-dos and tags.

Words you will see in this lesson

A few words about connected tables.

Small dictionary

Foreign keyA column that holds the primary key of a row in another table: owner_id -> user.id.

One-to-manyOne user, many to-dos. The foreign key is on the "many" side.

Many-to-manyMany to-dos, many tags. Needs a link table.

Link tableA table with one row per connection: (todo_id, tag_id).

CascadeDoing the same action to related rows - delete a user, delete their to-dos.

N+1 problem1 query for a list, then 1 more for each item - slow for long lists.

An everyday example: a school

Each student belongs to one class; a class has many students. The student card has the class number on it - that is the foreign key.
Students and clubs: a student joins many clubs, a club has many students. The school keeps a sign-up sheet with one line per student-club pair - the link table.
If a class closes, the school must decide what happens to its students - that is cascade.

Example 1 - the models

Read models.py from the top:

TodoTagLink - the link table. Both columns are foreign keys and together they are the primary key, so one pair can exist only once.
User.todos - a list of Todo objects. back_populates="owner" connects it to Todo.owner, so both sides stay in step.
Todo.owner_id - the real column, Field(foreign_key="user.id").
Todo.owner - the Python shortcut on top of owner_id.
Todo.tags and Tag.todos - many-to-many through link_model=TodoTagLink.
cascade_delete=True on User.todos - deleting a user deletes their to-dos (see "Deleting a user").
"Todo" and "Tag" are in quotes where the class is defined later in the file.

Example 1 - models.py

from sqlmodel import Field, Relationship, SQLModel


class TodoTagLink(SQLModel, table=True):             # the link table for many-to-many
    todo_id: int | None = Field(default=None, foreign_key="todo.id", primary_key=True)
    tag_id: int | None = Field(default=None, foreign_key="tag.id", primary_key=True)


class User(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(unique=True)

    todos: list["Todo"] = Relationship(back_populates="owner",
                                       cascade_delete=True)          # delete a user -> delete their todos


class Todo(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    title: str
    done: bool = False
    owner_id: int = Field(foreign_key="user.id")                   # the column in the table

    owner: User = Relationship(back_populates="todos")             # the Python shortcut
    tags: list["Tag"] = Relationship(back_populates="todos", link_model=TodoTagLink)


class Tag(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(unique=True)

    todos: list[Todo] = Relationship(back_populates="tags", link_model=TodoTagLink)

Example 2 - fill and walk the data

fill.py creates two users, two tags and four to-dos:

Todo(title=..., owner=ravi, tags=[home]) - we set objects, not ids. On commit SQLModel saves the users and tags first and fills owner_id and the link rows.
ravi.todos and home.todos load the related rows when you read them.
flights.tags.remove(home) + commit deletes one link row - the to-do and the tag stay.
select(Todo.title, Tag.name).join(Todo.tags) asks for pairs through the link table.

Example 2 - fill.py

from sqlmodel import Session, SQLModel, create_engine, select

from models import Tag, Todo, User

engine = create_engine("sqlite:///app.db")
SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    ravi, anu = User(name="ravi"), User(name="anu")
    home, work = Tag(name="home"), Tag(name="work")

    session.add_all([
        Todo(title="Buy milk", owner=ravi, tags=[home]),           # set the objects, not the ids
        Todo(title="Pay rent", owner=ravi, tags=[home]),
        Todo(title="Plan release", owner=anu, tags=[work]),
        Todo(title="Book flights", owner=anu, tags=[home, work]),
    ])
    session.commit()

    ravi = session.exec(select(User).where(User.name == "ravi")).one()
    print("ravi.todos:        ", [t.title for t in ravi.todos])
    flights = session.exec(select(Todo).where(Todo.title == "Book flights")).one()
    print("flights.owner:     ", flights.owner.name, "| owner_id =", flights.owner_id)
    print("flights.tags:      ", [t.name for t in flights.tags])
    print("home.todos:        ", [t.title for t in home.todos])

    flights.tags.remove(home)                                      # change the link
    session.commit()
    print("after remove:      ", [t.name for t in flights.tags])
    print("link rows:         ", session.exec(select(Todo.title, Tag.name).join(Todo.tags)).all())

Output

ravi.todos:         ['Buy milk', 'Pay rent']
flights.owner:      anu | owner_id = 2
flights.tags:       ['home', 'work']
home.todos:         ['Buy milk', 'Pay rent', 'Book flights']
after remove:       ['work']
link rows:          [('Plan release', 'work'), ('Book flights', 'work'), ('Buy milk', 'home'), ('Pay rent', 'home')]

Output - sqlite3 app.db ".schema todo" ".schema todotaglink"

CREATE TABLE todo (
	id INTEGER NOT NULL, 
	title VARCHAR NOT NULL, 
	done BOOLEAN NOT NULL, 
	owner_id INTEGER NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(owner_id) REFERENCES user (id)
);
CREATE TABLE todotaglink (
	todo_id INTEGER NOT NULL, 
	tag_id INTEGER NOT NULL, 
	PRIMARY KEY (todo_id, tag_id), 
	FOREIGN KEY(todo_id) REFERENCES todo (id), 
	FOREIGN KEY(tag_id) REFERENCES tag (id)
);

Trap 1: SQLite does not check foreign keys

fk.py ran against our first models.py (without cascade_delete):

Todo(title="Ghost task", owner_id=999) was saved, with no error - there is no user 999.
The schema says FOREIGN KEY(owner_id) REFERENCES user (id), but SQLite only checks it after PRAGMA foreign_keys = ON - for every new connection.
PostgreSQL and MySQL always check. SQLite is the odd one.

fk.py

from sqlmodel import Session, create_engine, select

from models import Todo, User

engine = create_engine("sqlite:///app.db")

# 1. A todo for a user that does not exist
with Session(engine) as session:
    session.add(Todo(title="Ghost task", owner_id=999))
    session.commit()
    print("owner_id=999:  saved, no error")

# 2. Delete a user who has todos
with Session(engine) as session:
    ravi = session.exec(select(User).where(User.name == "ravi")).one()
    session.delete(ravi)
    try:
        session.commit()
    except Exception as e:
        print("delete ravi:  ", str(e).splitlines()[0])
        print("               SQL:", str(e).splitlines()[1])
    try:
        session.exec(select(Todo)).all()                       # use the session again
    except Exception as e:
        print("next query:   ", type(e).__name__, str(e)[:75] + "...")
    session.rollback()                                         # the fix: roll back first
    print("after rollback:", len(session.exec(select(Todo)).all()), "todos")

Output

owner_id=999:  saved, no error
delete ravi:   (sqlite3.IntegrityError) NOT NULL constraint failed: todo.owner_id
               SQL: [SQL: UPDATE todo SET owner_id=? WHERE todo.id = ?]
next query:    PendingRollbackError This Session's transaction has been rolled back due to a previous exception...
after rollback: 5 todos

Trap 2: deleting a user who has to-dos

Look at the second part of that output:

session.delete(ravi) did not delete his to-dos. SQLAlchemy tried to "unlink" them: UPDATE todo SET owner_id = NULL.
owner_id is NOT NULL, so the commit failed with IntegrityError.
Then the next query on the same session failed too: PendingRollbackError. After a failed commit you MUST call session.rollback() before using the session again.
After the rollback all 5 to-dos were still there.

The fixes: cascade_delete and foreign keys on

Two changes:

cascade_delete=True on User.todos (in models.py above): deleting a user deletes their to-dos. Their link rows went too - 2 left, from anu’s to-dos. The tags stayed.
db.py switches foreign key checks on for every connection with an event listener. Now owner_id=999 failed: "FOREIGN KEY constraint failed".
Choose cascade on purpose: for to-dos it fits; for orders or invoices you usually refuse to delete the customer instead.

db.py

from sqlalchemy import event
from sqlmodel import create_engine

engine = create_engine("sqlite:///app.db")


@event.listens_for(engine, "connect")
def foreign_keys_on(dbapi_connection, connection_record):     # SQLite only: switch the checks on
    dbapi_connection.execute("PRAGMA foreign_keys = ON")

fk_fixed.py

from sqlmodel import Session, select

from db import engine
from models import Tag, Todo, TodoTagLink, User

with Session(engine) as session:
    session.add(Todo(title="Ghost task", owner_id=999))
    try:
        session.commit()
    except Exception as e:
        print("owner_id=999: ", str(e).splitlines()[0])
        session.rollback()

with Session(engine) as session:
    ravi = session.exec(select(User).where(User.name == "ravi")).one()
    session.delete(ravi)
    session.commit()
    print("delete ravi:   done")
    print("todos left:   ", session.exec(select(Todo.title)).all())
    print("link rows:    ", len(session.exec(select(TodoTagLink)).all()))
    print("tags left:    ", session.exec(select(Tag.name)).all())

Output

owner_id=999:  (sqlite3.IntegrityError) FOREIGN KEY constraint failed
delete ravi:   done
todos left:    ['Book flights', 'Plan release']
link rows:     2
tags left:     ['home', 'work']

Example 3 - users with their to-dos in an API

api.py has three versions of "list the users". An event listener counts every SQL query:

/users-plain returns the table objects with no response model. The JSON had name and id only - relationships are not included when a table object is turned into JSON.
/users uses response_model=list[UserWithTodos], so FastAPI reads user.todos for each user. It worked - with 23 queries for 22 users: 1 for the list, then 1 per user. That is the N+1 problem.
/users-fast adds .options(selectinload(User.todos)): 2 queries - one for the users, one for all their to-dos together. Same JSON.
With 1,000 users, /users would send 1,001 queries.

Example 3 - api.py

from typing import Annotated

from fastapi import Depends, FastAPI
from sqlalchemy import event
from sqlalchemy.orm import selectinload
from sqlmodel import Session, SQLModel, select

from db import engine
from models import User

queries = []


@event.listens_for(engine, "before_cursor_execute")
def count(conn, cursor, statement, *args):                    # write down every SQL command
    queries.append(statement.split()[0])


def get_session():
    with Session(engine) as session:
        yield session


SessionDep = Annotated[Session, Depends(get_session, scope="function")]


class TodoPublic(SQLModel):
    id: int
    title: str
    done: bool


class UserPublic(SQLModel):
    id: int
    name: str


class UserWithTodos(UserPublic):                              # a user plus their todos
    todos: list[TodoPublic] = []


app = FastAPI()


@app.get("/users-plain")
def users_plain(session: SessionDep):                         # returns table objects, no response model
    return session.exec(select(User)).all()


@app.get("/users", response_model=list[UserWithTodos])
def users(session: SessionDep):                               # todos loaded one user at a time
    return session.exec(select(User)).all()


@app.get("/users-fast", response_model=list[UserWithTodos])
def users_fast(session: SessionDep):
    return session.exec(select(User).options(selectinload(User.todos))).all()   # all todos in one go

try_api.py

from fastapi.testclient import TestClient
from sqlmodel import Session

from api import app, engine, queries
from models import User

with Session(engine) as session:                             # 20 more users (with no todos yet)
    for n in range(20):
        user = User(name=f"user{n}")
        session.add(user)
    session.commit()

c = TestClient(app)
for url in ["/users-plain", "/users", "/users-fast"]:
    queries.clear()
    r = c.get(url)
    data = r.json()
    print(f"{url:<13} -> {r.status_code}  {len(data)} users, {len(queries)} SQL queries")
    print("   first user:", data[0])

Output

/users-plain  -> 200  22 users, 1 SQL queries
   first user: {'name': 'ravi', 'id': 1}
/users        -> 200  22 users, 23 SQL queries
   first user: {'id': 1, 'name': 'ravi', 'todos': [{'id': 1, 'title': 'Buy milk', 'done': False}, {'id': 2, 'title': 'Pay rent', 'done': False}]}
/users-fast   -> 200  22 users, 2 SQL queries
   first user: {'id': 1, 'name': 'ravi', 'todos': [{'id': 1, 'title': 'Buy milk', 'done': False}, {'id': 2, 'title': 'Pay rent', 'done': False}]}

Tip: UserWithTodos uses TodoPublic, which has no owner field. If a nested model pointed back (todo -> owner -> todos -> ...), the answer would loop. Keep nested response models one direction.

Relationships at a glance

Foreign key

The real column.

owner_id: int = Field(foreign_key="user.id")

One-to-many

Both sides.

todos: list["Todo"] = Relationship(back_populates="owner")
owner: User = Relationship(back_populates="todos")

Many-to-many

Through a link table.

tags: list["Tag"] = Relationship(back_populates="todos", link_model=TodoTagLink)

Cascade

Delete the children too.

Relationship(back_populates="owner", cascade_delete=True)

SQLite FK checks

Per connection.

dbapi_connection.execute("PRAGMA foreign_keys = ON")

No N+1

Load children in one query.

select(User).options(selectinload(User.todos))

Try it yourself

The code does not change. Swap the content string and the program does something else entirely.

See the queries

“Add echo=True to the engine in db.py and call /users and /users-fast. Read the SQL of both.”

Tags in the answer

“Add tags: list[TagPublic] to the to-dos in UserWithTodos. How many queries now - and how do you fix it?”

Delete a tag

“Delete the tag "work". What happens to the to-dos and the link rows?”

Refuse instead

“Remove cascade_delete and write DELETE /users/{id} that answers 409 if the user still has to-dos.”

What usually goes wrong

Trusting SQLite foreign keys

owner_id=999 was saved. Switch PRAGMA foreign_keys = ON for every connection.

Deleting a parent without a plan

The commit failed with NOT NULL. Use cascade_delete=True, or refuse the delete.

Using the session after a failed commit

PendingRollbackError. Call session.rollback() first.

Reading relationships in a loop

23 queries for 22 users. Use selectinload.

Expecting relationships in plain JSON

Returning table objects showed no todos. Use a response model with the nested list.

Practice

Write these yourself before opening anything. Getting them wrong first is most of how this sticks.

Add POST /users/{user_id}/todos (create a to-do for that user, 404 if the user does not exist) and GET /tags/{name}/todos (all to-dos with that tag, with their owner’s name). Use selectinload so the second endpoint sends at most 3 queries; count them with the event listener.

Show hint

select(Tag).where(Tag.name == name).options(selectinload(Tag.todos).selectinload(Todo.owner)).

Key points

A foreign key column links a row to a row in another table.
Relationship gives Python shortcuts; back_populates keeps both sides in step.
Many-to-many needs a link table (link_model).
SQLite checks foreign keys only after PRAGMA foreign_keys = ON.
Without cascade, deleting a parent tries to set the children’s key to NULL.
After a failed commit, roll back before using the session again.
selectinload turns N+1 queries into 2.

Quick check before you move on

What happened to Todo(owner_id=999) in default SQLite?

It was saved - no error.

Which SQL did session.delete(ravi) try to run on his to-dos?

UPDATE todo SET owner_id=? - setting it to NULL, which NOT NULL refused.

How many queries did /users and /users-fast send for 22 users?

23 and 2.

Which table holds the many-to-many connections?

todotaglink - one row per (todo_id, tag_id).

Interview questions

What is the N+1 query problem and how do you fix it in SQLAlchemy?

Loading a list (1 query) and then lazily loading a relationship per item (N queries). Fix with eager loading - selectinload (a second IN query) or joinedload (a JOIN) - chosen per endpoint, and watch query counts in tests or logs.

How do you model many-to-many relationships?

With an association (link) table holding two foreign keys, usually as a composite primary key; add extra columns there (like created_at or role) when the link itself has data, and map it as its own model.

Quiz

1.
Why did /users-plain show no to-dos?
2.
What did cascade_delete do to the link rows of ravi’s to-dos?
3.
Why is the PRAGMA in a "connect" event listener?

Comments

Loading comments...