Week 15: Advanced Prisma — Complex Queries, N+1 & Migrations at Scale

1. Dynamic Queries at Runtime

A repository function like getOpenTasks() works when the filters are fixed. It stops working the moment a search endpoint needs to combine an arbitrary, optional subset of filters — status, assignee, and a date range, any of which might be omitted. Prisma's where object is a plain JavaScript object, which makes composing it from optional conditions straightforward:

import { Prisma } from "@prisma/client";

interface TaskSearchFilters {
  status?: string;
  assigneeId?: string;
  createdAfter?: Date;
}

async function searchTasks(filters: TaskSearchFilters) {
  const where: Prisma.TaskWhereInput = {};

  if (filters.status) where.status = filters.status;
  if (filters.assigneeId) where.assigneeId = filters.assigneeId;
  if (filters.createdAfter) where.createdAt = { gt: filters.createdAfter };

  return prisma.task.findMany({ where, orderBy: { createdAt: "desc" } });
}

Each conditional assignment only adds a key to where if the corresponding filter was actually supplied — an omitted filter simply never appears in the object Prisma receives, so it doesn't constrain the query at all. This is plain object-building, not a special query-composition API to learn, which is exactly what makes it easy to extend as new filters are added later.

2. include, select & Solving N+1

Prisma doesn't lazily load relations the way an ORM like SQLAlchemy or TypeORM can — a relation is only fetched if you explicitly ask for it with include or select. That sounds like it should make N+1 impossible, but the bug reappears in a different, still-common shape: fetching a list of tasks, then calling a second Prisma query per task inside a loop or a map callback to fetch each one's assignee separately.

const tasks = await prisma.task.findMany();   // 1 query

const enriched = await Promise.all(
  tasks.map(async (task) => ({
    ...task,
    // fires a SEPARATE query for every single task -- N more queries
    assignee: await prisma.user.findUnique({ where: { id: task.assigneeId } }),
  }))
);

include fixes this by fetching the relation in the same round trip as the main query, via a SQL join under the hood:

const tasks = await prisma.task.findMany({
  include: { assignee: true },   // one query, joined
});

select goes a step further and lets you name exactly which columns to return — useful for a list endpoint that only needs three fields, avoiding both the N+1 problem and the cost of over-fetching whole rows:

const taskSummaries = await prisma.task.findMany({
  select: {
    id: true,
    title: true,
    assignee: { select: { id: true, name: true } },
  },
});

3. Batch Operations & Zero-Downtime Migrations

Calling prisma.task.create() in a loop for thousands of rows issues thousands of individual INSERT statements. createMany batches that into a single statement:

await prisma.task.createMany({
  data: csvRows.map((row) => ({ title: row.title, status: "open" })),
  skipDuplicates: true,
});

A bulk update follows the same idea — one statement affecting many rows, rather than loading each record, mutating it, and saving it back individually:

await prisma.task.updateMany({
  where: { updatedAt: { lt: cutoffDate } },
  data: { status: "archived" },
});

Schema changes deserve the same care. Adding a required (NOT NULL) column to a large table can lock it for the duration of the rewrite in some databases — a multi-second lock is effectively an outage for every request touching that table. The safe pattern spreads the change across separate, sequential migrations instead of one:

// Migration 1 (expand): add the column optional -- no table lock
model Task {
  // ...
  priority String?   // nullable for now
}
// npx prisma migrate dev --name add_priority_nullable

// Migration 2: application code starts writing it; backfill existing rows
// UPDATE "Task" SET priority = 'normal' WHERE priority IS NULL;
// (run as a one-off script or a `prisma migrate dev --create-only` custom migration)

// Migration 3 (contract): once backfilled and the app fully depends on it
model Task {
  // ...
  priority String   // now required
}
// npx prisma migrate dev --name make_priority_required

No single migration in that sequence requires the old and new application code to disagree about the schema — which matters directly for a rolling deploy, where old and new instances briefly run side by side. A one-step migration that adds a required column the app immediately depends on breaks the moment an old instance, still serving traffic mid-rollout, tries to insert a row without it.

4. Hands-on Exercise

5. Knowledge Check

Three quick questions. Expand each to check your answer.