How does Prisma's query engine work, and what are the N+1 query problem and how to solve it with Prisma?

The N+1 Problem

// ❌ N+1 queries — 1 + N queries
const applications = await prisma.jobApplication.findMany();
// Query 1: SELECT * FROM JobApplication

for (const app of applications) {
  const rounds = await prisma.interviewRound.findMany({
    where: { applicationId: app.id }
  });
  // Queries 2-51: SELECT * FROM InterviewRound WHERE applicationId = ?
}

Fix 1: include (Eager Loading)

// ✅ 1 query with JOIN
const applications = await prisma.jobApplication.findMany({
  include: {
    interviews: true,
    offer: true,
    contacts: true,
  },
});
// Single query: SELECT ... FROM JobApplication LEFT JOIN InterviewRound ...

Fix 2: select (Precise Fields)

// ✅ Only fetch what you need
const applications = await prisma.jobApplication.findMany({
  select: {
    id: true,
    company: true,
    position: true,
    status: true,
    interviews: {
      select: {
        roundNumber: true,
        roundType: true,
        result: true,
      },
    },
  },
});

Fix 3: $queryRaw for Complex Queries

// ✅ Raw SQL for queries Prisma can't optimize
const stats = await prisma.$queryRaw`
  SELECT
    a.company,
    COUNT(DISTINCT a.id) as total_apps,
    COUNT(DISTINCT CASE WHEN a.status = 'OFFER' THEN a.id END) as offers,
    AVG(EXTRACT(EPOCH FROM (o.created_at - a.date_applied)) / 86400) as avg_days
  FROM "JobApplication" a
  LEFT JOIN "OfferDetails" o ON o.application_id = a.id
  GROUP BY a.company
  ORDER BY total_apps DESC
`;

Prisma Query Engine Architecture

TypeScript Code
    ↓
Prisma Client (generated)
    ↓
Query Engine (Rust binary)
    ↓
Database Driver (PostgreSQL/MongoDB)
    ↓
Database

Engine	How it works	Used in
Library (default)	Runs in same process	Serverless, Vercel
Binary	Separate process	Long-running servers
Data Proxy	Via Prisma's edge proxy	Edge functions

Debugging Queries

// Enable query logging
const prisma = new PrismaClient({
  log: [
    { emit: "stdout", level: "query" },  // Log all queries
    { emit: "stdout", level: "error" },
  ],
});

// Or use Prisma Studio to visualize data
// npx prisma studio

# Analyze query performance
PRISMA_QUERY_LOG=1 bun run dev
# Output: prisma:query SELECT "id", "company" FROM "JobApplication" WHERE ... (12ms)