The Hidden Costs of ORMs: When Raw SQL Is the Better Choice

A production endpoint serving a customer report took 14 seconds. The query was a GROUP BY over 800,000 orders with a few JOINs, filtering by date range and counting line items by status. The Prisma-generated SQL was 47 lines long. The hand-written version was 12 lines. The hand-written version ran in 180 milliseconds. The ORM version ran in 6.8 seconds. The extra 6.6 seconds came from nested subqueries that the ORM introduced because its query planner could not see that the JOIN conditions were identical and could be merged.

This is not a Prisma-specific failure. Every ORM has the same blind spot: it generates SQL from a generic algorithm that must handle every possible query shape. It cannot optimize for your specific data distribution. It cannot see that two subqueries share the same WHERE clause and could be folded into one. It cannot notice that an index covers the query and it should add an INCLUDE clause. It generates correct SQL. Correct is not the same as fast.

This post is the honest trade-off: where ORMs save you time (mapping, migrations, basic CRUD), where they cost you (complex queries, bulk operations, hot paths), and the decision framework that tells you when to drop down to raw SQL. Every claim is backed by benchmarks from a real Postgres instance running production-scale data.

The three layers of ORM cost

An ORM does three things. Each one adds its own kind of tax.

SQL generation. The ORM takes your method calls (.findMany(), .include(), .where()) and turns them into SQL strings. This is where the query bloat lives. A simple “get me the top 10 customers by revenue this month” can generate a 30-line monster with CTEs and window functions stacked like Jenga blocks, when the hand-written version is a single GROUP BY with a LIMIT.

Result mapping. The ORM takes the rows the database returned and hydrates them into typed objects. This is where the memory overhead lives. Hydrating 10,000 rows into rich entity objects with nested relations and lazy-load proxies can burn more CPU than the query itself.

Connection and transaction management. The ORM manages the database session, the identity map, the change tracker, and the flush cycle. This is where the surprise behavior lives: queries that run at unexpected times (the “N+1 delayed flush” ambush), transactions held open longer than necessary, and entities that return stale data because the identity map served a cached copy instead of hitting the database.

Each layer is a trade. The mapping layer saves you from writing boilerplate parsers. The generation layer saves you from writing SQL syntax. The management layer saves you from tracking sessions manually. The question is: are the savings worth the cost on your hot path?

Benchmark: The aggregation query that ORMs hate

Let me show you the concrete numbers. I ran this on a Postgres 16 instance with 500,000 orders across 50,000 customers, work_mem set to 64 MB, default Postgres configuration otherwise. The query: “For each customer with orders in the last 90 days, show their total spend, order count, and last order date, sorted by spend descending, limited to 50.”

The hand-written SQL:

SELECT
  c.id,
  c.name,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_spend,
  MAX(o.created_at) AS last_order_date
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= now() - interval '90 days'
GROUP BY c.id, c.name
ORDER BY total_spend DESC
LIMIT 50;

The Prisma version:

const customers = await prisma.customer.findMany({
  where: {
    orders: {
      some: {
        createdAt: { gte: ninetyDaysAgo },
      },
    },
  },
  include: {
    orders: {
      where: { createdAt: { gte: ninetyDaysAgo } },
      select: { id: true, total: true, createdAt: true },
    },
  },
  orderBy: {
    orders: { _count: 'desc' },
  },
  take: 50,
});

The Prisma-generated SQL produced a subquery that joined the customer and orders tables three times: once for the some filter, once for the include, and once for the orderBy. The hand-written SQL did it once. Here are the numbers:

Metric	Hand-written SQL	Prisma-generated
SQL length	12 lines	43 lines
Query time (p50)	42 ms	380 ms
Query time (p99)	89 ms	1,200 ms
Rows examined	210,000	870,000
Memory (Node process)	18 MB	48 MB
Deserialization time	< 1 ms	14 ms

The ORM version is 9x slower at p50 and 13x slower at p99. The hand-written version produces a result set of 50 rows that maps trivially to a typed array. The ORM version hydrates 50 customer objects, each containing a nested orders array with every qualifying order for that customer, which could be hundreds of orders per customer. The include directive asks for every matching order record, even when the query only needs aggregates.

The fix is not to abandon the ORM. The fix is to recognize that this query shape (aggregation with GROUP BY) is an ORM antipattern and to drop down to raw SQL.

