AsyncSession.refresh on self referencing relationships don't load correctly

[A00474880]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

class Node(SQLModel, table=True):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    parent_node_id: uuid.UUID | None = Field(
        default=None, foreign_key="node.id", nullable=True, ondelete="SET NULL"
    )
    parent_node: Optional["Node"] = Relationship(
        back_populates="sub_nodes",
        sa_relationship_kwargs=dict(remote_side="Node.id", lazy="raise"),
    )
    sub_nodes: list["Node"] = Relationship(
        back_populates="parent_node", sa_relationship_kwargs={"lazy": "raise"}
    )


async def create_node(session: AsyncSession) -> Node:
    node = Node(parent_node_id="19e42ad3-d0f6-48ea-a93b-bcb9f2de7718") #reference an existing node
    session.add(node)
    await session.commit()
    await session.refresh(node, attribute_names["parent_node", "sub_nodes"]
    return node

Description

In the example above, the node instance doesn't have parent_node loaded properly and the field throws:
InvalidRequestError: 'Node.parent_node' is not available due to lazy='raise'

sub_nodes loads properly and has value of empty list []

Using:
sqlalchemy Version: 2.0.44
sqlmodel Version: 0.0.27

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.27

Python Version

3.13.5

Additional Context

I'ved opened a similar discussion in SqlAlchemy and determined that this is specific to SQLModel.
SQLAlchemy test scripts:

import asyncio
from typing import List
from typing import Optional

from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
    pass


class Node(Base):
    __tablename__ = "node"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    parent_node_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("node.id"), nullable=True
    )
    parent_node: Mapped[Optional["Node"]] = relationship(
        back_populates="sub_nodes", remote_side="Node.id", lazy="raise"
    )
    sub_nodes: Mapped[List["Node"]] = relationship(
        back_populates="parent_node", lazy="raise"
    )


async def main():
    engine = create_async_engine("sqlite+aiosqlite:///:memory:")

    try:
        async with engine.begin() as conn:
            await conn.run_sync(Base.metadata.create_all)

        async with AsyncSession(engine) as session:
            parent = Node(id=1, name="parent")
            child = Node(id=2, name="child", parent_node_id=parent.id)
            session.add(parent)
            session.add(child)
            await session.commit()

            assert "parent_node" not in child.__dict__
            assert "sub_nodes" not in child.__dict__
            await session.refresh(child, attribute_names=["parent_node", "sub_nodes"])

            print(f"Child's parent: {child.parent_node}")
            print(f"Child's sub_nodes: {child.sub_nodes}")
    finally:
        await engine.dispose()


if __name__ == "__main__":
    asyncio.run(main())

Output:

Child's parent: <__main__.Node object at 0x7b36c11c6510>
Child's sub_nodes: []

SQLModel testscript:

import asyncio
from typing import Optional
import uuid

from sqlalchemy.ext.asyncio import create_async_engine
from sqlmodel.ext.asyncio.session import AsyncSession
from sqlmodel import Field, Relationship, SQLModel


class Node(SQLModel, table=True):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    parent_node_id: uuid.UUID | None = Field(
        default=None, foreign_key="node.id", nullable=True, ondelete="SET NULL"
    )
    parent_node: Optional["Node"] = Relationship(
        back_populates="sub_nodes",
        sa_relationship_kwargs=dict(remote_side="Node.id", lazy="raise"),
    )
    sub_nodes: list["Node"] = Relationship(
        back_populates="parent_node", sa_relationship_kwargs={"lazy": "raise"}
    )


async def main():
    engine = create_async_engine("sqlite+aiosqlite:///:memory:")

    try:
        async with engine.begin() as conn:
            await conn.run_sync(SQLModel.metadata.create_all)

        async with AsyncSession(engine) as session:
            parent = Node(name="parent")
            child = Node(name="child", parent_node_id=parent.id)
            session.add(parent)
            session.add(child)
            await session.commit()

            assert "parent_node" not in child.__dict__
            assert "sub_nodes" not in child.__dict__
            await session.refresh(child, attribute_names=["parent_node", "sub_nodes"])

            print(f"Child's parent: {child.parent_node}")
            print(f"Child's sub_nodes: {child.sub_nodes}")
    finally:
        await engine.dispose()


