Fastapi: [BUG] FastAPI doesn't return router result list when using asyncpg

[fvlima]

Describe the bug

I took the weekend to study a little the fastapi project, to try creating a full async HTTP request lifecycle (including IO with database). I had success (there are lots of things to improve and understand better) but I found a strange behavior when using postgres with asyncpg. When trying to fetch users from the endpoint [1], a server error is returned, and lots of validations errors are shown in the console. I think that is some strange behavior with asyncpg.Record, because in this point [2] the variable response_content has a list of records representing the result of the query executed. I tried changing the backend to postgres+aiopg and this problem doesn't happen. With this backend, the endpoint works as expected.

1 - http://localhost:8000/users/?q=User%20Name&limit=1&offset=0
2 - https://github.com/tiangolo/fastapi/blob/master/fastapi/routing.py#L116

To Reproduce

Create the main app

app = FastAPI()
app.include_router(routers.router)

db = databases.Database(settings.DATABASE_URL)


@app.middleware("http")
async def db_session_middleware(request: Request, call_next):
    request.state.db = db
    response = await call_next(request)
    return response


@app.on_event("startup")
async def startup():
    await db.connect()


@app.on_event("shutdown")
async def shutdown():
    await db.disconnect()

Create a router to fetch a query with many results and defining a response with typing.List[Model]

router = APIRouter()


@router.get(
    "/users/", 
    description="Endpoint to search users", 
    response_model=typing.List[User],
    dependencies=[Depends(Authentication)],
)
async def users(request: Request, p: Pagination = Depends()):
    query = select([user_table]).where(user_table.c.name == p.q).limit(p.limit).offset(p.offset)
    result = await request.state.db.fetch_all(query=query)

    return result

Create a model to be tied with the router

class User(BaseModel):
    id: UUID = ""
    address: Address = None
    email: EmailStr
    is_active: bool
    name: str = Field(..., min_length=8, max_length=64, description="The name that represents the user")
    permissions: typing.List[str] = []

    class Config:
        orm_mode = True

Database URL

DATABASE_URL=postgresql://users_api:users_api@localhost/users_api

Error

