[FIXED] FastAPI validation error on global retrieve

Issue

I have an issue with FastAPI coupled with SQLModel and I don't understand why it doesn't work. I have the following model:

class DeckBase(SQLModel):
    name: str = Field(sa_column=Column(TEXT))

    # region Foreign keys
    owner_id: int = Field(foreign_key="player.id")
    # endregion Foreign keys

class Deck(DeckBase, table=True):
    id: int = Field(primary_key=True)

    # region Relationship (access to the foreign key model)
    owner: Player = Relationship(back_populates="decks")
    cards: List["Card"] = Relationship(back_populates="decks", link_model=DeckCards)  # ManyToMany
    # endregion Relationship (access to the foreign key model)

Here is the database representation:

I've also defined the following function to retrieve all the decks accordingly to the SQLModel documentation:

@router.get("/", response_model=List[DeckRead])
async def read_all(offset: int = 0,
                   limit: int = Query(default=100, le=100),
                   db: Session = Depends(DbContext().get_db)):
    decks = db.execute(select(Deck).offset(offset).limit(limit)).all()
    return decks

With DeckRead being:

class DeckRead(DeckBase):
    id: int

But when i'm calling the route, i get this error:

[2024-01-02 18:37:19] [ERROR   ] uvicorn.error: Exception in ASGI application
Traceback (most recent call last):

... (skipping the traceback)

fastapi.exceptions.ResponseValidationError: 3 validation errors:
  {'type': 'missing', 'loc': ('response', 0, 'name'), 'msg': 'Field required', 'input': (Deck(id=1, name='Fire deck', owner_id=1),), 'url': 'https://errors.pydantic.dev/2.5/v/missing'}
  {'type': 'missing', 'loc': ('response', 0, 'owner_id'), 'msg': 'Field required', 'input': (Deck(id=1, name='Fire deck', owner_id=1),), 'url': 'https://errors.pydantic.dev/2.5/v/missing'}
  {'type': 'missing', 'loc': ('response', 0, 'id'), 'msg': 'Field required', 'input': (Deck(id=1, name='Fire deck', owner_id=1),), 'url': 'https://errors.pydantic.dev/2.5/v/missing'}

Why does it not work? Also, what bothers me is that doing the code below works fine:

@router.get("/", response_model=List[DeckRead])
async def read_all(offset: int = 0,
                   limit: int = Query(default=100, le=100),
                   db: Session = Depends(DbContext().get_db)):
    decks = db.get(Deck, 1)
    return [decks]

It looks like the all() function returns me something that fastapi doesn't understand

Thanks!

Solution

SQLModel uses a special exec function which wraps the underlying execute from SQLAlchemy. Simply changing your database call to

decks = db.exec(select(Deck).offset(offset).limit(limit)).all()

should do the trick.

The reason for this is that SQLALchemy"s execute will return a list of tuples (since you can select more than one model), unless you add .scalars() to your query. SQLModel has a special function for the specific case where you only select one model.

Answered By - M.O.