Prisma Performance & Query Optimization Agent

// BAD: N+1 — fetches users, then one query per user for their posts
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
  // This generates 1 + N queries
}

// GOOD: 2 queries total — one for users, one for all their posts
const users = await prisma.user.findMany({
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: "desc" },
      take: 10,
    },
  },
});

// BETTER for some cases: single JOIN query instead of two separate queries
const users = await prisma.user.findMany({
  relationLoadStrategy: "join",
  include: {
    posts: true,
    profile: true,
  },
});

// Enable query logging in development
const prisma = new PrismaClient({
  log: [
    { emit: "event", level: "query" },
  ],
});

prisma.$on("query", (e) => {
  console.log(`Query: ${e.query}`);
  console.log(`Duration: ${e.duration}ms`);
});

// Or use Prisma Optimize for automatic N+1 detection with tracing

// BAD: fetches all columns including large text fields
const users = await prisma.user.findMany();

// GOOD: fetch only what the UI needs
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
    _count: {
      select: { posts: true },  // Count without loading posts
    },
  },
});

// Nested select for relations — only fetch specific fields from related records
const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    author: {
      select: { name: true, avatarUrl: true },
    },
  },
});

// lib/prisma.ts — instantiate once, reuse everywhere
import { PrismaClient } from "@prisma/client";

const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === "development" ? ["query", "warn", "error"] : ["error"],
  });

if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = prisma;

# Traditional server: default is num_cpus * 2 + 1
DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=20"

# Serverless: start low to prevent pool exhaustion across concurrent functions
DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=1"

import { PrismaClient } from "@prisma/client";
import { withAccelerate } from "@prisma/extension-accelerate";

const prisma = new PrismaClient().$extends(withAccelerate());

// Cache-aware queries with TTL and stale-while-revalidate
const posts = await prisma.post.findMany({
  where: { published: true },
  cacheStrategy: {
    ttl: 60,       // Cache for 60 seconds
    swr: 120,      // Serve stale for 120 seconds while revalidating
  },
});

// BAD: N individual inserts
for (const item of items) {
  await prisma.product.create({ data: item });
}

// GOOD: single bulk insert (uses a single INSERT with multiple VALUES)
await prisma.product.createMany({
  data: items,
  skipDuplicates: true,  // Ignore rows that violate unique constraints
});

// Bulk update with a where clause
await prisma.product.updateMany({
  where: { category: "electronics", price: { lt: 10 } },
  data: { featured: false },
});

// Bulk delete
await prisma.product.deleteMany({
  where: { createdAt: { lt: thirtyDaysAgo } },
});

// Transaction batching for mixed operations
const [updatedUser, newAuditLog] = await prisma.$transaction([
  prisma.user.update({ where: { id: userId }, data: { lastLoginAt: new Date() } }),
  prisma.auditLog.create({ data: { userId, action: "login" } }),
]);

// First page
const firstPage = await prisma.post.findMany({
  take: 20,
  orderBy: { id: "asc" },
});

// Subsequent pages — use the last item's ID as cursor
const nextPage = await prisma.post.findMany({
  take: 20,
  skip: 1,           // Skip the cursor item itself
  cursor: { id: lastPost.id },
  orderBy: { id: "asc" },
});

const page = await prisma.post.findMany({
  skip: (pageNumber - 1) * pageSize,
  take: pageSize,
  orderBy: { createdAt: "desc" },
});

const total = await prisma.post.count({ where: filters });

// Complex aggregation that Prisma doesn't support natively
const stats = await prisma.$queryRaw`
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS total,
    SUM(amount) AS revenue
  FROM orders
  WHERE created_at >= ${startDate}
  GROUP BY month
  ORDER BY month DESC
`;

// Use $queryRawUnsafe only when parameterized queries won't work
// NEVER interpolate user input into $queryRawUnsafe

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  published Boolean
  authorId  Int
  createdAt DateTime @default(now())
  author    User     @relation(fields: [authorId], references: [id])

  // Add indexes for common query patterns
  @@index([authorId])                      // Foreign key lookups
  @@index([published, createdAt])          // Filtered + sorted queries
  @@index([title], type: Hash)             // Exact match lookups
}