ORMs vs Raw SQL

Expert · 22 min read · ▶ live playground · ✦ checkpoint

ORMs are useful because they map rows to objects, generate ordinary CRUD, and give teams migration and safety rails. They are not a replacement for reading SQL: the moment performance, reporting, joins-of-consequence, or windows matter, you need to inspect the query and often write the SQL yourself.

This lesson delivers the N+1 story promised in 16.4. The bug is not that ORMs exist; the bug is letting object-shaped code hide row-by-row database work.

What An ORM Does For You

An ORM—object-relational mapper—connects application objects to database rows. Instead of hand-writing every small query, you might write:

const customer = await orm.customer.findUnique({
  where: { id: customerId },
});

The ORM turns that into SQL, binds parameters, fetches rows, and maps the result into an object. That is genuinely helpful for the boring center of an app:

  • mapping: rows become language objects, and object fields become columns
  • CRUD: create, read, update, delete helpers for ordinary single-table work
  • migrations: versioned schema-change files, though 18.4 owns production migration strategy
  • safety rails: parameterized values, typed models, relation helpers, and transaction APIs

The trade is visibility. The code looks like objects, but the database still sees SQL, indexes, joins, locks, and result sets.

The N+1 Trap

N+1 means one query to fetch a list, then one more query for each row in that list. Here is the innocent-looking shape:

const customers = await orm.customer.findMany({ orderBy: { customerId: "asc" } });
 
for (const customer of customers) {
  customer.orders = await orm.order.findMany({
    where: { customerId: customer.customerId },
    orderBy: { placedOn: "asc" },
  });
}

In object code, that loop feels natural. In database code, it means repeated SQL. Watch the same shape in a tiny café setup:

CREATE TABLE orm_customers (
  customer_id integer PRIMARY KEY,
  customer_name text NOT NULL
);
 
CREATE TABLE orm_orders (
  order_id integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES orm_customers(customer_id),
  placed_on date NOT NULL,
  total numeric(6,2) NOT NULL
);
 
INSERT INTO orm_customers (customer_id, customer_name)
VALUES
  (1, 'Ada'),
  (2, 'Ken'),
  (3, 'Priya'),
  (4, 'Tomas');
 
INSERT INTO orm_orders (order_id, customer_id, placed_on, total)
VALUES
  (101, 1, DATE '2026-06-01', 18.50),
  (102, 1, DATE '2026-06-03', 12.00),
  (103, 2, DATE '2026-06-02', 9.75),
  (104, 3, DATE '2026-06-02', 44.20),
  (105, 3, DATE '2026-06-04', 17.25),
  (106, 3, DATE '2026-06-05', 6.00);
 
SELECT count(*) AS customers,
       (SELECT count(*) FROM orm_orders) AS orders,
       count(*) + 1 AS statements_in_naive_loop
FROM orm_customers;
CREATE TABLE
CREATE TABLE
INSERT 0 4
INSERT 0 6
 customers | orders | statements_in_naive_loop
-----------+--------+--------------------------
         4 |      6 |                        5
(1 row)

Four customers means five statements in the naive pattern: one list query, then four order queries. If the list has many rows, the statement count grows with it. That is the promised "one innocent loop, a thousand queries" failure mode as a scale phrase: not a claim about this tiny table, but the shape of the bug.

The generated SQL log would look like this:

[sql] SELECT customer_id, customer_name FROM orm_customers ORDER BY customer_id;
[sql] SELECT order_id, customer_id, placed_on, total FROM orm_orders WHERE customer_id = $1 ORDER BY placed_on; -- [1]
[sql] SELECT order_id, customer_id, placed_on, total FROM orm_orders WHERE customer_id = $1 ORDER BY placed_on; -- [2]
[sql] SELECT order_id, customer_id, placed_on, total FROM orm_orders WHERE customer_id = $1 ORDER BY placed_on; -- [3]
[sql] SELECT order_id, customer_id, placed_on, total FROM orm_orders WHERE customer_id = $1 ORDER BY placed_on; -- [4]

Join Or Preload In One Set

The raw SQL rewrite says the relationship out loud:

SELECT c.customer_id,
       c.customer_name,
       o.order_id,
       o.placed_on,
       o.total
FROM orm_customers c
LEFT JOIN orm_orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.placed_on, o.order_id;
 customer_id | customer_name | order_id | placed_on  | total
-------------+---------------+----------+------------+-------
           1 | Ada           |      101 | 2026-06-01 | 18.50
           1 | Ada           |      102 | 2026-06-03 | 12.00
           2 | Ken           |      103 | 2026-06-02 |  9.75
           3 | Priya         |      104 | 2026-06-02 | 44.20
           3 | Priya         |      105 | 2026-06-04 | 17.25
           3 | Priya         |      106 | 2026-06-05 |  6.00
           4 | Tomas         |     NULL | NULL       |  NULL
