SQLAlchemy

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL. It provides a comprehensive suite of enterprise-grade persistence patterns built on the identity map, unit of work, and data mapper models, layered on top of a standalone Core that exposes a full Python-based SQL expression language, schema metadata, connection pooling, and typed result handling.

The ORM and Core are cleanly separated so you can adopt either level independently, and a broad dialect system supports PostgreSQL, MySQL/MariaDB, SQLite, Oracle, and SQL Server through synchronous and asyncio drivers alike.

What You Get

• A production-grade ORM implementing the identity map, unit of work, and data mapper patterns with declarative model configuration
• A standalone Core SQL expression language for building database-agnostic queries that render to native SQL
• Connection pooling, transaction management, and a typed result/Row API shared across the whole toolkit
• A dialect system covering PostgreSQL, MySQL/MariaDB, SQLite, Oracle, and SQL Server with both sync and asyncio support
• Schema reflection and DDL generation to introspect existing databases or emit CREATE statements from Python metadata

Common Use Cases

• Mapping domain models to relational tables in web and backend applications
• Building complex, composable SQL queries programmatically without writing raw string SQL
• Powering the data layer of frameworks and tools such as Flask, FastAPI, and Alembic migrations
• Reflecting and manipulating existing database schemas across multiple database backends
• Running async database access with asyncio drivers for high-concurrency services

Under The Hood

Architecture SQLAlchemy is organized as two cooperating layers under lib/sqlalchemy. The Core (sql/, engine/, pool/) provides an SQL expression language whose constructs in sql/elements.py, sql/selectable.py, and sql/dml.py are compiled to backend SQL by sql/compiler.py, then executed through the engine/ Connection and Engine against pooled DBAPI connections. The ORM (orm/) builds on Core: orm/mapper.py maps classes to tables, orm/session.py implements the unit-of-work that tracks changes via the identity map and flushes them through orm/persistence.py, while orm/query.py and the 2.x select() path translate object-level queries into Core statements. Backend differences are isolated in dialects/ (postgresql, mysql, sqlite, oracle, mssql), each adapting compilation and typing to a specific database and driver.

Tech Stack The library targets Python 3.10+ with a single hard runtime dependency, typing-extensions, and an optional greenlet dependency enabling the asyncio API. Performance-critical helpers are written in Cython and compiled at build time (via setuptools and pyproject.toml build config), falling back to pure Python where unavailable. Optional driver extras pull in psycopg, asyncpg, mysqlclient, oracledb, pyodbc, and others per backend, and the package ships a py.typed marker for full static typing.

Code Quality The codebase is mature and rigorously tested, with roughly 325 test modules under test/ plus a purpose-built testing harness inside lib/sqlalchemy/testing used across backends and by third-party dialects. Static typing is thorough with PEP 484 annotations throughout, and quality gates are enforced via a pre-commit configuration and CI across many Python versions and databases. Naming and module boundaries are consistent and deliberate, reflecting two decades of maintenance.

API Design SQLAlchemy exposes an ergonomic, layered public API: high-level declarative ORM models with Mapped and mapped_column, a unified 2.0 select() query style shared between Core and ORM, and drop-into-Core access whenever explicit SQL control is needed. The surface is large because it is comprehensive, which contributes to a steeper learning curve, but it is extensively documented, richly exemplified in the examples/ directory, and consistent enough that patterns learned at one layer transfer to the others.