# SQLAlchemy From Zero > Learn Python's premier database toolkit - the ORM under Flask-SQLAlchemy and SQLModel: Core vs ORM, the engine and connections, declarative models, the Session and unit of work, the modern select() query API, relationships, loading strategies and the N+1 trap, and Alembic migrations. The library those wrappers wrap, made plain. --- # SQLAlchemy From Zero SQLAlchemy is the database toolkit most serious Python talks to a database through. If you used Flask-SQLAlchemy in [Flask From Zero](/guides/flask-from-zero) or SQLModel in [FastAPI From Zero](/guides/fastapi-from-zero), you used SQLAlchemy without meeting it - those are thin layers over this. Learning it directly is doubly worth it: it's the most powerful, flexible ORM in the Python world, and understanding it turns those wrappers from magic into "oh, that's SQLAlchemy, configured for me." The mental model that makes it click is that SQLAlchemy is really **two libraries stacked**: **Core** (a Pythonic way to build and run SQL - the foundation) and the **ORM** (maps Python classes to tables, built on Core). Most app code lives in the ORM, but knowing Core is there - and that you can always drop down to it - is what makes SQLAlchemy feel like a tool you command rather than fight. We build it idea-first the whole way, including the two ideas that govern every ORM: the **Session** (the unit of work) and how it decides what SQL to send. > πŸ“ This assumes **Python** (classes, decorators - [Python From Zero](/guides/python-from-zero)) and > basic **databases** (tables, keys, joins - [What a Database Is](/guides/what-a-database-is)). The ORM > concepts transfer directly from [Hibernate & JPA](/guides/hibernate-and-jpa-from-zero) - this is the > Python equivalent. SQLAlchemy needs a real database engine, so examples are shown with their output. ## How to read this Read in order - it builds one schema (authors, books, tags) from a bare engine up to relationships and migrations. Uses modern SQLAlchemy 2.0 style throughout. Phases carry difficulty badges. ## The phases **Part 1 - Foundations (🟒 Basic β†’ 🟑)** 1. **[What SQLAlchemy Is (Core vs ORM)](01-what-sqlalchemy-is.md)** 🟒 - the two-layer design and where it sits under the frameworks. 2. **[The Engine & Connecting](02-the-engine-and-connecting.md)** 🟒 - `create_engine`, connections, transactions, and running SQL with Core. 3. **[Defining Models](03-defining-models.md)** 🟑 - declarative classes, `Mapped`/`mapped_column`, and the table they imply. **Part 2 - The ORM in action (πŸ”΄ β†’ 🟑)** 4. **[The Session & Unit of Work](04-the-session-and-unit-of-work.md)** πŸ”΄ - the heart: the identity map, flush, and dirty tracking. 5. **[Querying with select()](05-querying-with-select.md)** 🟑 - the modern 2.0 query API: filtering, ordering, and fetching results. 6. **[Relationships](06-relationships.md)** πŸ”΄ - `relationship()`, foreign keys, one-to-many, many-to-many, `back_populates`. 7. **[Loading Strategies & the N+1 Trap](07-loading-strategies-and-n-plus-1.md)** πŸ”΄ - lazy vs eager, `selectinload`/`joinedload`, and the trap that bites everyone. **Part 3 - Real projects (🟑 β†’ 🟒)** 8. **[Migrations with Alembic](08-migrations-with-alembic.md)** 🟑 - versioned schema changes, autogenerate, and why `create_all` isn't enough. 9. **[SQLAlchemy in the Real World & Where to Go Next](09-where-to-go-next.md)** 🟒 - Core vs ORM choices, async SQLAlchemy, and what to build. > After this, Flask-SQLAlchemy and SQLModel read as conveniences over a Session, mapped classes, and > `select()` - the things you now understand directly. --- # What SQLAlchemy Is (Core vs ORM) If you've written Python that touches a database, you've almost certainly bumped into SQLAlchemy - maybe directly, maybe hiding under Flask-SQLAlchemy or SQLModel without you realizing it. It has a reputation for being big and a little intimidating, and there's a reason: it's not one thing, it's *two* things stacked on top of each other. Most of the confusion people have with SQLAlchemy - "wait, why are there two ways to do this?" - comes from not knowing that up front. Here's the one idea this whole guide hangs on: **SQLAlchemy is a toolkit with two layers.** Get that picture in your head now and everything else - engines, sessions, queries, relationships - slots into place. The real domain (authors and books) starts in [Phase 2](02-the-engine-and-connecting.md). ## What SQLAlchemy actually is πŸ“ **SQLAlchemy** - Python's premier database toolkit. It's the most powerful and flexible object-relational mapper in the Python ecosystem, and it's the engine underneath much of the rest: when you use Flask-SQLAlchemy in [Flask](/guides/flask-from-zero) or SQLModel in [FastAPI](/guides/fastapi-from-zero), *they are wrapping SQLAlchemy*. Learn it once and you understand what those convenience layers are doing. The "ORM" part of that name is the same idea you'd meet in any language. If you've read the [Hibernate & JPA guide](/guides/hibernate-and-jpa-from-zero), this transfers directly - only the language changes. πŸ“ **ORM (Object-Relational Mapper)** - a library that maps your **classes to tables** and your **objects to rows**. You declare the correspondence once ("this `Author` class maps to the `authors` table"), then work with plain Python objects: ask for an author, get an `Author`; save one, and the ORM writes the `INSERT` for you. It generates the SQL so you don't hand-write it. That mapping exists because your Python code thinks in **objects** (an `Author` has a name and holds a list of `Book`s) while a [database](/guides/what-a-database-is) thinks in **tables** (rows, columns, and foreign keys - numbers in one table pointing at another). Two different shapes for the same data. The ORM's job is to translate between them so you don't have to do it by hand on every query. ## The two layers - the key mental model Here is the picture worth memorizing. SQLAlchemy is built in two layers, one sitting on top of the other. πŸ“ **Core** - a Pythonic SQL expression language. It lets you build and run SQL *programmatically* - you construct queries out of Python objects and operators instead of gluing together SQL strings. Core is the foundation: it owns the connection, the dialects (PostgreSQL vs SQLite vs MySQL), and the actual execution. πŸ“ **The ORM** - the object layer built *on top of* Core. It maps your classes to tables and lets you work in objects (`session.add(author)`), and underneath it leans on Core to generate and run the real SQL. ```mermaid flowchart TD A[Your Python code] --> B[ORM
classes <-> tables] B --> C[Core
SQL expression language] A -.drop down anytime.-> C C --> D[DBAPI
driver: psycopg, sqlite3] D --> E[(Database)] ``` *What just happened:* the diagram is the whole guide in one image. Your code usually enters through the ORM (top), which translates objects into Core expressions, which Core turns into real SQL and hands to the **DBAPI** - Python's low-level database driver - which talks to the database. The dotted line matters: because the ORM is built *on* Core, you can always drop straight down to Core when you need to, in the same program, against the same connection. πŸ’‘ **The ORM is built on Core, and you never lose access to it.** This is the freedom that makes SQLAlchemy different from a "magic box" ORM. When the ORM's high-level approach gets awkward - a bulk update, a gnarly report - you reach down to Core (or raw SQL) without leaving the library or fighting it. One toolkit, two altitudes, and you choose which one fits the task. ## Core vs ORM - when to reach for each So you have two layers. Which do you actually use? The practical rule of thumb: - **ORM** - for your application's domain objects: CRUD on `Author` and `Book`, navigating relationships, the everyday "load this, change it, save it" work. This is where *most* application code lives. - **Core (or raw SQL)** - for bulk operations (update ten thousand rows at once), complex reporting and analytics queries, or any time you want precise control over the exact SQL generated. A tiny taste of the *shape* of each - don't worry about the details, just feel the difference. The ORM deals in objects: ```python # ORM: you think in objects, not tables. author = session.get(Author, 1) # -> an Author object author.name = "Ursula K. Le Guin" # change a Python attribute session.commit() # ORM writes the UPDATE for you ``` *What just happened:* you fetched an `Author` as a Python object, edited an attribute like any other object, and committed. You never wrote `UPDATE authors SET name = ...` - the ORM noticed the change and generated that SQL itself. This is the object world: rows feel like instances. Core deals in tables and SQL expressions instead: ```python # Core: you build a SQL statement out of Python objects. from sqlalchemy import select stmt = select(authors_table).where(authors_table.c.name == "Ursula K. Le Guin") result = connection.execute(stmt) # runs the SELECT, hands back rows ``` *What just happened:* here there's no `Author` object at all - you composed a `SELECT` from a table object and a `.where(...)` condition, then executed it to get back raw rows. It's closer to SQL, more explicit, and gives you exact control over the statement. Same library, deliberately lower altitude. Notice both used `select()` - that's Core's expression language, and the ORM borrows it too. The two layers share machinery, which is exactly why dropping between them is seamless. ## How it compares to the Django ORM If you've used Django, a fair question is "how is this different?" The plain one-liner: **Django's ORM is part of Django and built for it; SQLAlchemy is standalone and used everywhere outside Django** - under FastAPI, under Flask, in data scripts, in anything Python. The deeper difference is philosophy. Django's ORM favors *convention* - it's tightly integrated and hides a lot to keep simple cases short. SQLAlchemy favors *explicit* - it shows you the layers (Core under the ORM), gives you more power and control, and asks you to be a bit more deliberate in exchange. Neither is "better"; they're different bargains. But if you're working outside Django, SQLAlchemy is the standard, and its explicitness is what lets you drop to Core when you need to. ## A note on SQLAlchemy 2.0 One thing that trips up beginners isn't SQLAlchemy itself - it's *old tutorials*. SQLAlchemy had a major style shift, and the internet is full of both versions. πŸ“ **SQLAlchemy 2.0 style** - the modern API this guide uses throughout: `select()` for queries, `Mapped[...]` and `mapped_column()` to declare ORM models, and a `Session` you use with explicit blocks. It's clearer and type-friendly. Older **1.x** tutorials look noticeably different - they use a `Query` object (`session.query(Author).filter(...)`) and bare `Column(...)` declarations. So when you're searching for help and see `session.query(...)` or `Column(Integer, primary_key=True)` with no type hints, you're looking at the old style. It still runs, but it's not what we'll write. This guide stays in 2.0 the whole way so you learn one consistent, current shape. πŸ’‘ **Two layers, one toolkit - that's the frame.** Core (the SQL expression foundation) and the ORM (objects on top), in modern 2.0 style. Hold that picture and the rest of this guide is just filling it in: next we'll meet the **engine** - the thing that actually owns the connection to your database and sits at the bottom of both layers. ## Recap 1. **SQLAlchemy is Python's premier database toolkit** - the most powerful ORM in the ecosystem, and the library that Flask-SQLAlchemy and SQLModel wrap. 2. An **ORM** maps **classes to tables** and **objects to rows**, generating the SQL so you work in objects instead of hand-writing queries (the same idea as Hibernate, just in Python). 3. The key mental model: **two layers**. **Core** is a Pythonic SQL expression language (the foundation); the **ORM** maps classes to tables and is built *on top of* Core. 4. Use the **ORM** for everyday domain objects and CRUD; drop to **Core** (or raw SQL) for bulk operations, complex reports, and fine-grained control - and you can switch between them freely in the same program. 5. **vs Django:** Django's ORM is tied to Django and leans on convention; SQLAlchemy is standalone, more explicit and powerful, and the standard everywhere outside Django. 6. This guide uses modern **SQLAlchemy 2.0** style (`select()`, `Mapped`/`mapped_column`, `Session`); older 1.x tutorials show `Query` and bare `Column`, which look different - don't let them confuse you. ## Quick check Three questions on the framing that has to stick before Phase 2: ```quiz [ { "q": "What are the two layers SQLAlchemy is built from?", "choices": [ "Core (a Pythonic SQL expression language) and the ORM (maps classes to tables), with the ORM built on top of Core", "A frontend layer and a backend layer that run in separate processes", "The ORM and Django, which SQLAlchemy combines into one library", "A read layer and a write layer that connect to different databases" ], "answer": 0, "explain": "SQLAlchemy is a toolkit with two layers: Core is the foundational SQL expression language, and the ORM maps classes to tables on top of it. Because the ORM sits on Core, you can always drop down to Core when you need to." }, { "q": "When does it make sense to drop down from the ORM to Core (or raw SQL)?", "choices": [ "For bulk operations, complex reporting queries, or when you need fine control over the exact SQL", "Never - the ORM can do everything and Core is deprecated", "Only when the ORM is completely unavailable in your version", "For all everyday CRUD, since the ORM is just for setup" ], "answer": 0, "explain": "The ORM is best for everyday domain objects and CRUD. Core (or raw SQL) shines for bulk updates, heavy reporting/analytics queries, and any case where you want precise control over the generated SQL - and you can switch between them in the same program." }, { "q": "You find a tutorial using `session.query(Author).filter(...)` and `Column(Integer, primary_key=True)`. What's going on?", "choices": [ "It's the older 1.x style; this guide uses modern 2.0 (`select()`, `Mapped`/`mapped_column`, `Session`)", "It's a different library entirely, not SQLAlchemy", "It's the Core layer, which always looks like that", "It's invalid code that will not run at all" ], "answer": 0, "explain": "`session.query(...)` and bare `Column(...)` are the 1.x style. It still runs, but it looks different from the modern 2.0 API this guide uses throughout - `select()`, `Mapped[...]`, `mapped_column()`, and `Session`. Knowing the difference keeps old tutorials from confusing you." } ] ``` --- # The Engine & Connecting Before SQLAlchemy can map a single `Author` to a row, something has to actually *talk to the database* - open a connection, send SQL, read rows back, and clean up afterward. That something is the **Engine**. Everything else in this guide - models, the Session, `select()` - sits on top of the machinery you'll meet here. The mental model in one sentence: **the Engine is the thing that knows how to reach your database and hands out connections from a pool, and a Connection is a single live conversation with that database.** You make one Engine for your whole app, and you borrow short-lived connections from it whenever you need to run SQL. ## The Engine - your single source of DB connectivity πŸ“ You create an Engine with `create_engine(...)`, passing a **database URL** that says *what kind* of database, *who* you are, and *where* it lives. ```python from sqlalchemy import create_engine # SQLite, stored in a local file called app.db engine = create_engine("sqlite:///app.db") # A real server would look like this instead: # engine = create_engine("postgresql+psycopg://user:pass@localhost:5432/library") ``` *What just happened:* We built one `Engine` object. Notice what we did *not* do - nothing connected to the database. ⚠️ `create_engine()` is **lazy**: it parses the URL and gets ready, but the first real connection isn't opened until you actually ask to run something. That's why creating an Engine never fails because the DB is down - the failure comes later, when you connect. πŸ“ The **database URL** decodes like this: ```console postgresql+psycopg://user:pass@localhost:5432/library β””β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”˜ β””β”€β”€β”¬β”€β”€β”˜ β””β”€β”€β”€β”€β”¬β”€β”€β”€β”˜ β””β”€β”¬β”€β”˜ β””β”€β”€β”¬β”€β”€β”˜ dialect+driver credentials host port database ``` - **dialect** - which database (`sqlite`, `postgresql`, `mysql`). - **driver** (optional, after the `+`) - the actual Python library that does the talking (the DBAPI), e.g. `psycopg`. Leave it off and SQLAlchemy picks a default. - The rest is the usual *who / where / which database*. SQLite is the odd one out: it's just a file, so there's no host or login - `sqlite:///app.db` (three slashes, then a relative path) or `sqlite:///:memory:` for a throwaway in-memory DB. πŸ’‘ The Engine is meant to be **created once and shared**. Make it at startup, hand the same object to your whole app. More on *why* in a moment - it's the single most common Engine mistake. ## Connections & executing SQL (Core) To run SQL, you borrow a `Connection` from the Engine. The clean way is a `with` block, which guarantees the connection is returned (back to the pool) when you're done - even if an error is raised mid-way. ```python from sqlalchemy import text with engine.connect() as conn: result = conn.execute(text("SELECT id, name FROM author")) for row in result: print(row.id, row.name) ``` *What just happened:* `engine.connect()` checked out one connection. We ran a query with `conn.execute(...)`, got back a `Result`, and iterated it row by row. Each `row` is a lightweight named tuple - `row.name` and `row[1]` both work. When the `with` block ends, the connection is released automatically; you never call `.close()` yourself. πŸ“ We wrapped the SQL string in `text(...)`. SQLAlchemy doesn't run bare strings - `text()` marks "this is literal SQL I want sent as-is." It also gives you the *one* thing you must never skip: **bound parameters**. ⚠️ **Never** build SQL by gluing user input into the string. This is how SQL injection happens: ```python # 🚨 NEVER do this - a malicious name can rewrite your query name = user_input conn.execute(text(f"SELECT * FROM author WHERE name = '{name}'")) ``` Instead, leave a named placeholder (`:name`) and pass the value separately. The database driver keeps the value and the SQL apart, so input is always treated as *data*, never as code: ```python with engine.connect() as conn: result = conn.execute( text("SELECT id, name FROM author WHERE name = :name"), {"name": "Ursula K. Le Guin"}, ) author = result.fetchone() print(author) ``` *What just happened:* `:name` is a placeholder; the dict `{"name": ...}` fills it safely. You can also bind values fluently with `text("... :name").bindparams(name="...")` - same effect. Either way, the actual string sent to the DB never contains the user's text. This is non-negotiable: bound params on every query that touches input. ## Transactions - commit, or it didn't happen Here's a trap that catches everyone once. πŸ“ `engine.connect()` gives you a connection that does **not** commit on its own. If you `INSERT` and then just let the block end, SQLAlchemy rolls back - your write vanishes. You have to call `conn.commit()` yourself. ```python with engine.connect() as conn: conn.execute( text("INSERT INTO author (name) VALUES (:name)"), {"name": "N. K. Jemisin"}, ) conn.commit() # ← without this line, the insert is silently discarded ``` *What just happened:* We inserted a row, then explicitly committed to make it permanent. Forget the `commit()` and the row never lands - no error, just nothing. (This is "commit as you go" style.) The pattern you should reach for by default is `engine.begin()` instead. It opens a connection **and** a transaction, then commits automatically if the block finishes cleanly - or rolls back if any exception is raised: ```python with engine.begin() as conn: conn.execute( text("INSERT INTO book (title, author_id) VALUES (:title, :aid)"), {"title": "The Fifth Season", "aid": 1}, ) conn.execute( text("INSERT INTO tag (book_id, label) VALUES (:bid, :label)"), {"bid": 1, "label": "fantasy"}, ) # no commit() needed - leaving the block commits both inserts together ``` *What just happened:* Both inserts run inside one transaction. If the second one blows up, the first is rolled back too - you never end up with a book and no tag. This all-or-nothing behavior is the **atomicity** in ACID; for the full picture of what a transaction guarantees, see [/guides/transactions-and-acid](/guides/transactions-and-acid). πŸ’‘ Rule of thumb: use `engine.begin()` when you're writing, `engine.connect()` when you're only reading. `begin()` is the recommended default because it makes "did I remember to commit?" a non-question. ## The connection pool - why you make ONE Engine Opening a brand-new database connection is genuinely expensive - a TCP handshake, authentication, server-side setup. Doing that per query would crush a busy app. πŸ“ So the Engine keeps a **connection pool**: a small set of already-open connections it lends out and takes back. When you write `with engine.connect()`, you're usually grabbing a connection that's already warm, and returning it to the pool (not closing it) when the block ends. You don't manage any of this - it's the Engine's whole job. This is exactly why the Engine is a **share-one-for-the-whole-app** object. The pool only helps if everyone draws from the *same* pool. ⚠️ The classic mistake: calling `create_engine()` inside a request handler, a function, or a loop. Each call spins up a *fresh* pool, so connections are never reused, the pool can't do its job, and under load you'll exhaust the database's connection limit. Create the Engine **once** at startup and pass it around. One Engine per application (or per database), not per request. ## Result objects - reading rows your way `conn.execute(...)` returns a `Result`, and how you pull data out of it depends on the shape you want: ```python with engine.connect() as conn: result = conn.execute(text("SELECT id, name FROM author")) rows = result.all() # list of all rows (named tuples) # one_row = result.fetchone() # the next single row, or None # first_id = result.scalar() # first column of the first row for row in rows: print(row.id, row.name) ``` *What just happened:* `.all()` materializes every row into a list; `.fetchone()` pulls one at a time; `.scalar()` is the shortcut for "I just want the single value in the first column of the first row" (great for `SELECT COUNT(*)`). Two more you'll use constantly: ```python with engine.connect() as conn: # .scalars() β†’ just the first column of every row, not whole tuples names = conn.execute(text("SELECT name FROM author")).scalars().all() print(names) # ['Ursula K. Le Guin', 'N. K. Jemisin', ...] # .mappings() β†’ each row as a dict-like {column: value} for row in conn.execute(text("SELECT id, name FROM author")).mappings(): print(row["id"], row["name"]) ``` *What just happened:* `.scalars()` strips each row down to a single column - perfect when you selected one thing and want a plain list. `.mappings()` hands back dict-style rows so you can index by column name. Same `Result`, different lenses. πŸ’‘ Everything in this phase - the Engine, connections, the pool, `Result` - *is the Core layer*. When you start using the ORM in [Phase 4](04-the-session-and-unit-of-work.md), the Session uses an Engine and runs its SQL through exactly this machinery; it just builds the SQL from your Python classes and turns rows back into objects for you. You're not leaving Core behind - you're putting a friendlier layer on top of it. Next up, [Phase 3](03-defining-models.md): describing your `Author`, `Book`, and `Tag` tables as Python classes. ## Recap - `create_engine(url)` builds the **Engine** - your app's single source of DB connectivity. It's **lazy**: no connection opens until you actually run something. - The **database URL** is `dialect+driver://user:pass@host:port/database` (SQLite is just a file path). - Borrow a connection with `with engine.connect() as conn:` and run SQL via `conn.execute(text("..."))`. Use **bound parameters** (`:name` + a dict), never f-strings - that's SQL injection. - `engine.connect()` does **not** auto-commit (call `conn.commit()`); `engine.begin()` commits on success and rolls back on error - use it by default for writes. - The Engine holds a **connection pool**, so make **one** Engine for the whole app - never per request. - `Result` gives you rows many ways: `.all()`, `.fetchone()`, `.scalar()`, `.scalars()`, `.mappings()`. ## Quick check ```quiz [ { "q": "What does create_engine(\"sqlite:///app.db\") do at the moment you call it?", "choices": [ "Opens a connection to the database immediately", "Creates the Engine but connects lazily - no connection opens yet", "Runs a test query to confirm the database is reachable" ], "answer": 1, "explain": "create_engine() is lazy: it sets up the Engine and pool config, but the first real connection isn't opened until you execute something." }, { "q": "You run an INSERT inside `with engine.connect() as conn:` and the block ends without calling conn.commit(). What happens?", "choices": [ "The row is saved automatically when the block exits", "SQLAlchemy raises an error forcing you to commit", "The insert is rolled back - the row never lands" ], "answer": 2, "explain": "engine.connect() doesn't auto-commit. Without conn.commit() the transaction rolls back. Use engine.begin() to commit automatically on success." }, { "q": "Why should you create just ONE Engine and share it across your whole app?", "choices": [ "The Engine holds a connection pool; one Engine means connections get reused instead of re-opened", "SQLAlchemy forbids more than one Engine per process", "Each Engine can only run one query at a time" ], "answer": 0, "explain": "The Engine manages a pool of reusable connections. Creating one per request spawns a new pool each time, defeating reuse and exhausting the DB's connection limit." } ] ``` --- # Defining Models In [Phase 2](02-the-engine-and-connecting.md) you built an `engine` - the thing that knows how to talk to your database. But the engine alone doesn't know what your data *looks like*. It can run raw SQL, and that's it. This phase is where you teach SQLAlchemy the shape of your world: what an `Author` is, what a `Book` is, what columns they have, and how they become real tables. **A model class is a two-way map between a Python object and a database row.** On one side, an `Author` instance living in memory. On the other, a row in an `authors` table. Everything in this phase is you drawing that map once, in one place - and SQLAlchemy following it in both directions forever after. We're keeping the domain small and concrete: `Author`, `Book`, and `Tag`. Relationships between them (who wrote what, which tags a book carries) arrive in [Phase 6](06-relationships.md). For now each model stands alone - just its own columns, mapped to its own table. ## The declarative base πŸ“ **`DeclarativeBase`** - the shared parent class that turns your subclasses into mapped tables. Every model you write inherits from one common `Base`, and that's what wires each class into SQLAlchemy's machinery (its metadata registry, its mapping logic). You define it exactly once, near the top of your project: ```python from sqlalchemy.orm import DeclarativeBase class Base(DeclarativeBase): pass ``` *What just happened:* you created an empty class that does nothing visible - but by subclassing `DeclarativeBase`, your `Base` now carries a `metadata` object (a catalog of every table it knows about) and the declarative engine that reads your future classes. Each model subclasses `Base`, registers itself in that catalog, and becomes something SQLAlchemy can load and save. One `Base` per application is the norm; all your models share it. ## A mapped class (2.0 style) Now the real thing. Here's `Author`, mapped to a table: ```python from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column class Base(DeclarativeBase): pass class Author(Base): __tablename__ = "authors" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] bio: Mapped[str | None] ``` *What just happened:* `__tablename__` names the table (`authors`). Each attribute is a column, and the **type annotation drives the column type**: `Mapped[int]` becomes an integer column, `Mapped[str]` a string column. `mapped_column(primary_key=True)` marks `id` as the primary key. The annotation also controls nullability - `Mapped[str]` is `NOT NULL`, while `Mapped[str | None]` (the `| None` part) is nullable. So `bio` is an optional string column; `name` is required. Notice `name` and `bio` don't even need a `mapped_column(...)` call - when there's nothing to configure, the annotation alone is enough. πŸ’‘ The `Mapped[...]` annotation isn't just a type hint for your editor (though it gives you that too). SQLAlchemy reads it at class-definition time to decide the column's SQL type and nullability. The annotation and the column are the same decision, written once. Here's `Book` alongside it: ```python class Book(Base): __tablename__ = "books" id: Mapped[int] = mapped_column(primary_key=True) title: Mapped[str] year: Mapped[int | None] ``` *What just happened:* same pattern. `title` is a required string, `year` is an optional integer (some books in your shelf might not have a known publication year, so `int | None` lets the column hold `NULL`). Those two classes describe two real tables. This is the SQL SQLAlchemy would generate from them: ```sql CREATE TABLE authors ( id INTEGER NOT NULL, name VARCHAR NOT NULL, bio VARCHAR, PRIMARY KEY (id) ); CREATE TABLE books ( id INTEGER NOT NULL, title VARCHAR NOT NULL, year INTEGER, PRIMARY KEY (id) ); ``` *What just happened:* read it side by side with the classes and it clicks. `Mapped[int]` + `primary_key=True` became `INTEGER NOT NULL` plus a `PRIMARY KEY`. `Mapped[str]` became `VARCHAR NOT NULL`. The `| None` annotations (`bio`, `year`) became nullable columns - no `NOT NULL`. The class and the table are two views of the same thing. ⚠️ **Older 1.x tutorials look different - don't mix them up.** Pre-2.0 SQLAlchemy wrote columns like `name = Column(String)`, with no `Mapped[...]` annotation and a capital-C `Column`. That style still works for backwards compatibility, but it's the old way: it doesn't give you typed attributes, and it infers nothing from annotations. If you're starting fresh, use `Mapped[...]` + `mapped_column(...)` everywhere. When you copy a snippet from a blog and it uses bare `Column(...)`, you've found a pre-2.0 example - translate it before pasting. ## Column types & options The annotation handles the common cases (`int`, `str`, `bool`, `datetime`). When you need more control - a length limit, a uniqueness constraint, a default value, or a SQL type the annotation can't infer - you pass arguments to `mapped_column(...)`. Here's `Book` with the mapping spelled out, and a `Tag` model showing a few more options: ```python from datetime import datetime from sqlalchemy import String, Text, DateTime, func from sqlalchemy.orm import Mapped, mapped_column class Book(Base): __tablename__ = "books" id: Mapped[int] = mapped_column(primary_key=True) title: Mapped[str] = mapped_column(String(200), nullable=False) isbn: Mapped[str | None] = mapped_column(String(13), unique=True) description: Mapped[str | None] = mapped_column(Text) created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now()) class Tag(Base): __tablename__ = "tags" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50), unique=True) is_featured: Mapped[bool] = mapped_column(default=False) ``` *What just happened:* `String(200)` caps `title` at 200 characters (a plain `Mapped[str]` gives an unbounded `VARCHAR`, which some databases dislike). `unique=True` on `isbn` and `name` adds a uniqueness constraint - no two books share an ISBN, no two tags share a name. `Text` is for long, unbounded content where a length limit makes no sense. `default=False` on `is_featured` is a *Python-side* default: when you create a `Tag` without setting it, SQLAlchemy fills in `False`. `server_default=func.now()` is a *database-side* default: the database stamps `created_at` with the current time on insert. The types you'll reach for most: | You want | Annotation / type | |----------|-------------------| | Whole number | `Mapped[int]` (β†’ `Integer`) | | Short text | `Mapped[str]` or `mapped_column(String(n))` | | Long text | `mapped_column(Text)` | | True/false | `Mapped[bool]` (β†’ `Boolean`) | | Timestamp | `Mapped[datetime]` (β†’ `DateTime`) | πŸ’‘ The rule of thumb: let the annotation pick the type when the default is fine (`Mapped[int]`, `Mapped[bool]`), and reach into `mapped_column(...)` only when you need a length, a constraint, or a specific SQL type like `Text`. Don't pass `String` redundantly when `Mapped[str]` already says "string" - add it only when you want the length limit. ## Creating the schema You've defined the classes. The tables don't exist yet - your models are still just Python. To actually build them in the database, you ask `Base`'s metadata to create everything it knows about, using the engine from Phase 2: ```python from sqlalchemy import create_engine engine = create_engine("sqlite:///library.db") Base.metadata.create_all(engine) ``` *What just happened:* `Base.metadata` is the catalog of every model that subclassed `Base` - `Author`, `Book`, `Tag`. `create_all(engine)` walks that catalog and issues a `CREATE TABLE` for each one through the engine's connection. Run this once and your `library.db` now has three real tables matching your models. It's also smart enough to skip tables that already exist, so running it again is safe - it won't error or duplicate. ⚠️ **`create_all` builds, it doesn't alter.** It's perfect for getting started and for dev: define models, call `create_all`, start querying. But it only ever *creates missing* tables - it will not change a table that already exists. Add a column to your `Book` model after the table's been created, and `create_all` quietly does nothing to that table; your new column never appears. For evolving a schema that already has data - adding columns, changing types, renaming things - you need real migrations, which we cover in [Phase 8](08-migrations-with-alembic.md) with Alembic. Think of `create_all` as the zero-to-one tool, and Alembic as the one-to-many tool. ## `__repr__` & the model as source of truth One small quality-of-life addition. By default, printing a model instance gives you something useless like `<__main__.Author object at 0x7f3c...>`. A `__repr__` fixes that: ```python class Author(Base): __tablename__ = "authors" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] bio: Mapped[str | None] def __repr__(self) -> str: return f"Author(id={self.id!r}, name={self.name!r})" ``` *What just happened:* now an `Author` prints as `Author(id=1, name='Ursula K. Le Guin')` - readable in the REPL, in logs, in debugger output. It changes nothing about how the model maps to the table; it's purely for your eyes. Add a short `__repr__` to every model and your debugging sessions get much friendlier. πŸ’‘ That `Author` class is now the **single source of truth** for everything downstream. It defines the `authors` table's structure, it's the type your queries return in [Phase 5](05-querying-with-select.md), and it's the anchor relationships hang off of in [Phase 6](06-relationships.md). If you've come from Java's Hibernate/JPA, this will feel familiar: there, an `@Entity` class with `@Id` and `@Column` annotations plays the exact same role - one class that *is* the table, in both directions. SQLAlchemy's `Mapped[...]` + `mapped_column(...)` is the same idea wearing Python clothes. The concepts transfer cleanly; if you want the Java framing of mapping, see [/guides/hibernate-and-jpa-from-zero](/guides/hibernate-and-jpa-from-zero). Either way, the lesson is the same: nail the model, and everything else follows. Next, in [Phase 4](04-the-session-and-unit-of-work.md), you'll meet the **Session** - the object that takes these models and actually saves, loads, and tracks them. ## Recap 1. **`class Base(DeclarativeBase): pass`** is the shared parent every model subclasses; it carries the `metadata` catalog that knows about all your tables. Define it once. 2. A model is a class with **`__tablename__`** and columns written as **`Mapped[...]` + `mapped_column(...)`**. The type annotation drives the column's SQL type, and **`Mapped[str | None]`** makes a column nullable. 3. The **2.0 declarative style** (`Mapped[...]` / `mapped_column(...)`) replaces the old 1.x `Column(...)` style - recognize old snippets and translate them before reusing. 4. **`mapped_column(...)`** takes options when the annotation isn't enough: `String(200)`, `Text`, `nullable`, `unique`, Python `default=`, and DB-side `server_default=`. 5. **`Base.metadata.create_all(engine)`** builds every mapped table through your Phase 2 engine - great for dev, but it only *creates* missing tables; it won't alter existing ones (use Alembic, Phase 8). 6. A **`__repr__`** makes instances readable, and the model class is the **single source of truth** that drives the table, queries, and relationships - the same role a JPA `@Entity` plays in Java. ## Quick check Test yourself on the ideas most likely to trip you up when writing models: ```quiz [ { "q": "In SQLAlchemy 2.0, what makes a column nullable?", "choices": [ "Passing nullable=True is the only way; the annotation is ignored", "Annotating it as Mapped[str | None] - the `| None` tells SQLAlchemy the column can be NULL", "Leaving out the type annotation entirely", "Setting primary_key=False" ], "answer": 1, "explain": "The Mapped[...] annotation drives nullability. Mapped[str] is NOT NULL; Mapped[str | None] is nullable. SQLAlchemy reads the `| None` at class-definition time to decide the column's NOT NULL constraint." }, { "q": "You've already created the `books` table with create_all, then you add a new `subtitle` column to the Book model and call create_all again. What happens to the table?", "choices": [ "create_all adds the subtitle column automatically", "create_all drops and recreates the table with the new column", "Nothing changes - create_all only creates missing tables, it never alters existing ones; you need a migration (Alembic)", "create_all raises an error because the table already exists" ], "answer": 2, "explain": "create_all is build-only and idempotent: it skips tables that already exist and never alters them. The new column won't appear. Changing a schema that already exists is what Alembic migrations (Phase 8) are for." }, { "q": "You see a tutorial that writes `name = Column(String)` with no `Mapped[...]` annotation. What is this?", "choices": [ "A syntax error - that style never worked", "The pre-2.0 (1.x) declarative style; the modern equivalent is `name: Mapped[str] = mapped_column(...)`", "A way to define a column that is automatically the primary key", "The only correct way to declare a non-nullable column" ], "answer": 1, "explain": "Bare `Column(...)` with no annotation is the older 1.x style. It still works for compatibility, but the 2.0 style uses `Mapped[...]` + `mapped_column(...)`, which gives typed attributes and infers type/nullability from the annotation. Translate old snippets before reusing them." } ] ``` --- # The Session & Unit of Work In [Phase 3](03-defining-models.md) you taught SQLAlchemy the shape of your world - `Author`, `Book`, `Tag` - and even built the tables with `create_all`. But a mapping just sits there. Something has to actually *use* it: insert a new author, fetch one back, change a title and have that change reach the database. That something is the **`Session`**, and it's the single most important object in the whole ORM. If you take one idea from this entire guide, take this one. Almost every SQLAlchemy surprise you'll ever hit - a change that saved without you calling save, a query that ran once instead of twice, a lazy load that worked here but exploded there - traces straight back to what's in this file. ## The mental model: a workbench, not a pipe πŸ“ People picture an ORM as a pipe: Python object goes in one end, SQL comes out the other, a row lands in the table. That picture will mislead you for years. The Session isn't a pipe - it's a **workbench**. When you add or load objects, the Session lays them out on a bench it keeps for the duration of your work, watches them, and only sends SQL to the database when it decides it's time. The **`Session`** is your handle to that workbench. It holds your objects, tracks every change you make to them, and talks to the database through the `engine` you built in [Phase 2](02-the-engine-and-connecting.md). You open one with the modern context-managed pattern: ```python from sqlalchemy import create_engine from sqlalchemy.orm import Session engine = create_engine("sqlite:///library.db") with Session(engine) as session: # do all your work here - add, load, modify ... ``` *What just happened:* `Session(engine)` created a workbench bound to your engine - that's how it knows which database to talk to. The `with` block is the modern, recommended way to use it: when the block ends (normally or via an exception), the Session is closed and cleaned up automatically - no dangling connections. Everything you do with your models happens inside this block. πŸ’‘ If you've come from Java's Hibernate/JPA, this is the *exact* same idea wearing Python clothes. SQLAlchemy's `Session` is Hibernate's `EntityManager`/persistence context: a per-unit-of-work, in-memory area that manages your objects and syncs them to the database on *its* schedule. If the workbench framing feels familiar, that's why - see [/guides/hibernate-and-jpa-from-zero](/guides/hibernate-and-jpa-from-zero) for the Java framing. The concepts transfer cleanly in both directions. ## Persisting: `add` + `commit` Let's save an `Author` and a `Book`. Two steps: hand the object to the Session with `add`, then make it permanent with `commit`. ```python with Session(engine) as session: author = Author(name="Ursula K. Le Guin") book = Book(title="A Wizard of Earthsea") session.add(author) # author is now on the workbench (pending) session.add(book) print(author.id) # None - no INSERT has run yet session.commit() # NOW the INSERTs fire print(author.id) # 1 - the database assigned it, SQLAlchemy read it back ``` ```sql INSERT INTO authors (name) VALUES ('Ursula K. Le Guin'); INSERT INTO books (title) VALUES ('A Wizard of Earthsea'); ``` *What just happened:* `Author(name=...)` made a plain Python object - at that moment the Session knows nothing about it. `session.add(author)` placed it on the workbench, but **no SQL ran yet**, which is why `author.id` is still `None`. The `INSERT`s didn't fire on the `add` lines - they fired at `commit`. That's when SQLAlchemy sent the pending work to the database, the database assigned each row a primary key, and SQLAlchemy read those ids back and populated `author.id` (now `1`). That gap between "I told the Session about this object" and "the SQL actually ran" is the whole story of this phase. ⚠️ **Nothing hits the database permanently until `commit`.** Until then your changes live only on the workbench. If the `with` block exits without a `commit` - including because an exception was raised - the work is rolled back and never reaches the database. `add` schedules; `commit` saves. ## The identity map & unit of work This is the core idea, and it surprises everyone the first time. πŸ“ Within a single Session, each database row maps to exactly **one** Python object - this is the **identity map**. Ask the Session for author `1` ten times and you get the *same instance* back every time, and after the first lookup, **no further queries**. Watch how many `SELECT`s come out: ```python with Session(engine) as session: first = session.get(Author, 1) # runs a SELECT, puts the Author on the bench second = session.get(Author, 1) # same id, same Session print(first is second) # True - not just equal, the SAME object ``` ```sql SELECT authors.id, authors.name FROM authors WHERE authors.id = 1; ``` ```console True ``` *What just happened:* two `get` calls, but **only one `SELECT`**. The first call ran the query, built an `Author` from the row, and stored it on the workbench. The second call found it already there and handed it straight back from memory - no database round trip. And `first is second` is `True`: not two objects with equal data, the very *same* object. That's the identity map guaranteeing one row, one object, per Session. πŸ’‘ The other half of this idea is the **unit of work**. The Session doesn't send your changes to the database one at a time as you make them. It collects everything - new objects from `add`, modifications to existing ones, deletions - and flushes them together as a single coordinated batch, in the right order, at the right moment. You describe *what* the data should look like; the Session figures out the minimal set of `INSERT`/`UPDATE`/`DELETE` statements to get there and runs them as one unit. The identity map is what makes this possible: one object per row means there's exactly one source of truth on the bench to watch and synchronize. ## Flush vs commit & dirty tracking Two words get thrown around a lot, and conflating them causes real confusion. πŸ“ **Flush** = send the pending SQL to the database (the `INSERT`s, `UPDATE`s, `DELETE`s), but inside the still-open transaction. πŸ“ **Commit** = make it all permanent. Here's the relationship: `commit` always flushes first, then commits. And the Session also **autoflushes** automatically right before it runs a query - so that any pending changes are visible to that query. Most of the time you never call `flush()` yourself; it happens for you. You think in terms of `commit`. But knowing flush exists explains *when* your SQL actually runs. Now the behavior that feels like magic until you understand the workbench: **dirty tracking** (also called *dirty checking*). πŸ“ When you change an attribute on an object the Session is managing, the Session *notices* - and issues an `UPDATE` at the next flush. There is **no explicit save call**. ```python with Session(engine) as session: book = session.get(Book, 1) # load it - now managed by the Session print(book.title) # 'A Wizard of Earthsea' book.title = "A Wizard of Earthsea (Illustrated)" # just set the attribute session.commit() # the Session noticed - UPDATE fires ``` ```sql SELECT books.id, books.title FROM books WHERE books.id = 1; UPDATE books SET title='A Wizard of Earthsea (Illustrated)' WHERE books.id = 1; ``` *What just happened:* you loaded a `Book`, which put it on the workbench under the Session's watch. Then you just **assigned a new value** to `book.title` - no `session.add`, no `session.save`, no `session.update` (there is no such method). At `commit`, the Session compared the object's current state to what it loaded, saw `title` had changed, and emitted exactly one `UPDATE` for that one column. This is the unit of work doing its job: you mutate plain Python objects, and the Session translates your mutations into the right SQL. (Java/Hibernate developers know this exact behavior - it's the same dirty checking, see [/guides/hibernate-and-jpa-from-zero](/guides/hibernate-and-jpa-from-zero).) ## Object states & the session-scope rule Every object, from the Session's point of view, is always in exactly one of four **states**. Learn these names - error messages, docs, and your own debugging all speak this language. πŸ“ The four states: - **Transient** - a brand-new object you made with `Book(...)`. The Session has never heard of it; it's not on the bench and has no row. (`Book(title="...")` before any `add`.) - **Pending** - you've called `add`, so it's on the bench and scheduled for `INSERT`, but the flush hasn't happened yet. (After `add`, before `commit`/`flush`.) - **Persistent** - on the bench, tracked by the Session, *and* tied to a real database row. This is the state where dirty tracking works. (After commit, or anything `get`/a query returns.) - **Detached** - *was* persistent, but its Session has closed. It still holds its data, but nobody's watching it; changes go nowhere. (An object you loaded, after the `with` block ended.) ```mermaid stateDiagram-v2 [*] --> Transient: Book(...) Transient --> Pending: session.add() Pending --> Persistent: flush / commit [*] --> Persistent: get() / query loads it Persistent --> Detached: session closes ``` ⚠️ Here's the trap that bites everyone eventually: **a detached object can't lazy-load.** If you load an `Author` inside a `with Session(...)` block, let the block end, and *then* try to walk to a relationship that wasn't fetched yet (say `author.books`), there's no open Session to run the query through - and you get a `DetachedInstanceError`. You don't have relationships yet (they arrive in [Phase 6](06-relationships.md)), and we'll meet this error properly in [Phase 7](07-loading-strategies-and-n-plus-1.md). But you already understand *why* it happens: no open Session, no workbench, nothing to do the lazy load. That's the payoff of learning states first. πŸ’‘ So how should you scope a Session? The pattern is **one Session per unit of work**: open it, do a coherent chunk of work, `commit` (or `rollback` on error), close it - exactly what the `with` block gives you. In a web app this becomes **one Session per request**. Keep them short-lived. ⚠️ And do **not** share a single Session across threads or across concurrent requests - a Session is not thread-safe, and sharing one is a classic source of corrupted state and baffling bugs. One unit of work, one Session, then let it go. πŸ’‘ This is the lens for everything that follows. Nearly every ORM behavior in the rest of this guide is one of these Session ideas wearing a costume: - *"I changed a field and it saved without calling save"* β†’ the object was **persistent**, and dirty tracking caught the change. - *"The same query ran once instead of twice"* β†’ the **identity map** served the second lookup from memory. - *"`is` returned `True` for two loads"* β†’ the identity map gave you one object per row. - *"Why did this `DetachedInstanceError` blow up?"* β†’ you touched a **detached** object after its Session closed. Master the Session, and the rest of SQLAlchemy is just details. Next, in [Phase 5](05-querying-with-select.md), you'll use this Session to run real queries with `select()`. ## Recap 1. The **`Session`** is your handle to the ORM - a workbench that holds your objects, tracks their changes, and talks to the database via the engine. Open it with `with Session(engine) as session:`. It's SQLAlchemy's equivalent of Hibernate's `EntityManager`/persistence context. 2. **`session.add(obj)`** schedules an insert; **`session.commit()`** makes it permanent. The primary key is populated only *after* the flush/commit - nothing hits the database permanently until `commit`. 3. The Session is an **identity map**: within one Session, a row maps to exactly one object, so `get` the same id twice β†’ one `SELECT` and the *same* instance (`is` is `True`). It batches all changes and applies them as one **unit of work**. 4. **Flush** sends pending SQL inside the transaction (it autoflushes before queries and on commit); **commit** makes it permanent. **Dirty tracking** means changing an attribute on a persistent object issues an `UPDATE` at flush - with **no explicit save call**. 5. Every object is **transient** (new, unknown), **pending** (added, not flushed), **persistent** (tracked + in the DB), or **detached** (Session closed, unwatched). A detached object can't lazy-load - that's the `DetachedInstanceError` you'll meet in Phase 7. 6. πŸ’‘ Scope a Session as **one unit of work** (one per request in web apps); keep it short-lived and never share one across threads. Nearly every ORM behavior traces back to the Session - it's the lens for the rest of the guide. ## Quick check The three ideas that explain the most future bugs: ```quiz [ { "q": "You call `session.add(author)` and then immediately print `author.id`. What do you see, and why?", "choices": [ "The real primary key - `add` runs the INSERT immediately", "None - `add` only schedules the insert; the id isn't populated until the flush/commit, when the database assigns it", "A randomly generated UUID assigned by SQLAlchemy", "It raises an error because the object isn't committed yet" ], "answer": 1, "explain": "`add` places the object on the workbench in the pending state but runs no SQL. Nothing hits the database until commit (or a flush), so the database hasn't assigned a primary key yet and `author.id` is None. After commit, SQLAlchemy reads the assigned id back and populates it." }, { "q": "Inside one Session, you call `session.get(Author, 1)` twice. How many SELECT queries run, and is the result the same object?", "choices": [ "One SELECT; both calls return the same object instance (`is` is True) - the identity map serves the second call from memory", "Two SELECTs; you get two separate objects with equal data", "Two SELECTs, but SQLAlchemy returns the same object both times", "Zero SELECTs; get never touches the database" ], "answer": 0, "explain": "The Session is an identity map. The first get runs the SELECT and stores the Author on the workbench; the second get finds it already there and returns that same instance from memory with no new query, so `is` is True. One row maps to exactly one object per Session." }, { "q": "You load a Book (it's now persistent), set `book.title = \"New Title\"`, and call `session.commit()`. There's no `session.save()` call. What happens?", "choices": [ "Nothing is saved - you must call session.save() or session.update() to persist the change", "It raises an error because you modified an object without re-adding it", "An UPDATE fires at commit - dirty tracking noticed the changed attribute, so no explicit save call is needed", "The whole row is re-inserted as a new record" ], "answer": 2, "explain": "While an object is persistent, the Session watches it. Changing an attribute marks it dirty, and at the next flush (which commit triggers) the Session emits an UPDATE for the changed column. This is dirty tracking - there is no session.save()/session.update() method; mutating the object is the save." } ] ``` --- # Querying with select() In [Phase 4](04-the-session-and-unit-of-work.md) the Session learned to save your `Author`, `Book`, and `Tag` objects and track their changes. Now you want them back. This is the half of the round-trip you'll spend most of your life in: reading. Adding a book happens once; *finding* books happens constantly. **A query is a sentence you build up, then hand to the Session to say out loud.** You construct a `select(...)` statement - a Python object that *describes* what you want, piece by piece - and the Session executes it against the database and brings back the rows. The statement itself runs nothing; it's just a recipe. Building and running are two separate steps, and keeping them separate is what makes SQLAlchemy queries composable. Everything you write here maps to SQL you already know. If `WHERE`, `ORDER BY`, and `JOIN` feel shaky, keep [/guides/sql-joins-explained](/guides/sql-joins-explained) open in a tab - this phase is mostly those same ideas, expressed in Python instead of a string. ## The select() construct πŸ“ **`select(Model)`** - the modern way to describe a read. You build the statement with `select(Book)`, then run it through the Session with `session.execute(stmt)` or, more commonly, `session.scalars(stmt)`. Here's the simplest possible query - every book in the table: ```python from sqlalchemy import select stmt = select(Book) books = session.scalars(stmt).all() for book in books: print(book.title) ``` *What just happened:* `select(Book)` built a statement object - nothing touched the database yet. Passing it to `session.scalars(...)` is what actually ran the query; `.all()` collected the results into a list of `Book` instances. You can build the statement on one line and execute it on another, store it in a variable, pass it to a function - it's an ordinary Python object until the Session runs it. That tiny statement generated this SQL: ```sql SELECT books.id, books.title, books.year FROM books ``` *What just happened:* `select(Book)` expands to "select every column of the `books` table." SQLAlchemy read the column list straight off your model (the source of truth from [Phase 3](03-defining-models.md)) and wrote the `SELECT`. Reading the Python and the SQL side by side is the habit to build now - every construct in this phase corresponds to a clause you can point at. ⚠️ **You'll see `session.query(Book)` everywhere - it's the old way.** Pre-2.0 SQLAlchemy queried with `session.query(Book).filter(...).all()`, and a huge amount of tutorial and StackOverflow code still uses it. It isn't deleted and it still runs, but the 2.0 style is `select()` + `session.execute`/`scalars`. When you copy a snippet built on `session.query(...)`, you've found a pre-2.0 example - translate it to `select(...)` before pasting, the same way you translate bare `Column(...)` into `mapped_column(...)`. ## Getting results back The statement is the same; how you *pull* results depends on what you want. Four tools cover almost everything: ```python # A list of objects all_books = session.scalars(select(Book)).all() # Just the first object, or None if there are no rows first_book = session.scalars(select(Book)).first() # Exactly one scalar value (one object, or one column) the_book = session.scalar(select(Book).where(Book.id == 1)) # By primary key - the fast path same_book = session.get(Book, 1) ``` *What just happened:* `session.scalars(stmt)` returns a stream of single objects (the "scalar" part means "unwrap each row to its one entity, don't hand me a row tuple"); `.all()` makes a list, `.first()` takes one or `None`. `session.scalar(stmt)` (singular) is the shortcut for "I expect one value" - it runs the statement and returns the first scalar directly. And `session.get(Book, 1)` is special: it's the **primary-key lookup**. It can serve the object straight from the Session's identity map ([Phase 4](04-the-session-and-unit-of-work.md)) without hitting the database at all if it's already loaded - so reach for `get` whenever you're fetching by id. πŸ’‘ The naming trips people up, so anchor it: **`scalars` (plural) β†’ many objects, `scalar` (singular) β†’ one value, `get` β†’ one object by primary key.** When you find yourself writing `select(Book).where(Book.id == ...)`, stop - that's exactly what `session.get` is for. ## Filtering with where() A query with no filter returns the whole table, which is rarely what you want. `.where(...)` narrows it, and it maps directly to SQL's `WHERE`: ```python # Books published after 2000 recent = session.scalars( select(Book).where(Book.year > 2000) ).all() # Two conditions - chained .where() calls are ANDed together recent_pythons = session.scalars( select(Book) .where(Book.year > 2000) .where(Book.title.ilike("%python%")) ).all() # OR - combine with | and wrap each side in parens classics_or_new = session.scalars( select(Book).where((Book.year < 1950) | (Book.year > 2020)) ).all() # Membership - IN a set of values picks = session.scalars( select(Book).where(Book.id.in_([1, 4, 7])) ).all() ``` *What just happened:* the comparison `Book.year > 2000` doesn't compute a Python boolean - it builds a SQL condition object, because `Book.year` is a mapped column, not a plain number. Stacking `.where(...)` calls **ANDs** them (both must hold). For **OR**, combine conditions with `|` and wrap each in parentheses - Python's operator precedence will bite you otherwise, so the parens are mandatory, not stylistic. `.ilike("%python%")` is a case-insensitive `LIKE` (the `i`), matching any title containing "python" in any casing. `.in_([...])` matches a column against a list - far cleaner than OR-ing a dozen equalities. Here's the SQL behind the two-condition query, so you can see the `AND`: ```sql SELECT books.id, books.title, books.year FROM books WHERE books.year > 2000 AND books.title ILIKE '%python%' ``` *What just happened:* the two chained `.where(...)` calls became `year > 2000 AND title ILIKE ...`. This is the same filtering you'd write by hand in SQL ([/guides/sql-joins-explained](/guides/sql-joins-explained) leans on the same `WHERE` semantics) - SQLAlchemy is just letting you build the condition out of Python expressions instead of a string, which means your editor and type checker can help you. ## Ordering, limiting, and picking columns Three more clauses round out everyday reads: sort the results, take a slice, and (when you don't need full objects) fetch just the columns you care about. ```python # Newest first, then take 10 - classic pagination page = session.scalars( select(Book) .order_by(Book.year.desc()) .limit(10) .offset(0) ).all() ``` *What just happened:* `.order_by(Book.year.desc())` sorts by year, descending (`.asc()` for the other direction). `.limit(10)` caps the result at 10 rows; `.offset(0)` skips none - bump it to `.offset(10)` for the next page, `.offset(20)` for the page after. `limit` + `offset` is the standard pagination pair. When you only need a couple of fields - say, a dropdown of titles and years - selecting whole `Book` objects is wasteful. Ask for specific columns instead: ```python rows = session.execute( select(Book.title, Book.year).order_by(Book.title) ).all() for title, year in rows: print(title, year) ``` *What just happened:* notice two changes. First, `select(Book.title, Book.year)` lists columns, not the whole model. Second, we used `session.execute(...)`, **not `scalars`** - because the result isn't single objects anymore, it's **rows of tuples** like `("Fluent Python", 2022)`. Each row unpacks into `title, year`. Use `scalars` when you want objects; use `execute` when you select individual columns and want the row tuples. That's the dividing line between the two methods. πŸ’‘ Reach for column selects when you're reading a lot of rows but only displaying a field or two - you skip the cost of building full mapped objects. Reach for `select(Book)` (with `scalars`) when you actually need the objects: to read several attributes, to modify them, or to follow their relationships. ## Joins and aggregates (a taste) Real questions span tables - "books by Ursula K. Le Guin," "how many books per author." That's `.join(...)` and aggregate functions. We'll keep this to a taste; the relationship machinery that makes joins effortless lands in [Phase 6](06-relationships.md). ```python from sqlalchemy import func # Books written by a specific author - join books to authors le_guin_books = session.scalars( select(Book) .join(Author) .where(Author.name == "Ursula K. Le Guin") ).all() # How many books each author has - count + group_by counts = session.execute( select(Author.name, func.count(Book.id)) .join(Book) .group_by(Author.name) ).all() for name, n in counts: print(name, n) ``` *What just happened:* `.join(Author)` stitches `books` to `authors` on their relationship (once you've defined it in Phase 6, SQLAlchemy figures out the join condition for you), and the `.where(...)` filters on a column from the *joined* table. The second query introduces `func.count(Book.id)` - an aggregate - with `.group_by(Author.name)` to count books per author. Because it selects columns (`name` and a count), it's `session.execute` returning tuples, not `scalars`. πŸ’‘ Every query here is the **same `select(...)` object, built up by chaining methods** - `.where`, `.order_by`, `.join`, `.group_by` - and then handed to the Session to run. This is SQLAlchemy Core's expression language surfaced inside the ORM; the composability is the whole point. ⚠️ **Count your queries as you go.** Every example above is *one* `SELECT`. The danger arrives in Phase 6: once books have an `.author` relationship, it's tempting to loop over books and read `book.author.name` each time - and that quietly fires a *separate* query per book. That's the **N+1 problem**, and it's the single most common SQLAlchemy performance trap. We name it here so the habit - watch the SQL your code generates - is already in place when [Phase 7](07-loading-strategies-and-n-plus-1.md) shows you how to kill it. ## Recap 1. **`select(Model)`** builds a statement (it runs nothing); the Session executes it via **`session.scalars(stmt)`** (for objects) or **`session.execute(stmt)`** (for column/row tuples). 2. **`session.query(...)` is the pre-2.0 style** - common in old tutorials, still works, but translate it to `select()` for new code. 3. Pull results with **`.all()`** (list), **`.first()`** (one or `None`), **`session.scalar(stmt)`** (one value), and **`session.get(Model, pk)`** (primary-key lookup, the fast path that can skip the database). 4. **`.where(...)`** filters; chained `.where` calls are **AND**, `|` with parentheses is **OR**, plus `.ilike("%x%")` for case-insensitive matching and `.in_([...])` for membership. 5. **`.order_by(col.desc())`**, **`.limit()`**/**`.offset()`** (pagination), and **`select(Book.title, Book.year)`** for lightweight column reads that come back as **tuples** (use `execute`, not `scalars`). 6. **`.join(...)`**, **`func.count()`**, and **`.group_by(...)`** give a taste of cross-table queries - and the rule to carry forward is **count the SQL you generate**, because relationships set up the N+1 trap (Phase 7). ## Quick check Test yourself on the distinctions most likely to trip you up when querying: ```quiz [ { "q": "You write `select(Book.title, Book.year)` and want the results. Which method fits, and what comes back?", "choices": [ "session.scalars(stmt).all() - a list of Book objects", "session.execute(stmt).all() - a list of row tuples like ('Fluent Python', 2022)", "session.get(stmt) - a single Book by primary key", "session.scalar(stmt) - the title string only" ], "answer": 1, "explain": "Selecting specific columns returns rows of tuples, not mapped objects, so you use session.execute(...). Use scalars only when you select whole entities like select(Book) and want them unwrapped to single objects." }, { "q": "You want books where year < 1950 OR year > 2020. Which is correct?", "choices": [ "select(Book).where(Book.year < 1950).where(Book.year > 2020)", "select(Book).where(Book.year < 1950 or Book.year > 2020)", "select(Book).where((Book.year < 1950) | (Book.year > 2020))", "select(Book).where(Book.year < 1950, Book.year > 2020)" ], "answer": 2, "explain": "OR uses | with each condition wrapped in parentheses (the parens are required because of Python operator precedence). Chained .where() calls are ANDed, and Python's `or` keyword doesn't build a SQL condition the way | does." }, { "q": "You need the Book with id 5, and it may already be loaded in this Session. What's the best call?", "choices": [ "session.get(Book, 5) - the primary-key lookup that can serve it from the identity map without hitting the database", "session.scalars(select(Book)).all() then search the list in Python", "session.query(Book).get(5) - the modern 2.0 way", "session.execute(select(Book.id)) and match on 5" ], "answer": 0, "explain": "session.get(Model, pk) is the dedicated primary-key fetch. It can return the object straight from the Session's identity map if it's already loaded, avoiding a database round-trip - exactly the case described." } ] ``` --- # Relationships Up to [Phase 5](05-querying-with-select.md), every model has been a loner. An `Author` had a name and a bio; a `Book` had a title and a year; nothing pointed at anything else. But the whole reason you reached for a relational database is that things *relate* - an author writes books, a book wears tags. This phase is where you wire those connections so you can walk from one object to another in plain Python: `author.books`, `book.author`, `book.tags`. ## The mental model: a foreign key with a Python face πŸ“ **A relationship lives in two places at once.** In the database, "this book was written by that author" is a single column: `books.author_id` holds the `id` of a row in `authors`. That's all a foreign key is - a column whose value matches some other table's primary key. In the ORM, that *same* link shows up as an attribute you navigate: `book.author` hands you the whole `Author` object, and `author.books` hands you the list of `Book`s. One foreign key in the database, two attributes in Python - and `relationship()` is the thing that keeps those two views talking to each other. If foreign keys themselves are fuzzy, [Relationships & Keys](/guides/relationships-and-keys) is the prerequisite - it explains primary keys, foreign keys, and referential integrity from the ground up. And [SQL Joins Explained](/guides/sql-joins-explained) shows how the database stitches the rows back together underneath what `relationship()` does for you. Here's the domain we'll build for the rest of this phase: ```mermaid erDiagram AUTHOR ||--o{ BOOK : writes BOOK }o--o{ TAG : "tagged with" ``` *What just happened:* one author writes many books (`1 - *`), and books and tags form a many-to-many (`* - *`) - a book carries many tags, a tag labels many books. Two relationship shapes, two SQLAlchemy tools. We'll do the one-to-many first, since it's the one you'll write most. ## One-to-many: foreign key + relationship() A one-to-many needs two ingredients, and it's worth knowing which is which. **The foreign key is the part the database cares about.** **The `relationship()` is the part *you* care about** - it's pure ORM convenience that turns that key into a navigable attribute. Start with the foreign key. Many books point to one author, so the `author_id` column lives on `Book`: ```python from sqlalchemy import ForeignKey from sqlalchemy.orm import Mapped, mapped_column, relationship class Author(Base): __tablename__ = "authors" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] books: Mapped[list["Book"]] = relationship(back_populates="author") class Book(Base): __tablename__ = "books" id: Mapped[int] = mapped_column(primary_key=True) title: Mapped[str] author_id: Mapped[int] = mapped_column(ForeignKey("authors.id")) author: Mapped["Author"] = relationship(back_populates="books") ``` *What just happened:* `author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))` is the real foreign key - an integer column on `books` that references the `id` column of `authors`. That single line is all the database needs. The two `relationship()` calls are the ORM layer on top: `Author.books` is the collection side (typed `list["Book"]`), and `Book.author` is the single-object side (typed `"Author"`). The string `"Book"` is a forward reference - `Book` isn't defined yet when `Author` is being read, so you name it as a string and SQLAlchemy resolves it later. πŸ“ **`back_populates` is what links the two `relationship()` calls into one.** `Author.books` says `back_populates="author"` and `Book.author` says `back_populates="books"`: each names the attribute on the *other* class. That cross-reference tells SQLAlchemy "these two attributes are the same relationship seen from opposite ends" - so it keeps them in sync in memory. Set one, and SQLAlchemy updates the other for you (more on that in a moment). Now you can walk the link both ways: ```python author = session.get(Author, 1) for book in author.books: # one-side β†’ many-side print(book.title) book = session.get(Book, 5) print(book.author.name) # many-side β†’ one-side ``` *What just happened:* `author.books` gives you a list of that author's `Book` objects; `book.author` gives you the one `Author` who wrote it. You never wrote a JOIN or touched `author_id` by hand - `relationship()` issues the query and hands back live objects. (When those queries actually fire, and why that can bite you, is [Phase 7](07-loading-strategies-and-n-plus-1.md).) Only the foreign key shows up in the schema - the `relationship()` calls generate no SQL of their own: ```sql CREATE TABLE authors ( id INTEGER NOT NULL, name VARCHAR NOT NULL, PRIMARY KEY (id) ); CREATE TABLE books ( id INTEGER NOT NULL, title VARCHAR NOT NULL, author_id INTEGER NOT NULL, PRIMARY KEY (id), FOREIGN KEY(author_id) REFERENCES authors (id) ); ``` *What just happened:* there's one foreign key in the whole picture - `books.author_id`, with a `FOREIGN KEY ... REFERENCES authors(id)` constraint that the database enforces. Both `author.books` and `book.author` are just two ways of reading that one column. The `relationship()` calls added zero columns; they're ergonomics, not storage. ## The both-sides gotcha Here's where `back_populates` earns its keep, and where people trip. Because the two sides are linked, you only ever need to touch **one** of them - appending to the collection sets the other side automatically: ```python author = Author(name="Ursula K. Le Guin") book = Book(title="A Wizard of Earthsea") author.books.append(book) # this ALSO sets book.author = author print(book.author.name) # β†’ "Ursula K. Le Guin" (already wired up) session.add(author) session.commit() # author_id is written correctly ``` *What just happened:* appending `book` to `author.books` made SQLAlchemy set `book.author = author` in the same breath - that's `back_populates` doing its job in memory. When you `commit`, SQLAlchemy reads the relationship, fills in `book.author_id` with the author's id, and saves both rows. You added `author` to the session and `book` came along with it through the relationship. (Equivalently, you could set `book.author = author` and SQLAlchemy would add `book` to `author.books` - the sync goes both ways.) ⚠️ **The trap is reaching around the relationship.** Two ways to make your objects and your database disagree: - **Setting only the raw `author_id`.** If you write `book.author_id = 1` by hand instead of `book.author = author`, the relationship attribute `book.author` may still read as stale or `None` in the current session until things refresh - the ORM's in-memory graph and the column you poked don't match. Prefer assigning the object (`book.author = author`) and let SQLAlchemy manage the id. - **Forgetting to add the parent to the session.** If you build `author.books.append(book)` but never `session.add(author)` (and there's no cascade reaching `book`), nothing gets persisted - your beautifully linked objects never touch the database. πŸ’‘ The reliable habit: link objects by assigning the relationship attribute (append to the collection, or set the single side), add the top of the graph to the session, and let SQLAlchemy compute the foreign-key ids. Don't hand-edit `_id` columns unless you have a specific reason - that's working *under* the tool instead of *with* it. ## Many-to-many: the association table Books and tags are many-to-many: a book has many tags, a tag labels many books. Neither table can hold the foreign key - which single row would `books.tag_id` even point at? - so the database uses a third table whose only job is to pair ids. In SQLAlchemy you declare that table directly with `Table(...)` and hand it to `relationship()` as `secondary=`: ```python from sqlalchemy import Column, ForeignKey, Integer, Table from sqlalchemy.orm import Mapped, mapped_column, relationship book_tag = Table( "book_tag", Base.metadata, Column("book_id", Integer, ForeignKey("books.id"), primary_key=True), Column("tag_id", Integer, ForeignKey("tags.id"), primary_key=True), ) class Book(Base): __tablename__ = "books" id: Mapped[int] = mapped_column(primary_key=True) title: Mapped[str] tags: Mapped[list["Tag"]] = relationship(secondary=book_tag, back_populates="books") class Tag(Base): __tablename__ = "tags" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] books: Mapped[list["Book"]] = relationship(secondary=book_tag, back_populates="tags") ``` *What just happened:* `book_tag` is a bare association table - two foreign-key columns, `book_id` and `tag_id`, that together form its primary key (each pairing is unique). It's a `Table`, not a model class, because it carries no data of its own; it exists only to connect. Each `relationship(secondary=book_tag, ...)` tells SQLAlchemy "to get from a `Book` to its `Tag`s, hop through `book_tag`." `back_populates` ties the two sides together exactly like the one-to-many did, so adding on one side reflects on the other. Navigating it feels identical to the one-to-many - the join table is invisible: ```python book = session.get(Book, 5) tag = session.get(Tag, 2) book.tags.append(tag) # inserts a row into book_tag session.commit() for t in book.tags: # β†’ the tags on this book print(t.name) for b in tag.books: # β†’ every book wearing this tag print(b.title) ``` *What just happened:* `book.tags.append(tag)` doesn't touch `books` or `tags` - it inserts a `(book_id, tag_id)` row into `book_tag`. Because of `back_populates`, `tag.books` now includes that book too. You work in objects on both ends; SQLAlchemy manages the pairing rows in the table you never query directly. πŸ’‘ **When the link itself needs data, drop the bare `Table`.** A plain association table holds *only* the two ids. The moment you need to record something *about* the pairing - when the tag was applied, who applied it, a relevance score - there's nowhere to put it. The fix is to promote the join to a real model (an *association object*, e.g. a `BookTag` class with its own columns plus two `relationship()`s back to `Book` and `Tag`). Rule of thumb: pure pairing β†’ `secondary=` table; pairing *with attributes* β†’ association object. ## Cascades: propagating deletes to children By default, deleting an `Author` does not delete their `Book`s - SQLAlchemy will try to null out `books.author_id` and leave the books orphaned (or error if the column is `NOT NULL`). Often that's not what you want: if a book can't exist without its author, deleting the author should delete the books too. That's what `cascade` controls. πŸ“ **`cascade="all, delete-orphan"`** makes the children share the parent's lifecycle. Set it on the *collection* side of the relationship: ```python class Author(Base): __tablename__ = "authors" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] books: Mapped[list["Book"]] = relationship( back_populates="author", cascade="all, delete-orphan", ) ``` *What just happened:* `"all"` propagates the usual operations (save, refresh, and crucially delete) from an `Author` to its `Book`s - `session.delete(author)` now deletes that author's books too. `delete-orphan` adds one more rule: if you *remove* a book from `author.books` (so it no longer belongs to any author), that book is deleted on commit rather than left dangling with a null `author_id`. This is exactly right for a true parent-child where the child can't outlive the parent. ⚠️ **Be deliberate - cascade deletes are easy to over-apply.** The cascade belongs on the side that *owns* the children's lifecycle (here, `Author.books`). Do not slap it on a relationship pointing at something *shared*. A cascade-delete on the many-to-many `Book.tags`, for instance, would delete the `Tag` rows themselves when you delete a book - but tags are shared across many books; you meant to remove the *pairing*, not destroy the tag. Ask "do I own this thing, or just reference it?" Cascade only what you own. πŸ’‘ Step back at what relationships bought you. You now move through your data as objects - `author.books`, `book.tags`, `book.author` - instead of writing JOINs by hand. That's a genuine ergonomic win. But it hides something: every one of those attribute accesses can fire a query you didn't write. Loop over a hundred authors touching `author.books` each time and you've quietly issued a hundred-and-one queries. That hidden cost is the **N+1 problem**, and taming it - eager loading, `selectinload`, `joinedload` - is exactly what [Phase 7](07-loading-strategies-and-n-plus-1.md) is about. ## Recap 1. A relationship is **one foreign key in the database** but **a navigable attribute in Python**; `relationship()` translates between the two. The FK (`ForeignKey("authors.id")`) is what the database stores - the `relationship()` calls add no columns. 2. A **one-to-many** needs the FK column on the many-side (`Book.author_id`) plus a pair of `relationship(back_populates=...)` calls - `Author.books` and `Book.author` - wired to each other. 3. **`back_populates` keeps both sides in sync in memory**: appending to `author.books` also sets `book.author`. Link objects through the relationship attributes; let SQLAlchemy fill in the FK ids. 4. The **gotchas** are reaching around the relationship: hand-setting the raw `_id`, or building the link but forgetting to `session.add()` the parent (with no cascade to carry the child). 5. A **many-to-many** uses an **association `Table`** passed as `secondary=`; navigate `book.tags` / `tag.books` and SQLAlchemy manages the pairing rows. When the link needs its own data, use an **association object** instead of a bare table. 6. **`cascade="all, delete-orphan"`** on the collection side propagates deletes to children the parent owns (Authorβ†’Books) - be deliberate, and never cascade-delete shared data like tags. ## Quick check Lock in the ideas most likely to bite you when wiring relationships: ```quiz [ { "q": "In a one-to-many between Author and Book, where does the foreign key column actually live?", "choices": [ "On Author, as a list of book ids", "On Book, as author_id referencing authors.id", "In a separate join table linking the two", "Nowhere - relationship() stores the link internally" ], "answer": 1, "explain": "Many books point to one author, so the FK lives on the many-side: books.author_id references authors.id. The two relationship() calls (Author.books, Book.author) are ORM navigation on top of that one column; they add no columns of their own." }, { "q": "With back_populates wired between Author.books and Book.author, you call author.books.append(book). What happens to book.author?", "choices": [ "It stays None until you set it manually", "It is set to author automatically, in memory, by back_populates", "It raises an error because you set the wrong side", "It is only set after you call session.commit()" ], "answer": 1, "explain": "back_populates links the two attributes as one relationship seen from both ends. Appending to author.books immediately sets book.author = author in memory - you only need to touch one side. On commit, SQLAlchemy writes book.author_id from that." }, { "q": "You put cascade=\"all, delete-orphan\" on the many-to-many Book.tags relationship and then delete a book. What goes wrong?", "choices": [ "Nothing - that's the correct place for the cascade", "It deletes the shared Tag rows themselves, not just the book's pairings, removing tags other books still use", "It deletes the book but leaves the join rows dangling", "It refuses to delete because tags are referenced elsewhere" ], "answer": 1, "explain": "Cascade belongs on relationships to children you own. Tags are shared across many books, so cascade-deleting them destroys data other books depend on. You only meant to remove the pairing rows in the association table. Ask 'do I own this, or just reference it?' and cascade only what you own." } ] ``` --- # Loading Strategies & the N+1 Trap [Phase 6](06-relationships.md) handed you something that feels like magic: write `author.books` and a list of `Book` objects appears. No JOIN, no `author_id` fiddling, no SQL at all on your screen. That ergonomic win is real - and it hides the single most important performance question in the whole guide: **when you touch `author.books`, what does SQLAlchemy actually do behind your back?** The answer is the difference between a page that loads in 10 milliseconds and one that loads in 10 seconds. Almost every "SQLAlchemy is slow" complaint you'll ever read traces back to getting this wrong without noticing. We're going to make it visceral - you're going to *see* the flood of queries - because once you've watched one loop fire 101 queries, you will never write a blind loop over a relationship again. ## The mental model: a relationship loads when you touch it πŸ“ **By default, a `relationship()` is *lazy*: it loads nothing until you access the attribute, and at the exact moment you do, SQLAlchemy fires a query.** The attribute isn't your data sitting there waiting - it's a trigger. Reading it pulls the trigger. Hold that one sentence the whole phase: **lazy means the query runs the moment you read the attribute, not before.** Convenient, because you don't load books for an author whose books you never look at. Dangerous, because the cost is invisible in the Python - an attribute access looks free, and it isn't. Watch a single lazy load happen: ```python author = session.get(Author, 1) # query #1: load the author row print(author.name) # no query - name came back with the author for book in author.books: # πŸ’₯ touching .books NOW fires query #2 print(book.title) ``` The SQL that actually hits the database: ```sql -- session.get(Author, 1): SELECT authors.id, authors.name FROM authors WHERE authors.id = 1; -- ...nothing more until you touch author.books, and THEN: SELECT books.id, books.title, books.author_id FROM books WHERE books.author_id = 1; ``` *What just happened:* `session.get` ran exactly **one** query - for the author. `author.name` was already in hand, so reading it cost nothing. But the line `for book in author.books` is where the second query fires: that's the lazy relationship "waking up." Lazy isn't *whether* you pay for the books - it's *when*. And that timing is the root of both problems in this phase. ## DetachedInstanceError: the lazy load that can't fire πŸ“ A lazy relationship can only run its query while the `Session` is still **open and watching the object**. Recall from [Phase 4](04-the-session-and-unit-of-work.md): once the Session closes, every object it loaded becomes **detached** - nobody's tracking it, and there's no live Session to run a query through. So if you touch a lazy attribute *after* the Session closed, the trigger has nothing to fire into, and SQLAlchemy raises. This is the classic web-app bug, and the shape is always the same: **load in one place, access in another.** ```python # --- data layer: the Session opens AND closes here --- def load_author(author_id): with Session(engine) as session: author = session.get(Author, author_id) # .books NOT loaded (lazy) return author # ← the `with` block exits: Session closed, `author` is now DETACHED # --- view / template layer: the Session is long gone --- author = load_author(1) for book in author.books: # πŸ’₯ the lazy trigger fires into nothing print(book.title) ``` ```console sqlalchemy.orm.exc.DetachedInstanceError: Parent instance is not bound to a Session; lazy load operation of attribute 'books' cannot proceed ``` *What just happened:* `load_author` opened a Session, fetched the author *without* the books, then the `with` block closed the Session - detaching the author. Back in the view, `author.books` asks the lazy trigger to load, but its Session is gone: no open Session, no query, no data, exception. ⚠️ The tempting "fix" is to make the relationship eager so it loads before the Session closes - but that just trades this crash for the N+1 you're about to meet. The real fix is to **load the books deliberately while the Session is open**, which is the rest of this phase. This is Phase 4's rule biting: *a detached object can't lazy-load.* ## The N+1 problem: the main event This is the one. The performance killer that ships to production looking completely innocent, sails through code review, works flawlessly on your laptop, and then falls over the first time it meets real data. Watch closely. You load all your authors - one clean query - then loop over them to print each author's book count. Every `author.books` access is a lazy load, so every iteration pulls the trigger: ```python authors = session.scalars(select(Author)).all() # query #1: all the authors for author in authors: print(f"{author.name}: {len(author.books)} books") # ↑ each iteration fires ANOTHER query ``` It reads like an ordinary loop. It is a slow-motion disaster. Here's the SQL SQLAlchemy actually emits with, say, 100 authors: ```sql SELECT authors.id, authors.name FROM authors; -- the "1": one query for all authors SELECT books.id, books.title, books.author_id FROM books WHERE books.author_id = 1; -- the "N" begins... SELECT books.id, books.title, books.author_id FROM books WHERE books.author_id = 2; SELECT books.id, books.title, books.author_id FROM books WHERE books.author_id = 3; SELECT books.id, books.title, books.author_id FROM books WHERE books.author_id = 4; -- ... one more SELECT for every single author ... SELECT books.id, books.title, books.author_id FROM books WHERE books.author_id = 99; SELECT books.id, books.title, books.author_id FROM books WHERE books.author_id = 100; ``` *What just happened:* **1 query to load the authors, then N more - one per author - to load each one's books.** That's `1 + N` queries. 100 authors = **101 queries**. A thousand authors = 1001. Each one is a full round trip to the database: network hop, parse, plan, execute, return. Individually they're quick; multiplied by N they're a stampede, and your endpoint crawls. This is the **N+1 problem**, and it is the number-one reason ORMs get blamed for being slow. ⚠️ The cruelty of N+1 is that it's *invisible in the code and scales with your data, not your logic*. The Python is a clean loop. It works perfectly with 3 authors in your test database. Then it meets 5,000 authors in production and dies - and nobody changed a line. It passes code review because there's nothing to see; the query count lives in the data, not the source. The only way to catch it is to *watch the SQL*. This isn't a SQLAlchemy quirk - it's an ORM-shaped trap that bites every ORM the same way. If you've met it in Java, [Hibernate & JPA from Zero](/guides/hibernate-and-jpa-from-zero) walks the identical problem with `JOIN FETCH`; the disease and the cure are the same, only the syntax changes. And when the slow query is one you *did* write deliberately - not an accidental flood - that's a different skill, measuring and reading query plans, covered in [Why Is My Query Slow?](/guides/why-is-my-query-slow). ## Eager loading: selectinload vs joinedload The cure for N+1 is to tell SQLAlchemy up front, *I'm going to need the books - fetch them together.* You do that per query with `.options(...)` on your `select`, and you have two main tools. They both eliminate the N+1; they differ in *how* the SQL comes out, and that difference matters. ### selectinload - a second query with IN (best for collections) `selectinload(Author.books)` issues **one extra query** that loads all the needed books at once, using an `IN` clause over the author ids it already fetched: ```python from sqlalchemy.orm import selectinload authors = session.scalars( select(Author).options(selectinload(Author.books)) ).all() for author in authors: print(f"{author.name}: {len(author.books)} books") # no extra queries - already loaded ``` ```sql -- query #1: the authors SELECT authors.id, authors.name FROM authors; -- query #2: ALL their books in one shot, via IN SELECT books.id, books.title, books.author_id FROM books WHERE books.author_id IN (1, 2, 3, 4, ..., 99, 100); ``` *What just happened:* **101 queries collapsed to 2.** The first loads the authors; the second loads every one of their books in a single `IN (...)` query, and SQLAlchemy distributes the rows back onto the right `author.books` collections. The loop now runs without emitting a single extra `SELECT`. Two queries regardless of whether you have 100 authors or 100,000 - that's the win. (For very large id sets SQLAlchemy chunks the `IN` list, so it may be 2–3 queries, not literally 2 - still flat, not `1+N`.) ### joinedload - a single JOIN (best for to-one) `joinedload(Author.books)` instead folds the books into the *same* query with a `LEFT OUTER JOIN`: ```python from sqlalchemy.orm import joinedload book = session.scalars( select(Book).options(joinedload(Book.author)) # many Books β†’ one Author each ).all() for b in book: print(f"{b.title} by {b.author.name}") # author already loaded, no extra query ``` ```sql -- ONE query: books and their authors joined together SELECT books.id, books.title, books.author_id, authors.id AS author_id_1, authors.name FROM books LEFT OUTER JOIN authors ON authors.id = books.author_id; ``` *What just happened:* **one query did the whole job.** The JOIN pulled each book and its author in the same result set, so `b.author.name` is already in memory - no `1+N`, in fact no second query at all. πŸ’‘ **When to use which.** Reach for `selectinload` for **collections / one-to-many** (`Author.books`, `Book.tags`): a JOIN over a collection multiplies rows (one author row repeated per book), so the separate `IN` query is leaner and avoids that blow-up. Reach for `joinedload` for **many-to-one / one-to-one** (`Book.author`): there's exactly one row on the other side, so the JOIN adds no duplication and saves you a round trip. The rough rule: **collections β†’ `selectinload`, to-one β†’ `joinedload`.** When unsure, default to `selectinload` - it's the safer choice because it never row-multiplies. ## Choosing your strategy - and the discipline that saves you You can also set a *default* strategy on the relationship itself with `lazy=`: ```python class Author(Base): __tablename__ = "authors" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] # default to selectin loading every time an Author is queried books: Mapped[list["Book"]] = relationship( back_populates="author", lazy="selectin", # "select" (lazy, the default), "selectin", "joined", "raise" ) ``` *What just happened:* `lazy="selectin"` makes `Author.books` eager-load by `IN`-query *every* time you fetch an `Author`, no `.options()` needed. Handy when you essentially always need the books - but it's a blunt instrument: it loads them on the one endpoint that never touches `author.books` too. πŸ’‘ **Prefer per-query `.options()` over a relationship-wide `lazy=`.** Different use-cases need different data: the list page needs book *counts*, the export needs every book *and* its tags, the search box needs neither. Baking one strategy into the mapping forces a compromise on all of them. Keeping the default lazy and eager-loading explicitly per query lets each use-case load exactly what it needs and nothing more. (One sharp variant of `lazy=` is worth knowing: `lazy="raise"` makes any *accidental* lazy load throw instead of silently firing SQL - a great way to force every load to be deliberate and catch N+1 at the source.) ⚠️ **The `joinedload` + collection + pagination trap.** If you `joinedload` a *collection* and then add `.limit()` / `.offset()` for pagination, the JOIN has already multiplied your rows (one author row per book), so the `LIMIT` chops *rows*, not *authors* - you'll get a wrong, ragged page. SQLAlchemy handles this for you *only* if you use `selectinload` (it paginates the parents, then loads their children separately). So: paginate a one-to-many β†’ use `selectinload`, never `joinedload`. πŸ’‘ Here's the throughline, the habit that separates people who fight SQLAlchemy from people who command it: **default to lazy, eager-load explicitly per query with `.options()`, and ALWAYS watch the SQL count.** N+1 never announces itself - the only way to catch it is to *see* the queries. So make them visible while you develop: - Run with `create_engine(url, echo=True)` and actually look at the console for one page or endpoint. If a single user action prints a column of near-identical `SELECT`s, you've found an N+1. - Better, add a **query counter** to your tests (a SQLAlchemy event listener on `"before_cursor_execute"`, or a library like `nplusone`) that asserts "this endpoint runs at most 3 queries." That turns N+1 from a thing you discover in production into a test that fails in CI. πŸ“ **"SQLAlchemy is slow" is almost always an unnoticed N+1.** SQLAlchemy isn't slow - a loop that secretly fires 500 queries is slow, and the ORM just made it effortless to write that loop without seeing it. Counting your queries is how you stay on the fast side of that line. ## Recap 1. A `relationship()` is **lazy by default**: it loads nothing until you access the attribute, and the moment you do, SQLAlchemy fires a query. The cost is invisible in the Python. 2. **`DetachedInstanceError`** happens when you touch a lazy relationship after the `Session` closed (the object is detached, [Phase 4](04-the-session-and-unit-of-work.md)). The classic web bug: load in the data layer, access in the template. Fix it by loading while the Session is open - not by going eager. 3. The **N+1 problem**: load N parents in 1 query, then trigger 1 query per parent by touching its lazy collection in a loop = `1 + N` queries. 100 authors β†’ 101. It's invisible in code and scales with your *data*, not your logic - so it passes review and dies in production. 4. **`selectinload(Author.books)`** fixes it with one extra `IN` query (best for **collections** - no row multiplication); **`joinedload(Book.author)`** fixes it with a single JOIN (best for **to-one** - no duplication, one round trip). Pass either via `.options()` on the `select`. 5. **Default lazy, eager-load per query.** Prefer per-query `.options()` over a relationship-wide `lazy=`; ⚠️ never `joinedload` a collection you're paginating (use `selectinload`), and consider `lazy="raise"` to forbid accidental lazy loads. 6. πŸ’‘ The discipline: **watch the query count** with `echo=True` or a query counter in tests. Most "SQLAlchemy is slow" is really an unnoticed N+1. ## Quick check Lock in the one idea that wrecks more SQLAlchemy apps than any other: ```quiz [ { "q": "You run `select(Author)` to load 100 authors, then loop over them reading `author.books` on each (a default lazy relationship). How many SQL queries does SQLAlchemy run?", "choices": [ "1 - SQLAlchemy loads everything in a single query", "2 - one for authors, one for all books", "101 - one to load the authors, then one more per author to load its books (the N+1 problem)", "100 - one per author" ], "answer": 2, "explain": "This is the textbook N+1: 1 query for the authors, then N=100 lazy loads (one per author the moment you touch author.books in the loop) = 101 total. The loop looks innocent but each iteration pulls a lazy trigger that fires its own SELECT." }, { "q": "Your data-layer function loads an Author inside a `with Session(...)` block and returns it, then a template loops over `author.books` and crashes with DetachedInstanceError. What's the correct fix?", "choices": [ "Eager-load the books while the Session is open (e.g. .options(selectinload(Author.books)))", "Change the relationship to lazy='joined' so it's always eager everywhere", "Catch the exception and return an empty list", "Move session.close() to run later, in the template" ], "answer": 0, "explain": "The crash happens because the Session closed (the `with` block exited) and the author is now detached - a lazy trigger can't fire with no live Session. The right fix is to load the books deliberately while the Session is open, via selectinload/joinedload in .options(). Going blanket-eager 'fixes' the crash but reintroduces over-fetching and N+1 risk elsewhere." }, { "q": "You need each Author's books (a one-to-many collection) loaded efficiently, and you're paginating the authors with .limit()/.offset(). Which loader should you use, and why?", "choices": [ "joinedload - a single JOIN is always fastest for any relationship", "selectinload - it loads the collection in a separate IN query, so pagination correctly limits authors (joinedload's JOIN multiplies rows and breaks the LIMIT)", "Either works identically for paginated collections", "Neither - you must keep it lazy when paginating" ], "answer": 1, "explain": "For collections, selectinload is the right default: it paginates the parent authors first, then loads their books in one IN query. joinedload on a collection multiplies rows (one author row per book), so .limit() chops rows instead of authors and you get a wrong page. Rule of thumb: collections β†’ selectinload, to-one β†’ joinedload." } ] ``` --- # Migrations with Alembic **Your models define what the schema *should* be; Alembic figures out how to *get there* from whatever the database currently is - one small, reviewable, reversible step at a time.** Your `Author`, `Book`, and `Tag` classes are the destination. The live database is the starting point. A migration is the recorded set of turns that drives from one to the other. And because every turn is written down and applied in order, every environment - your laptop, a teammate's, staging, production - drives the exact same route and ends up at the exact same place. Up to now you've been leaning on `create_all`. That was the right tool for getting off the ground. This phase is about the moment it stops being enough - which arrives the first time you change a model whose table already exists. ## Why `create_all` isn't enough ⚠️ Cast your mind back to [Phase 3](03-defining-models.md). `Base.metadata.create_all(engine)` walks your models and issues a `CREATE TABLE` for each one - **but only for tables that don't already exist.** It creates; it never alters. That's not a bug, it's the whole design. And it's exactly what leaves you stranded once your schema starts to evolve. Watch the trap spring. You ship `Book` with a `title` and a `year`. Weeks later you decide every book needs a `subtitle`, so you add the column to the model and call `create_all` again: ```python class Book(Base): __tablename__ = "books" id: Mapped[int] = mapped_column(primary_key=True) title: Mapped[str] year: Mapped[int | None] subtitle: Mapped[str | None] # newly added Base.metadata.create_all(engine) # run again, hoping it adds the column ``` *What just happened:* nothing - and that's the problem. `create_all` sees that a `books` table already exists, shrugs, and moves on. It does **not** compare your new model to the existing table. It does **not** add the `subtitle` column. Your code now expects a column the database doesn't have, and the next query that touches `subtitle` blows up with an "no such column" error. `create_all` only knows how to go from *nothing* to *something*; it has no idea how to go from *something* to *something slightly different*. And even where it *could* help, you wouldn't want it to. ⚠️ Running schema-building code by hand against a live production database - no record of what ran, no way to undo it, no ordering guarantees across environments - is precisely the held-breath dread that the [database migrations guide](/guides/database-migrations) is written to cure. Real schemas evolve: columns get added, types get widened, tables get renamed. You need a tool that treats each of those changes as a versioned, ordered, reversible unit. That tool is Alembic. πŸ’‘ Reframe it once and it sticks: `create_all` is the **zero-to-one** tool (build the schema the first time, great for a fresh dev database). Alembic is the **one-to-many** tool (evolve a schema that already exists and may already hold data you can't afford to lose). ## What Alembic is πŸ“ **Alembic** is SQLAlchemy's migration tool - written by Mike Bayer, the same author as SQLAlchemy itself, so it understands your models natively. It gives you exactly the thing `create_all` lacks: a sequence of **migration scripts**, each one a small Python file with an `upgrade()` function (apply this change) and a `downgrade()` function (undo it). Alembic records which scripts have run in a dedicated version table inside your database, and applies any outstanding ones **in order**. πŸ’‘ The cleanest way to think about it: **Alembic is version control for your schema.** Each migration is a commit. The version table is the equivalent of "which commit am I currently on." `upgrade` moves you forward through history; `downgrade` rewinds. Just as Git lets a whole team converge on the same source code, Alembic lets every environment converge on the same schema. You wire it up once, per project, with `alembic init`: ```bash alembic init alembic ``` *What just happened:* Alembic scaffolds a folder (here called `alembic/`) plus an `alembic.ini` config file at your project root. Inside the folder are an `env.py` (the script Alembic runs on every migration), a `script.py.mako` template, and an empty `versions/` directory where your migration scripts will live. Nothing has touched your database yet - this is pure setup. The one edit that makes autogenerate work is pointing `env.py` at your models' metadata. Open `alembic/env.py` and set the `target_metadata`: ```python # alembic/env.py from myapp.models import Base # wherever your DeclarativeBase lives target_metadata = Base.metadata ``` *What just happened:* you handed Alembic the same `Base.metadata` catalog that `create_all` reads - the registry of every table your `Author`, `Book`, and `Tag` models describe. Now Alembic can compare *that* (what your models say) against the *actual* database (what currently exists) and work out the difference. That comparison is the foundation of the next section. ## Autogenerate: let Alembic write the migration πŸ“ Here's the feature that makes Alembic a joy rather than a chore. `alembic revision --autogenerate` **diffs your models against the current database** and writes a migration script that closes the gap - you don't hand-write the `CREATE TABLE` or `ADD COLUMN` yourself. Give the database a fresh start and create the books table: ```bash alembic revision --autogenerate -m "add books table" ``` *What just happened:* Alembic connected to the database, read its current state (no `books` table yet), compared it to `Base.metadata` (which has a `Book` model), saw the difference, and wrote a new file into `alembic/versions/`. The `-m` message becomes part of the filename and a human-readable label, exactly like a commit message. The script is generated but **not yet applied** - it's just sitting there waiting for you to review and run it. Open the generated file and you'll see something like this: ```python """add books table Revision ID: a1b2c3d4e5f6 Revises: Create Date: 2026-06-23 10:14:02.118 """ import sqlalchemy as sa from alembic import op # revision identifiers, used by Alembic. revision = "a1b2c3d4e5f6" down_revision = None def upgrade() -> None: op.create_table( "books", sa.Column("id", sa.Integer(), nullable=False), sa.Column("title", sa.String(), nullable=False), sa.Column("year", sa.Integer(), nullable=True), sa.PrimaryKeyConstraint("id"), ) def downgrade() -> None: op.drop_table("books") ``` *What just happened:* read it top to bottom. The header carries this migration's own `revision` id and its `down_revision` (the one it builds on - `None` here because it's the first). `upgrade()` is the change applied going forward: `op.create_table(...)` builds `books`, with one `sa.Column` per field of your `Book` model - and notice it faithfully translated your annotations (`Mapped[str]` β†’ `nullable=False`, `Mapped[int | None]` β†’ `nullable=True`), the same mapping you saw produce SQL in Phase 3. `downgrade()` is the exact inverse: `op.drop_table("books")` undoes it. Every migration carries both directions, which is what makes rollbacks possible. Later, when you add that `subtitle` column to the model and autogenerate again, you'd get a much smaller script - just the delta: ```python def upgrade() -> None: op.add_column("books", sa.Column("subtitle", sa.String(), nullable=True)) def downgrade() -> None: op.drop_column("books", "subtitle") ``` *What just happened:* this is the difference that `create_all` could never produce. Alembic saw the `books` table already existed, diffed it against the model, found one missing column, and emitted a single `op.add_column` (with the matching `op.drop_column` to reverse it). Small, surgical, and reversible - one step on the route. ⚠️ **Always read the autogenerated script before you trust it.** Autogenerate is a brilliant first draft, not a final answer. It reliably catches added/dropped tables and columns and many type changes, but it has real blind spots: a **column rename looks like a drop-plus-add** to the differ (it would delete the old column - and its data - and create an empty new one, which is almost never what you want); some **type changes and constraint tweaks** it misses or gets subtly wrong; and it can't see **data migrations** at all. Treat the generated file as a pull request from a fast but literal-minded colleague: review every line, and edit it before you apply it. ## Applying and reverting Once you've reviewed the script, you apply it. `alembic upgrade head` runs every migration that hasn't run yet, in order, up to the latest: ```bash alembic upgrade head ``` ```console INFO [alembic.runtime.migration] Context impl SQLiteImpl. INFO [alembic.runtime.migration] Running upgrade -> a1b2c3d4e5f6, add books table ``` *What just happened:* "head" means the newest revision, the same way it does in Git. Alembic checked its version table, saw that revision `a1b2c3d4e5f6` hadn't been applied, ran that migration's `upgrade()`, and stamped the version table to record it. The `books` table now exists. Run `upgrade head` again and it does nothing - there's nothing newer than head, so it's safe to repeat. Made a mistake, or want to back out the last change? `downgrade -1` rewinds one step by running that migration's `downgrade()`: ```bash alembic downgrade -1 ``` *What just happened:* Alembic looked at the current revision, ran its `downgrade()` function (here, `op.drop_table("books")`), and moved the version pointer back one. This is why both functions matter: a migration is only as reversible as its `downgrade()` is accurate. `-1` means "one step back"; you can also downgrade to a specific revision id, or all the way to `base` (the very beginning). Two commands keep you oriented - where am I, and how did I get here: ```bash alembic current # which revision the database is on right now alembic history # the full ordered list of migrations ``` *What just happened:* `current` prints the revision the database's version table is stamped with - your "you are here" marker. `history` lists every migration in order, newest to oldest, so you can see the whole route and which revisions sit ahead of or behind your current position. Reach for these any time you're unsure what state an environment is in. ## The real workflow (and the gotchas that bite) πŸ’‘ Put it all together and the day-to-day loop is just four beats, repeated forever: 1. **Change your models** β†’ verify: the model classes describe the schema you want. 2. **`alembic revision --autogenerate -m "..."`** β†’ verify: a new file appears in `versions/`. 3. **Review the generated script** β†’ verify: every `op.*` line is what you actually intended (watch for the rename-as-drop trap). 4. **`alembic upgrade head`** β†’ verify: `alembic current` shows the new revision; the change is live. Then - crucially - **commit the migration file to Git alongside the model change.** The migration is source code. When a teammate pulls your branch, their `alembic upgrade head` replays your exact script against their database; staging and production do the same on deploy. Everyone applies the same changes in the same order and lands on the same schema. That's the entire payoff: no more "works on my machine, broken on yours" for the database. A handful of gotchas separate smooth Alembic users from the ones who get burned: - ⚠️ **Never edit a migration that's already been applied or shared.** Once a script has run anywhere beyond your own machine - or been pushed to the shared branch - it's history. Editing it means some environments ran the old version and some run the new, and they silently diverge. The fix for "I got that migration wrong" is always a **new** migration on top, never a retroactive edit. - ⚠️ **Data migrations are hand-written.** Autogenerate only ever touches structure (DDL). If a change needs you to *move or transform existing rows* - backfill a new column, split a field, normalize values - you write those `op.execute(...)` / bulk-update steps into `upgrade()` yourself. The differ cannot infer intent about data. - ⚠️ **Coordinate on a team to avoid two heads.** If two people each autogenerate a migration off the same parent, you end up with two "head" revisions and Alembic refuses to pick one. It's the schema equivalent of a merge conflict; resolve it with `alembic merge` (or by rebasing one revision onto the other) before deploying. πŸ’‘ **Your models define the schema; Alembic evolves it safely.** `create_all` got you to version one. From here on, every change to your `Author`, `Book`, or `Tag` tables flows through a reviewed, ordered, reversible migration - and your database stops being the scary part of shipping. ## Recap 1. **`create_all` only creates missing tables** - it never alters an existing one. Add a column to a model whose table already exists and `create_all` does nothing; your code and schema drift apart. It's the zero-to-one tool, not the one-to-many tool. 2. **Alembic is version control for your schema.** Each migration is a script with `upgrade()` and `downgrade()`; a version table tracks which have run; outstanding ones apply in order. 3. **Set up once** with `alembic init`, then point `env.py`'s `target_metadata` at your `Base.metadata` so Alembic can diff models against the live database. 4. **Autogenerate writes the migration for you** (`alembic revision --autogenerate -m "..."`) by diffing models vs. database - but always **review** it: renames look like drop+add, some type changes are missed, and data migrations aren't detected at all. 5. **Apply and revert** with `alembic upgrade head` (run all pending) and `alembic downgrade -1` (undo one); `alembic current` and `alembic history` tell you where you are and how you got there. 6. **The loop:** change models β†’ autogenerate β†’ review β†’ upgrade head; **commit migrations to Git** so every environment converges. Never edit an applied/shared migration (write a new one), hand-write data migrations, and coordinate to avoid conflicting heads. ## Quick check Lock in the ideas most likely to save you from a production scare: ```quiz [ { "q": "You added a `subtitle` column to your Book model whose table already exists, then ran Base.metadata.create_all(engine) again. What happens?", "choices": [ "create_all adds the subtitle column to the existing table", "Nothing changes - create_all only creates missing tables and never alters existing ones, so the column won't appear; you need a migration", "create_all drops and recreates the books table with the new column", "create_all raises an error because the table already exists" ], "answer": 1, "explain": "create_all is build-only: it skips tables that already exist and never alters them. The new column won't appear, and queries touching it will fail. Evolving an existing schema is exactly what Alembic migrations are for." }, { "q": "What is the safest mental model for the relationship between your models and Alembic?", "choices": [ "Alembic generates your models from the database automatically", "Your models define what the schema should be; Alembic figures out the ordered, reversible steps to get the live database there", "Alembic replaces your models entirely once migrations exist", "Models and migrations are unrelated; you maintain each by hand separately" ], "answer": 1, "explain": "Models are the destination (the schema you want); the live database is the starting point; a migration is the recorded, reversible route between them. autogenerate diffs the two to write that route." }, { "q": "Why must you ALWAYS review an autogenerated migration before applying it?", "choices": [ "Autogenerate is usually wrong about adding and dropping tables", "It has blind spots - a column rename looks like a drop-plus-add (losing data), some type changes are missed, and data migrations aren't detected at all", "Reviewing is only needed the very first time you run Alembic", "Alembic refuses to apply a migration until you manually rewrite every line" ], "answer": 1, "explain": "Autogenerate is a strong first draft, not a final answer. It reliably handles added/dropped tables and columns, but renames look like drop+add (which would delete data), certain type/constraint changes are missed, and it can't infer data migrations. Treat it as a PR to review and edit." } ] ``` --- # SQLAlchemy in the Real World & Where to Go Next Look at the ground you've covered. You started thinking of an ORM as a box that turned Python objects into rows by some unknowable trick. Now you can name every gear inside it. You understand the **engine** and the connection pool underneath it. You understand the **Session** - the unit of work, the identity map, the flush that emits SQL you never explicitly asked for. You can write a modern `select()`, map a `relationship()`, and - the big one - you can *see* the **N+1 problem** coming and reach for `selectinload` before it ever reaches production. And you know that `create_all` is a toy and Alembic is how real schemas change. Most of all, you can read the SQL. With `echo=True` on, SQLAlchemy stopped being magic and became a tool whose output you can predict and debug. That's the whole game. A data layer is no longer something that happens *to* you - it's something you reason about. This last phase isn't new mechanics. It's about where everything you learned actually lives in real codebases. ## The magic, revealed πŸ’‘ Here's the moment it clicks. If you went through [Flask From Zero](/guides/flask-from-zero), you met **Flask-SQLAlchemy** - that `db` object you imported, the one where `db.session.add()` and `User.query` somehow just worked. You now know exactly what's underneath. It *is* this. Flask-SQLAlchemy is a thin convenience layer that wires up the engine for you, hands you a Session scoped to each request, and gives the declarative base a friendlier face. Every concept - the Session, the unit of work, the mapped class - is something you've spent the last eight phases inside. And if you went through [FastAPI From Zero](/guides/fastapi-from-zero), you met **SQLModel** - SebastiΓ‘n RamΓ­rez's library where one class is somehow both a database table *and* a Pydantic model. That's not a different ORM. SQLModel is **SQLAlchemy and Pydantic fused into one declaration**: the table half is SQLAlchemy mapping (the `Mapped` columns, the relationships, the Session), the validation half is Pydantic. You learned both halves separately; SQLModel just stacks them. The point lands like this: **most database access in serious Python is SQLAlchemy** - sometimes directly, far more often wrapped. You didn't learn a niche library. You learned the engine that the popular wrappers wrap. When the generated query is slow, or the lazy-load fires at the wrong moment, or the SQL looks wrong, you're not staring at a sealed box anymore. You can open it. ## Core vs ORM, in practice Back in Phase 1 you learned that SQLAlchemy is two libraries stacked: **Core** (Pythonic SQL building) and the **ORM** (classes mapped to tables) on top of it. At the time that was a mental model. Now it becomes a habit - knowing *which layer for which job*. πŸ’‘ Here's the clear-eyed map a seasoned hand carries: - **ORM for domain objects and CRUD - the 90%.** Loading an author, saving a book with its tags, updating a row, walking relationships. This is what the ORM was built for, and it's where the overwhelming majority of your code lives. Reach for it by default. - **Core or raw SQL for bulk operations.** Updating fifty thousand rows by loading each one into the Session, mutating it, and flushing is the slow path - you're paying for identity tracking you don't need. A single Core `update()` statement, or plain SQL, does it in one trip. - **Core or raw SQL for gnarly reporting.** When you need a seven-way join, window functions, or a recursive CTE tuned to the bone, the ORM fights you. Don't fight back. Drop down and let the database do what it's great at. The quiet win is that you can make every one of these calls now. Knowing *when the ORM is the wrong tool* is itself a skill the ORM can't teach you - you earned it by understanding what the Session costs. Never being trapped in one layer is the whole reason the two-library design exists. ## Async SQLAlchemy πŸ“ One branch worth knowing exists, even if you don't need it today. Modern async frameworks like FastAPI want to talk to the database without blocking the event loop, and SQLAlchemy has a full async story for exactly that: `create_async_engine` in place of `create_engine`, an `AsyncSession` in place of `Session`, and an async driver under the hood (like `asyncpg` for PostgreSQL instead of the synchronous `psycopg`). The reassuring part: it's the *same concepts you already know*, with `await` sprinkled in. You still build a `select()`, you still work through a Session, you still dodge N+1 with eager loading. The shapes are identical; the calls are awaited. You don't need to relearn SQLAlchemy to go async - you need to learn where the `await` keywords go. (The one real gotcha: lazy loading doesn't play well with async, so eager loading via `selectinload` shifts from good-practice to near-mandatory.) So here's the full landscape of where your knowledge travels: ```mermaid flowchart TD A[SQLAlchemy core skill:
engine, Session, select, relationships] --> B[Flask-SQLAlchemy] A --> C[SQLModel] A --> D[Async: AsyncEngine + AsyncSession] A --> E[Alembic migrations] ``` Every branch is the thing you just learned, pointed at a different framework. ## What to build, and a last word Reading got you here. Building is what makes it stay. The schema from this guide - **authors, books, tags** - is a perfect sandbox because it has every relationship shape and the N+1 trap baked right in. A couple of no-nonsense projects: - **Build the model standalone.** Define authors, books, and tags with their relationships (one-to-many and many-to-many), then write a query that loads authors with their books using `selectinload` - and watch the query count drop from N+1 to two. Then add an **Alembic migration** to create the schema instead of `create_all`. That's the two things that separate a tutorial from production: fetch strategy and real migrations, practiced for real. - **Or wire it into a framework.** Drop SQLAlchemy into a [Flask](/guides/flask-from-zero) or [FastAPI](/guides/fastapi-from-zero) app, expose a couple of endpoints, and keep `echo=True` on. Watch the SQL scroll past as requests come in. This is the most satisfying exercise in the whole guide - you'll see, line by line, the thing you learned to read being written and run for you. Whichever you pick, **finish one.** A small app you actually debugged teaches more than three half-built ones. And when you want the canonical reference, bookmark the **official SQLAlchemy 2.0 documentation** - specifically the *Unified Tutorial*. It's thorough and genuinely good, and you can now read it as someone who recognizes the concepts rather than meeting them cold. You're leaving able to map classes to tables, command the Session, write modern `select()` queries, dodge the N+1 trap, choose when *not* to use the ORM at all, version your schema with Alembic, and read the SQL underneath all of it. The ORM was never magic - it's the Session and the engine you now understand. Go build the small thing. ## Recap 1. **Flask-SQLAlchemy and SQLModel *are* SQLAlchemy.** Flask-SQLAlchemy pre-wires the engine and a per-request Session; SQLModel fuses SQLAlchemy mapping with Pydantic validation. Most Python data access is SQLAlchemy, directly or wrapped - and you now see through both. 2. **Core vs ORM is a habit, not just a model.** ORM for domain objects and CRUD (the 90%); drop to Core or raw SQL for bulk operations and complex reporting. Knowing both means never being trapped. 3. **Async SQLAlchemy exists and is the same concepts awaited.** `create_async_engine`, `AsyncSession`, an async driver like `asyncpg` - for frameworks like FastAPI. Eager loading becomes near-mandatory since lazy loading doesn't suit async. 4. **Build the authors/books/tags schema for real:** relationships, a `selectinload` to dodge N+1, and an Alembic migration. Or wire SQLAlchemy into Flask or FastAPI and watch the SQL with `echo=True`. Finish one. 5. **The 2.0 docs (the Unified Tutorial) are your reference now** - and you can finally read them as someone who recognizes the gears. ## Quick check One last check - on how SQLAlchemy actually shows up in the real world: ```quiz [ { "q": "You used Flask-SQLAlchemy's db object in a Flask app. What is it actually doing under the hood?", "choices": [ "Wrapping SQLAlchemy - it pre-wires the engine and hands you a Session scoped to each request, all the machinery you learned in this guide", "Replacing SQLAlchemy with its own brand-new ORM engine built into Flask", "Talking to the database with hand-written SQL and no ORM involved", "Caching every table in memory so the database is never queried" ], "answer": 0, "explain": "Flask-SQLAlchemy is a thin convenience layer over SQLAlchemy: it configures the engine and gives you a per-request Session and a friendlier declarative base. Every concept under it - the Session, the unit of work, the mapped class - is exactly what you learned directly." }, { "q": "You need to update fifty thousand rows in one shot. What's the mature call?", "choices": [ "Use a Core update() statement or raw SQL - loading every row into the Session to mutate and flush it is the slow path", "Load all fifty thousand entities into the Session, change each one, and flush", "Avoid the update entirely because SQLAlchemy cannot run bulk statements", "Rewrite your models so the update becomes a single get_one_or_none call" ], "answer": 0, "explain": "The ORM handles the ~90% that's CRUD and domain logic. For bulk operations, loading each row pays for identity tracking you don't need - a single Core update() (or raw SQL) does it in one trip. Knowing when to drop down a layer is part of the skill." }, { "q": "What's true about async SQLAlchemy compared to what you already learned?", "choices": [ "Same concepts, awaited - create_async_engine and AsyncSession with an async driver, where eager loading matters even more because lazy loading doesn't suit async", "A completely different library with its own query language you'd start from scratch", "Faster automatically with no code changes at all", "Only usable with Flask, never with FastAPI" ], "answer": 0, "explain": "Async SQLAlchemy keeps the same shapes - select(), the Session, relationships - and adds await. You swap create_engine for create_async_engine, Session for AsyncSession, and use an async driver like asyncpg. Because lazy loading doesn't play well with async, eager loading via selectinload becomes near-mandatory." } ] ```