FastApi sqlalchemy Connection was closed in the middle of operation

Question

I have an async FastApi application with async sqlalchemy, source code (will not provide schemas.py because it is not necessary):

database.py

from sqlalchemy import (
    Column,
    String,
)
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.decl_api import DeclarativeMeta

from app.config import settings


engine = create_async_engine(settings.DATABASE_URL)
Base: DeclarativeMeta = declarative_base()
async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)


class Titles(Base):
    __tablename__ = "titles"
    id = Column(String(100), primary_key=True)
    title = Column(String(100), unique=True)


async def get_session() -> AsyncSession:
    async with async_session() as session:
        yield session

routers.py

import .database
from fastapi_utils.cbv import cbv
from fastapi_utils.inferring_router import InferringRouter


router = InferringRouter()


async def get_titles(session: AsyncSession):
    results = await session.execute(select(database.Titles)))
    return results.scalars().all()


@cbv(router)
class TitlesView:
    session: AsyncSession = Depends(database.get_session)

    @router.get("/titles", status_code=HTTP_200_OK)
    async def get(self) -> List[TitlesSchema]:
        results = await get_titles(self.session)
        return [TitlesSchema.from_orm(result) for result in results]

main.py

from fastapi import FastAPI

from app.routers import router 


def create_app() -> FastAPI:
    app = FastAPI()
    app .include_router(routers, prefix="/", tags=["Titles"])

    return printer_app


app = create_app()

It runs with docker:

CMD ["uvicorn", "main:app", "--reload", "--host", "0.0.0.0", "--port", "8000", "--limit-max-requests", "10000"]

And it has Postgres database with default settings in docker too. It all runs at docker-swarm. Works fine at first, accepts all requests. But if you leave it for 15-30 minutes (I did not count), and then make a request, it will not work:

<class 'asyncpg.exceptions.ConnectionDoesNotExistError'>: connection was closed in the middle of operation

And right after that I send the next request and it doesn't throw an error. What could it be? How do I get rid of the ConnectionDoesNotExistError?

Answer 1

I solve that using pool_pre_ping setting like that:

engine = create_async_engine(DB_URL, pool_pre_ping=True)

https://docs.sqlalchemy.org/en/14/core/pooling.html