Chapter 30 — Key Takeaways

The big idea

An ORM maps objects to rows so you work in your language, not SQL strings. It's a powerful convenience — and it hides the SQL, which makes understanding SQL more important, not less. An ORM is a convenience for people who know SQL, not a replacement for it.

What an ORM gives you

  • Object↔row mapping; a query API in Python; change tracking (modify object → ORM issues UPDATE); relationship navigation (customer.orders); automatic parameterization (injection-safe — Ch. 29).

SQLAlchemy

  • Core (a SQL builder) vs. ORM (objects), both on the same engine. Work through a Session (unit of work). Define models with mapped_column and relationship.

The N+1 problem (the signature trap)

  • Accessing a relationship in a loop with default lazy loading fires 1 + N queries (one per parent). Fast in tests (5 rows), catastrophic in production (50,000) — and invisible in the slow-query log (each query is fast; the problem is the count). (Case Study 1.)
  • Diagnose with SQL logging (echo=True) — watch the queries.
  • Fix with eager loading: selectinload (a second IN query) or joinedload (a JOIN) — load related data in a fixed few queries.
  • For aggregates, prefer a GROUP BY/SQL query over materializing all objects.

ORM vs. raw SQL

  • ORM: everyday CRUD and object navigation.
  • Raw SQL / Core: complex reports, window functions, recursive CTEs, heavy aggregations — often clearer and faster than wrestling the ORM. (Case Study 2.)
  • Mix freely (SQLAlchemy text() runs raw SQL in the same session). "Always use the ORM" is an anti-pattern.

Common mistakes

N+1 (no eager loading); not knowing the generated SQL (echo=True); forcing complex queries through the ORM; "I use an ORM so I don't need SQL" (the expensive misconception); loading huge result sets as objects (use Core/COPY, Ch. 31).

You can now…

  • ☐ Define models, query via a Session, navigate relationships.
  • ☐ Recognize, diagnose (watch the SQL), and fix N+1 with eager loading.
  • ☐ Decide ORM vs. raw SQL per query.
  • ☐ Explain why ORM use requires SQL knowledge.

Looking ahead

Chapter 31 — Bulk Data & ETL. Moving data at scale: COPY (orders of magnitude faster than row-by-row inserts), bulk load/unload, staging tables, and ETL/ELT foundations.

One sentence to carry forward: Use the ORM for object CRUD and drop to SQL for analytics — and always watch the SQL it generates, because the N+1 problem and other traps are only visible (and fixable) if you understand the queries underneath.