Eager Loading of Relationships in Async Database Access - jbrucker/home-log GitHub Wiki

The data model for User contains a relationship to a User_Password object, like this:

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True, nullable=False)
    email: Mapped[str] = mapped_column(String(100), unique=True, nullable=False)
    username: Mapped[str] = mapped_column(String(50))
    created_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), nullable=False, server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), server_default=func.now())

    user_password: Mapped["UserPassword"] = relationship("UserPassword", uselist=False, backref="user", 
                                                         cascade="all, delete-orphan")

class UserPassword(Base):
    ...
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"),
                     nullable=False)

The application performs database I/O using an async database driver with SqlAlchemy's async ORM, and the data access functions uses async code as well. For example:

# user_dao.py
from sqlalchemy.ext.asyncio import AsyncSession

async def get_user_by_id(session: AsyncSession, user_id: int) -> models.User | None:
    """Get a user from database using the primary key (id)."""
    ...
    result = await session.get(models.User, user_id)
    return result

An sqlalchemy.exc.MissingGreenlet exception is raised when I do something like this:

user = await user_dao.get_user_by_id(session, user_id=1)

if user.user_password:    <---- Raised sqlalchemy.exc.MissingGreenlet exception
    # get the hashed password

The cause is that user.user_password tries to access a lazy-loaded relationship outside of an async context or without the proper async database session.

If user.user_password is not eagerly loaded, SQLAlchemy tries to fetch it lazily. In async mode (with SQLAlchemy 1.4+ and async drivers), lazy loading requires a running greenlet (async context). If you access the relationship after the session is closed or outside the async context, SQLAlchemy can't perform the database query and raises MissingGreenlet.

How to fix

Option 1: Eagerly load the relationship

Use eager loading for relationships you need immediately after object creation or retrieval.

Modify the query in create_user to eagerly load user_password using selectinload or joinedload:

# In your DAO or query
from sqlalchemy.orm import selectinload, joinedload

stmt = select(User).options(selectinload(User.user_password)).where(User.id == user_id)
result = await session.execute(stmt)
user = result.scalar_one()

or

stmt = select(User).options(joinedload(User.user_password))).where(User.id == user_id)
result = await session.execute(stmt)
user = result.scalar_one()

Comparison

Command	SQL statements	Risk of Duplicate User rows?	Use Case
joinedload	1	Yes - one per parent x child	Many-to-one or small one-to-many where performance matters
selectinload	2	No	General use; one-to-many or many-to-many

• joinedload uses a LEFT OUTER JOIN
• selectinload uses 2 selects: SELECT users and SELECT userpasswords IN (...)
• For one-to-one, as in this case, joinedload is preferred. You can also add a .unique() operation to the statement to mitigate duplicate rows.

Can also use with SqlAlchemy ORM get statement:

from sqlalchemy.orm import joinedload

user = await session.get(User, user_id, options=[joinedload(User.user_password)])

Option 2: Avoid accessing unloaded relationships

If you know the relationship should be None after creation, you can check the foreign key or avoid accessing the relationship property directly.