SQLAlchemy

The Python ORM and SQL toolkit, two decades in: the data layer beneath the FastAPI ecosystem.

SQLAlchemy is Python's database toolkit and ORM: a mature Core expression language compiling to SQL across engines, an optional ORM layer with sessions and relationships, and the foundation under half the Python data stack, including tools like Alembic for migrations.

Why founders use it: it is the stable abstraction with no dead ends. The Core layer writes SQL that reads like SQL, engine-agnostic, with connection pooling baked in, and the ORM layer maps Python objects when that pays, with the constant ability to drop to explicit SQL or to textual queries when the abstraction costs. FastAPI and Flask ecosystems assume it, and two decades of production hardening show in the edges that work.

A realistic setup: engine per environment, session factory scoped to requests, models with explicit relationships, Alembic migrations generated from model diffs, and query analysis turning on when the slow logs speak.

Trade-offs: the unit-of-work pattern and session lifecycle are genuine learning curves, async support arrived late and with caveats, and raw performance tuning eventually wants driver-specific knowledge. Compare with SQLModel for the FastAPI-native blend and with the Django ORM inside that framework.