add support for selectinload relationship loading technique when orm_mode is enabled

[idanfishman]

Initial Checks

• [X] I have searched Google & GitHub for similar requests and couldn't find anything
• [X] I have read and followed the docs and still think this feature is missing

Description

Hi There, I have faced a performance/deal breaker issue while using FastAPI with Pydantic, Asyncpg, SqlAlchemy.

The case: You have a relationship between two SqlAlchemy models (Tables) which configured the relationship lazy attribute to lazy. https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html

class Branch(Base):
    __tablename__ = "branch"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid4, index=True)

    title = Column(String(length=100), nullable=False, unique=True)
    description = Column(String(length=400))

    change_requests = relationship(
        "ChangeRequest",
        order_by="ChangeRequest.created_at",
    )

class ChangeRequest(Base):
    __tablename__ = "change_request"

    number = Column(String(length=20), primary_key=True, index=True)

    type = Column(String(length=1), nullable=False)
    description = Column(String(length=75))
    status = Column(String(length=1), nullable=False)

    branch_id = Column(UUID(as_uuid=True), ForeignKey("branch.id"), nullable=False)

You have created recursive Pydantic Model which uses orm_mode. You have created a FastAPI Endpoint which returns as response the recursive Pydantic model

You get this error:

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/14/xd2s)

The only available solution for this problem is to configure the relationship loading technique to eager select for example selectin

class Branch(Base):
    __tablename__ = "branch"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid4, index=True)

    title = Column(String(length=100), nullable=False, unique=True)
    description = Column(String(length=400))

    change_requests = relationship(
        "ChangeRequest",
        lazy="selectin",
        order_by="ChangeRequest.created_at",
    )

this solution affects the whole relationship, while the real solution is simple, just use eager loading for recursive models using the SqlAlchemy selectinload function. https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html.

Affected Components

• [ ] Compatibility between releases
• [ ] Data validation/parsing
• [X] Data serialization - .dict() and .json()
• [ ] JSON Schema
• [ ] Dataclasses
• [X] Model Config
• [ ] Field Types - adding or changing a particular data type
• [ ] Function validation decorator
• [ ] Generic Models
• [X] Other Model behaviour - construct(), pickling, private attributes, ORM mode
• [ ] Settings Management
• [X] Plugins and integration with other tools - mypy, FastAPI, python-devtools, Hypothesis, VS Code, PyCharm, etc.

[samuelcolvin]

We don't have any special support for sqlalchemy.

How is pydantic supposed to know it shouldn't loo at that attribute?

This there's a generic way to say "this attitude is lazy, please don't get it" we can ignore it. Otherwise I don't see where to go???

[idanfishman]

What do you suggest for people who uses SqlAlchemy via Async to do? I went trough stackoverflow and people ended up not using pydantic, which I think is not great thing for you guys.

[samuelcolvin]

I'm happy to fix it, or review a PR which fixes it, but I hoped you might come with a solution as well as a problem.

Ideally that's how open source works.

Otherwise I'll try to look into it when preparing for V2.

[idanfishman]

I will try to fix it and maybe create a PR, will notify you next week 😄

[samuelcolvin]

Any fix will need to target V2, so might need to made in pydantic-core.

One option would be a black list of attributes to ignore, would that work for you?

[samuelcolvin]

Looking at this again, there's a chance it's already fixed in pydantic-core as we only get the attributes required (unless you set extra=allow).

Probably the first step would be to see if it's still a bug with pydantic-core.