SQLAlchemy
By the SQLAlchemy project
SQLAlchemy is a Python SQL toolkit and Object-Relational Mapping (ORM) library that provides both a full ORM layer and a lower-level Core for writing expressive, database-agnostic SQL.
Definition
SQLAlchemy is a Python SQL toolkit and Object-Relational Mapping (ORM) library that provides both a full ORM layer and a lower-level Core for writing expressive, database-agnostic SQL.
Overview
SQLAlchemy is unusual among ORMs in that it is explicitly split into two layers: SQLAlchemy Core, a Pythonic SQL expression language and connection/engine abstraction, and the SQLAlchemy ORM, built on top of Core, which maps Python classes to database tables using the declarative or imperative mapping styles. This layered design means developers can drop down to near-raw, database-agnostic SQL through Core when the ORM's abstractions get in the way, without leaving the SQLAlchemy ecosystem entirely. The ORM layer uses a Unit of Work pattern: objects loaded or created within a `Session` are tracked, and changes are flushed to the database as a single coordinated transaction, which helps avoid redundant or out-of-order SQL statements. SQLAlchemy supports PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server through a dialect system, and its 2.0 release unified the Core and ORM query APIs around a consistent `select()` construct. Schema migrations for SQLAlchemy projects are almost universally handled by Alembic, built by the same author, which autogenerates migration scripts by diffing declarative model metadata against the live database. This pairing — SQLAlchemy for modeling and querying, Alembic for versioned schema evolution — is the de facto standard data-access stack for Python web frameworks like FastAPI and Flask, and features prominently in data-heavy Python curricula such as Python for AI & ML. Compared to Django's built-in ORM, SQLAlchemy trades some out-of-the-box convenience for finer control over generated SQL, connection pooling behavior, and query composition, which makes it a common choice for teams that need precise performance tuning or work across multiple, non-Django frameworks.
Key Features
- Two-layer design: Core (SQL expression language) and ORM (class mapping)
- Declarative and imperative mapping styles for defining models
- Unit of Work session pattern that batches changes into coordinated transactions
- Unified query API (select()) introduced in SQLAlchemy 2.0
- Connection pooling and engine configuration for performance tuning
- Dialect system supporting PostgreSQL, MySQL, SQLite, Oracle, and SQL Server
- Relationship loading strategies (lazy, eager, joined, subquery)
- Tight pairing with Alembic for versioned schema migrations