implementing async database queries in FastAPI - connections not being closed properly
Can someone help me understand I've been struggling with this for a few days now and could really use some help... I'm working with an scenario with my FastAPI application where it seems that the database connections are not being closed properly when using async queries with SQLAlchemy. I am using FastAPI 0.75.0 and SQLAlchemy 1.4.27, and I'm trying to implement asynchronous database access using the asyncpg driver. When I run the following code to fetch data asynchronously, I sometimes get the `OperationalError: (asyncpg.exceptions.CannotConnectNow) connection already in use` behavior. Here's the code snippet: ```python from fastapi import FastAPI, Depends from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine, async_sessionmaker from sqlalchemy.orm import sessionmaker DATABASE_URL = 'postgresql+asyncpg://user:password@localhost/dbname' engine = create_async_engine(DATABASE_URL, echo=True) session_local = async_sessionmaker(bind=engine, class_=AsyncSession) app = FastAPI() async def get_db(): async with session_local() as session: yield session @app.get("/items/") async def read_items(db: AsyncSession = Depends(get_db)): result = await db.execute("SELECT * FROM items") items = result.scalars().all() return items ``` I have ensured that I am using `async with` when creating the session, but it appears that some connections are still left open after requests are processed. This is particularly problematic under load, as I’ve noticed spikes in connection errors when multiple requests hit the `/items/` endpoint simultaneously. I've tried adding connection pooling options to the `create_async_engine`, but it didn’t seem to help. Here’s how I set that up: ```python engine = create_async_engine(DATABASE_URL, echo=True, pool_size=10, max_overflow=20) ``` Despite these adjustments, the question continues. Are there any best practices for managing async database connections in FastAPI with SQLAlchemy, or is there something I might be missing in my implementation? Any insights would be greatly appreciated! For context: I'm using Python on macOS. Am I missing something obvious? I'm coming from a different tech stack and learning Python. Any help would be greatly appreciated!