Skip to content

ADR-004: SQLAlchemy 2.0 with Async Support

Status

Accepted

Context

We need an ORM for database access in GrinSystem. Requirements: - Async support (FastAPI is async) - Type safety - Good ecosystem support - Migration support - Multi-tenancy RLS compatibility

Decision

We will use SQLAlchemy 2.0 with async support.

Key features used: - AsyncSession for async database operations - select() with 2.0 style queries - Mapped classes with type hints - Alembic for migrations - Connection pool with async support

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine

engine = create_async_engine(DATABASE_URL)
async_session = async_sessionmaker(engine, class_=AsyncSession)

async with async_session() as session:
    result = await session.execute(select(Recipe).where(Recipe.id == id))
    recipe = result.scalar_one_or_none()

Consequences

Positive

  • Native async/await support
  • Type-safe queries with 2.0 style
  • Mature ecosystem, well documented
  • Alembic integration for migrations
  • Works with PostgreSQL RLS
  • Large community, ongoing development

Negative

  • Learning curve for async patterns
  • More complex than synchronous ORM
  • Connection management requires care
  • Some third-party tools don't support async

Neutral

  • Requires greenlet for some operations

Alternatives Considered

Option Pros Cons Why not chosen
SQLAlchemy 1.4 sync Mature, simple Not async, blocks event loop Performance issue
SQLAlchemy 2.0 async Async, typed, mature Learning curve Chosen
Tortoise ORM Async-native, Django-like Smaller ecosystem, less mature Risk for production
Prisma Type-safe, async Python client less mature Ecosystem risk
Raw SQL Maximum control No ORM benefits, boilerplate Reinventing wheel

References