Fastapi: [BUG] sqlalchemy relationship fields are absent in router response

[plankter]

There is still an issue with sqlalchemy Data Models similar to the https://github.com/tiangolo/fastapi/issues/4

If one has such models relationship as:

Base = declarative_base()

class Parent(Base):
    children = relationship("Child", back_populates="parent")

class Child(Base):
    parent_id = Column(
        Integer,
        ForeignKey('parent.id', onupdate='CASCADE', ondelete='CASCADE'),
        index=True
    )
    parent = relationship("Parent", back_populates="children")

then the JSON response body of the API call does return only parent_id correct value and parent field has undefined value.

However when one uses a helper method like:
def json(self): return { 'parent_id': self.parent_id, 'parent': self.parent }
then response bode has all proper values.

UPDATE:

the same issue occurs with all SqlAlchemy hybrid_property fields.

[tiangolo]

This is due to SQLAlchemy lazy-loading behavior.

By the time a model is converted to a dict, the data has not been populated (fetched from the DB) yet.

If you do anything that calls child.parent, even a single expression with that, SQLAlchemy will go and fetch the data for you. Having it available for serialization.

---

The current implementation takes whatever is returned by your path operation function, does it's best effort to convert it to something serializable with JSON (e.g. a dict) and then, if there was a Pydantic model declared as the response, passes it to that model.

And as the SQLAlchemy model doesn't really have a parent property yet at that time, it is not passed to the Pydantic model.

---

I'm thinking about changing the serialization implementation when there's a Pydantic model declared, to instead of trying to convert whatever is returned to a dict before passing it to the Pydantic model, to use the fields declared in the Pydantic model and try to extract them from that returned object.

This would probably solve the SQLAlchemy lazy-loading issues.

---

There's something else to have in mind. If FastAPI tried to extract the data directly without a Pydantic model, circular relationships would be a problem, as it would be trying to get child.parent.children[0].parent.children[0].partent, etc.

But if there's a Pydantic model with some specific non-circular fields declared, FastAPI could make SQLAlchemy generate/fetch the needed data before serializing it. Would that work for you?

[plankter]

Thank you for the detailed explanation.

I hope your suggestion will help. As about circular relationships, i suppose i already encountered them even with proper Pydantic models, though i have to test it more...

[tiangolo]

Just so you know, I just opened a PR in Pydantic: https://github.com/samuelcolvin/pydantic/pull/520 that would help us improve this.

[pourquoi]

same issue
while waiting for the PR, i'm setting the relationship to load every time:

parent = relationship("Parent", back_populates="children", lazy=False)

[pourquoi]

to follow on that, here is a simplified gist of how i'm "translating" from sqlalchemy to pydantic
https://gist.github.com/pourquoi/dd88e08e72b4a2a4e960a0106bdeda24
the pydantic models must be designed to prevent circular reference

I'm still new to python so i would appreciate any comments on how you guys are doing it.

[tiangolo]

The latest Pydantic version includes "ORM mode", to solve these specific use cases.

I just finished a deep integration of it into FastAPI, just released it in version 0.30.0 :tada:

Here are the new docs: https://fastapi.tiangolo.com/tutorial/sql-databases/

It should solve lazy-loading, hybrid-properties, dynamic attributes, relationships, and others. And it should work with all the ORMs, SQLAlchemy, Peewee, Tortoise ORM, GINO, etc.

You declare the data you want to export in Pydantic models and they take care of extracting it from your ORM models.

[github-actions[bot]]

Assuming the original issue was solved, it will be automatically closed now. But feel free to add more comments or create new issues.