taming N+1 queries in Next.js edge routes with Prisma

a common pain point in server‑side rendering with Prisma is the hidden N+1 query explosion when fetching relational data for each page request. the latency shows up as a slow first paint and spikes your database load, especially on edge‑runtime routes that lack connection pooling.

the bottleneck

when you run a findMany with include on a one‑to‑many relation, Prisma emits a separate query per parent row. on a page that lists 50 posts with their author records, you end up with 51 queries – one for the posts and 50 for the authors. on edge functions this can exceed the 10‑second timeout and cause throttling.

batching with groupBy and Promise.all

instead of letting Prisma resolve each relation, pull the related ids first, batch‑load the children, and stitch the results in memory. this keeps the edge runtime within its connection limits and reduces round‑trips.

// pages/api/edge/posts.ts (edge runtime)
import { createEdgeRouter } from 'next-edge-router';
import { prisma } from '@/lib/prisma';

export const GET = createEdgeRouter(async (req) => {
  // 1️⃣ fetch posts and author ids in one query
  const posts = await prisma.post.findMany({
    select: { id: true, title: true, authorId: true },
    take: 50,
  });

  // 2️⃣ batch‑load authors
  const authorIds = [...new Set(posts.map(p => p.authorId))];
  const authors = await prisma.user.findMany({
    where: { id: { in: authorIds } },
    select: { id: true, name: true },
  });
  const authorMap = Object.fromEntries(authors.map(a => [a.id, a]));

  // 3️⃣ attach authors without extra queries
  const enriched = posts.map(p => ({
    ...p,
    author: authorMap[p.authorId] ?? null,
  }));

  return new Response(JSON.stringify(enriched), { status: 200 });
});

trade‑offs & edge cases

• connection limits – edge runtimes spin up a fresh DB connection per invocation. keep the pool tiny (poolSize: 1) and reuse the same Prisma client across calls via a module‑level singleton.
• stale data – the two‑step fetch can race with concurrent writes. if strict consistency is required, wrap the reads in a transaction (prisma.$transaction([...])) but remember that edge runtimes don’t support long‑running transactions well.
• complex nesting – for deeper graphs (post → comments → likes) the manual stitching becomes cumbersome. at that point consider GraphQL with dataloader or Prisma’s executeRaw to craft a single join.
• cold starts – the extra JavaScript to map ids adds a few milliseconds, but it’s usually dwarfed by the saved DB round‑trips. benchmark with fetch in the Vercel Edge Playground to confirm.

production checklist

1. enable log: ['query'] in Prisma dev to spot hidden N+1 patterns.
2. set connection_limit in your Supabase/Postgres plan to match edge concurrency.
3. add a health‑check endpoint that runs the batch query and alerts on latency spikes.

the pattern works well for read‑heavy pages, but if you need real‑time updates you’ll end up pulling the same data on every request. how do you balance edge‑runtime constraints with live data freshness in your stack?

Comments

[Jessica Williams]

The batch-then-stitch pattern is a clear way to keep round trips down on the edge. One thing worth double-checking is the premise: as far as I know, Prisma resolves an include on a one-to-many relation with batched queries rather than one query per parent row, so the log: ['query'] setting from your checklist is probably the best first step to confirm that an N+1 really appears in your case. The N+1 usually shows up when relations are fetched inside a loop, and a join-based relation load strategy may also be worth comparing if your Prisma version supports it.