👀 Views: 500 💬 Answers: 1 📅 Created: 2025-06-16

python fastapi sqlalchemy asyncio Python

I'm confused about Could someone explain I'm currently building an API using FastAPI and SQLAlchemy, and I've run into a `sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) want to operate on a closed database.` behavior when trying to execute multiple asynchronous queries in parallel... I'm using SQLite for local development, but I plan to switch to PostgreSQL in production. I have several endpoints that are making database calls using async/await patterns, and it seems that under load, I hit a wall where connections get exhausted. Here’s a simplified version of my code: ```python from fastapi import FastAPI from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine, async_sessionmaker from sqlalchemy.orm import sessionmaker DATABASE_URL = "sqlite+aiosqlite:///./test.db" engine = create_async_engine(DATABASE_URL, echo=True, future=True) SessionLocal = async_sessionmaker(bind=engine, class_=AsyncSession, expire_on_commit=False) app = FastAPI() async def get_db(): async with SessionLocal() as session: yield session @app.post("/items/") async def create_item(item: Item, db: AsyncSession = Depends(get_db)): db.add(item) await db.commit() await db.refresh(item) return item @app.get("/items/{item_id}") async def read_item(item_id: int, db: AsyncSession = Depends(get_db)): return await db.get(Item, item_id) ``` In my tests, when I rapidly hit the `/items/` endpoint for creating items, I sometimes see the connection pool being exhausted and I get the aforementioned behavior, which I believe is related to the database not being able to keep up with the number of concurrent requests. I've tried increasing the `pool_size` and `max_overflow` settings in the `create_async_engine` call and also added `expire_on_commit=False`, but I still face issues under high concurrency. I also confirmed that I'm using SQLAlchemy 1.4 and FastAPI 0.68.2. Is there a better way to manage database connections in an asynchronous context using FastAPI and SQLAlchemy to prevent these errors? I've been using Python for about a year now. Thanks for taking the time to read this! The project is a mobile app built with Python. Could this be a known issue?