SQLAlchemy One-to-Many Relationships

Suppose we have:

• One Project
• Many Property So the relationship is:

Project 1 ────────< Property

The many side (Property) owns the foreign key pointing to the primary key of the one side (Project).

1. Foreign Key

For example:

class Project(Base):
    __tablename__ = "project"

    id = mapped_column(primary_key=True)


class Property(Base):
    __tablename__ = "property"

    id = mapped_column(primary_key=True)
    project_id = mapped_column(ForeignKey("project.id"))

The database structure is conceptually:

project
-------
id (PK)
1
2
3


property
--------
id (PK)
project_id (FK)
101   1
102   1
103   1
104   2

Here:

• Project 1 has properties 101, 102, and 103
• Project 2 has property 104

So:

Project 1 → many Property
Property 101 → one Project

The foreign key is usually named something like:

project_id

because it refers to project.id.

2. relationship()

The ForeignKey establishes the database-level relationship.
The relationship() establishes the ORM-level relationship.

For example:

class Project(Base):
    __tablename__ = "project"

    id = mapped_column(primary_key=True)

    properties = relationship(
        "Property",
        back_populates="project"
    )


class Property(Base):
    __tablename__ = "property"

    id = mapped_column(primary_key=True)
    project_id = mapped_column(ForeignKey("project.id"))

    project = relationship(
        "Project",
        back_populates="properties"
    )

Now SQLAlchemy lets you navigate the relationship through Python attributes:

project.properties

returns:

[Property(...), Property(...), Property(...)]

while:

property.project

returns:

Project(...)

So the type relationship is:

Project.properties → list[Property]
Property.project   → Project

back_populates

back_populates tells SQLAlchemy that these two relationships are two sides of the same relationship.

Project.properties
        ↕
Property.project

Because of this, SQLAlchemy can keep the in-memory object graph synchronised.

For example:

project = Project()
property = Property()

project.properties.append(property)

and SQLAlchemy understands:

project.properties
        ↓
property.project = project
        ↓
property.project_id = project.id

So when the project gets its primary key and the session flushes, SQLAlchemy can insert the corresponding project_id into property.

Important distinction

back_populates does not create the foreign key.
These have separate responsibilities:

ForeignKey     → database relationship
relationship() → ORM relationship
back_populates → links the two ORM relationship attributes

3. Lazy Loading

By default, SQLAlchemy relationships are generally loaded lazily.
Suppose:

project = session.get(Project, 1)

SQLAlchemy initially retrieves the project:

SELECT *
FROM project
WHERE id = 1;

The properties have not necessarily been loaded yet.
When you do:

project.properties

SQLAlchemy may then issue another query:

SELECT *
FROM property
WHERE project_id = 1;

So:

Load Project
     ↓
Access project.properties
     ↓
Load Properties

This is useful because you don't retrieve related data unnecessarily.
However, repeatedly doing this can cause the N+1 query problem.
For example:

projects = session.query(Project).all()

for project in projects:
    print(project.properties)

Conceptually this can result in:

1 query → load all projects
N queries → load properties for each project

So:

1 + N queries

4. Eager Loading

Eager loading tells SQLAlchemy to retrieve the related objects as part of the initial loading process.
For example:

from sqlalchemy.orm import selectinload

projects = session.scalars(
    select(Project).options(
        selectinload(Project.properties)
    )
).all()

SQLAlchemy can then load the projects and their properties using additional planned queries, typically something like:

SELECT *
FROM project;

followed by:

SELECT *
FROM property
WHERE project_id IN (...);

So instead of:

1 + N queries

you can often get:

2 queries

The main difference are:

Lazy loading  → related data loaded when accessed
Eager loading → related data loaded as part of the intended fetch operation

Memory trade-off

Eager loading can increase memory usage because more ORM objects are materialised immediately.
However, the performance trade-off is more nuanced than simply:

Lazy   = more queries
Eager  = fewer queries

The best strategy depends on:

• number of related rows
• query frequency
• result-set size
• whether relationships are actually needed
• type of eager loading (joinedload, selectinload, etc.)

5. Cascade

This part of your notes needs the most correction.
Cascade controls what ORM operations on a parent are propagated to related objects.
For example:

properties = relationship(
    "Property",
    back_populates="project",
    cascade="all, delete-orphan"
)

delete-orphan adds another rule:
A child that is no longer associated with its parent is considered an orphan and will be deleted.

all is effectively a shorthand for several cascade behaviours, including:

save-update
merge
refresh-expire
expunge
delete

More on that at: https://docs.sqlalchemy.org/en/21/orm/cascades.html

SQLAlchemy's default save-update cascade generally causes related transient objects to be pulled into the session when they are associated through the relationship.

For example:

project = Project()

property = Property()

project.properties.append(property)

session.add(project)

Because of the relationship cascade, the property can also become part of the session.
Then:

session.commit()

can persist both objects.
Conceptually:

session.add(project)
       ↓
Project tracked
       ↓
Property associated through relationship
       ↓
Property also tracked
       ↓
INSERT Project
INSERT Property

Overall...

A useful way to remember the four concepts is:
| Concept | Responsibility |
| ---------------- | -------------------------------------------- |
| ForeignKey | Defines the relationship in the database |
| relationship() | Defines the relationship in the ORM |
| back_populates | Connects the two ORM sides |
| cascade | Defines how certain ORM operations propagate |
| Lazy loading | Load relationship when accessed |
| Eager loading | Load relationship as part of the query |

One additional distinction worth remembering is that ORM cascade and database-level ON DELETE CASCADE are different mechanisms. SQLAlchemy can delete child objects itself, while the database can also be configured to perform cascading deletes via the foreign key.

So the overall structure is:

                 DATABASE
                     │
            ForeignKey(project_id)
                     │
                     ▼
Project 1 ─────────< Property
   │                    │
   │ relationship()     │ relationship()
   │                    │
   ▼                    ▼
properties            project
   │                    │
   └──── back_populates┘
            │
            ▼
         cascade
            │
            ▼
   controls ORM operation
       propagation

Useful Reading & References:
https://learn.miguelgrinberg.com/product/sqlalchemy2