(7 rows)

That is one statement. It returns one row per customer-order pair, with a NULL-filled order side for customers without orders. An ORM eager-load or preload option usually asks it to produce this shape, or a close cousin:

const customers = await orm.customer.findMany({
  include: { orders: true },
  orderBy: { customerId: "asc" },
});

Some ORMs use one join. Some use two set-based queries: all customers, then all orders where customer_id IN (...), then stitch in memory. Both are fine. The thing you are avoiding is one query per parent row.

If the app needs nested data, PostgreSQL can shape it too:

SELECT c.customer_id,
       c.customer_name,
       count(o.order_id) AS order_count,
       COALESCE(
         jsonb_agg(
           jsonb_build_object(
             'order_id', o.order_id,
             'placed_on', o.placed_on,
             'total', o.total
           )
           ORDER BY o.placed_on, o.order_id
         ) FILTER (WHERE o.order_id IS NOT NULL),
         '[]'::jsonb
       ) AS orders
FROM orm_customers c
LEFT JOIN orm_orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;
 customer_id | customer_name | order_count |                                                                                          orders
-------------+---------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           1 | Ada           |           2 | [{"total": 18.50, "order_id": 101, "placed_on": "2026-06-01"}, {"total": 12.00, "order_id": 102, "placed_on": "2026-06-03"}]
           2 | Ken           |           1 | [{"total": 9.75, "order_id": 103, "placed_on": "2026-06-02"}]
           3 | Priya         |           3 | [{"total": 44.20, "order_id": 104, "placed_on": "2026-06-02"}, {"total": 17.25, "order_id": 105, "placed_on": "2026-06-04"}, {"total": 6.00, "order_id": 106, "placed_on": "2026-06-05"}]
           4 | Tomas         |           0 | []
(4 rows)

This is the core N+1 fix: fold related rows set-wise, not row-by-row from the application.

The 80/20 Treaty

Raw SQL should not mean unsafe SQL. 18.2 still applies: use parameters, review the query, and keep it in source control. The difference is that you are choosing to write the relational shape directly instead of asking an object API to guess it.

Log It, EXPLAIN It

The habit that keeps ORMs honest is simple: log the SQL your ORM writes. During development, turn on query logging around new endpoints and look for repeated patterns:

request GET /customers
  1x SELECT ... FROM customers ...
  4x SELECT ... FROM orders WHERE customer_id = $1 ...

Then take the slow or repeated SQL and run EXPLAIN (COSTS OFF) on the database. Plans do not care whether SQL came from your hand or from an ORM:

EXPLAIN (COSTS OFF)
SELECT c.customer_id,
       c.customer_name,
       o.order_id,
       o.placed_on,
       o.total
FROM orm_customers c
LEFT JOIN orm_orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.placed_on, o.order_id;
                     QUERY PLAN
----------------------------------------------------
 Sort
   Sort Key: c.customer_id, o.placed_on, o.order_id
   ->  Hash Right Join
         Hash Cond: (o.customer_id = c.customer_id)
         ->  Seq Scan on orm_orders o
         ->  Hash
               ->  Seq Scan on orm_customers c
(7 rows)

In production tuning, EXPLAIN ANALYZE gives real row counts, but it executes the query. Keep the 16.3 discipline: read the plan structure, compare estimates to reality when safe, and change one thing at a time.

Query Builders In The Middle

A query builder constructs SQL through a structured API without mapping every table to long-lived objects. It often sits between raw strings and a full ORM:

const rows = await db
  .selectFrom("orm_customers as c")
  .leftJoin("orm_orders as o", "o.customer_id", "c.customer_id")
  .select([
    "c.customer_id",
    "c.customer_name",
    "o.order_id",
    "o.placed_on",
    "o.total",
  ])
  .orderBy("c.customer_id")
  .orderBy("o.placed_on")
  .execute();

The advantage is explicit relational shape with help for parameters, composition, and type checking. The risk is the same as always: if the builder hides the generated SQL from you, turn on logging and read it.

Try the N+1 rewrite below. The first query computes the small-table counts; the second is the set-based join you want your ORM, query builder, or raw SQL to produce.

sql — playgroundlive
⌘/Ctrl + Enter to run

ORMs are productivity tools, not database blindfolds. Next lesson turns to migrations: how schema changes move through code review and deployment without breaking live application code.

Checkpoint

Answer all three to mark this lesson complete

+50 XP on completion