This is the database-agnostic SQLAlchemy 2.0 textbook. For a Postgres-specific companion volume, see The Postgres-focused SQLAlchemy Textbook .
Pairs with FastAPI , Pydantic v2 , and Alembic .
SQLAlchemy 2.0 introduction: Core vs ORM, the unified API, engine + connection, basic select / insert / update / delete.
Engine and connection management: pool types, sizing, pre-ping, recycle, events, debugging.
Schema in SQLAlchemy 2.0: typed mapped columns, Python types vs DB types, defaults, indexes, constraints, table_args.
Session deep dive: lifecycle, identity map, flush, commit, expire_on_commit, refresh, the unit of work.
Selecting rows in SQLAlchemy 2.0: select(), where, joins, group_by, aggregates, subqueries, CTEs, scalars vs rows.
Relationship() in depth: configuration, loading strategies, N+1 prevention, association tables, polymorphic patterns.
Transactions and concurrency: isolation levels, savepoints, FOR UPDATE locking, optimistic locking, retry patterns, and the cross-DB differences.
Async patterns in SQLAlchemy 2.0: AsyncSession, expire_on_commit, async streaming, lazy loading caveats, AsyncSession scopes.
Practical SQLAlchemy performance: avoiding N+1, bulk_insert_mappings, server-side cursors, EXPLAIN ANALYZE patterns.
Schema migrations: Alembic integration, autogenerate, manual edits, branching, online schema changes, deploy patterns.