Problems with Async Database Queries Using SQLAlchemy in FastAPI - Connection Pool Errors
I'm integrating two systems and I've been banging my head against this for hours. I'm working on a project and hit a roadblock. I'm working with issues when trying to execute asynchronous database queries with SQLAlchemy in my FastAPI application. My setup involves using PostgreSQL with SQLAlchemy 1.4 and FastAPI 0.68.1. The behavior I consistently see is `sqlalchemy.exc.DisconnectionError: DisconnectionError('Could not reconnect to database')`. This occurs when I attempt to make multiple requests concurrently. I’ve ensured that I’m using an async session with the `AsyncSession` from SQLAlchemy. Here’s a snippet of my code: ```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) AsyncSessionLocal = async_sessionmaker(bind=engine, class_=AsyncSession) app = FastAPI() async def get_db() -> AsyncSession: async with AsyncSessionLocal() as session: yield session @app.get("/items/{item_id}") async def read_item(item_id: int, db: AsyncSession = Depends(get_db)): result = await db.execute(select(Item).where(Item.id == item_id)) return result.scalars().first() ``` I’ve tried adjusting the connection pool size by modifying `create_async_engine` with `pool_size` and `max_overflow`, but the errors continue. Here’s what I added: ```python engine = create_async_engine(DATABASE_URL, pool_size=20, max_overflow=10) ``` Additionally, I’ve confirmed that my database is set to allow multiple connections. I’ve also utilized `asyncio.run()` for testing and this led to a `RuntimeError: want to run the event loop while another loop is running` behavior. I’m unsure if the scenario is with how I’m managing the database connections or if there’s another cause. Any suggestions on how to resolve these issues would be greatly appreciated! My development environment is Linux. Has anyone else encountered this? This is for a CLI tool running on Windows 10.