Prisma in Production: Schema Design, Migrations, Connection Management, and Query Optimization

Prisma's type safety and migration workflow are genuinely good. Its query performance is not automatically good — you have to understand how include, select, and relations translate to SQL, and where the N+1 problem hides. Most teams hit a Prisma performance problem at some point and don't realize the ORM is generating twenty queries where one would do.

Schema Design Patterns

// prisma/schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins"]  // Prisma 5.7+: use JOINs instead of separate queries
}

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")  // Required for migrations
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // One-to-many: a user has many projects
  projects  Project[]

  // One-to-one: a user has one profile
  profile   Profile?

  // Many-to-many via explicit join table (preferred — allows extra fields on the relationship)
  teamMemberships TeamMember[]

  @@index([email])  // Explicit index — Prisma doesn't add these for non-unique fields
  @@map("users")    // Map to lowercase table name (PostgreSQL convention)
}

model Project {
  id          String     @id @default(cuid())
  name        String
  slug        String     @unique
  visibility  Visibility @default(PRIVATE)
  ownerId     String
  tenantId    String
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt
  deletedAt   DateTime?  // Soft delete pattern

  owner       User       @relation(fields: [ownerId], references: [id], onDelete: Cascade)

  // Composite index for common query pattern: get all projects for a tenant
  @@index([tenantId, deletedAt])
  @@index([ownerId])
  @@map("projects")
}

enum Visibility {
  PRIVATE
  TEAM
  PUBLIC
}

// Explicit join table — more control than implicit many-to-many
model TeamMember {
  userId    String
  teamId    String
  role      TeamRole  @default(MEMBER)
  joinedAt  DateTime  @default(now())

  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  team      Team      @relation(fields: [teamId], references: [id], onDelete: Cascade)

  @@id([userId, teamId])  // Composite primary key
  @@map("team_members")
}

Avoiding N+1 Queries

// src/services/project.service.ts
import { prisma } from "@/lib/prisma";

// BAD: N+1 — one query for projects, then one per project for owner
async function getProjectsBad(tenantId: string) {
  const projects = await prisma.project.findMany({
    where: { tenantId, deletedAt: null },
  });

  // N additional queries — one per project
  const withOwners = await Promise.all(
    projects.map(async (p) => ({
      ...p,
      owner: await prisma.user.findUnique({ where: { id: p.ownerId } }),
    }))
  );

  return withOwners;
}

// GOOD: single query with JOIN (Prisma 5+ relationJoins preview)
async function getProjectsGood(tenantId: string) {
  return prisma.project.findMany({
    where: { tenantId, deletedAt: null },
    include: {
      owner: {
        select: { id: true, name: true, email: true }, // Only fetch needed fields
      },
    },
    orderBy: { createdAt: "desc" },
    take: 50,
  });
}

// BETTER: use select for maximum control over what's fetched
async function getProjectsOptimal(tenantId: string) {
  return prisma.project.findMany({
    where: { tenantId, deletedAt: null },
    select: {
      id: true,
      name: true,
      slug: true,
      visibility: true,
      createdAt: true,
      owner: {
        select: { id: true, name: true },
      },
      // Don't include heavy fields you don't need
      // _count: { select: { members: true } } — add aggregates without fetching records
    },
    orderBy: { createdAt: "desc" },
    take: 50,
  });
}

Pagination Patterns

// src/services/pagination.ts

// Cursor-based pagination (preferred for large datasets)
export async function getProjectsPage(params: {
  tenantId: string;
  cursor?: string;  // Last project ID from previous page
  pageSize?: number;
}) {
  const { tenantId, cursor, pageSize = 20 } = params;

  const projects = await prisma.project.findMany({
    where: { tenantId, deletedAt: null },
    cursor: cursor ? { id: cursor } : undefined,
    skip: cursor ? 1 : 0,  // Skip the cursor item itself
    take: pageSize + 1,     // Fetch one extra to determine if there's a next page
    orderBy: { createdAt: "desc" },
    select: {
      id: true,
      name: true,
      createdAt: true,
    },
  });

  const hasNextPage = projects.length > pageSize;
  const items = hasNextPage ? projects.slice(0, -1) : projects;
  const nextCursor = hasNextPage ? items[items.length - 1]?.id : null;

  return { items, nextCursor, hasNextPage };
}

// Offset pagination (for numbered pages in UI)
export async function getProjectsOffset(params: {
  tenantId: string;
  page: number;
  pageSize?: number;
}) {
  const { tenantId, page, pageSize = 20 } = params;

  const [total, items] = await prisma.$transaction([
    prisma.project.count({ where: { tenantId, deletedAt: null } }),
    prisma.project.findMany({
      where: { tenantId, deletedAt: null },
      skip: (page - 1) * pageSize,
      take: pageSize,
      orderBy: { createdAt: "desc" },
    }),
  ]);

  return {
    items,
    total,
    page,
    totalPages: Math.ceil(total / pageSize),
  };
}

