Modeling self-referential relationships for trees

Give the table a nullable parent_id foreign key to its own primary key, declare parent with remote_side=[id] and children with back_populates="parent" — then load bounded depths with chained selectinload() and unbounded ones with a recursive CTE. This guide belongs to modeling relationships, cascades and association objects.

Quick Answer

A relationship from a table to itself has two ends that look identical to SQLAlchemy. remote_side is what tells it which end is the parent.

Before — SQLAlchemy cannot tell parent from child:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Category(Base):
    __tablename__ = "categories"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    parent_id: Mapped[int | None] = mapped_column(ForeignKey("categories.id"))

    parent: Mapped["Category | None"] = relationship()
    children: Mapped[list["Category"]] = relationship()
# sqlalchemy.exc.ArgumentError: Category.parent and back-reference Category.children are
# both of the same direction symbol('ONETOMANY').  Did you mean to set remote_side on the
# many-to-one side ?

After — the parent side declares itself the "one" end:

from sqlalchemy import CheckConstraint, ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Category(Base):
    __tablename__ = "categories"
    __table_args__ = (
        CheckConstraint("parent_id IS NULL OR parent_id <> id", name="not_own_parent"),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    position: Mapped[int] = mapped_column(default=0)
    parent_id: Mapped[int | None] = mapped_column(
        ForeignKey("categories.id", ondelete="CASCADE"), index=True
    )

    parent: Mapped["Category | None"] = relationship(
        back_populates="children", remote_side=[id]
    )
    children: Mapped[list["Category"]] = relationship(
        back_populates="parent",
        cascade="all, delete-orphan",
        passive_deletes=True,
        order_by="Category.position",
    )

remote_side=[id] says the id column is on the far side of parent, which makes parent many-to-one and children one-to-many. The index on parent_id matters: every "children of" query filters on it.

Execution Context & Async Workflow Integration

An adjacency list stores one edge per row, which makes writes trivial and reads recursive. That asymmetry is the whole story of working with trees in an ORM.

Walking a tree by attribute access is one query per node visited. Synchronously that is slow; under AsyncSession it does not work at all, because each step is a lazy load and raises MissingGreenlet. Every traversal therefore has to decide its depth in advance.

For a bounded depth — a navigation menu three levels deep — chained eager loading is the simplest answer, and costs one query per level:

from sqlalchemy import select
from sqlalchemy.orm import selectinload

from shop.models import Category

stmt = (
    select(Category)
    .where(Category.parent_id.is_(None))
    .options(
        selectinload(Category.children).selectinload(Category.children)
    )
    .order_by(Category.position)
)
roots = (await session.scalars(stmt)).all()      # three levels, three queries

The same shape can be declared once on the relationship with lazy="selectin", join_depth=2, which applies to every query that loads a Category — convenient for a model that is always read as a tree, and wasteful for one that usually is not.

For an unbounded depth, no number of chained loaders is correct, and the query belongs in SQL. A recursive CTE walks the whole subtree in one statement:

from sqlalchemy import literal, select
from sqlalchemy.orm import aliased

from shop.models import Category


def subtree(root_id: int):
    base = (
        select(Category.id, Category.parent_id, Category.name, literal(0).label("depth"))
        .where(Category.id == root_id)
        .cte("subtree", recursive=True)
    )
    child = aliased(Category)
    return base.union_all(
        select(child.id, child.parent_id, child.name, (base.c.depth + 1))
        .join(base, child.parent_id == base.c.id)
    )

Selecting from that CTE returns every descendant with its depth in one round trip, and the rows can be assembled into a nested structure in Python by parent_id. The mechanics — and how to stop a cycle turning the query into an infinite loop — are covered in implementing recursive CTEs for hierarchical data and detecting cycles in recursive CTEs.

Cascades work the same way they do between two tables, with one extra consequence: ON DELETE CASCADE on a self-referential foreign key deletes the entire subtree, at any depth, in one statement. That is usually what a folder or menu wants, and emphatically not what a comment thread wants.

Resolving Warnings, Errors & Common Mistakes

Cycle prevention deserves code rather than a constraint, because a database CHECK can only see one row. A move has to verify that the new parent is not inside the subtree being moved:

from sqlalchemy import select

from shop.models import Category


async def move_category(session, node: Category, new_parent_id: int | None) -> None:
    if new_parent_id is not None:
        if new_parent_id == node.id:
            raise ValueError("a category cannot be its own parent")
        ancestors = await session.execute(ancestor_ids(new_parent_id))   # recursive CTE
        if node.id in {row[0] for row in ancestors}:
            raise ValueError("cannot move a category beneath its own descendant")
    node.parent_id = new_parent_id
    await session.commit()

Without that check the moved subtree becomes a disconnected ring: it no longer appears under any root, every recursive query that starts at a root misses it, and a recursive query that starts inside it never terminates. The rows are all still there, which is what makes the bug so confusing when it is eventually noticed.

Advanced: Bulk Reads, Moves and Deletes

Three operations dominate real tree workloads, and each has a set-based form that avoids walking node by node.

Reading an entire tree for a navigation menu or a category picker is usually better done flat. Select every row once, ordered, and assemble the structure in Python:

from collections import defaultdict

from sqlalchemy import select

from shop.models import Category


async def load_full_tree(session) -> list[dict]:
    rows = (await session.execute(
        select(Category.id, Category.parent_id, Category.name)
        .order_by(Category.parent_id.nulls_first(), Category.position)
    )).all()

    children: dict[int | None, list[dict]] = defaultdict(list)
    for node_id, parent_id, name in rows:
        children[parent_id].append({"id": node_id, "name": name, "children": children[node_id]})
    return children[None]

One query, one pass, and the nested lists are built by reference as the loop goes. For a few thousand nodes this beats any per-level strategy, and it is trivially cacheable.

Moving a subtree in an adjacency list is a single UPDATE of one row: changing a node's parent_id moves everything beneath it, because the descendants' own edges are unchanged. This is the adjacency list's great advantage over materialised paths, where a move rewrites every descendant.

Deleting is where the model choice shows. With ON DELETE CASCADE, deleting a node removes its subtree in one statement. To promote children instead of deleting them, reassign before the delete:

from sqlalchemy import delete, update

from shop.models import Category


async def delete_and_promote(session, node_id: int) -> None:
    node = await session.get(Category, node_id)
    await session.execute(
        update(Category)
        .where(Category.parent_id == node_id)
        .values(parent_id=node.parent_id)          # grandparent, or NULL for a root
    )
    await session.execute(delete(Category).where(Category.id == node_id))
    await session.commit()

Both statements are set-based, so the cost does not depend on how many children there are. The ORM-enabled update() and delete() forms are covered in using ORM-enabled UPDATE and DELETE statements, including the identity-map synchronisation that matters when the session already holds those rows.

If subtree reads dominate and the tree is deep, that is the point to consider a materialised path or PostgreSQL's ltree — a prefix match on an indexed column beats recursion — or a closure table when reads must be fast and writes are rare. Both are optimisations to reach for with a measurement in hand, not defaults.

Ordering, Depth and Presentation

Trees are almost always rendered, and two presentational details cause most of the remaining trouble: sibling order and depth.

Sibling order must be explicit. Without order_by on the relationship, children arrive in whatever order the database returns them, which is stable enough in development to look intentional and changes under load, after a vacuum, or when the plan switches to an index scan. An integer position column ordered on the relationship fixes the display order; reordering is then an update of the affected siblings:

from sqlalchemy import update

from shop.models import Category


async def reorder(session, parent_id: int | None, ordered_ids: list[int]) -> None:
    for position, node_id in enumerate(ordered_ids):
        await session.execute(
            update(Category)
            .where(Category.id == node_id, Category.parent_id == parent_id)
            .values(position=position)
        )
    await session.commit()

The parent_id predicate is deliberate: it makes the statement a no-op for an id that is not actually a child of that parent, so a stale client cannot reorder someone else's subtree.

Depth is worth carrying in the query rather than computing in Python. The recursive CTE above already produces it, and it is what lets a template indent without walking parents. It also enables a cheap guard against runaway data: adding WHERE depth < 20 to the recursive term bounds the query even if a cycle slips past the application checks.

For breadcrumbs — the path from a node back to the root — the recursion runs the other way, following parent_id upward:

from sqlalchemy import literal, select
from sqlalchemy.orm import aliased

from shop.models import Category


def ancestors(node_id: int):
    base = (
        select(Category.id, Category.parent_id, Category.name, literal(0).label("up"))
        .where(Category.id == node_id)
        .cte("ancestors", recursive=True)
    )
    parent = aliased(Category)
    return base.union_all(
        select(parent.id, parent.parent_id, parent.name, (base.c.up + 1))
        .join(base, base.c.parent_id == parent.id)
    )

Ordering that result by up descending gives root-first breadcrumbs in one query, where attribute access would have cost one query per level and, under async, would not have worked at all.

When a tree is read on every page and changes rarely, denormalising the rendered structure — a JSONB document rebuilt whenever the tree changes — removes the query entirely. Querying and indexing JSONB columns covers storing and invalidating such a document safely.

Frequently Asked Questions

Related

• Modeling Relationships, Cascades and Association Objects — The parent guide: relationship shapes, loading and ownership.
• Implementing recursive CTEs for hierarchical data — Loading a subtree of unknown depth in one query.
• Configuring cascade delete and delete-orphan correctly — What a delete does to the rows below it.
• Detecting cycles in recursive CTEs for graph data — Stopping a corrupt hierarchy from hanging a query.