For AI agents: the complete documentation index is at llms.txt. Remove the trailing slash from a page URL and append .md to read its Markdown version. For the docs home, use index.md.

Relationships

Foreign key relationships are used to link two tables together. For example, the Post model may have a field, user_id, with a foreign key of user.id, referencing a User model. This would allow us to automatically query the Post objects associated with a user, or find the User object associated with a Post.

To establish bidirectional relationships a model must correctly set the back_populates keyword argument on the relationship() to the relationship attribute in the other model.

Foreign Key Relationships

To create a relationship, first add a column that references the primary key of the related table with ForeignKey, then add a relationship() attribute which can be used to access the related objects.

import reflex as rx
from sqlalchemy import ForeignKey
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    MappedAsDataclass,
    mapped_column,
    relationship,
)


class Base(MappedAsDataclass, DeclarativeBase):
    """Base class for the app's tables."""


class Post(Base):
    __tablename__ = "post"

    id: Mapped[int] = mapped_column(init=False, primary_key=True)
    title: Mapped[str]
    body: Mapped[str]
    user_id: Mapped[int] = mapped_column(ForeignKey("user.id"))

    user: Mapped["User"] = relationship(back_populates="posts", init=False, repr=False)
    flags: Mapped[list["Flag"]] = relationship(
        back_populates="post", default_factory=list, repr=False
    )


class User(Base):
    __tablename__ = "user"

    id: Mapped[int] = mapped_column(init=False, primary_key=True)
    username: Mapped[str]
    email: Mapped[str]

    posts: Mapped[list[Post]] = relationship(
        back_populates="user", default_factory=list, repr=False
    )
    flags: Mapped[list["Flag"]] = relationship(
        back_populates="user", default_factory=list, repr=False
    )


class Flag(Base):
    __tablename__ = "flag"

    id: Mapped[int] = mapped_column(init=False, primary_key=True)
    post_id: Mapped[int] = mapped_column(ForeignKey("post.id"), index=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("user.id"))
    message: Mapped[str]

    post: Mapped[Post] = relationship(back_populates="flags", init=False, repr=False)
    user: Mapped[User] = relationship(back_populates="flags", init=False, repr=False)
Expand

Relationships are declared with repr=False because the dataclass repr would otherwise follow every link, and with init=False or a default_factory so the constructor takes the foreign key column instead.

See the SQLAlchemy Relationship Docs for more details.

Serializing Linked Objects

Reflex sends a DeclarativeBase model with the columns and relationships that its query loaded. It never runs a query to load more. A column or relationship that was not loaded is left out of the data sent to the frontend.

Load every relationship that the page reads. A left-out relationship is undefined in the browser: post.user.username renders as blank, but post.flags.length() fails and the page shows a render error.

When a loaded relationship leads back to an object that is already being sent, Reflex sends only that object's primary key. For example, with selectinload(Post.user).selectinload(User.posts), each post appears in its own user.posts list as {"id": 1}.

Querying Relationships

The examples below open sessions with the async Session factory from the database overview.

Inserting Linked Objects

The following example assumes that the flagging user is stored in the state as a User instance and that the post id is provided in the data submitted in the form.

from typing import Any


class FlagPostForm(rx.State):
    user: User

    @rx.event
    async def flag_post(self, form_data: dict[str, Any]):
        async with Session() as session:
            flag = Flag(
                message=form_data.pop("message"),
                post_id=int(form_data.pop("post_id")),
                user_id=self.user.id,
            )
            session.add(flag)
            await session.commit()

How are Relationships Dereferenced?

By default, the relationship attributes are in lazy loading or "select" mode, which generates a query on access to the relationship attribute. An async session cannot run that query implicitly, so reading a relationship that was not loaded raises an error instead. Load the relationships a handler or the UI reads up front, with one of these eager loading mechanisms, set on the relationship object or when performing the query.

  • "joined" or joinedload - generates a single query to load all related objects at once.
  • "subquery" or subqueryload - generates a single query to load all related objects at once, but uses a subquery to do the join, instead of a join in the main query.
  • "selectin" or selectinload - emits a second (or more) SELECT statement which assembles the primary key identifiers of the parent objects into an IN clause, so that all members of related collections / scalar references are loaded at once by primary key

There are also non-loading mechanisms, "raise" and "noload" which are used to specifically avoid loading a relationship. For an object that belongs to the current session, await session.refresh(obj, ["relationship_name"]). An object stored in state belongs to no session, so query it again with an eager loading option instead.

Each loading method comes with tradeoffs and some are better suited for different data access patterns. See SQLAlchemy: Relationship Loading Techniques for more detail.

Querying Linked Objects

To query the Post table and include all User and Flag objects up front, the .options interface will be used to specify selectinload for the required relationships. Using this method, the linked objects will be available for rendering in frontend code without additional steps.

from sqlalchemy import select
from sqlalchemy.orm import selectinload


class PostState(rx.State):
    posts: list[Post] = []

    @rx.event
    async def load_posts(self):
        async with Session() as session:
            result = await session.scalars(
                select(Post)
                .options(
                    selectinload(Post.user),
                    selectinload(Post.flags).selectinload(Flag.user),
                )
                .limit(15)
            )
            self.posts = result.all()


def post_list() -> rx.Component:
    return rx.foreach(
        PostState.posts,
        lambda post: rx.vstack(
            rx.text(post.title, " by ", post.user.username),
            rx.text(post.flags.length(), " flags"),
            rx.foreach(
                post.flags,
                lambda flag: rx.text(flag.message, " from ", flag.user.username),
            ),
        ),
    )
Expand

The loading methods create new query objects and thus may be chained if the relationship itself has other relationships that need to be loaded. In this example, the page reads flag.user, so the Flag.user relationship is chain loaded from the Post.flags relationship.

Specifying the Loading Mechanism on the Relationship

Alternatively, the loading mechanism can be specified on the relationship by passing lazy=method to relationship(), which will use the given loading mechanism in all queries by default.

class Post(Base):
    __tablename__ = "post"
    ...
    user: Mapped["User"] = relationship(
        back_populates="posts",
        lazy="selectin",
        init=False,
        repr=False,
    )
    flags: Mapped[list["Flag"]] = relationship(
        back_populates="post",
        lazy="selectin",
        default_factory=list,
        repr=False,
    )

For master-detail interfaces — a table of parent rows where selecting a row reveals its related rows — it is not necessary to eager load every relationship up front. Store the selected id in state and query only the related rows for that selection, filtering on the foreign key.

import reflex as rx
from sqlalchemy import select


class PostFlagsState(rx.State):
    posts: list[Post] = []
    selected_post_id: int | None = None
    selected_flags: list[Flag] = []

    @rx.event
    async def load_posts(self):
        async with Session() as session:
            self.posts = (await session.scalars(select(Post).limit(15))).all()

    @rx.event
    async def select_post(self, post_id: int):
        self.selected_post_id = post_id
        async with Session() as session:
            result = await session.scalars(select(Flag).where(Flag.post_id == post_id))
            self.selected_flags = result.all()

This runs a single indexed query per selection and avoids holding every child row in state — the index=True on Flag.post_id keeps the foreign-key lookup off a full table scan (adding the index to an existing table requires a migration). Avoid the inverse pattern of querying children in a Python loop over parents (the "N+1" pattern) — when the children of many parents are needed at once, use one query with .in_() on the foreign key, or one of the eager loading options above.

async def load_flags(posts: list[Post]) -> list[Flag]:
    post_ids = [post.id for post in posts]
    async with Session() as session:
        result = await session.scalars(select(Flag).where(Flag.post_id.in_(post_ids)))
        return list(result.all())