Transactions

// src/services/project.service.ts

// Interactive transaction — use for business logic that needs to read then write
export async function transferProjectOwnership(
  projectId: string,
  newOwnerId: string
): Promise<void> {
  await prisma.$transaction(async (tx) => {
    // Read current state
    const project = await tx.project.findUniqueOrThrow({
      where: { id: projectId },
      select: { ownerId: true, tenantId: true },
    });

    // Validate new owner is in the same tenant
    const newOwner = await tx.user.findFirst({
      where: {
        id: newOwnerId,
        teamMemberships: {
          some: { team: { tenantId: project.tenantId } },
        },
      },
    });

    if (!newOwner) {
      throw new Error("New owner must be a member of the same tenant");
    }

    // Write
    await tx.project.update({
      where: { id: projectId },
      data: { ownerId: newOwnerId },
    });

    await tx.auditLog.create({
      data: {
        action: "project.ownership_transferred",
        targetId: projectId,
        actorId: newOwnerId,
        metadata: { previousOwnerId: project.ownerId },
      },
    });
  }, {
    isolationLevel: "Serializable", // Prevent concurrent ownership transfers
    timeout: 10_000,
  });
}

Prisma Client Setup for Production

// src/lib/prisma.ts

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

// Prevent multiple instances in development (hot reload)
declare global {
  // eslint-disable-next-line no-var
  var __prisma: PrismaClient | undefined;
}

function createPrismaClient() {
  return new PrismaClient({
    log:
      process.env.NODE_ENV === "development"
        ? ["query", "error", "warn"]
        : ["error"],

    // Log slow queries in production
    ...(process.env.NODE_ENV === "production" && {
      log: [
        { emit: "event", level: "query" },
        { emit: "stdout", level: "error" },
      ],
    }),
  });
}

export const prisma = globalThis.__prisma ?? createPrismaClient();

if (process.env.NODE_ENV !== "production") {
  globalThis.__prisma = prisma;
}

// Log slow queries to observability system
if (process.env.NODE_ENV === "production") {
  prisma.$on("query", (e) => {
    if (e.duration > 200) {
      console.warn("Slow Prisma query", {
        query: e.query,
        duration: e.duration,
        params: e.params,
      });
    }
  });
}

Migrations in Production

# Development: generate migration from schema changes
npx prisma migrate dev --name add_project_tags

# Production: apply pending migrations (never use migrate dev in prod)
npx prisma migrate deploy

# Check migration status
npx prisma migrate status

# For dangerous migrations (adding NOT NULL column to large table):
# 1. Add column as nullable
# 2. Backfill data
# 3. Add NOT NULL constraint
# Never do this in a single migration on a large table — it takes an exclusive lock

# Zero-downtime approach with manual migration:
# Step 1: Add nullable column (no lock issue)
ALTER TABLE projects ADD COLUMN tags TEXT[] DEFAULT '{}';

# Step 2: Backfill in batches (Prisma script)
# Step 3: Add default + NOT NULL (quick on already-filled column)
ALTER TABLE projects ALTER COLUMN tags SET NOT NULL;

// scripts/backfill-project-tags.ts
// Safe backfill script with batches + progress tracking

async function backfillProjectTags() {
  const BATCH_SIZE = 500;
  let cursor: string | undefined;
  let processed = 0;

  while (true) {
    const projects = await prisma.project.findMany({
      where: { tags: null },
      take: BATCH_SIZE,
      cursor: cursor ? { id: cursor } : undefined,
      skip: cursor ? 1 : 0,
      select: { id: true },
    });

    if (projects.length === 0) break;

    await prisma.project.updateMany({
      where: { id: { in: projects.map((p) => p.id) } },
      data: { tags: [] },
    });

    processed += projects.length;
    cursor = projects[projects.length - 1].id;
    console.log(`Backfilled ${processed} projects`);

    // Pause between batches to avoid overwhelming the database
    await new Promise((r) => setTimeout(r, 100));
  }

  console.log(`Backfill complete: ${processed} projects updated`);
}

See Also

• Database Migrations: Zero Downtime — safe schema changes
• PostgreSQL Performance Tuning — indexes and query plans
• Database Connection Pooling — PgBouncer with Prisma
• TypeScript Testing Patterns — testing Prisma queries

Working With Viprasol

Prisma is excellent for developer experience but requires deliberate configuration to perform well at scale. We design Prisma schemas with proper indexes, audit queries for N+1 problems, configure connection pooling that works with serverless Prisma deployments, and write safe migration procedures for production databases with millions of rows.

Database engineering → | Talk to our engineers →