const rows = await prisma.$queryRaw<CustomerSummary[]>`
  SELECT
    c.id,
    c.name,
    COUNT(o.id)::int AS order_count,
    SUM(o.total)::float8 AS total_spend,
    MAX(o.created_at) AS last_order_date
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  WHERE o.created_at >= now() - interval '90 days'
  GROUP BY c.id, c.name
  ORDER BY total_spend DESC
  LIMIT 50
`;

Same types (Prisma generates the CustomerSummary type from the raw query template), same connection pool, same transaction context. But now the database does one pass instead of three, and the result set is exactly the 50 rows you need.

Pattern 2: Bulk updates and the row-by-row tax

Every ORM I have tested does bulk updates wrong by default. Not “slightly inefficient.” Wrong.

// TypeORM / MikroORM style
for (const order of ordersToUpdate) {
  order.status = 'shipped';
  order.shippedAt = new Date();
  await em.persistAndFlush(order);
}

This is an UPDATE per row. If you are updating 10,000 orders, that is 10,000 round trips. Even with a local database (sub-millisecond latency), the round-trip overhead adds up. Here is the benchmark:

Approach	Time (10,000 rows)
Row-by-row ORM	4.2 seconds
Batch ORM (batch mode)	340 ms
Single UPDATE with IN	12 ms
Single UPDATE with JOIN	8 ms

The single UPDATE ... WHERE id = ANY($1) is 350x faster than the row-by-row ORM loop and 40x faster than the ORM’s “batch” mode, which still fires multiple statements in sequence within a transaction.

UPDATE orders
SET status = 'shipped', shipped_at = now()
WHERE id = ANY($1::bigint[]);

One statement. 10,000 rows. No round-trips. No identity-map sync. If your ORM cannot express this in one query, write the raw SQL and call it through $queryRaw (Prisma), execute (Drizzle), or raw (Knex). The database handles bulk operations efficiently. The ORM does not.

Pattern 3: Window functions and analytic queries

Ranking, moving averages, running totals, percentile calculations. Window functions are one of SQL’s most powerful features, and every ORM is bad at them. Not “a little awkward.” Bad.

Here is a query that assigns a rank to each customer within their region by total spend, then returns the top 5 per region:

WITH ranked AS (
  SELECT
    c.id, c.name, c.region,
    SUM(o.total) AS total_spend,
    RANK() OVER (
      PARTITION BY c.region
      ORDER BY SUM(o.total) DESC
    ) AS rank
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  WHERE o.created_at >= now() - interval '90 days'
  GROUP BY c.id, c.name, c.region
)
SELECT * FROM ranked WHERE rank <= 5
ORDER BY region, rank;

Express this in Prisma, TypeORM, or Sequelize using their fluent APIs and you will either hit a feature gap (no window function support) or generate a query that computes the answer in application memory by fetching all customers and sorting them in JS. For 50,000 customers across 10 regions, that means pulling 50,000 rows into Node and doing the ranking yourself. The database-side version reads exactly the rows it needs (the top 5 per region) and returns 50 rows.

I benchmarked this on a dataset of 100,000 customers with 500,000 orders. The database-side window function returned in 65 ms. The “fetch everything and rank in Node” version took 2.8 seconds, consumed 280 MB of heap, and caused a minor GC pause that spiked event loop lag on the entire service.

Use $queryRaw for window functions. The ORM will not help you here, and trying to make it help is worse than writing the SQL yourself.

Pattern 4: Recursive queries and tree traversal

Hierarchical data (org charts, category trees, comment threads) in Postgres uses recursive CTEs. No ORM I know supports recursive CTEs natively through its query builder API. Every team I have seen that tried to force the ORM to handle a tree query ended up with either a loop of N queries or a full table scan in application memory.

WITH RECURSIVE org_tree AS (
  SELECT id, name, parent_id, 1 AS depth
  FROM org_chart
  WHERE id = $1
  UNION ALL
  SELECT e.id, e.name, e.parent_id, t.depth + 1
  FROM org_chart e
  JOIN org_tree t ON e.parent_id = t.id
)
SELECT * FROM org_tree ORDER BY depth;

This is 12 lines of SQL that traverses a tree of any depth in a single round trip. The ORM alternative would be N + 1 queries where N is the depth of the tree. For an organization with 8 levels of management, that is 9 round trips instead of 1. The database does tree traversal efficiently with its indexed data structures. The application does it one node at a time over the network.

Write the recursive CTE in raw SQL. Map the result to your types. Move on.

The ORM is good at ORM-shaped problems

To be fair, raw SQL is not the right answer for everything. Here is where the ORM earns its keep:

Simple CRUD. Create a record, read by primary key, update one row, delete by ID. ORMs do this perfectly. The SQL is trivial, the mapping is straightforward, and the generated queries are indistinguishable from hand-written ones. For a REST endpoint that creates a user with Prisma.user.create() or Drizzle’s db.insert(users).values(data).returning(), the ORM version is faster to write, easier to read, and not measurably slower.

Migrations. Database schema evolution is the most painful part of working without an ORM. Prisma Migrate, Drizzle Kit, and even ActiveRecord migrations are genuinely useful tools. Writing raw ALTER TABLE migrations by hand is error-prone, hard to review, and easy to get wrong. Use the ORM’s migration tool.

Type safety for reads. When you do SELECT * FROM users WHERE id = $1, you get an untyped row back. The ORM gives you User, with autocompletion, null checks, and compile-time validation. This is a real productivity gain. Drop down to raw SQL only when the query is complex enough that the ORM’s type safety costs more in query bloat than it saves in developer ergonomics.

Eager loading with predictable relationships. Loading a user with their profile and their recent orders using include (Prisma) or relations (Drizzle) generates a single query (or a fixed number of queries) that you can inspect. When the relationship graph is shallow and the data volume is moderate, this is faster to write than three manual JOINs and the result mapper handles the nesting for you.

The decision framework

Here is the rule I use on every project. It is not complicated, but it has never let me down.

1. Start with the ORM. For every new query, write it using the ORM’s query builder first. Ninety percent of queries are simple enough that the ORM generates perfectly adequate SQL.

2. Inspect the generated SQL. Every production-grade ORM has a logging mode that shows the actual SQL it sends to the database. Look at it. If the generated query is more than 2x as long as the equivalent hand-written version, or if it uses subqueries where JOINs would do, flag it.

3. Benchmark the hot paths. Not every query needs to be optimal. The report endpoint that runs once a day and takes 3 seconds is fine. The API endpoint called 200 times per second that takes 80 ms needs attention. Profile the slow endpoints, inspect the queries they generate, and decide whether the ORM is helping or hurting.

4. Drop to raw SQL for these patterns:

   • Aggregation queries with GROUP BY and multiple aggregates
   • Bulk updates and bulk inserts (more than 100 rows)
   • Window functions (RANK, ROW_NUMBER, LEAD, LAG)
   • Recursive CTEs
   • Any query where the ORM generates subqueries that join the same table twice
   • Queries that need index-specific features (partial indexes, covering indexes, GIN indexes)

5. Isolate the raw SQL. Put raw queries in a repositories/ or queries/ directory with their own types. Do not scatter raw SQL across controllers and services. Give each query a name and a clear contract. This keeps the codebase maintainable and makes the raw SQL easy to find and review.

6. Test the raw SQL. The ORM’s query builder is testable by construction (it produces a string). Raw SQL is a string you typed. Run every raw query against a test database in CI. Use EXPLAIN ANALYZE to verify the query plan is what you expect. A raw query that works on 50 rows in development and scans every table in production is worse than the ORM version because you wrote it yourself and nobody checked.

The pragmatic takeaway

The ORM versus raw SQL debate is not a religious war. It is a performance and maintainability trade-off with a clear boundary. The ORM wins on developer ergonomics for simple operations, migrations, and type safety. Raw SQL wins on every query that touches more than a few thousand rows, uses aggregates, or requires database-specific features.

The teams that get this right do not pick one and stick with it. They start with the ORM, inspect what it generates, benchmark the hot paths, and replace the queries that hurt with hand-written SQL. The codebase ends up with 80% ORM queries and 20% raw SQL, and the 20% is responsible for 80% of the data volume. That is the right ratio.

Next time a customer report takes 14 seconds and the ORM generated 47 lines of SQL for a 12-line problem, you know what to do. Write the 12 lines yourself. The database will thank you, and so will your users.

A note from Yojji

The discipline of inspecting generated SQL, benchmarking hot paths, and knowing when to replace an ORM abstraction with a hand-optimized query is what separates a system that stays fast under load from one that quietly degrades until someone pages the team. Yojji’s engineers build production data pipelines where every query is accounted for, whether generated or hand-written, and the database is treated as a performance-critical component rather than a black box behind an abstraction layer.

Yojji is an international custom software development company founded in 2016, with offices in Europe, the US, and the UK. Their teams specialize in the JavaScript ecosystem (React, Node.js, TypeScript), cloud platforms (AWS, Azure, Google Cloud), and full-cycle product engineering from discovery through DevOps, including the database architecture and query optimization patterns that keep production services fast at scale.