if __name__ == "__main__":
    asyncio.run(main())

Output:

Child's parent: 
Child's sub_nodes: []

[A00474880]

Here's the sqlalchemy thread for anyone interested.
sqlalchemy/sqlalchemy#13081

As discussed, in my actual use case I have set expire_on_commit=False which makes the above testcase work. I will try and add a real reproduction when I have time to demo the problem better.

[itzzdev09]

Before trusting the remote_side=lambda: [Node.id] fix above, I actually reproduced this rather than reasoned about it (that answer reads like the same AI-generated pattern that's been showing up confidently-wrong on other SQLModel discussions lately, so worth double-checking before anyone marks it accepted).

Setup: fresh venv, sqlmodel==0.0.45, sqlalchemy==2.0.54 (current as of today), your original repro script unmodified — remote_side="Node.id" as a plain string, no lambda.

Result: the exact InvalidRequestError: ... is not available due to lazy='raise' you reported is gone on current versions. But the bug isn't fixed, it just changed shape:

await session.refresh(child, attribute_names=["parent_node", "sub_nodes"])
print(repr(child.parent_node))          # Node()   <- not None, not the real parent
print("parent_node" in child.__dict__)  # True
print(child.parent_node.id)             # raises sqlalchemy.exc.MissingGreenlet

child.parent_node isn't None and it isn't your real parent object either — it's a near-empty, expired stub Node instance. The SQL log confirms refresh() runs a normal query to get parent_node_id, then a separate query for the parent row (SELECT node.id, node.name, node.parent_node_id WHERE node.id = ?) — but the result never actually gets attached to that stub before refresh() returns; the stub stays expired. So merely reading .id off it later tries to lazily reload it, and if that happens outside an active await, you get MissingGreenlet instead of the lazy='raise' error you'd expect (this refresh()-driven scalar reload path apparently doesn't go through the same lazy='raise' guard that direct relationship access does).

I don't have a fix for session.refresh(attribute_names=[...]) itself on a self-referential m2o with lazy='raise' — I'd call this a real bug in the SQLAlchemy/SQLModel async-refresh interaction rather than a config mistake, and I'd file it as such rather than looking for a Relationship() kwarg workaround. Also worth checking sqlalchemy/sqlalchemy#13081 (the sibling thread you opened) — if their reproduction with expire_on_commit=False sidesteps this, that's consistent with the stub being an expired-attribute problem, not a relationship-configuration problem.

What actually works reliably, and what I'd use in production instead of refresh(attribute_names=...) for this case:

from sqlalchemy.orm import selectinload
from sqlmodel import select

stmt = (
    select(Node)
    .where(Node.id == child.id)
    .options(selectinload(Node.parent_node), selectinload(Node.sub_nodes))
)
child = (await session.exec(stmt)).one()

This issues explicit, single-purpose SELECTs for the relationships through the real relationship loader (respecting lazy='raise' correctly since you're opting in via .options()), and I verified it does return the fully-populated parent, not a stub.

[amasen02]

Solid catch @itzzdev09 on the expired stub and MissingGreenlet behavior in SQLAlchemy 2.0.54.

You're completely right: selectinload via an explicit select() statement is the cleanest and most dependable pattern here. In async SQLAlchemy and SQLModel, relying on session.refresh(..., attribute_names=[...]) for self-referential graph edges frequently runs into attribute expiration traps because the scalar reload path doesn't eagerly populate the related identity map attributes before returning.

Explicitly using .options(selectinload(Node.parent_node), selectinload(Node.sub_nodes)) avoids that entire class of issues and handles lazy="raise" cleanly.