INFO:     127.0.0.1:56901 - "GET /users/?q=User%20Name&limit=1&offset=0 HTTP/1.1" 500 Internal Server Error
ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/uvicorn/protocols/http/httptools_impl.py", line 385, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/uvicorn/middleware/proxy_headers.py", line 45, in __call__
    return await self.app(scope, receive, send)
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/fastapi/applications.py", line 149, in __call__
    await super().__call__(scope, receive, send)
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/starlette/applications.py", line 102, in __call__
    await self.middleware_stack(scope, receive, send)
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/starlette/middleware/errors.py", line 181, in __call__
    raise exc from None
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/starlette/middleware/errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/starlette/middleware/base.py", line 25, in __call__
    response = await self.dispatch_func(request, self.call_next)
  File "./app/main.py", line 20, in db_session_middleware
    response = await call_next(request)
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/starlette/middleware/base.py", line 45, in call_next
    task.result()
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/starlette/middleware/base.py", line 38, in coro
    await self.app(scope, receive, send)
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/starlette/exceptions.py", line 82, in __call__
    raise exc from None
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/starlette/exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/starlette/routing.py", line 550, in __call__
    await route.handle(scope, receive, send)
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/starlette/routing.py", line 227, in handle
    await self.app(scope, receive, send)
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/starlette/routing.py", line 41, in app
    response = await func(request)
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/fastapi/routing.py", line 205, in app
    response_data = await serialize_response(
  File ".pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/fastapi/routing.py", line 126, in serialize_response
    raise ValidationError(errors, field.type_)
pydantic.error_wrappers.ValidationError: 3 validation errors for User
response -> 0 -> email
  field required (type=value_error.missing)
response -> 0 -> is_active
  field required (type=value_error.missing)
response -> 0 -> name

Pdb

> /.pyenv/versions/3.8.2/envs/sample-fast-api/lib/python3.8/site-packages/fastapi/routing.py(117)serialize_response()
-> value, errors_ = field.validate(response_content, {}, loc=("response",))
(Pdb) ll
 94     async def serialize_response(
 95         *,
 96         field: ModelField = None,
 97         response_content: Any,
 98         include: Union[SetIntStr, DictIntStrAny] = None,
 99         exclude: Union[SetIntStr, DictIntStrAny] = set(),
100         by_alias: bool = True,
101         exclude_unset: bool = False,
102         exclude_defaults: bool = False,
103         exclude_none: bool = False,
104         is_coroutine: bool = True,
105     ) -> Any:
106         if field:
107             errors = []
108             response_content = _prepare_response_content(
109                 response_content,
110                 by_alias=by_alias,
111                 exclude_unset=exclude_unset,
112                 exclude_defaults=exclude_defaults,
113                 exclude_none=exclude_none,
114             )
115             if is_coroutine:
116                 breakpoint()
117  ->             value, errors_ = field.validate(response_content, {}, loc=("response",))
118             else:
119                 value, errors_ = await run_in_threadpool(
120                     field.validate, response_content, {}, loc=("response",)
121                 )
122             if isinstance(errors_, ErrorWrapper):
123                 errors.append(errors_)
124             elif isinstance(errors_, list):
125                 errors.extend(errors_)
126             if errors:
127                 raise ValidationError(errors, field.type_)
128             return jsonable_encoder(
129                 value,
130                 include=include,
131                 exclude=exclude,
132                 by_alias=by_alias,
133                 exclude_unset=exclude_unset,
134                 exclude_defaults=exclude_defaults,
135                 exclude_none=exclude_none,
136             )
137         else:
138             return jsonable_encoder(response_content)
(Pdb) response_content
[<databases.backends.postgres.Record object at 0x10c3d5430>]
(Pdb) response_content[0]._row
<Record id=UUID('e108216a-01fe-4974-b1c1-e4858be9bb91') address_id=UUID('12f6fb53-821d-48c2-b429-154694155e79') email='[email protected]' is_active=True name='User Name' password='de3f039bb1b804e1f8b1641beeeff1e2f2f96d100d79a354fe0e52780dda43173d76f71eb935c82d5a4e748d790d4fb7e274ef81833f906a47f2c33f612801dea7c7b4db55ceae37ddbea56456b8b561c3291d1e195227003aa9b0c80455d53f' permissions=['create', 'update', 'delete']>

Changing to postgres+aiopg the response as showed bellow and all results are returned as expected

INFO:     127.0.0.1:57000 - "GET /users/?q=User%20Name&limit=1&offset=0 HTTP/1.1" 200 OK

Environment

• OS: macOS
• FastAPI Version 0.54.1
• Python version 3.8.2
• SQLAlchemy 1.3.16
• asyncpg 0.20.1
• databases 0.3.2
• email-validator 1.1.0
• pydantic 1.5.1
• starlette 0.13.2
• uvicorn 0.11.5
• uvloop 0.14.0

[chris-allnutt]

pydantic.error_wrappers.ValidationError: 3 validation errors for User
response -> 0 -> email
  field required (type=value_error.missing)
response -> 0 -> is_active
  field required (type=value_error.missing)
response -> 0 -> name

This is your error, whatever is mapping the database results to those is not setting those values.

Can you show the code you're using to fetch the results from the database and the map them to your pydantic models?

[fvlima]

This is your error, whatever is mapping the database results to those is not setting those values.

Yeap, but using another backend the same code works as expected. The code is that I showed in To reproduce:

@router.get(
    "/users/", 
    description="Endpoint to search users", 
    response_model=typing.List[User],
    dependencies=[Depends(Authentication)],
)
async def users(request: Request, p: Pagination = Depends()):
    query = select([user_table]).where(user_table.c.name == p.q).limit(p.limit).offset(p.offset)
    result = await request.state.db.fetch_all(query=query)

    return result

And about the data, is that I showed in Pbd section too, and pointed to the code here https://github.com/tiangolo/fastapi/blob/master/fastapi/routing.py#L116. The response_content has the list data retrieved from db https://github.com/encode/databases/blob/master/databases/backends/postgres.py#L155 (as shown below) but errors happen when trying to serialize/validate

response_content
[<databases.backends.postgres.Record object at 0x10c3d5430>]

response_content[0]._row
<Record id=UUID('e108216a-01fe-4974-b1c1-e4858be9bb91') address_id=UUID('12f6fb53-821d-48c2-b429-154694155e79') email='[email protected]' is_active=True name='User Name' permissions=['create', 'update', 'delete']>

If I use the backend postgres+aiopg, this code works well, but if I change to the backend postgres that will use ascynpg, this problem happens.

[fvlima]

I created a repository to keep my studies centralized, so if it's necessary to see all project code, it can be done by here https://github.com/fvlima/sample-fast-api/blob/without-gino/app/apps/users/routers.py#L17-L27

[cbows]

I can confirm this issue.
If I use databases with the postgres+asyncpg backend, I get a lot of field required (type=value_error.missing) errors. Using the same query, and instantiating the model manually in ipython via Model(**record) works as expected. Without response_model, I can confirm that all relevant fields exist, are of correct type and are returned to the client, but adding response_model=typing.List[Model] raises said errors.

[acnebs]

I haven't actually tested this to make sure, but at first glance I think this is because asyncpg's Record type is more dict than object, so you cannot access properties with dot notation (foo.bar)

This then breaks pydantic's orm_mode, which is expecting objects with named attributes, not a dict-like structure that requires foo['bar'] for accessing values (and doesn't actually have those as attrs).

