We love Prisma. We use it for every new project. But at 50k requests per minute on our payment platform, we had to face a hard truth: Prisma's query generation was adding 40-80ms per query in ways we couldn't control.
The Problem with ORM Query Generation
ORMs generate SQL dynamically. For simple CRUD operations this is fine. For analytics queries, reporting dashboards, and complex joins, the generated SQL is often significantly worse than what a human would write.
// Prisma ORM — what you write
const orders = await prisma.order.findMany({
where: { userId, status: "completed" },
include: {
items: { include: { product: true } },
payments: true,
},
orderBy: { createdAt: "desc" },
take: 20,
});
// What Prisma generates (simplified) — 3 separate queries!
// SELECT * FROM orders WHERE ...
// SELECT * FROM order_items WHERE order_id IN (...)
// SELECT * FROM products WHERE id IN (...)
// SELECT * FROM payments WHERE order_id IN (...)Prisma's N+1 handling uses batched queries, not JOINs. For 20 orders with 10 items each, this is 4 queries vs 1 optimized JOIN.
The Pattern We Switched To
For our high-traffic paths (checkout, dashboard, reporting), we moved to raw SQL with a thin type-safe wrapper. Prisma stays for writes and simple reads.
import { db } from "@/lib/db"; // pg or postgres.js
// Type-safe raw query with template literals
async function getOrderSummary(userId: string, limit = 20) {
const rows = await db<OrderRow[]>`
SELECT
o.id,
o.total,
o.status,
o.created_at,
COUNT(oi.id) AS item_count,
ARRAY_AGG(p.name ORDER BY p.name) AS product_names
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN products p ON p.id = oi.product_id
WHERE o.user_id = ${userId}
AND o.status = 'completed'
GROUP BY o.id, o.total, o.status, o.created_at
ORDER BY o.created_at DESC
LIMIT ${limit}
`;
return rows;
}Performance Results
- Dashboard query: 340ms → 45ms (87% reduction)
- Order list endpoint: p99 dropped from 890ms to 120ms
- Database CPU utilization: 78% → 34%
- Able to remove one RDS read replica
When to Use Each
- Prisma: User auth, CRUD operations, admin panel, anything that touches < 3 tables
- Raw SQL: Analytics, reporting, anything with aggregations or complex JOINs
- Raw SQL: Any query touching > 1M rows
- Raw SQL: Any endpoint in your p95 > 200ms monitoring
Don't abandon ORMs entirely. Use them for what they're great at — schema migrations, simple CRUD, and type safety on writes. Reserve raw SQL for performance-critical reads.