#SQLAlchemy
8 posts
SQLAlchemy 2.0 #7 Alembic and Production Setup: Migrations, Async, Team Rules
The series finale on operational topics: why create_all cannot handle schema changes and Alembic takes over, what autogenerate detects and what it misses, the review rules for migration files, going async with create_async_engine and AsyncSession and the lazy-loading restriction that comes with it, laying out models, sessions, and settings in a project, and the team rules worth enforcing.
SQLAlchemy 2.0 #6 Advanced Queries: Joins, Aggregation, Subqueries, Bulk Operations
Building real-world queries on select() alone: the difference between scalars and execute return shapes, combining conditions with or_, joins and explicit ON clauses, reading group_by aggregates through labels, subqueries and EXISTS, LIMIT-OFFSET pagination and its limits versus keyset pagination, and bulk INSERT and UPDATE that bypass the ORM unit of work.
SQLAlchemy 2.0 #5 Relationships: One-to-Many, Many-to-Many, and the N+1 Problem
The ORM at its best and its most dangerous — relationship(): how foreign keys and relationship() divide the work, declaring bidirectional one-to-many with back_populates, many-to-many through a secondary table, cascade and delete-orphan for parent-child lifecycles, spotting the N+1 problem that lazy loading creates by reading the echo log, and choosing between selectinload and joinedload to fix it.
SQLAlchemy 2.0 #4 The Session: Change Tracking, flush vs commit, and the Four Object States
The heart of the ORM, the Session: how it batches changes as a unit of work and emits them as SQL, the difference between flush and commit, the four object states — transient, pending, persistent, detached — the identity map that returns the same object for the same row, why attribute access after commit triggers a new query, and the one-session-per-request scoping rule for web applications.
SQLAlchemy 2.0 #3 Defining ORM Models: DeclarativeBase, Mapped, mapped_column
The 2.0-style way of declaring tables as Python classes: the DeclarativeBase inheritance structure, how Mapped type hints and mapped_column divide the work, the rules mapping Python types to database types, how Optional drives nullable, declaring defaults, unique constraints, and indexes, and the naming convention that gives constraints predictable names from day one.
SQLAlchemy 2.0 #2 Engines and Transactions: Connection Pools and the Two Commit Patterns
The foundation of SQLAlchemy: engines and transactions. Why connection pools exist and how they behave, what pool_size and max_overflow actually limit, the commit-as-you-go and begin-once patterns split between connect() and begin(), why transactions never auto-commit, defining tables with MetaData and Table, and running CRUD with Core expressions.
SQLAlchemy 2.0 #1 The Big Picture: Core, ORM, and the 2.0 Style
First post in a series covering SQLAlchemy, the de facto standard database library for Python, from the ground up: the two-layer structure of Core and ORM, why 1.x-era code and 2.0-style code look different, the unified select()-centric query style and type hint support, installation and the first connection, and running raw SQL with text().
Modern Python in Practice #3: Connecting a DB — SQLAlchemy 2.x + Alembic
SQLAlchemy 2.x's new style — Mapped/mapped_column, async sessions, integration with FastAPI's dependency injection, and Alembic migrations.