ORMs Are Leaky Abstractions — How Dropping Prisma Cut Our API Latency by 80%

# ORMs Are Leaky Abstractions — How Dropping Prisma Cut Our API Latency by 80%

The dashboard wouldn't load. Not "wouldn't load fast" — wouldn't load at all.

We had 900 concurrent tenants hitting an endpoint that aggregated analytics across 14 related tables. The response time graph looked like a hockey stick: flat for months, then vertical. p95 crossed 22 seconds. AlertManager screamed. Customers tweeted.

We did what every team does first: scaled up. Doubled the RDS instance size. Added read replicas. Tuned work_mem and effective_cache_size. The dashboard went from 22 seconds to 18. Still unusable.

Then we opened the PostgreSQL query log — and what we found was the moment I stopped trusting ORMs.

The Smoking Gun: Prisma's Query Log

Here's what a single dashboard request actually executed. This was a "light" version — a user loading one project overview:

SELECT "public"."projects"."id", "public"."projects"."name", ... 
FROM "public"."projects" WHERE "public"."projects"."tenant_id" = $1;

SELECT "public"."memberships"."id", "public"."memberships"."role", ... 
FROM "public"."memberships" WHERE "public"."memberships"."project_id" = $2;
SELECT "public"."memberships"."id", "public"."memberships"."role", ... 
FROM "public"."memberships" WHERE "public"."memberships"."project_id" = $3;
SELECT "public"."memberships"."id", "public"."memberships"."role", ... 
FROM "public"."memberships" WHERE "public"."memberships"."project_id" = $4;
-- repeated 47 more times for 50 projects

SELECT "public"."users"."id", "public"."users"."email", ... 
FROM "public"."users" WHERE "public"."users"."id" = $5;
-- repeated 133 more times for each unique user

The page loaded 50 projects. Prisma issued 1 query for the project list, then N queries per relation, per row. Total: 237 queries to render one page.

Our hand-written SQL replacement executed the same page in 4 queries.

The N+1 Problem Is Not Dead

The N+1 problem has been documented since the Rails days. ORM authors know about it. Every ORM has a solution: eager loading, include, with, select_related, preload.

Prisma's solution is the include directive:

// The "fix" we tried first
const projects = await prisma.project.findMany({
  where: { tenantId },
  include: {
    memberships: {
      include: { user: true }
    },
    deployments: true,
    metrics: {
      where: { recordedAt: { gte: thirtyDaysAgo } }
    }
  }
});

This reduced the query count — but created a different problem. Prisma's include issues separate SQL queries for each relation behind the scenes. It batches them, but you still get one query per relation type per nesting level. With our schema — 4 nested include levels across 14 relations — we were still looking at 15-20 queries per request.

Worse, Prisma has no support for GROUP BY aggregations in include at all. That means every dashboard that needed "active users per project" or "error rate per deployment" required a separate aggregation step. In application code. On 900 concurrent connections.

// This. Is. Not. Possible. In. Prisma.
// You cannot do this with include:
const projects = await prisma.$queryRaw`
  SELECT 
    p.*,
    COUNT(DISTINCT m.user_id) as member_count,
    COUNT(DISTINCT d.id) FILTER (WHERE d.status = 'failed') as failed_deployments,
    AVG(m2.response_time_ms) as avg_response_time
  FROM projects p
  LEFT JOIN memberships m ON m.project_id = p.id
  LEFT JOIN deployments d ON d.project_id = p.id
  LEFT JOIN metrics m2 ON m2.project_id = p.id 
    AND m2.recorded_at >= NOW() - INTERVAL '30 days'
  WHERE p.tenant_id = $1
  GROUP BY p.id
`;

The above is one query. With Prisma, it would be 5-8 queries plus application-level aggregation. That's not a leaky abstraction — that's a sieve.

What Our Replacement Stack Looks Like

We didn't go full caveman and delete all abstractions. We built a thin, type-safe SQL layer that gives us the two things we actually needed from Prisma:

Here's the core pattern we standardized:

import { sql, createPool } from 'slonik';
import { z } from 'zod';

const pool = createPool(process.env.DATABASE_URL!);

const ProjectSchema = z.object({
  id: z.string(),
  name: z.string(),
  memberCount: z.number(),
  failedDeployments: z.coerce.number(),
  avgResponseTime: z.coerce.number(),
});

type ProjectSummary = z.infer<typeof ProjectSchema>;

async function getTenantProjects(tenantId: string): Promise<ProjectSummary[]> {
  return pool.connect(async (conn) => {
    const rows = await conn.query(sql.type(ProjectSchema)`
      SELECT 
        p.id,
        p.name,
        COUNT(DISTINCT m.user_id)::int AS "memberCount",
        COUNT(DISTINCT d.id) FILTER (WHERE d.status = 'failed')::int AS "failedDeployments",
        ROUND(AVG(m2.response_time_ms)::numeric, 1) AS "avgResponseTime"
      FROM projects p
      LEFT JOIN memberships m ON m.project_id = p.id
      LEFT JOIN deployments d ON d.project_id = p.id
      LEFT JOIN metrics m2 ON m2.project_id = p.id 
        AND m2.recorded_at >= NOW() - INTERVAL '30 days'
      WHERE p.tenant_id = ${tenantId}
      GROUP BY p.id
      ORDER BY p.name
    `);
    return rows;
  });
}

