Case Study 2 — When the ORM Was the Wrong Tool

ORMs are great for object CRUD and miserable for complex analytics. A team spent days wrestling a reporting query into their ORM, producing convoluted code and slow SQL — then rewrote it as plain SQL in an afternoon, clearer and faster. The lesson: the ORM is a tool, not a mandate.

Background

A team needed a monthly cohort report: for each signup month, how many customers were retained (placed an order) in each subsequent month — a classic cohort-retention matrix involving date bucketing, multiple joins, conditional aggregation, and window-style calculations. Their codebase used SQLAlchemy's ORM for everything, and there was a strong cultural norm: "we always use the ORM; raw SQL is a code smell."

So they tried to build the cohort report through the ORM.

The struggle

The query is fundamentally an analytical one — grouping, date math, conditional counts across a join of customers and orders. Expressed through the ORM's object-and-relationship model, it became a tangle:

  • They chained group_by, func.date_trunc, case, and several joins into a single massive SQLAlchemy expression that spanned dozens of lines and was nearly unreadable.
  • Relationship loading kept fighting them; they ended up bypassing the object layer anyway with func calls and column expressions — using the ORM as an awkward SQL-string-builder.
  • The generated SQL (revealed with echo=True) was worse than what they'd write by hand — extra subqueries, suboptimal joins — and slow.
  • Debugging meant mentally translating the Python expression back into SQL to figure out what it was doing.

They spent the better part of two days and still didn't have it right or fast. The ORM's strengths — mapping rows to objects, navigating relationships — were irrelevant to this query (it returns an aggregate matrix, not objects), and its abstraction was pure friction.

The fix: just write the SQL

They stepped back and wrote the report as a plain SQL query (using CTEs and conditional aggregation — Chapters 7, 11), run through the SQLAlchemy session with text():

from sqlalchemy import text

cohort_report = session.execute(text("""
    WITH cohorts AS (
        SELECT customer_id, date_trunc('month', created_at) AS cohort_month
        FROM customers
    ),
    activity AS (
        SELECT o.customer_id, date_trunc('month', o.order_date) AS active_month
        FROM orders o
    )
    SELECT c.cohort_month,
           a.active_month,
           COUNT(DISTINCT a.customer_id) AS retained
    FROM cohorts c
    JOIN activity a ON a.customer_id = c.customer_id
    GROUP BY c.cohort_month, a.active_month
    ORDER BY c.cohort_month, a.active_month;
""")).all()

This was clearer (it reads as the analytical question it is), faster (hand-written, optimizable SQL), and took an afternoon instead of two days. It still ran through the same session/connection, was parameterized where it needed user input, and lived in the data-access layer alongside the ORM code. The ORM was kept for the 90% of the app that's object CRUD; this one analytical query used the right tool.

The analysis

  1. ORMs are for objects; analytics are for SQL. An ORM excels at "load this customer and its orders as objects." A cohort-retention matrix isn't objects — it's an aggregate report. Forcing analytical SQL through an object-mapper is using a screwdriver as a hammer.

  2. "Always use the ORM" is a cultural anti-pattern. Treating raw SQL as forbidden leads to days wasted contorting the ORM and to worse generated SQL. The ORM is a convenience for the common case, not a law. SQLAlchemy itself makes dropping to SQL trivial (text(), Core) — the designers expect you to.

  3. Readability flips for complex queries. For CRUD, the ORM is more readable than SQL. For a multi-CTE aggregation, SQL is more readable than the equivalent ORM expression — it states the analytical intent directly. Use whichever expresses this query most clearly.

  4. You still need to know SQL. The team could only write the clean SQL because they understood CTEs, date_trunc, and conditional aggregation (Part II). The ORM didn't save them from learning SQL; the moment they hit a hard query, SQL knowledge was the solution. (Chapter 1's misconception, again.)

  5. Mix freely. ORM for object work, raw SQL/Core for reports — in the same session, the same data-access layer. There's no purity to maintain; there's only the right tool per query.

Discussion questions

  1. Why is a cohort-retention report a poor fit for an object-relational mapper?
  2. What went wrong when they forced it through the ORM (code clarity and generated SQL)?
  3. Why was the raw-SQL version both clearer and faster?
  4. Why is "raw SQL is a code smell, always use the ORM" a harmful norm?
  5. ⭐ Give your own rule of thumb for deciding, per query, whether to use the ORM or drop to SQL.