Why We Ban ORM Abstractions for Heavy Aggregation Queries

The Problem with Entity Graphs

When engineering web applications, ORMs like Prisma or Hibernate promise that you never have to think in SQL. For simple mutations—fetching a user profile, updating an invoice status—this abstraction is useful.

However, when your client asks for an operational dashboard summarizing ledger reconciliations across 100,000 orders grouped by merchant and payment gateway, object relational mappers default to fetching monstrous entity graphs into Node memory.

The Hidden N+1 in Analytical Dashboards

Rather than letting PostgreSQL's query optimizer aggregate rows on disk using indexed joins, careless ORM methods fire dozens of sequential round trips or allocate hundreds of megabytes in the V8 heap.

Controlled Raw SQL with Drizzle sql template

In DEVRun projects, we keep type safety without sacrificing engine power. Using Drizzle's typed sql template tag, our queries compile to single-roundtrip Common Table Expressions (CTEs):

WITH daily_totals AS (
  SELECT 
    date_trunc('day', created_at) AS day,
    gateway_id,
    SUM(amount) AS gross_volume,
    COUNT(id) AS transaction_count
  FROM transactions
  WHERE status = 'settled'
  GROUP BY 1, 2
)
SELECT * FROM daily_totals ORDER BY day DESC;

Our Studio Rules for Queries

Rule 1: Simple CRUD uses typed Drizzle schema builders. Rule 2: Multi-table joins and aggregation live in audited SQL files or typed CTEs. Rule 3: Always verify with EXPLAIN (ANALYZE, BUFFERS).