Handling Asynchronous Database Transactions in FastAPI with SQLAlchemy
I'm maintaining legacy code that I'm building a FastAPI application and need to handle asynchronous database transactions using SQLAlchemy... However, I'm running into issues when trying to commit changes to the database within an async function. It seems like the session is not being properly handled, leading to the following error: ``` sqlalchemy.exc.InvalidRequestError: This session is in 'pending' state; it is not possible to execute against this session. ``` I have defined my database session as an async context manager, but I'm not sure if I'm using it correctly. Here's a simplified version of my code: ```python from fastapi import FastAPI, Depends from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession from sqlalchemy.orm import sessionmaker from sqlalchemy import Column, Integer, String, select, insert from sqlalchemy.ext.declarative import declarative_base DATABASE_URL = "postgresql+asyncpg://user:password@localhost/db" engine = create_async_engine(DATABASE_URL, echo=True) Base = declarative_base() SessionLocal = sessionmaker(bind=engine, class_=AsyncSession, expire_on_commit=False) async def get_db(): async with SessionLocal() as session: yield session app = FastAPI() class Item(Base): __tablename__ = 'items' id = Column(Integer, primary_key=True, index=True) name = Column(String, index=True) @app.post('/items/') async def create_item(item: Item, db: AsyncSession = Depends(get_db)): async with db.begin(): db.add(item) await db.commit() return item ``` In the code, I’ve defined an asynchronous `create_item` endpoint that should add an item to the database. However, I keep encountering the invalid request error. I've tried moving the `commit()` call outside of the `async with db.begin():` block, but that leads to a different error: ``` sqlalchemy.exc.StatementError: (sqlalchemy.exc.NoResultFound) No row was found for one() ``` Does anyone have insights on how to properly manage asynchronous database transactions with SQLAlchemy in FastAPI? Any advice or code examples would be greatly appreciated. I'm working on a application that needs to handle this. What's the best practice here? I've been using Python for about a year now. Any pointers in the right direction? Am I missing something obvious?