MissingGreenlet exception while creating both parent and child instances and returning parent

[ArazHeydarov]

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

from collections.abc import AsyncIterator
from contextlib import asynccontextmanager

import uvicorn
from fastapi import FastAPI
from sqlalchemy.ext.asyncio import create_async_engine
from sqlmodel import Field, Relationship, SQLModel, select
from sqlmodel.ext.asyncio.session import AsyncSession


class Parent(SQLModel, table=True):
    __tablename__ = "parent"

    id: int | None = Field(default=None, primary_key=True)
    name: str
    intermediates: list["Intermediate"] = Relationship(sa_relationship_kwargs={"lazy": "selectin"})


class ParentResponseSchema(SQLModel):
    id: int
    name: str
    intermediates: list["IntermediateResponseSchema"]


class ParentRequestSchema(SQLModel):
    name: str
    intermediates: list[str]


class Intermediate(SQLModel, table=True):
    __tablename__ = "intermediate"

    id: int | None = Field(default=None, primary_key=True)
    name: str
    parent_id: int = Field(foreign_key="parent.id")
    children: list["Child"] = Relationship(sa_relationship_kwargs={"lazy": "selectin"})


class IntermediateResponseSchema(SQLModel):
    id: int
    name: str
    parent_id: int
    children: list["ChildResponseSchema"]


class Child(SQLModel, table=True):
    __tablename__ = "child"

    id: int | None = Field(default=None, primary_key=True)
    name: str
    intermediate_id: int = Field(foreign_key="intermediate.id")


class ChildResponseSchema(SQLModel):
    id: int
    name: str


engine = create_async_engine("postgresql+asyncpg://postgres:admin123@localhost:5432/postgres")


@asynccontextmanager
async def lifespan(_: FastAPI) -> AsyncIterator[None]:
    async with engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.create_all)
    yield
    # async with engine.begin() as conn:
    #     await conn.run_sync(SQLModel.metadata.drop_all)


app = FastAPI(lifespan=lifespan)


@app.post("/parent", response_model=ParentResponseSchema)
async def create(body: ParentRequestSchema):
    async with AsyncSession(engine) as session:
        query = select(Parent).where(Parent.name == body.name)
        result = await session.exec(query)
        parent = result.first()
        if parent is None:
            parent = Parent(name=body.name)
            session.add(parent)
            await session.commit()
            await session.refresh(parent)

        for intermediate_name in body.intermediates:
            query = select(Intermediate).where(Intermediate.name == intermediate_name)
            result = await session.exec(query)
            intermediate = result.first()
            if intermediate is None:
                intermediate = Intermediate(name=intermediate_name, parent_id=parent.id)
                session.add(intermediate)
                await session.commit()
                await session.refresh(intermediate)

            parent.intermediates.append(intermediate)
    return parent


@app.get("/parent", response_model=ParentResponseSchema)
async def alive():
    async with AsyncSession(engine) as session:
        parent = await session.get(Parent, 1)
    return parent


if __name__ == "__main__":
    uvicorn.run("main:app", host="localhost", port=8080, reload=True)

Description

• Sending JSON request containing Parent and relative Child(intermediate) instance information to FastAPI application
• Checking if the record in DB exists. If not, create records for both Parent and relative Child (Intermediate).
• Make a relationship between them
• Returning the Parent instance

Receiving sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/20/xd2s)

Disclaimer: It's possible that the problem is related to SQLAlchemy greenlet contexts; however, since the error happens when I try to return the Parent as response model, I thought this place is more suitable for opening this discussion.

The most important note here is that the problem only happens when the instances are not in DB and they are created. If you make a POST request 3 times, where all Parent and Instances are created, you don't receive any exceptions afterwards.

To reproduce the issue, send a POST request to localhost:8000/parent with body:

{
    "name": "parent1",
    "intermediates": ["child1", "child2"]
}

The problem is not related to async lazy loading; for this purpose, I also added a get endpoint to show that it works without any issues.

Operating System

macOS

Operating System Details

No response

SQLModel Version

sqlmodel==0.0.24

Python Version

Python 3.13.2

Additional Context

No response

[YuriiMotov]

At the moment FastAPI validates response, your DB session is closed, but child objects are not loaded by default and sqlalchemy will try to load children. But this will happen in non-async context and that will cause that error.

To avoid this error you should pre-load child objects using selectinload:

from sqlalchemy.orm import selectinload

...
        parent = await session.get(Parent, 1, options=[selectinload(Parent.children)])