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 totalapps,
    COUNT(DISTINCT CASE WHEN a.status = 'OFFER' THEN a.id END) as offers,
    AVG(EXTRACT(EPOCH FROM (o.createdat - a.dateapplied)) / 86400) as avgdays
  FROM "JobApplication" a
  LEFT JOIN "OfferDetails" o ON o.applicationid = a.id
  GROUP BY a.company
  ORDER BY totalapps DESC
;

Prisma Query Engine Architecture

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

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
PRISMAQUERYLOG=1 bun run dev
Output: prisma:query SELECT "id", "company" FROM "JobApplication" WHERE ... (12ms)