This is longer than the Prisma equivalent — no denying it. It's more code. But it's code that does exactly what it says, makes exactly one round-trip to Postgres, and produces a type-safe result without any code generation step. The Zod schema doubles as both validation and your TypeScript type definition.

Real Benchmarks: Before and After

We instrumented every endpoint with OpenTelemetry spans around database calls. Here are the numbers from production, averaged over one week before and after the migration:

The query reduction was the real win. With 48,000 queries per minute, even with PgBouncer, Postgres was doing enormous amounts of redundant work — fetching the same user rows hundreds of times. After the migration, 4,200 queries per minute doing the same business logic, because each query actually fetched what it needed in one pass.

Our RDS bill went from $1,240/month to $380/month because we could downgrade from db.r6g.2xlarge to db.r6g.large. The Prisma "abstraction tax" was $860/month in AWS compute.

The Query Composition Pattern That Made This Work

The biggest valid criticism of raw SQL is that it's hard to compose. Prisma's where builder is genuinely good for building dynamic filters. We solved this with a simple SQL fragment composition pattern:

import { sql } from 'slonik';

// Reusable filter fragments
function tenantFilter(tenantId: string) {
  return sql.fragment`p.tenant_id = ${tenantId}`;
}

function dateRangeFilter(start: Date, end: Date) {
  return sql.fragment`m2.recorded_at >= ${start.toISOString()} 
    AND m2.recorded_at <= ${end.toISOString()}`;
}

function statusFilter(statuses: string[]) {
  if (statuses.length === 0) return sql.fragment`TRUE`;
  return sql.fragment`d.status = ANY(${sql.array(statuses, 'text')})`;
}

// Compose them
async function getFilteredProjects(
  tenantId: string,
  dateRange: { start: Date; end: Date },
  deploymentStatuses: string[]
) {
  const filters = [
    tenantFilter(tenantId),
    dateRangeFilter(dateRange.start, dateRange.end),
    statusFilter(deploymentStatuses),
  ];

  return pool.any(sql.type(ProjectSchema)`
    SELECT 
      p.id, p.name,
      COUNT(DISTINCT d.id)::int as "deploymentCount"
    FROM projects p
    LEFT JOIN deployments d ON d.project_id = p.id
    LEFT JOIN metrics m2 ON m2.project_id = p.id
    WHERE ${sql.join(filters, sql.fragment` AND `)}
    GROUP BY p.id
    ORDER BY p.name
  `);
}

This is our "ORM replacement" — about 80 lines of utility code, not 80,000 lines of library. The sql.fragment type from Slonik prevents SQL injection by tagging all interpolated values, and sql.join composes fragments with a separator. It's not an ORM. It doesn't try to be. It's just typesafe SQL composition — and that's all we needed.

Migrations Without the Headache

One unexpected benefit: our migration workflow got cleaner. Prisma's declarative schema-to-SQL mapping breaks when you need anything beyond basic DDL — partial indexes, custom constraints, triggers, materialized views. You end up in prisma migrate dev --create-only purgatory, editing generated SQL files manually.

With node-pg-migrate, every migration is a TypeScript file:

// migrations/1715827200000_add-project-indices.ts
import { MigrationBuilder } from 'node-pg-migrate';

export async function up(pgm: MigrationBuilder): Promise<void> {
  pgm.createIndex('projects', 'tenant_id', { name: 'idx_projects_tenant' });
  pgm.createIndex('memberships', ['project_id', 'user_id'], {
    name: 'idx_memberships_project_user',
    unique: true,
  });
  // Prisma can't express partial indexes at all
  pgm.createIndex('deployments', 'project_id', {
    name: 'idx_deployments_active',
    where: "status NOT IN ('archived', 'deleted')",
  });
}

export async function down(pgm: MigrationBuilder): Promise<void> {
  pgm.dropIndex('projects', 'tenant_id', { name: 'idx_projects_tenant' });
  pgm.dropIndex('memberships', [], { name: 'idx_memberships_project_user' });
  pgm.dropIndex('deployments', [], { name: 'idx_deployments_active' });
}

No generated code. No prisma generate step in CI that takes 45 seconds. No try merging main and pray on every schema change. Just SQL migrations in TypeScript — and a clean down function that actually works, which Prisma still doesn't support natively.

When Prisma — and ORMs Generally — Is Still the Right Call

I'm not an absolutist. There are real scenarios where Prisma wins:

