# How an ORM Works > Understand the pattern under every ORM - Hibernate, SQLAlchemy, GORM, EF Core - instead of memorizing one library's API: the object-relational mismatch, mapping objects to tables, the identity map and unit of work, change tracking and dirty checking, lazy loading and the N+1 trap, how a query builder becomes SQL, and when not to use an ORM at all. Language-agnostic, concept-first. --- # How an ORM Works You've probably used an ORM - Hibernate in Java, SQLAlchemy in Python, GORM in Go, Entity Framework Core in C# - or you will soon. Each has its own API, but they're all solving the *same* problem the same way, and once you understand the underlying pattern, every one of them reads as "oh, that's the same idea, named differently." That's the goal here: not a tour of one library, but the **concepts every ORM shares**, so the next ORM you meet is mostly vocabulary. The core problem is the **object-relational impedance mismatch**: your code thinks in objects with references to other objects, but a relational database thinks in rows, columns, and foreign keys. An **Object-Relational Mapper** is the layer that translates between those two worlds - turning objects into rows and back, tracking what you changed, and generating the SQL. The mental model to hold is that an ORM is doing four jobs: **mapping** (objects ↔ tables), **identity & tracking** (remembering which objects came from where and what changed), **loading** (deciding when to fetch related data), and **translating** (turning your queries into SQL). Hold those four jobs and any ORM's behavior - including its surprises - becomes predictable. > πŸ“ This is a **concept** guide, deliberately language-agnostic - code samples are short pseudocode, and > each idea links to where you've seen it concretely: [Hibernate & JPA](/guides/hibernate-and-jpa-from-zero), > [SQLAlchemy](/guides/sqlalchemy-from-zero), [GORM](/guides/gorm-from-zero), and > [EF Core](/guides/efcore-from-zero). It assumes basic **databases** - tables, keys, joins, transactions > ([What a Database Is](/guides/what-a-database-is), [Relationships & Keys](/guides/relationships-and-keys)). ## How to read this Read in order - each phase is one of the jobs an ORM does, building from the mismatch up to the trade-offs. Phases carry difficulty badges. ## The phases 1. **[What an ORM Is (the Mismatch)](01-what-an-orm-is.md)** 🟒 - objects vs rows, and the four jobs an ORM does. 2. **[Mapping Objects to Tables](02-mapping-objects-to-tables.md)** 🟑 - classes↔tables, fields↔columns, references↔foreign keys. 3. **[The Identity Map & Unit of Work](03-identity-map-and-unit-of-work.md)** πŸ”΄ - one object per row, and batching changes into one commit. 4. **[Change Tracking & Dirty Checking](04-change-tracking.md)** πŸ”΄ - how the ORM knows what to UPDATE without you telling it. 5. **[Lazy Loading & the N+1 Trap](05-lazy-loading-and-n-plus-1.md)** πŸ”΄ - when related data loads, and the query explosion every ORM can cause. 6. **[Building the Query (to SQL)](06-building-the-query.md)** 🟑 - how a query builder / object query becomes parameterized SQL. 7. **[When Not to Use an ORM](07-when-not-to-use-an-orm.md)** 🟒 - the real limits, raw SQL, and where to go next. > The throughline: an ORM **maps** objects to rows, keeps an **identity map** + **unit of work** to track > them, **loads** related data on some strategy, and **translates** your queries to SQL. Four jobs - learn > them once, recognize them everywhere. --- # What an ORM Is (the Mismatch) Here's the mental model to carry through this whole guide: an **ORM is a translator** standing between two worlds that don't speak the same language. On one side is your code, which thinks in **objects**. On the other side is a relational database, which thinks in **rows and columns**. Those two ways of seeing data don't line up, so something has to sit in the middle and translate, constantly, in both directions. That translator is the ORM - whether it's Hibernate/JPA, SQLAlchemy, GORM, or Entity Framework Core, they're all the same idea wearing different clothes. > πŸ“ This phase assumes you know what a relational database is - tables, columns, rows, foreign keys. If > any of that feels shaky, read [What a Database Is](/guides/what-a-database-is) first; everything below > leans on it. ## The mismatch: objects vs rows Your code thinks in **objects**. An object holds fields, points at *other* objects by reference, can be part of an inheritance hierarchy, and has an **identity** (this `user` in memory is *the same* user as that one over there). Objects naturally form a **graph** - a `user` that holds a list of `orders`, where each `order` holds a reference back to its `user`. A relational database thinks in none of those terms. It has **tables** with **columns**, **rows** identified by keys, **foreign keys** that link one table to another by storing a number, and it answers questions with **set-based queries** over those tables. There are no references, no inheritance, no in-memory identity - there's a `user_id` column holding the value `5`. This gap has a name: πŸ“ **The object-relational impedance mismatch.** Objects (references, inheritance, identity, graphs) and relational tables (columns, foreign keys, sets) are two different shapes for the same data. They don't naturally fit, so any time data crosses between them, *someone* has to translate. An ORM is that someone. Here's the same data in both shapes. First, how your code sees it - a graph of objects pointing at each other: ```text user(id=5, name="Ada") └─ orders ──> [ order(id=101, product="Lamp", user ──> back to user 5), order(id=102, product="Desk", user ──> back to user 5) ] ``` *What just happened:* the `user` object holds a real reference to a list of `order` objects, and each `order` holds a reference *back* to the user - a web you can walk by following pointers, `user.orders[0].product`, with no ID numbers in sight. Now the same data as the database stores it - flat tables joined by a number: ```text users orders +----+-------+ +-----+---------+----------+ | id | name | | id | product | user_id | +----+-------+ +-----+---------+----------+ | 5 | Ada | | 101 | Lamp | 5 | +----+-------+ | 102 | Desk | 5 | +-----+---------+----------+ ``` *What just happened:* the reference disappeared. The link between a user and their orders is now just the number `5` repeated in the `user_id` column. To walk from a user to their orders, you don't follow a pointer - you run a query that *matches* `users.id` against `orders.user_id`. Same data, completely different shape. That distance between the two pictures is the mismatch, and closing it is the ORM's whole reason to exist. ## What an ORM does: translate, so you stay in objects To feel why the ORM earns its keep, look at what you'd do **without** one: write the SQL by hand, then hand-copy each result row, column by column, into an object. ```text rows = db.query("SELECT id, name FROM users WHERE id = 5") row = rows[0] user = new User() user.id = row["id"] user.name = row["name"] // ...and you do this for every column, every table, every query, forever ``` *What just happened:* you did two jobs by hand - wrote raw SQL, then **mapped** the result row into a `User` object field by field. It works, but it's tedious and fragile: add a column and you edit this mapping code everywhere a user loads. With an ORM, that round trip is automated. You ask for an object and you get an object: ```text user = repo.find(5) print(user.name) // "Ada" ``` *What just happened:* the ORM generated the `SELECT`, ran it, and built the `User` object for you - the entire manual dance above collapsed into one line. You asked in objects and got an object back. That's the trade the ORM offers: it does the translating so you stay in the world your code already thinks in. ## πŸ“ The four jobs every ORM does Every ORM - no matter the language - does the same four jobs. These are the spine of this guide; each later phase takes one apart in detail. Hold them in your head and any ORM's behavior, including its surprises, becomes predictable. 1. **Mapping** - connecting objects to tables: which class is which table, which field is which column, and how a reference becomes a foreign key. *(Phase 2: [Mapping Objects to Tables](02-mapping-objects-to-tables.md).)* 2. **Identity & tracking** - keeping exactly one object per row in memory, and remembering which objects came from where and what you changed. *(Phases 3–4: [The Identity Map & Unit of Work](03-identity-map-and-unit-of-work.md) and [Change Tracking & Dirty Checking](04-change-tracking.md).)* 3. **Loading** - deciding *when* to fetch related data: the moment you load the user, or later when you first touch their orders. *(Phase 5: [Lazy Loading & the N+1 Trap](05-lazy-loading-and-n-plus-1.md).)* 4. **Translating** - turning the queries you write in object terms into real, parameterized SQL the database can run. *(Phase 6: [Building the Query](06-building-the-query.md).)* ⚠️ Different ORMs use different words for these - "session," "context," "entity manager," "tracking," "eager vs lazy." Don't let the vocabulary fool you. Underneath, every one of them is doing these four jobs. When a new ORM confuses you, ask "which of the four is this?" and the fog usually lifts. ## The trade-off, named plainly An ORM buys you convenience: you write in objects, it handles the SQL and the mapping. But it isn't free magic. It's a **layer of abstraction** sitting between you and your database - and like every abstraction, it leaks. The ORM will sometimes generate SQL you didn't expect, fetch more (or less) than you wanted, or turn one innocent line of code into a hundred queries. Understanding what it's doing underneath is exactly why this guide exists, and why the last phase is [When Not to Use an ORM](07-when-not-to-use-an-orm.md). To see these four jobs in a real library - actual code, not pseudocode - each of these is the same idea made concrete: [Hibernate & JPA](/guides/hibernate-and-jpa-from-zero) (Java), [SQLAlchemy](/guides/sqlalchemy-from-zero) (Python), [GORM](/guides/gorm-from-zero) (Go), and [EF Core](/guides/efcore-from-zero) (C#). For now, stay at the concept level - get the mismatch and the four jobs solid here, and every one of those libraries will feel like review. ## Recap 1. **An ORM is a translator** between your code's objects and a relational database's rows and columns. 2. **The reason it exists is the impedance mismatch** - objects (references, inheritance, identity, graphs) and tables (columns, foreign keys, sets) are two different shapes for the same data. 3. **Without an ORM you do two jobs by hand:** write the SQL *and* map each result row into objects. An ORM automates that whole round trip so you stay in objects. 4. **Every ORM does four jobs:** mapping, identity & tracking, loading, and translating - the spine of this guide. 5. **The trade is convenience for a leaky layer** you still have to understand; that understanding is what the rest of this guide builds. ## Quick check ```quiz [ { "q": "What is the 'object-relational impedance mismatch'?", "choices": [ "A bug where the database returns the wrong rows", "The fact that objects (references, inheritance, identity, graphs) and relational tables (columns, foreign keys, sets) are different shapes for the same data", "A slowdown caused by writing too much SQL by hand", "The difference between two database vendors' SQL dialects" ], "answer": 1, "explain": "Code thinks in objects and graphs; the database thinks in tables and foreign keys. They don't line up, so something must translate - and that something is the ORM." }, { "q": "Without an ORM, what two jobs do you have to do by hand to load data into objects?", "choices": [ "Open a connection and close a connection", "Write the SQL query and map each result row into objects field by field", "Define the table and define the index", "Validate the input and log the result" ], "answer": 1, "explain": "You write the raw SQL, then hand-copy each column of each row into your object. An ORM automates both halves of that round trip." }, { "q": "Which set is the four jobs every ORM does?", "choices": [ "Connecting, authenticating, encrypting, logging", "Indexing, caching, sharding, replicating", "Mapping, identity & tracking, loading, and translating", "Parsing, compiling, optimizing, executing" ], "answer": 2, "explain": "Mapping (objects ↔ tables), identity & tracking (one object per row + what changed), loading (when to fetch related data), and translating (queries to SQL). These four are the spine of every ORM." } ] ``` --- # Mapping Objects to Tables [Phase 1](01-what-an-orm-is.md) named the four jobs an ORM does. This phase is the first and most foundational one: **mapping**. Before an ORM can track your changes or build a query, it has to know one thing - *which object goes with which row*. Everything else builds on that answer. Here's the mental model to hold the whole way through: **mapping is a small set of correspondence rules.** Not magic, and not a lot of rules - the ORM looks at your classes and your schema and lines them up, piece by piece, following the same handful of correspondences every time. Internalize those rules and you can predict what *any* ORM will do with *any* class, and the SQL it'll generate. ## The correspondence rules Five rules cover almost everything you'll meet: - a **class ↔ a table** - `User` lines up with the `users` table. - a **field / property ↔ a column** - `user.email` lines up with the `email` column. - a **primary key field ↔ the PK column** - usually an `id` field maps to the `id` primary key. - an **object reference ↔ a foreign key** - `order.customer` (a pointer to another object) lines up with `orders.customer_id` (a foreign key column). - a **collection ↔ a one-to-many** - `customer.orders` (a list of objects) lines up with many rows in `orders` that share the same `customer_id`. And the special case: a **many-to-many ↔ a join table**. The first three are "shape of one thing" rules. The last two are the interesting ones, because that's where the [object-relational mismatch](01-what-an-orm-is.md) really bites: your code holds a *reference* (a direct pointer from one object to another), but the database has no pointers - only foreign keys, columns holding the *value* of some other row's primary key. The ORM's job is to fake the pointer using the key. ## Seeing the rules on real classes Say you have a `Customer` that owns a list of `Order` objects, and each `Order` points back to its `Customer`. Here's the mapping the ORM applies, in pseudocode: ```text class Customer: table customers: id ↔ id (PK) name ↔ name orders -> [Order] ↔ (no column - lives in orders) class Order: table orders: id ↔ id (PK) total ↔ total customer -> Customer ↔ customer_id (FK -> customers.id) ``` *What just happened:* The scalar fields (`name`, `total`) became plain columns. `Order.customer` became the `customer_id` foreign key - the one column that actually stores the relationship. `Customer.orders` has *no column at all* - it's the *reverse view* of that same foreign key. Ask for `customer.orders` and the ORM runs `SELECT * FROM orders WHERE customer_id = ?`. One relationship, one foreign key, seen from two directions. Many-to-many is the case where neither side can hold the key - a student takes many courses, a course has many students, and there's nowhere to put a single `course_id` on `students`. So the ORM introduces a third table: ```text class Student: class Course: join table enrollments: courses ──────────── students ↔ student_id (FK -> students.id) course_id (FK -> courses.id) ``` *What just happened:* The `enrollments` join table holds one row per (student, course) pairing. `student.courses` and `course.students` are *both* reverse views into that table - the ORM reads it from whichever side you asked. You typically never write a class for `enrollments`; the ORM manages it behind the collection. (If the relationship needs its own data - say, an enrollment *date* - most ORMs make you promote it to a real class; see [Relationships & Keys](/guides/relationships-and-keys).) > πŸ’‘ If a relationship ever confuses you, find the foreign key. The FK is the source of truth; the object reference and the collection are two convenient views of it. "Which table has the `_id` column?" answers "who owns this relationship?" ## Convention, then configuration How does the ORM *know* that `Customer` maps to `customers` and `Order.customer` maps to `customer_id`? Two layers, true of every ORM: 1. **Convention** - sensible defaults inferred from your names and types. Class `Customer` β†’ table `customers` (pluralized, lowercased). Field `email` β†’ column `email`. A reference named `customer` β†’ foreign key `customer_id`. You write nothing; the defaults carry you a long way. 2. **Configuration** - explicit overrides for when the defaults don't fit: a legacy table named `tbl_cust`, a column called `email_address`, a key that isn't `id`. You annotate the mapping to correct it. The *idea* is identical across the ecosystem; only the syntax differs: - **Hibernate / JPA** (Java) - annotations on the class: `@Entity`, `@Table(name=...)`, `@Column`, `@ManyToOne`, `@JoinColumn`. - **SQLAlchemy** (Python) - declarative models: a class subclasses a base, columns are class attributes, relationships use `relationship()`. - **GORM** (Go) - struct tags: `gorm:"column:email_address"` right on the struct field. - **EF Core** (C#) - conventions plus the **Fluent API** (`modelBuilder.Entity<...>().Property(...)`) or data attributes. > πŸ’‘ Reach for configuration only to *correct* a convention the ORM got wrong - not to restate one it > already got right. Re-declaring `@Column(name = "email")` on a field already named `email` is noise. > The less you configure, the more readable the mapping, and the easier it is to spot genuine deviations. ## The sharpest edge: inheritance Here's where the mismatch cuts deepest. Your classes can *inherit* - `SavingsAccount` and `CheckingAccount` both extend `Account`. SQL has no concept of inheritance: a table is a flat list of columns, there's no "this table extends that one." So the ORM has to *choose a strategy* to flatten a class hierarchy into tables, and each choice is a real tradeoff: - **Single-table** - one table for the whole hierarchy, with a **discriminator column** (e.g. `account_type`) saying which subclass each row is. Subclass-only columns are nullable for other rows. Fast queries (no joins), but a wide table full of nulls. - **Joined / table-per-subclass** - a base table (`accounts`) plus one table per subclass (`savings_accounts`, `checking_accounts`), linked by sharing the primary key. Clean and normalized, but loading a subclass means a **join** on every read. - **Table-per-class** - each concrete class gets its own standalone table with *all* its columns (inherited ones copied in). No joins for a single type, but "all accounts regardless of type" forces a `UNION` across every table. The tradeoff in one line: **single-table buys query speed with nullable clutter; joined buys normalization with extra joins; table-per-class buys per-type simplicity with painful cross-type queries.** Most teams reach for single-table unless the hierarchy is wide or the nulls become genuinely misleading. Know that the ORM is making this choice on your behalf, and that the strategy you pick shows up directly in the SQL you'll later debug. ## Mapping runs both directions > πŸ“ Mapping is **bidirectional**. On *load*, the ORM goes **row β†’ object**: it reads a row and pours the > column values into a fresh object's fields - this is called **hydration**. On *save*, it runs the > reverse - **object β†’ row** - reading your object's fields and writing them out as `INSERT` or `UPDATE` > column values. The same correspondence rules drive both directions - that's the point of having rules instead of hand-written code. Hydration is also where foreign keys get turned *back* into references: when the ORM hydrates an `Order` and sees `customer_id = 42`, it knows `order.customer` should resolve to the `Customer` with id 42 (whether it fetches that customer now or later is the *loading* job, coming in a later phase). This load/save round-trip is why the relationship modeling in [Relationships & Keys](/guides/relationships-and-keys) matters so much: the ORM can only hydrate references and collections correctly if the foreign keys it's reading are sound. Get the keys right in the schema, and the mapping rules do the rest, in both directions, every time. ## Recap - **Mapping is a small, fixed set of correspondence rules** - learn them once and you can predict any ORM's behavior and its SQL. - The core five: **class ↔ table**, **field ↔ column**, **PK field ↔ PK column**, **object reference ↔ foreign key**, **collection ↔ one-to-many** (with **many-to-many ↔ a join table**). - An object **reference** and a **collection** are two views of the *same* foreign key; the FK is the source of truth for who owns a relationship. - ORMs map by **convention** (defaults from names/types) plus **configuration** (annotations / declarative models / struct tags / Fluent API) - configure only to override what the convention got wrong. - **Inheritance** has no SQL equivalent, so the ORM picks a strategy - **single-table**, **joined**, or **table-per-class** - each trading query speed against normalization. - Mapping is **bidirectional**: **hydration** turns rows into objects on load; the reverse turns objects into rows on save. ## Quick check ```quiz [ { "q": "Your code has `order.customer` (a reference to a Customer object). What does the ORM map that to in the database?", "choices": ["A new column on the customers table", "A foreign key column like customer_id on the orders table", "A separate join table linking the two", "Nothing - references aren't stored"], "answer": 1, "explain": "An object reference maps to a foreign key. order.customer corresponds to orders.customer_id, a column holding the referenced customer's primary key." }, { "q": "Your table is named `tbl_cust` instead of the default `customers`. How do you tell the ORM?", "choices": ["You can't - you must rename the table", "Through configuration (an annotation, tag, or fluent mapping) that overrides the convention", "By renaming your class to TblCust", "The ORM auto-detects any table name"], "answer": 1, "explain": "Convention gives defaults; configuration overrides them. You'd add an annotation/tag/fluent rule to point the class at tbl_cust." }, { "q": "What is 'hydration' in an ORM?", "choices": ["Writing an object's fields out as an UPDATE", "Building a SQL query from an object", "Reading a database row and filling an object's fields with its column values", "Caching a query result for reuse"], "answer": 2, "explain": "Hydration is the row β†’ object direction of mapping: on load, the ORM pours column values into a fresh object's fields." } ] ``` --- # The Identity Map & Unit of Work Here's a thing that trips people up the first time they really look at ORM code: you load some objects, change a few fields, call `commit()` - and somehow the right SQL comes out, in the right order, all at once. No `UPDATE` written by you. No `INSERT` in the middle. Where did it come from? The answer is the **session** - the workspace every ORM gives you for one chunk of business work. Hibernate calls it a `Session`, SQLAlchemy calls it a `Session`, EF Core calls it a `DbContext`, GORM hands you a `*gorm.DB`. Different names, same thing: a short-lived scratchpad, usually living for one web request, that holds your objects while you work. > πŸ“ **Mental model:** the session is a workspace with two superpowers. First, an **identity map** - at most one in-memory object per database row, so you never end up holding two diverging copies of the same thing. Second, a **unit of work** - it watches everything you touch and writes it all out together in one commit. Hold those two ideas and the "magic" stops being magic. This is the phase where the previous two click into place. You learned to [map objects to tables](02-mapping-objects-to-tables.md); now you'll see what the session does with those mapped objects while you work. ## Superpower one: the identity map The rule is simple to state and surprisingly load-bearing: **within one session, each database row is represented by exactly one object in memory.** Load row id=5, then load it again - you get the *same instance* back, not a copy. ```text session = open_session() a = session.find(User, 5) b = session.find(User, 5) assert a is b # TRUE - same object, not just equal values ``` *What just happened:* the first `find` hit the database, built a `User` object for row 5, and recorded it in the session's identity map keyed by `(User, 5)`. The second `find` looked in that map first, found row 5 already there, and handed back the *exact same object* - `a` and `b` aren't two equal users, they're one user with two names pointing at it. That buys you two real things: - **Consistency.** Because there's only one object for row 5, there's no way to end up with two copies that disagree. If you change `a.email`, then read `b.email`, you see the new value - they're the same object. Without an identity map, you could load the same row in two places, edit one, and silently lose track of which copy is "true." - **A first-level cache.** The second `find` skipped the database entirely. Inside one session, repeated lookups of the same row are free. (This is *first-level* cache, scoped to the session - not a shared application-wide cache.) > ⚠️ **The identity map does NOT cross sessions.** It's one-object-per-row *within a single session*, full stop. Open two sessions and ask each for row 5, and you get **two different objects** - one in each session's map. They can drift apart; changing one doesn't touch the other. If you've ever been surprised that "the same row" came back as unrelated objects, this is why: you crossed a session boundary. ## Superpower two: the unit of work The identity map answers "which object is this row?" The **unit of work** answers "what did I change, and how do I save it?" While you work inside a session, it quietly keeps a to-do list. Every object you create, every object you modify, every object you delete gets tracked. You don't write SQL as you go. You mutate objects. Then, at the boundary - when you `commit()` (or `flush()`) - the session takes its whole to-do list and writes it out **together**: the right statements, in a dependency-safe order, batched, inside **one database transaction**. ```text session = open_session() alice = session.find(User, 5) bob = session.find(User, 9) alice.email = "alice@new.example" # tracked: UPDATE pending bob.is_active = false # tracked: UPDATE pending session.add(User(name="Carol")) # tracked: INSERT pending session.commit() # --- only now does SQL run, all inside ONE transaction: --- # BEGIN # UPDATE users SET email='alice@new.example' WHERE id=5 # UPDATE users SET is_active=false WHERE id=9 # INSERT INTO users (name) VALUES ('Carol') # COMMIT ``` *What just happened:* none of the three lines that changed data touched the database when you wrote them - they updated in-memory objects and added entries to the session's to-do list. The single `commit()` opened a transaction, emitted all three statements at once, and closed it. Because it's one transaction, it's **all-or-nothing**: if the `INSERT` fails, the two `UPDATE`s roll back too - nobody is left half-saved. (That guarantee is exactly atomicity from [Transactions & ACID](/guides/transactions-and-acid) - the unit of work leans directly on it.) Batching also lets the ORM order statements so foreign-key dependencies are satisfied - parent before child - instead of you hand-sequencing every write. > πŸ’‘ **This is the answer to the opening puzzle.** ORM code reads as "load objects, mutate them, commit" with no SQL in the middle *because the session is accumulating a to-do list and running it at the boundary.* The gap between your edits and the SQL isn't missing code - it's the unit of work doing its job. Once you see the session as "collect changes now, flush them at commit," the control flow stops feeling like sleight of hand. ## ⚠️ Don't let the session live too long Both superpowers depend on the session being **short-lived** - scoped to one unit of work, typically one web request. A session that hangs around is a slow-motion bug: - **It leaks memory.** The identity map holds onto every object you've loaded. A session that lives for hours, loading thousands of rows, keeps thousands of objects pinned in memory - they can't be collected because the session still references them. - **It goes stale.** The identity map keeps handing you the *cached* version of row 5 from whenever you first loaded it. If another process updated that row meanwhile, your long-lived session never notices - it's serving old data from its own map. The discipline is the same across every ORM: **open a session, do one unit of business work, commit, dispose.** One per request is the standard shape. Resist keeping a single global session "to save the overhead" - you'll trade a tiny startup cost for memory leaks and stale reads that are miserable to debug. ## Recap - The **session** (Hibernate `Session`, SQLAlchemy `Session`, EF Core `DbContext`, GORM's `*gorm.DB`) is a short-lived workspace for one unit of business work, usually one request. - The **identity map** keeps at most one in-memory object per row within a session: loading the same row twice returns the *same instance*, giving you consistency (no diverging copies) and a first-level cache (the second load can skip the DB). - ⚠️ Identity is **per session** - two different sessions each get their own object for the same row, and those objects can drift apart. - The **unit of work** tracks every new, changed, and deleted object, then writes them all out together - batched, ordered, inside one transaction - when you commit or flush. - That's why ORM code has "no SQL in the middle": the session collects changes and flushes them at the boundary, leaning on transactional atomicity ([Transactions & ACID](/guides/transactions-and-acid)) for all-or-nothing. - ⚠️ Keep sessions short - one per unit of work. Long-lived sessions leak memory (the identity map pins objects) and serve stale data. ## Quick check ```quiz [ { "q": "Inside one session, you call session.find(User, 5) twice into variables a and b. What is true?", "choices": ["a and b are equal copies but different objects", "a is b - the same object instance", "the second call always re-queries the database", "b is null because 5 is already loaded"], "answer": 1, "explain": "The identity map keeps one object per row per session, so the second find returns the very same instance and can skip the database." }, { "q": "You change two loaded objects and add a new one, then call commit(). When does the SQL run?", "choices": ["Each line runs its own SQL immediately as you write it", "All of it runs together in one transaction at commit", "Nothing runs until you call flush() separately", "The UPDATEs run immediately, the INSERT waits for commit"], "answer": 1, "explain": "The unit of work tracks the changes in memory and writes them out together, batched and in one transaction, at commit - that's why there's no SQL in the middle." }, { "q": "Two separate sessions each load row id=5. What's the relationship between the two objects?", "choices": ["They are the same object - identity is global", "They are two different objects that can drift apart", "The second session's load fails because row 5 is locked", "They share one cache so changing one changes the other"], "answer": 1, "explain": "The identity map is per session. Across different sessions there is no shared identity, so each session has its own object for row 5 and they can diverge." } ] ``` --- # Change Tracking & Dirty Checking [Phase 3](03-identity-map-and-unit-of-work.md) introduced the **unit of work**: the session batches up everything that happened during a transaction and flushes it as one coordinated set of writes at commit. That raises the question we waved past - *how does the unit of work know what to write?* You loaded a hundred objects, poked at three of them, and called `commit`. The session has to figure out that exactly those three rows need an UPDATE, and which columns. That detective work is **change tracking**, and the specific trick most ORMs use is **dirty checking**. > πŸ“ The mental model: **change tracking is how the unit of work knows what to write.** You don't tell the > ORM "this object changed" - you mutate the object, and the session *notices*. There is no explicit `save` > call on a loaded-and-modified object. That's the whole magic, and once you see how it works, the magic > stops being scary and becomes predictable. ## You mutate; the session figures out the SQL Here's the move that confuses people the first time they see it. You load an object, change a field, commit. No `update`, no `save`, nothing that screams "write to the database." ```text user = session.find(User, 5) # SELECT ... WHERE id = 5 user.email = "new@example.com" # just a plain field assignment session.commit() # an UPDATE appears, all by itself # β†’ UPDATE users SET email = ? WHERE id = 5 ``` *What just happened:* You never asked for an UPDATE. The session was *tracking* `user` from the moment `find` returned it (it lives in the identity map from Phase 3). At commit, the session looked at every object it was tracking, decided `user` had changed, and generated the SQL. This is exactly the behavior you get in **Hibernate**, **SQLAlchemy**, and **EF Core** out of the box - assignment is enough. Nobody is being clever in your code; the cleverness is in the session. ## The two ways an ORM notices There are two main mechanisms ORMs use to know an object changed. Most popular ORMs use the first. ### 1. Snapshot / dirty checking When the session loads an object, it quietly keeps a **snapshot** - a copy of the object's original column values as they came out of the database. The live object is what your code mutates; the snapshot is frozen. At flush time, the session walks every tracked object and **compares current values to the snapshot field by field**. Any field that differs makes the object "dirty," and the ORM emits an UPDATE touching *only the changed columns*. ```text # at load: session stores snapshot = { email: "old@x.com", name: "Sam", age: 30 } user.email = "new@x.com" # at flush: compare live object to snapshot # email: "new@x.com" != "old@x.com" β†’ dirty # name: "Sam" == "Sam" β†’ unchanged # age: 30 == 30 β†’ unchanged # result β†’ UPDATE users SET email = ? WHERE id = 5 (only email) ``` *What just happened:* The session didn't watch you type - it held the "before" picture and diffed it against the "after" picture at flush. Because only `email` differed, the UPDATE sets only `email`, not the whole row. This snapshot-and-diff approach is **Hibernate's** and **EF Core's** default change tracking, and how **SQLAlchemy** tracks attribute changes too. ### 2. Proxies / explicit notification Snapshotting has a cost (more on that below), so some setups instead **record changes as they happen**. The ORM wraps your object in a **proxy**, or asks your class to fire a notification on every property set (the `INotifyPropertyChanged` pattern in the .NET world). Now there's no need to diff against a snapshot at flush - the tracker already has a list of exactly which fields were touched. ```text # proxy-wrapped object: every setter is intercepted user.email = "new@x.com" # proxy records: "email was changed" # at flush: no diff needed - the change list already says email is dirty # result β†’ UPDATE users SET email = ? WHERE id = 5 ``` *What just happened:* Instead of comparing before/after at the end, the object reported each change the instant it occurred. EF Core can run in this mode with change-tracking proxies, and Hibernate offers bytecode-enhanced tracking for the same reason. The payoff is no snapshot to store and no full scan at flush; the cost is your entities have to cooperate - be proxyable or implement the notification interface. ## Inserts, updates, and deletes - all decided at flush Change tracking isn't only about edits. The session classifies *every* tracked object into one of a few states and computes the minimal set of statements when it flushes: ```text new_user = User(name="Kai") session.add(new_user) # tracked as NEW β†’ will INSERT user.email = "new@x.com" # tracked, value changed β†’ will UPDATE session.delete(old_user) # marked removed β†’ will DELETE session.commit() # the unit of work emits, in dependency order: # INSERT INTO users ... # UPDATE users SET email = ? WHERE id = 5 # DELETE FROM users WHERE id = 9 ``` *What just happened:* Three different intentions - add, mutate, remove - became three SQL statements, and the session worked out which is which. A freshly constructed object you `add`/`persist` becomes an INSERT; one you mutated becomes an UPDATE; one you `delete` becomes a DELETE. Untouched tracked objects produce no SQL at all - this is the unit of work from Phase 3 doing its job, fed by the change tracker. ## ⚠️ The detached-object trap Now the part that bites real applications. Everything above assumes the object is **tracked by the current session**. An object the session *isn't* tracking has no snapshot and no proxy hookup, so mutating it does **nothing** at commit - the session never looks at it, never diffs it, never writes it. This is the single most common ORM surprise in web apps, because web apps constantly build objects the session has never seen: ```text # A typical web handler: data = request.json # { "id": 5, "email": "new@x.com" } user = User(id=5, email=data["email"]) # brand-new object, NOT from this session session.commit() # ...nothing happens. No UPDATE. No error. ``` *What just happened:* You constructed `user` yourself from an HTTP payload. The session has no snapshot for it and isn't tracking it - to the session, this object does not exist. Commit writes nothing, and you get the maddening "I clearly changed it and the database didn't update" bug. The same happens to an object loaded in a *different* session, or one that's already closed: once it's outside a live session's tracking, it's **detached**, and edits to it are invisible. The fix is to hand the object back to a session so it starts tracking again - **re-attach or merge** it: ```text user = User(id=5, email="new@x.com") # detached, untracked managed = session.merge(user) # session now tracks a managed copy session.commit() # β†’ UPDATE users SET email = ? WHERE id = 5 ``` *What just happened:* `merge` (its name in Hibernate and SQLAlchemy; EF Core uses `Update` and `Attach`) brings the object's values into a session-tracked entity, loading the existing row if needed so it has a snapshot to diff against. Now there's something to track, so the change gets written. The rule to burn in: **a mutation only counts if a live session is tracking the object.** When you build objects outside the session - request payloads, cross-session caches, serialized data - re-attach them first. This trap is identical in spirit across **Hibernate**, **SQLAlchemy**, **EF Core**, and friends; only the method names differ. ## πŸ’‘ Tracking isn't free - and you can turn it off Dirty checking buys a lot of convenience, but it has a real cost: the session has to *hold a snapshot for every loaded object* and *scan all of them at every flush* to find what changed. Load 10,000 rows to render a report, and you've paid for 10,000 snapshots and a 10,000-object diff - for data you never intend to write back. That's why every serious ORM gives you a **no-tracking mode** for read-only work: ```text # read-only: don't snapshot, don't track, don't diff report_rows = session.query(User).no_tracking().all() # EF Core: AsNoTracking() # faster, leaner - but these objects are detached: report_rows[0].email = "x" # ⚠️ has no effect on commit (nothing is tracking them) ``` *What just happened:* You told the ORM "I'm only reading," so it skipped the snapshot and the change scan - cheaper memory, faster flush. **EF Core** spells this `AsNoTracking()`; **Hibernate** has read-only and stateless sessions; **SQLAlchemy** lets you bypass the identity-map/tracking path for similar reasons. The trade-off is the detached-object trap on purpose: reach for no-tracking only when you genuinely don't plan to save these objects. ## Recap - **Change tracking is how the unit of work knows what to write.** You mutate a loaded object and the session generates the UPDATE - there is no explicit `save` on a tracked, modified object. - Two mechanisms: **snapshot/dirty checking** (keep the original values, diff at flush, UPDATE only changed columns - Hibernate's and EF Core's default) and **proxy/notification** tracking (record each change as it happens, no diff needed). - At flush, the session classifies tracked objects: **new β†’ INSERT, changed β†’ UPDATE, removed β†’ DELETE**, and emits the minimal set of statements. - ⚠️ **Detached objects aren't tracked.** Mutating an object the current session never loaded (e.g. built from a request payload or a closed session) does nothing on commit - you must `merge`/`Update`/`Attach` it first. This is the #1 real-world ORM surprise. - πŸ’‘ Tracking costs memory and flush time. Use **no-tracking** mode (`AsNoTracking`, read-only/stateless sessions) for read-only queries - accepting that those objects become detached. ## Quick check ```quiz [ { "q": "In an ORM with default dirty checking, what makes a loaded object get an UPDATE at commit?", "choices": ["Calling session.save(object) explicitly", "Mutating one of its fields - the session diffs it against its snapshot", "Adding it with session.add()", "Nothing; loaded objects are never updated"], "answer": 1, "explain": "The session keeps a snapshot of the loaded values and compares it at flush. A differing field marks the object dirty, so an UPDATE for the changed columns is generated - no explicit save needed." }, { "q": "You build a User from an HTTP request payload, set its email, and call commit(). No UPDATE happens. Why?", "choices": ["The email value was invalid", "The object is detached - the session isn't tracking it, so it has no snapshot to diff and is ignored", "Commit only runs INSERTs, never UPDATEs", "The identity map blocked the write"], "answer": 1, "explain": "An object the current session never loaded is detached: no snapshot, no tracking. Mutating it does nothing on commit. You must merge/Update/Attach it so the session tracks it." }, { "q": "Why might you use a no-tracking mode (e.g. EF Core's AsNoTracking) for a read-only report query?", "choices": ["It makes UPDATEs faster", "It skips snapshotting and the flush-time diff, saving memory and time for data you won't write back", "It automatically attaches objects to the session", "It enables lazy loading"], "answer": 1, "explain": "Tracking costs a snapshot per object plus a scan at every flush. For read-only data you never intend to save, no-tracking skips all of that. The trade-off: those objects are detached and won't be written." } ] ``` --- # Lazy Loading & the N+1 Trap [Phase 4](04-change-tracking.md) covered how the ORM figures out *what to write* without you spelling it out. This phase is the mirror image on the read side: when you load an object that has related data - a `Customer` with `orders`, a `Post` with `comments` - *when* do those related rows actually come back? The ORM has to choose, and the choice it makes (or the one you forget to make) is behind the single most common ORM performance bug in existence. > πŸ“ The mental model: **loading is the choice of *when* related data is fetched.** Not *whether* - you'll > get the data either way - but *when*. Two strategies: **lazy** (fetch the related data the moment you first > access it) and **eager** (fetch it up front, in the same trip, when you ask for the parent). Hold that one > distinction and the whole N+1 mess becomes predictable instead of mysterious. ## Lazy loading: a placeholder until you touch it With lazy loading, related data is **not** fetched when you load the parent. Instead the ORM hands you back a **proxy** - a placeholder that looks like the collection (or the related object) but holds no rows yet. The real query fires the instant you first *access* it. ```text customer = repo.find(Customer, 5) # 1 query: SELECT * FROM customers WHERE id = 5 # customer.orders is a proxy - no orders fetched yet print(customer.orders.count) # touching it NOW triggers: # SELECT * FROM orders WHERE customer_id = 5 ``` *What just happened:* Loading the customer was one query. The `orders` collection came back as an empty-handed placeholder. Only when you reached for `customer.orders` did the ORM quietly run a second query to fill it. This is convenient - you get related data on demand, paying for exactly what you touch - and it's the default in **Hibernate** (collections are lazy unless told otherwise) and a configurable mode in **SQLAlchemy**, **GORM**, and **EF Core**. The catch: "fires when you access it" is easy to do *inside a loop* without realizing. ## Eager loading: bring it along up front With eager loading, you tell the ORM up front "I'll need the orders too," and it fetches them in the same round trip - either by a JOIN, or by a single follow-up batched query keyed on the parents it loaded. ```text customer = repo.query(Customer).with_related("orders").find(5) # β†’ SELECT ... FROM customers # LEFT JOIN orders ON orders.customer_id = customers.id # WHERE customers.id = 5 # orders are already in memory - no second query when you touch them print(customer.orders.count) # no query: already loaded ``` *What just happened:* By asking for `orders` eagerly, the ORM loaded the customer and its orders together. Accessing `customer.orders` later costs nothing - the rows are already there. Same idea, different spellings per ORM (named below). Eager loading trades "fetch on demand" for "fetch now, all at once," and that trade is exactly what saves you from the trap below. ## ⚠️ The N+1 trap Here's where lazy loading quietly turns into a disaster. You load a list of parents (1 query), then loop over them and touch a lazy relationship on each one. Every touch fires its own query. ```text customers = repo.all() # 1 query: SELECT * FROM customers for c in customers: print(c.orders.count) # lazy access β†’ 1 query PER customer # SELECT * FROM orders WHERE customer_id = ? # total = 1 (the customers) + N (one per customer) = N+1 queries ``` *What just happened:* The outer query loaded N customers. Then, because `orders` is lazy, each loop iteration fired a fresh query to load *that* customer's orders. With 1,000 customers that's **1 + 1,000 = 1,001 queries** instead of the 1–2 you actually needed. This is the **N+1 problem**, and it's universal - every ORM that supports lazy loading can produce it. The reason it's so dangerous is that it **hides**. The code reads beautifully - a clean loop over objects, no SQL in sight. With ten rows in your dev database it runs in milliseconds and every test passes. Then production has a hundred thousand rows, the page takes nine seconds, and the database is on fire - it only bites under real data volume, exactly when you can least afford it. For the full diagnosis-and-fix playbook, see [The N+1 Query Problem](/guides/n-plus-one-queries); for reading a slow endpoint's SQL, see [Why Is My Query Slow?](/guides/why-is-my-query-slow). ## The fix: eager-load the relationship The cure is to tell the ORM to fetch the related data up front, collapsing N+1 queries into one or two. The idea is identical everywhere; only the method name changes: - **Hibernate / JPA** - `JOIN FETCH` in JPQL, or `@EntityGraph` - **SQLAlchemy** - `selectinload(...)` (batched follow-up) or `joinedload(...)` (single JOIN) - **GORM** - `Preload("Orders")` - **EF Core** - `Include(c => c.Orders)` ```text customers = repo.query(Customer).with_related("orders").all() # one strategy β†’ a single JOIN: # SELECT ... FROM customers LEFT JOIN orders ON orders.customer_id = customers.id # another strategy β†’ 2 queries total (batched): # SELECT * FROM customers # SELECT * FROM orders WHERE customer_id IN (1, 2, 3, ... ) ← all at once for c in customers: print(c.orders.count) # no query - orders already loaded # total = 1 or 2 queries, regardless of how many customers ``` *What just happened:* Instead of N separate per-customer queries, the ORM either JOINed the orders in or ran **one** follow-up query with an `IN (...)` over all the customer ids. Either way the loop now fires **zero** queries - the data is already in memory. 1,001 queries became 1 or 2 - the same move whether you're writing `JOIN FETCH`, `selectinload`, `Preload`, or `Include`. ## ⚠️ But don't eager-load everything The temptation after getting burned by N+1 is to eager-load *all the things, always*. That swings you into the opposite ditch: - **Over-fetching** - you pull related data you never actually use on this page, wasting bandwidth, memory, and database work for rows that get thrown away. - **Cartesian explosion** - eager-loading *several* collections with JOINs multiplies rows. A customer with 50 orders and 50 addresses, JOINed together, returns 50 Γ— 50 = **2,500 rows** for one customer - the database materializes the cross product and the ORM has to de-duplicate it back into objects. (This is why batched strategies like `selectinload` often beat a big multi-JOIN: separate `IN (...)` queries don't multiply.) So the rule is: **choose the loading strategy per query, based on what that query will actually use.** Need the orders on this page? Eager-load orders, and *only* orders. There is no single "correct" setting - the right answer depends on each query's access pattern. > πŸ’‘ The reliable way to *know* which trap you're in is to **watch the SQL the ORM generates**. Turn on query > logging (or use a query-count assertion in tests) and count the statements per request. One innocent loop > firing 200 queries jumps out immediately once you can see them - and seeing them is the whole skill. Phase 6 > covers how those statements get built; [Why Is My Query Slow?](/guides/why-is-my-query-slow) covers reading > them. This trap is *identical in spirit* across every mainstream ORM - same disease, same cure, different vocabulary. If you want to see it concretely in the library you actually use, each of these guides walks the exact loading APIs: [Hibernate & JPA](/guides/hibernate-and-jpa-from-zero), [SQLAlchemy](/guides/sqlalchemy-from-zero), [GORM](/guides/gorm-from-zero), and [EF Core](/guides/efcore-from-zero). ## Recap - **Loading is the choice of *when* related data is fetched:** lazy (on first access) vs eager (up front, in the same request). - **Lazy loading** hands back a proxy/placeholder and runs a separate query the moment you touch the relationship - convenient, on-demand, the default in several ORMs. - ⚠️ **The N+1 trap:** load N parents (1 query), loop and touch a lazy relationship on each (N queries) = **N+1** total. It hides in clean-looking loops and only bites under real data volume. - **The fix is to eager-load** the relationship so it arrives in 1–2 queries: Hibernate `JOIN FETCH` / `@EntityGraph`, SQLAlchemy `selectinload` / `joinedload`, GORM `Preload`, EF Core `Include`. - ⚠️ **Don't eager-load everything** - it over-fetches, and JOINing multiple collections causes a cartesian explosion. Choose the strategy **per query**, and πŸ’‘ **watch the generated SQL** to confirm. ## Quick check ```quiz [ { "q": "You load 500 customers, then loop over them printing customer.orders.count, where orders is lazy. How many queries run?", "choices": ["1 query - the loop reuses the first result set", "501 queries - 1 for the customers plus 1 per customer", "500 queries - one JOIN per customer", "2 queries - the ORM always batches"], "answer": 1, "explain": "This is the N+1 problem: 1 query loads the customers, then each lazy access in the loop fires its own query for that customer's orders - 500 more. Total 1 + 500 = 501." }, { "q": "What is the standard fix for an N+1 query problem?", "choices": ["Add an index to the orders table", "Eager-load the relationship up front (JOIN FETCH / selectinload / Preload / Include) so it arrives in 1–2 queries", "Call commit() before the loop", "Disable change tracking on the customers"], "answer": 1, "explain": "Eager loading fetches the related rows up front - via a JOIN or a single batched IN(...) query - so the loop touches data already in memory and fires no further queries. Each ORM names it differently but it's the same move." }, { "q": "Why is 'just eager-load everything, always' a bad default?", "choices": ["Eager loading is always slower than lazy loading", "It over-fetches unused data, and JOINing multiple collections causes a cartesian explosion (rows multiply)", "It breaks change tracking", "It only works in Hibernate"], "answer": 1, "explain": "Eager-everything pulls data you may not use and, when several collections are JOINed, multiplies rows (50 orders Γ— 50 addresses = 2,500 rows for one parent). Choose the loading strategy per query based on what it actually needs." } ] ``` --- # Building the Query (to SQL) Here's the mental model to carry through this whole phase: **you describe a query in your language's objects, and the ORM compiles that description into parameterized SQL.** You never write `SELECT ... WHERE ... ORDER BY` yourself - you call methods, chain criteria, write a LINQ expression, or build a QuerySet. The ORM reads what you built and emits the SQL. That's the fourth job: **translating**. You've already met the other three - mapping objects to tables, keeping an identity map and unit of work, loading related data. This one is the part most people *think* the ORM is, since it's the part you touch on every read path. Look at the shape of it. You write something like this: ```text q = repo.where(status = "active") .order_by(created_at desc) .limit(10) results = q.all() ``` And the ORM turns it into roughly this: ```sql SELECT * FROM customers WHERE status = ? ORDER BY created_at DESC LIMIT 10 ``` *What just happened:* every method on your chain became a clause in the SQL. `.where(...)` became `WHERE`, `.order_by(...)` became `ORDER BY`, `.limit(10)` became `LIMIT 10`, and `"active"` became a bound parameter (`?`) instead of being pasted into the string. You described the *what*; the ORM produced the *how*. ## The clause mapping Once you see one ORM do this, you see all of them do it. The names differ, the pieces line up the same way: | You build (objects) | ORM emits (SQL) | |---|---| | `.where(...)` / filter / criteria | `WHERE` | | `.order_by(...)` | `ORDER BY` | | `.limit(n)` / `.offset(n)` | `LIMIT` / `OFFSET` | | traverse a relation in the query | `JOIN` | | pick specific fields | a narrower `SELECT` (a *projection*) | That last one is worth a name: a **projection** is when you ask for only some columns instead of the whole row - a smaller `SELECT` list and less data over the wire. The four ORMs you'll actually meet each have their own front-end for building this description, but all compile to the same kind of SQL: **Hibernate** (HQL or the Criteria API), **SQLAlchemy** (the `select()` construct), **GORM** (chained methods - `.Where(...).Order(...).Limit(...)`), **EF Core** (LINQ expressions over the entity set). Different vocabulary, identical idea: an object-shaped description in, parameterized SQL out. For the SQL clauses themselves, [Querying Basics: SELECT & WHERE](/guides/querying-basics-select-where) is the ground floor. ## Building a query runs no SQL > ⚠️ This is the one that trips people: **constructing a query does not touch the database.** Each `.where(...)` or `.order_by(...)` only adds to a *description*. The SQL fires only when you ask for results - when you enumerate the query, or call `.all()`, `.first()`, `.count()`, or iterate it in a loop. This is called **deferred** (or **lazy**) **execution**, and it's why you can compose a query in steps: ```text q = repo.all_customers() # no SQL yet - just a base query if filter_active: q = q.where(status = "active") # still no SQL - refining the description if newest_first: q = q.order_by(created_at desc) # still nothing has run page = q.limit(20).all() # NOW the SQL is built and sent, once ``` *What just happened:* the first three lines built up a query object across several `if` branches without ever hitting the database. The single `.all()` at the end is the moment of truth - that's when the ORM compiles everything you assembled into one `SELECT` and sends it. One round trip, not four. The flip side is the gotcha: if a query object looks "done" but you never enumerate it, no query ran - and if you accidentally enumerate it twice (iterate it, then call `.count()` on it), some ORMs run the SQL twice. When in doubt whether something hit the database, look at the query log. ## Parameterization is free, and it's why you're safe > πŸ’‘ Notice that `"active"` in the first example became `?` in the SQL, not `'active'` spliced into the string. That's **parameterization**, and every ORM does it automatically: your values travel to the database as **bound parameters**, separate from the SQL text. This is the single biggest security win of using an ORM. Because the value is never concatenated into the query string, there's nothing for malicious input to "break out" into - an ORM query is **injection-safe by default**. Compare: ```sql -- What a naive string-concat would build (DANGEROUS): SELECT * FROM customers WHERE status = 'active'; DROP TABLE customers; --' -- What the ORM actually sends (safe - the value is bound, not parsed as SQL): SELECT * FROM customers WHERE status = ? -- parameter 1: active'; DROP TABLE customers; -- ``` *What just happened:* in the safe version, the entire hostile string - semicolons, `DROP TABLE`, and all - arrives as the *value* of parameter 1. The database compares it against `status` as plain text; it is never parsed as SQL, so it can't do anything. You get this for free by using `.where(...)` instead of building strings yourself. The mechanics of `WHERE` and the injection trap it avoids are covered in [Querying Basics: SELECT & WHERE](/guides/querying-basics-select-where). ## The leaky abstraction (read the SQL anyway) So far this sounds like a clean wall between your objects and the SQL. It isn't, and pretending it is will eventually cost you a slow page or a baffling bug. > ⚠️ The translation is a **leaky abstraction**: the same object-query can compile to *very different* SQL depending on small choices, and some expressions can't be translated at all. Two ways it leaks: 1. **Same query, different SQL.** Whether you eager-load a relation, how you express a filter, whether you select whole entities or a projection - each can change the generated SQL from one tidy join into a pile of extra queries (the N+1 problem from [Phase 5](05-lazy-loading-and-n-plus-1.md)), or from an indexed lookup into a full scan. The object code looks innocent; the SQL tells the real story. 2. **Untranslatable expressions.** Put logic in your query that the ORM can't express in SQL - a call to one of your own language functions, an operation with no SQL equivalent - and it has two unhappy options: some ORMs (older EF, for instance) silently fall back to pulling rows into memory and filtering there (**client-side evaluation**), which can drag your whole table across the wire; others throw an error and make you rewrite it. "It compiled in my language" does not mean "it became efficient SQL." The takeaway isn't "don't trust ORMs." It's that **the ORM does not free you from understanding SQL.** On a cold path, fine - let it generate whatever. On a hot path, turn on the query log, read the SQL it produced, and judge it like you'd judge SQL you wrote by hand. When a query is mysteriously slow, [Why Is My Query Slow?](/guides/why-is-my-query-slow) is where you go next. ## Recap - The ORM's fourth job is **translating**: you describe a query in objects (method chain, criteria, LINQ, QuerySet) and it compiles that to **parameterized SQL**. - The pieces map cleanly - `.where` β†’ `WHERE`, `.order_by` β†’ `ORDER BY`, `.limit/.offset` β†’ `LIMIT/OFFSET`, relation traversal β†’ `JOIN`, picking fields β†’ a narrower `SELECT` (a projection). - **Building a query runs no SQL.** Execution is deferred until you enumerate (`.all()`, `.first()`, `.count()`, iteration) - which is exactly why you can compose a query across several steps. - **Parameterization is automatic**, so ORM queries are **injection-safe by default**: values travel as bound parameters, never spliced into the SQL text. - The translation is a **leaky abstraction**: the same object-query can produce wildly different SQL, and some expressions force client-side evaluation or an error. You still have to read the generated SQL on hot paths. ## Quick check ```quiz [ { "q": "When does an ORM actually send SQL to the database for a query you've been building with .where(...) and .order_by(...)?", "choices": ["As soon as you call .where(...)", "On each chained method call", "Only when you enumerate it - .all(), .first(), .count(), or iteration", "When the program exits"], "answer": 2, "explain": "Building a query just constructs a description (deferred/lazy execution). The SQL is compiled and sent only when you ask for results - which is why you can compose a query in steps." }, { "q": "Why are ORM queries injection-safe by default?", "choices": ["The ORM scans values for the word DROP", "Values become bound parameters, sent separately from the SQL text rather than concatenated into it", "The database refuses any query with a semicolon", "The ORM runs every query inside a transaction"], "answer": 1, "explain": "Parameterization is automatic: a value like \"active\" becomes ? in the SQL and travels as a bound parameter, so hostile input arrives as data and is never parsed as SQL." }, { "q": "What does it mean that ORM query translation is a 'leaky abstraction'?", "choices": ["The ORM leaks memory on large queries", "The same object-query can compile to very different SQL, and some expressions can't be translated - so you still must read the generated SQL", "ORMs always generate slower SQL than hand-written queries", "You can never use raw SQL once you adopt an ORM"], "answer": 1, "explain": "Small choices change the emitted SQL dramatically, and untranslatable expressions force client-side evaluation or an error. The ORM doesn't free you from understanding SQL on hot paths." } ] ``` --- # When Not to Use an ORM Six phases ago an ORM was a black box that sometimes did surprising things. Now you know the four jobs every one of them is doing: **mapping** objects to tables, keeping an **identity map + unit of work** to track them, **loading** related data on some strategy, and **translating** your queries into SQL. That's the whole machine - and the real payoff isn't that you can use an ORM, it's that you can *predict* one. Every ORM, and every ORM surprise, now decomposes into those four jobs. > πŸ“ The mental model for this finale: an ORM is **a tool, not a cage.** It's brilliant at one shape of work > and awkward at others, and knowing the difference is what separates people who fight their ORM from people > who reach past it at exactly the right moment. Every ORM lets you drop to raw SQL when you need to - that > escape hatch is a feature, not an admission of defeat. ## Where ORMs shine: CRUD over object graphs The thing an ORM is *built* for - and genuinely great at - is the everyday work of an application: load a record and its related records, change a few fields, save them back. Create, read, update, delete, over a graph of connected objects. This is most of your app code, and for it the ORM is a joy: you think in objects, the four jobs run quietly underneath, and you skip the boilerplate INSERT/UPDATE statements. ```text order = session.find(Order, 42) # load the order and (eagerly) its lines order.status = "shipped" # mutate an object order.add_line(Item("sticker", 3)) # touch the graph session.commit() # ORM works out the UPDATE + INSERT ``` That's the 80% case, and reaching for raw SQL there would be busywork. The ORM earns its keep. ## Where to reach past it The trouble starts when the work stops looking like "fetch some objects, poke them, save them." A few shapes where the object-graph model fights you: - **Complex reporting and analytics.** Heavy joins, aggregations, window functions, CTEs, ranking. The object query language gets awkward fast and the generated SQL is often suboptimal - write the SQL yourself for clearer code *and* a faster query. - **Bulk operations.** Updating or deleting millions of rows. A naive ORM loop loads every row into an object, mutates it, and writes it back - slow and memory-hungry. A single set-based `UPDATE ... WHERE` does it in one statement. (Most ORMs offer a bulk/execute escape hatch - use it instead of the loop.) - **Performance-critical hot paths.** The one query that runs ten thousand times a second, where you need to hand-tune the exact SQL and indexes. The abstraction that helps everywhere else gets in your way here. - **Database-specific features the ORM doesn't model.** Vendor extensions, exotic types, fancy locking hints. If the ORM has no vocabulary for it, don't contort the ORM - write the SQL. πŸ’‘ The decision is rarely "ORM or not" for the *whole app*. It's per-query: which shape is this piece of work? ```mermaid flowchart TD A[New query] --> B{CRUD over
object graph?} B -->|Yes| C[Use the ORM] B -->|No| D{Bulk / report /
hot path?} D -->|Yes| E[Raw SQL or
bulk escape hatch] D -->|No| F[Micro-ORM or
query builder] ``` ## The alternatives spectrum Past the full ORM, there's a whole range of tools, trading "magic" for "control": - **Raw SQL.** You write the query, the driver runs it, you read rows out by hand. Maximum control, zero mapping help. Perfect for the gnarly report. - **Query builders** (jOOQ in Java, Knex in JavaScript). Build SQL *programmatically* - type-safe, composable - but don't map rows into domain objects or track changes. SQL-shaped power with nicer ergonomics than string concatenation. - **Micro-ORMs** (Dapper in .NET, sqlx and sqlc in Go). The sweet spot for many: **you write the SQL**, and the library maps result rows onto objects for you. Fast, predictable, none of the magic from Phases 3–5, and none of its costs. πŸ’‘ Many strong teams **mix**: a full ORM for writes and ordinary CRUD, and raw SQL or a micro-ORM for heavy reads and reports. That's not hedging - it's using each tool for the shape it fits. ## A clear recap of the costs This guide has been candid about where ORMs bite, and it's worth gathering those in one place. None of them is a reason to *avoid* ORMs - they're reasons to *understand* them, which you now do: - **Hidden queries / N+1** ([Phase 5](05-lazy-loading-and-n-plus-1.md)) - lazy loading can fire a flood of small SELECTs without a single line in your code looking suspicious. - **The detached-object trap** ([Phase 4](04-change-tracking.md)) - mutate an object the session isn't tracking and the change silently vanishes at commit. - **The leaky abstraction** ([Phase 6](06-building-the-query.md)) - the ORM promises to hide SQL, but to use it well you still have to know what SQL it generates. - **A real learning curve** - sessions, flushing, fetch strategies, lifecycle states. It's genuinely a lot. Here's the reframe: every one of those is a *job you now recognize*. N+1 is the loading job misconfigured. The detached trap is the tracking job's boundary. The leak is the translating job showing through. You're not memorizing landmines anymore - you're reading a machine you understand. ## Where to go next The four concrete ORM guides will now read completely differently - where they once looked like four unrelated APIs to memorize, you'll see **the same four jobs, only configured**: [Hibernate & JPA from Zero](/guides/hibernate-and-jpa-from-zero) (Java's ORM and the JPA spec), [SQLAlchemy from Zero](/guides/sqlalchemy-from-zero) (Python's, with its explicit session), [GORM from Zero](/guides/gorm-from-zero) (Go's, lighter on magic), and [EF Core from Zero](/guides/efcore-from-zero) (.NET's, with change-tracking front and center). Whichever you use, keep one habit: **watch the SQL.** Turn on query logging, read what your ORM emits, and when a query is slow, go diagnose it - [Why Is My Query Slow?](/guides/why-is-my-query-slow) is your next stop the first time a page drags. An ORM maps objects to rows, tracks them, loads their relations, and translates your queries - four jobs, recognized everywhere, in every ORM you'll ever touch. ## Recap - ORMs are **excellent at CRUD over object graphs** - the 80% of app code that loads, mutates, and saves records and their relations. Don't write raw SQL for that; the ORM earns its keep. - **Reach past the ORM** for complex reporting/analytics, bulk operations, performance-critical hot paths, and DB-specific features the ORM can't model. The choice is usually per-query, not whole-app. - The **alternatives spectrum** runs from raw SQL (full control) through query builders (jOOQ, Knex - SQL programmatically, no mapping) to micro-ORMs (Dapper, sqlx, sqlc - you write SQL, they map rows). - πŸ’‘ Mixing is normal and smart: **full ORM for writes/CRUD, raw SQL or a micro-ORM for heavy reads.** - The real costs - N+1, the detached trap, the leaky abstraction, the learning curve - are reasons to *understand* ORMs, not avoid them. Each one maps to one of the four jobs you now know. - An ORM is a tool, not a cage: every one lets you drop to raw SQL when you need to. ## Quick check ```quiz [ { "q": "Which kind of work is an ORM genuinely the best tool for?", "choices": ["A report with heavy joins, aggregations, and window functions", "CRUD over an object graph - load records and their relations, mutate, save", "Updating ten million rows in one go", "A query that runs thousands of times a second and must be hand-tuned"], "answer": 1, "explain": "ORMs shine at create/read/update/delete over connected objects - the bulk of app code. Reporting, bulk updates, and hot paths are exactly where you reach past the ORM." }, { "q": "What best describes a micro-ORM like Dapper, sqlx, or sqlc?", "choices": ["A full ORM with identity map, lazy loading, and dirty checking", "You write the SQL; it maps result rows onto objects - no tracking or lazy-loading magic", "A tool that generates SQL programmatically but never maps rows to objects", "A database driver with no mapping at all"], "answer": 1, "explain": "Micro-ORMs sit between raw SQL and a full ORM: you author the SQL yourself, and they handle mapping rows to objects - fast and predictable, with none of the identity-map/tracking machinery." }, { "q": "You need to update millions of rows. Why is a naive ORM loop the wrong approach?", "choices": ["ORMs can't run UPDATE statements", "It loads every row into an object to mutate it - slow and memory-heavy; a set-based UPDATE ... WHERE is one statement", "The identity map forbids bulk writes", "Lazy loading would re-fetch each row twice"], "answer": 1, "explain": "A naive loop materializes each row as an object, mutates it, and writes it back. A single set-based UPDATE ... WHERE does the whole thing in one statement - use the ORM's bulk/execute escape hatch instead." } ] ```