Backend6 min read

SQLAlchemy

Map Python classes to database tables with SQLAlchemy - Core and ORM modes, relationships, migrations with Alembic, and query optimization.

SQLAlchemyPythonORMDatabasePostgreSQL

skill: SQLAlchemy category: Backend tags: [SQLAlchemy, Python, ORM, Database, PostgreSQL] readingTime: 6

SQLAlchemy is the most powerful Python ORM - it maps to every database, supports complex queries, and gives you escape hatches to raw SQL when the ORM can't express what you need. Use the ORM for most work; drop to Core or raw SQL for complex analytical queries.

Workflow

Define models with DeclarativeBaseDeclarativeBase maps Python classes to database tables with type annotations that also work for IDE autocomplete.
Manage sessions correctlySQLAlchemy sessions track objects and flush changes to the database. Sessions must be closed after each request - use context managers.
Query with select and relationshipsSQLAlchemy 2.0 uses the select() function - more explicit and composable than the 1.x query() API.
Migrate schema with AlembicAlembic is the standard migration tool for SQLAlchemy - generates migration scripts from model changes like Django's makemigrations.
Use async SQLAlchemy with FastAPIAsync SQLAlchemy with asyncpg allows truly non-blocking database calls in async frameworks.

Output Format

SQLAlchemy setup: DeclarativeBase models, context-managed sessions, select() queries with eager loading, Alembic migrations.

Base(DeclarativeBase)
User(Base): mapped_column, relationship
SessionLocal = sessionmaker(engine)
with get_db() as db:
    stmt = select(User).options(selectinload(User.orders))
    users = db.scalars(stmt).all()

Examples

Input

Output

Edge Cases & Gotchas

expire_on_commit - By default, attributes expire after commit and trigger a new SELECT on access. Set expire_on_commit=False in sessionmaker for APIs that read objects after commit.
Connection pool exhaustion - pool_size defaults to 5 - insufficient for concurrent async requests. Set pool_size=10-20 plus max_overflow for burst capacity.
Alembic autogenerate misses - Alembic autogenerate detects column adds/removes but misses constraints, sequences, and custom types. Always review generated migrations before applying.
Bulk inserts performance - db.add() in a loop creates one INSERT per object. Use db.bulk_insert_mappings() or session.execute(insert(Model), list_of_dicts) for batch inserts.

When to Break These Rules

Use raw SQL with psycopg3 for complex analytical queries with CTEs and window functions - SQLAlchemy ORM makes these awkward.
Use Tortoise ORM or SQLModel for greenfield async-first projects - they have cleaner async APIs than SQLAlchemy's async layer.

Reference Files

https://docs.sqlalchemy.org/en/20/
https://alembic.sqlalchemy.org/