Prototypes and MVPs. When you don't know your schema yet and you're changing 12 tables every sprint, Prisma's db push and auto-generated types are genuinely faster than writing SQL by hand. The performance doesn't matter when you have 3 users. CRUD-heavy admin panels. If 90% of your endpoints are SELECT * FROM x WHERE id = $1, Prisma's generated types and autocompletion save real time. The abstraction actually fits the problem. Teams without strong SQL skills. If your team knows TypeScript deeply but SQL shallowly, Prisma's type system catches errors that raw SQL wouldn't. A TypeScript compiler error is better than a 500 in production because someone typo'd a column name.

The issue is when teams stay on Prisma past the point where their workload outgrows it — when they have analytics dashboards, complex aggregations, or 50-table domain models. That's the moment the abstraction tax goes from "annoying" to "existential."

The Migration Strategy: How We Swapped Engines Mid-Flight

You can't rewrite your entire data layer in one sprint. We did it in phases over six weeks:

Phase 1: Dual-write shadow mode (Week 1-2)

// We added a feature flag to route specific endpoints
const useRawSql = await featureFlags.isEnabled('raw-sql', user.tenantId);

if (useRawSql) {
  return getTenantProjects(tenantId);  // new implementation
}
return getTenantProjectsPrisma(tenantId); // old implementation

This let us run both implementations against real traffic and compare not just performance, but correctness. We diffed the JSON responses and found three subtle bugs in the raw SQL implementation before a single user saw them.

Phase 2: Gradual rollout (Week 3-4)

We flipped the flag from 1% of tenants to 100% over two weeks, monitoring p95 latency and error rates through DataDog. Each increment exposed a different edge case — timezone handling in date filters, null handling in aggregations, character encoding in tenant names. We fixed them in hours, not days, because the flag was instant rollback.

Phase 3: Prisma removal (Week 5-6)

Once all traffic hit the raw SQL path, we deleted Prisma from package.json, removed prisma generate from CI, and watched our node_modules shrink by 84MB. The prisma/ directory with 47 migration files was replaced by a migrations/ directory with 23 consolidated files.

The Hidden Cost Nobody Talks About: Debugging

Here's a scenario that happened to us twice: a query was slow. p95 on the deployment history endpoint was creeping up. With Prisma, debugging meant:

1. Find which Prisma calls were slow (easy — query logging)
2. Translate the Prisma findMany({ where, include, orderBy, cursor, take }) call into the actual SQL it generates
3. Realize the generated SQL has a subquery you didn't ask for because Prisma decided to translate a relation filter into a lateral join
4. Try to rewrite the Prisma call to force a different query plan
5. Realize Prisma doesn't let you control the query plan
6. Give up and write a $queryRaw escape hatch

With raw SQL, debugging is:

1. See the slow query in pg_stat_statements
2. Run EXPLAIN ANALYZE on it
3. Add an index or rewrite the query
4. Ship it

That's not a workflow preference — it's a completely different category of problem-solving. The ORM inserts a translation layer between you and the database. When performance breaks, you have to debug through that layer.

Should You Drop Your ORM?

Not tomorrow. But you should start measuring the cost.

Here's a concrete, non-destructive first step: add query logging to a single high-traffic endpoint. Check how many SQL statements execute per request. If you're seeing more than 5-10 queries for a page that should load in 200ms, your ORM is the bottleneck — not your database, not your network, not your infra. It's the translation layer.

If you're seeing numbers like we were — 200+ queries for one page — you have an ORM problem. Start planning the migration.

If you're building something new, ask yourself: do I need a full ORM, or do I need typesafe SQL? For most applications past the prototype phase, the answer is the latter. Slonik, sqlc, pgtyped, and yes, even the humble pg driver with JSDoc types, all give you database access without the abstraction tax.

The best abstraction isn't the one that hides the most — it's the one that hides the least while still being productive. SQL is already an abstraction. You don't need an abstraction on top of your abstraction.

// The only query builder we use now
// In a file called "db/queries/projects.ts"

import { sql, pool } from '../connection';
import { ProjectSummary, projectSummarySchema } from './types';

export const projectQueries = {
  getTenantProjects: (tenantId: string) =>
    pool.any(sql.type(projectSummarySchema)`
      SELECT p.id, p.name,
        COUNT(DISTINCT m.user_id)::int AS "memberCount"
      FROM projects p
      LEFT JOIN memberships m ON m.project_id = p.id
      WHERE p.tenant_id = ${tenantId}
      GROUP BY p.id
      ORDER BY p.name
    `),

  getProjectById: (id: string) =>
    pool.maybeOne(sql.type(ProjectSchema)`
      SELECT * FROM projects WHERE id = ${id}
    `),

  updateProject: (id: string, data: UpdateProjectInput) =>
    pool.one(sql.type(ProjectSchema)`
      UPDATE projects 
      SET ${sql.assignmentList(data, 'snake')}
      WHERE id = ${id}
      RETURNING *
    `),
};

Clean. Composable. Type-safe. One query per operation. No code generation. No 84MB of node_modules. No dashboard that takes 22 seconds to load.

That's not "going back to the old way" — it's realizing the old way was never wrong. We just got distracted by shiny abstractions that promised to save us from a language that was never the problem.