@cbows Model(**record) then works because rather than using the orm_mode instantiation method, you're just spreading the dict-like as kwargs, which pydantic can obviously handle.

But again, this is just a guess, haven't actually checked any of this.

[tiangolo]

Thanks for the help here everyone! :clap: :bow:

@fvlima after this line:

result = await request.state.db.fetch_all(query=query)

You could print or log the result, see its shape, data type, etc. That would probably show why it is not being able to read the data from your return.

You could try the debug function from https://python-devtools.helpmanual.io/

[fvlima]

Hey @tiangolo, thank you to answer this issue. I already know the resulting shape and its data type, and it's the same that I showed here https://github.com/tiangolo/fastapi/issues/1369#issuecomment-623561557 and here https://github.com/tiangolo/fastapi/issues/1369#issue-611549439. My point is that the same code has different behavior if I change the database backend from aiohttp to asyncpg. With the aiohttp everything works as expected, but changing it to asyncpg, I face this problem showed here. So, this situation is a behavior that should be controlled by the fastapi framework or by pydantic the lib?

> /sample-fast-api/app/apps/users/routers.py(27)users_search()
->
(Pdb) type(result)
<class 'list'>
(Pdb) type(result[0])
<class 'databases.backends.postgres.Record'>
(Pdb) type(result[0]._row)
<class 'asyncpg.Record'>
(Pdb) result[0]._row
<Record id=UUID('24ca2936-5044-4d3e-bafe-904a68b0194f') address_id=UUID('d20918bf-0e82-4ae5-a2d0-cb569b6ac0c4') email='[email protected]' is_active=True name='User Name' password='da71e06594909c8d5f702eab9dec56ae8535ea19533a4b78ab3b97870399f7588835249e055909a0d16d713d1fb40218a49096aca5a2a9c5f334577d06244543378a5a0056882492e8415bc5d28277942d34a350c028f2428025c630ccd5ac3c' permissions=['create', 'update', 'delete']>

[tiangolo]

I'm currently not using encode/databases and I'm not an expert on it, but I know it's not an ORM, and from the data shapes you show it seems that result[0] doesn't have a shape compatible with your Pydantic model, it seems it has an attribute ._row that contains the actual data instead of it being accessible like a dict or an object/class.

It might be that databases is having a different behavior depending on the driver used, not sure (maybe they have it documented).

But anyway, if you need to support a custom different data type that doesn't behave like a dictionary or an object with attributes (e.g. an object with a single attribute ._row that in turn contains the actual data) then you might want to implement a GetterDict https://pydantic-docs.helpmanual.io/usage/models/#orm-mode-aka-arbitrary-class-instances

[Kludex]

I'm doing the following treatment to overturn this situation:

class AttrDict(dict):
    def __init__(self, *args, **kwargs):
        super(AttrDict, self).__init__(*args, **kwargs)
        self.__dict__ = self

class CRUDBase(Generic[CreateSchemaType, UpdateSchemaType, OutputSchemaType]):
    def __init__(self, table: Table):
        self._table = table

    async def get(self, **kwargs) -> OutputSchemaType:
        query = (
            self._table
                .select()
                .where(and_(getattr(self._table.c, key) == value for key, value in kwargs.items()))
        )
        obj = await database.fetch_one(query=query)
        return obj if obj is None else AttrDict(**dict(obj))

You can use the returned object and validate it with Pydantic now.

[fvlima]

So, I will close this issue since that is not a problem here. Maybe it can be handled by the pydantic project, but I think this is a topic for another discussion. If we can just override this behavior with AttrDict, there is a way to resolve this problem bu now.