Why we stopped using ORMs for complex queries

ORMs are great until they're not. After hitting the ceiling on three different projects, we rebuilt our data layer around raw SQL + typed query builders.

We used Prisma on every project for two years. It was good. The DX is excellent, the type safety is genuine, and for standard CRUD - fetching a user with their associated records, paginating a list, creating an entity - it's hard to beat. Then we started hitting the ceiling.

Where Prisma stops being the right tool

The first project that pushed us was a financial analytics dashboard. The queries required: complex window functions for running totals, multiple CTEs with recursive elements, conditional aggregations across related tables, and result sets that didn't map cleanly to any Prisma model. We spent three days trying to express these queries in Prisma's API, fell back to $queryRaw for most of them, and then asked: why are we using Prisma for this at all?

The second project was a high-traffic API. Prisma's query engine adds a layer of indirection - your Node.js process communicates with the Prisma engine (a Rust binary) via a local socket, which then talks to the database. This overhead is negligible for most use cases and significant for latency-sensitive endpoints handling thousands of requests per second.

The N+1 problem at scale

Prisma handles simple relations through automatic joins. Complex nested relations with conditional includes and custom ordering generate query patterns that are hard to inspect and harder to optimise. We had a dashboard endpoint that was making 40+ database queries per request - Prisma was handling each relation fetch separately, and the auto-batching wasn't kicking in as expected. The fix required restructuring our schema relations and using Prisma's $transaction API in ways that undermined the DX benefit.

What we use now

We've settled on a split approach. For new applications:

• Drizzle ORM for standard entity CRUD and simple relations - it's lightweight, generates real SQL you can read, and has excellent TypeScript support without the Prisma engine overhead
• kysely for complex but typed query building - it expresses SQL directly in TypeScript with full type safety, so you get IDE autocomplete on your CTEs
• Raw pg (node-postgres) with tagged template literals for the 5% of queries that need full SQL control
• Database-level constraints and generated columns instead of application-level logic wherever possible

The migration process

We didn't do a big-bang migration. We identified the query patterns causing problems, extracted them to a raw SQL layer first, and migrated the rest of the data layer incrementally. Both approaches can coexist in the same application for the transition period. The most important thing we did was write a comprehensive query performance baseline before migrating - so we could actually demonstrate the improvement.

When ORMs are still the right call

For most CRUD-heavy applications, standard SaaS platforms, and teams that prioritise developer speed over query-level optimisation - an ORM is still the right choice. The DX benefits are real. The type safety is real. The migration tooling is excellent. We use Prisma on new projects where the data access patterns are straightforward. The lesson isn't 'ORMs are bad' - it's 'know when you've outgrown them.'

Frequently asked questions

When should I use an ORM versus raw SQL?

For CRUD-heavy applications with straightforward data access patterns, an ORM is usually the right call. The type safety, migration tooling, and developer experience are genuinely useful. The point where ORMs become a problem is when your queries require complex joins, window functions, recursive CTEs, or patterns that do not map cleanly to the ORM model. That is when you have either outgrown it or you are using the raw query escape hatch so often that the ORM is providing no value.

Does Prisma cause performance problems at high traffic?

For most applications, no. The overhead Prisma adds is negligible at typical SaaS traffic levels. It becomes a real consideration at thousands of requests per second on latency-sensitive endpoints, because Prisma routes queries through its own engine process before reaching the database. If you are measuring P95 and P99 latency carefully and the Prisma layer is visible there, that is when it is worth evaluating Drizzle or kysely as alternatives.

What is the N+1 query problem?

It happens when fetching a list of records triggers a separate database query for each related record, instead of joining everything in one efficient query. An endpoint that fetches 100 orders and then runs a separate query to get the customer for each one is making 101 queries instead of 1. ORMs can hide this because the queries happen automatically. Checking a query log under realistic load is the fastest way to find out whether you have N+1 problems.

What is Drizzle ORM and why would I use it over Prisma?

Drizzle is a lightweight TypeScript ORM that generates readable SQL you can inspect directly, without routing queries through a separate engine process. It has strong type safety and is simpler to deploy in serverless and edge environments. We reach for it when we want ORM-style ergonomics for standard CRUD but want more control and less overhead than Prisma provides.