SQLAlchemy

SQLAlchemy is the most powerful and widely used database toolkit in Python — the foundation beneath FastAPI apps, Flask backends, data pipelines, and countless production systems. It's really two tools in one: Core, an expressive SQL-building layer, and the ORM, an object-mapping layer built on top of Core. You can use either or both, which is why it fits everything from a quick script to a large domain-driven application.

Unlike ORMs that hide SQL as much as possible, SQLAlchemy's philosophy is to give you a faithful, composable abstraction over SQL that never pretends the relational model isn't there. That makes it more to learn than Django's batteries-included ORM — and far more capable when your queries get serious. SQLAlchemy 2.0 modernized the whole API with full type-hint support and a unified async/sync interface.

TL;DR

Quick Example

Declarative models and a session, in SQLAlchemy 2.0 style:

Core Concepts

Core vs ORM

The two-layer design is SQLAlchemy's defining trait:

Core builds SQL programmatically and composably (queries are objects you can combine, parameterize, reuse); the ORM adds identity mapping, change tracking, and relationships on top. Data engineers often live in Core; application developers in the ORM; most real projects use both.

The Session and Unit of Work

The Session is SQLAlchemy's most important — and most misunderstood — concept. It's a unit of work: it holds objects you've loaded or added, tracks what changed, and flushes all pending changes as SQL in the right order when you commit.

Key implications: objects are bound to their session; the session is a transaction boundary; and you generally want one session per request/task, not one global session. Web frameworks provide session-per-request scoping (FastAPI dependencies, Flask-SQLAlchemy).

Relationship Loading Strategies

Where SQLAlchemy makes you think — and where it's better than ORMs that hide it. Accessing user.orders lazily triggers a query per access (N+1); you choose the loading strategy explicitly:

Making the strategy explicit is more work than Django's select_related but gives precise control over the query shape — the trade that runs through all of SQLAlchemy.

Migrations with Alembic

Alembic (by SQLAlchemy's author) is the migration tool: it autogenerates migration scripts by diffing your models against the database, which you review and apply. Standard migration workflow — versioned, reversible, committed to git — with the caveat that autogenerate needs a human check (it misses some changes like column renames, seeing them as drop+add).

SQLAlchemy vs Other Python ORMs

SQLAlchemy is the choice when you want power and framework-independence — FastAPI's de facto ORM, and the tool for anything with genuinely complex queries. SQLModel (from FastAPI's author) wraps SQLAlchemy + Pydantic for lighter CRUD with the same engine underneath, a popular on-ramp. Django's ORM is only "less powerful" at the complex-query margin — for Django apps it's the right tool.

Common Mistakes

One Global Session

A module-level session shared across requests corrupts state, leaks transactions, and breaks under concurrency. Use one session per request/task, opened and closed around the unit of work (a context manager, a framework-scoped dependency).

N+1 from Lazy Loading

The default lazy strategy silently emits a query per relationship access — fine for one object, disastrous in a loop over a list. Add selectinload/joinedload options to queries whose relations you'll access. Enable SQL echo (echo=True) in dev to see the N+1.

Detached-Instance Errors

Accessing a lazily-loaded attribute after its session closed raises DetachedInstanceError (common when returning ORM objects from a request handler after the session scope ends). Eager-load what you need before the session closes, or convert to plain dicts / Pydantic models at the boundary.

Forgetting to Commit (or Committing Too Much)

Changes stay in the session until commit(); forget it and nothing persists. Conversely, committing inside a loop instead of once at the end multiplies transaction overhead. Commit at the unit-of-work boundary.

Using the ORM for Bulk Operations

Updating a million rows by loading a million objects is pathological. Use Core bulk statements (update().where(...), session.execute(insert(...), rows)) for mass changes — orders of magnitude faster.

FAQ

Core or ORM — which should I use?

Both, by task. The ORM for application domain objects (users, orders — things with identity and relationships you navigate). Core for bulk operations, reporting/analytical SQL, ETL, and anywhere you want direct control of the generated SQL. They interoperate freely in one codebase; knowing when to drop from ORM to Core is a mark of fluency.

Is SQLAlchemy hard to learn?

Steeper than Django's ORM, yes — the Session/unit-of-work model, loading strategies, and Core/ORM duality are real concepts. But that depth is why it handles complex cases the simpler ORMs can't, and 2.0's typed API made it considerably more approachable. Budget the learning; it pays back on any non-trivial project.

What changed in SQLAlchemy 2.0?

A major modernization: select() for all queries (unifying Core and ORM query style), full type-hint support (Mapped[...], typed results), first-class async (AsyncSession), and cleaner APIs. Pre-2.0 tutorials look meaningfully different — learn 2.0 style.

Does SQLAlchemy support async?

Yes — AsyncSession and async engines (via async drivers like asyncpg) provide a full async API, important for FastAPI and other async frameworks. The async and sync APIs mirror each other closely.

SQLAlchemy or Prisma-style tooling?

Different ecosystems — SQLAlchemy is Python's answer, Prisma is TypeScript's. Within Python, SQLAlchemy is the powerhouse; SQLModel is the lighter FastAPI-oriented wrapper over it; Django's ORM owns the Django world. There's no direct cross-language choice unless you're picking your backend language.

Related Topics

References