getting joined tables from sqlmodel as a nested responde model in fastapi

Question

I cannot figure it out how to display a one to many relationship using fastapi and sqlmodel. I've read through this question but my case seems to be slightly different. Specially in the function call.

This is my schemas.py:

from typing import Optional
from sqlmodel import Field, Relationship, SQLModel

class BinaryBase(SQLModel):
    product_id: int
    software_install_path: Optional[str] = None
    host_id: int = Field(foreign_key="host.id", nullable=False)


class Binary(BinaryBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    host_id: int = Field(foreign_key="host.id", nullable=False)
    host: "Host" = Relationship(back_populates="binaries")


class HostBase(SQLModel):
    name: str
    region: Optional[str] = None
    os_version: Optional[str] = None
    network: Optional[int] = None


class Host(HostBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    binaries: list[Binary] = Relationship(back_populates='host')


class HostReadWithBinary(HostBase):
    bins: list[HostBase] = []


class BinaryReadWithHost(BinaryBase):
    host: BinaryBase

And this is my main.py:

from fastapi import Depends, FastAPI
from sqlmodel import Session, col, select

...

@app.get(
    "/binaries/",
    response_model=list[HostReadWithBinary]
)
def get_binary(name: Optional[str] = None, session: Session = Depends(get_session)) -> list[HostReadWithBinary]:
    query = select(Host).limit(100)
    if name:
        query = query.where(col(Host.name).contains(name.lower()))

    return session.exec(query).all()

The Host table represents the 1 part and the Binary table represents the many part. I would like to get a response of all the BinaryBase attributes for all the hosts eagerly. But what I get is this:

[
  {
    "name": "hkl20014889",
    "region": "HK",
    "os_version": "Red Hat 6.10",
    "network": 3,
    "bins": []
  },
  {
    "name": "hkl20016283",
    "region": "HK",
    "os_version": "Red Hat 6.10",
    "network": 3,
    "bins": []
  },
.... 

Theoreticaly bins should hold the attributes of the Host table when id in Host joins host_id in Binary.

Answer 1

You need to realize that when you define a response_model for a route, it will always try to parse whatever data comes out of your route handler function (get_binary in this case) through that model. This is done by essentially calling the .from_orm method on the response model, which goes through all fields defined on it and tries to find corresponding attributes (i.e. with the same names) on the object you pass to it.

The model you specified (in a list, but the argument stands) is HostReadWithBinary. Aside from the fields defined on its parent model HostBase it only has the field bins, which is supposed to be a list of HostBase.

First of all, I think you meant to declare that bins field to be of the type list[BinaryBase], not list[HostBase]. If you had named the field correctly, this would have caused an error, but this is where your second mistake comes in.

You also named the field on your response model bins, but your handler function performs a query that returns a list of Host model instances. That model does not have a bins field. It has a field named binaries. This means that when the from_orm method gets to the HostReadWithBinary.bins field, it checks if the corresponding Host instance has an attribute named bins. It does not find one, but no problem because you set a default for HostReadWithBinary.bins, namely the empty list [], so that is what is set on each of the resulting response model instances.

You should therefore be able to fix your error by changing the response model definition like this:

class HostReadWithBinary(HostBase):
    binaries: list[BinaryBase] = []

Alternatively, you can change the name of the relationship field on your Host model:

class Host(HostBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    bins: list[Binary] = Relationship(back_populates='host')


class HostReadWithBinary(HostBase):
    bins: list[BinaryBase] = []

They need to be the same, otherwise parsing an object of one model to the other will not work (properly).

---

Side note: You also mistakenly annotated the host field on BinaryReadWithHost with BinaryBase instead of HostBase.

PS: I also just noticed a minor mistake related to type annotations. You declare the return type of your route handler function to be list[HostReadWithBinary], but that is not what it returns. It returns list[Host]. This is part of the misunderstanding with the response models. The decorated version of your route is what returns list[HostReadWithBinary]. Your route handler get_binary by itself (i.e. before decoration) returns list[Host], which is then passed to the wrapper around it and that wrapper parses it to list[HostReadWithBinary] and sends that data on its way (to the client eventually). This wrapper action obviously happens behind the scenes and is part of that FastAPI decorator magic.