Prisma ORM in Production: Schema Design, Migrations, and Performance Patterns

Prisma has become the default ORM for TypeScript backend projects. The appeal is immediate: define your schema in a declarative file, run a migration command, and get a fully typed client with autocomplete for every query. No raw SQL strings. No mismatched types between your database and your code. No runtime query builder errors. If a column does not exist, the TypeScript compiler tells you before the code runs. Here is how to use Prisma effectively in production.

Schema Design

The schema.prisma file is the single source of truth for your database structure. Every model maps to a table, every field to a column, and every relation to a foreign key constraint:

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String
  role      Role     @default(MEMBER)
  posts     Post[]
  comments  Comment[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
  @@map("users")
}

model Post {
  id          String    @id @default(cuid())
  title       String    @db.VarChar(255)
  slug        String    @unique
  content     String?
  published   Boolean   @default(false)
  publishedAt DateTime?
  author      User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId    String
  tags        Tag[]
  comments    Comment[]
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  @@index([authorId, published, publishedAt(sort: Desc)])
  @@map("posts")
}

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]

  @@map("tags")
}

model Comment {
  id        String   @id @default(cuid())
  body      String
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId  String
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId    String
  createdAt DateTime @default(now())

  @@index([postId, createdAt(sort: Desc)])
  @@map("comments")
}

enum Role {
  ADMIN
  EDITOR
  MEMBER
}

Key design decisions: cuid() generates collision-resistant IDs without database coordination. @@index creates composite indexes matching your query patterns. @@map decouples Prisma model names from database table names. onDelete: Cascade ensures referential integrity when parents are deleted.

Migrations

# Create and apply a migration
npx prisma migrate dev --name add_comments_table

# Apply pending migrations in production (CI/CD)
npx prisma migrate deploy

# Reset the database (dev only, destroys data)
npx prisma migrate reset

# Generate the client after schema changes
npx prisma generate

Each migration creates a SQL file in prisma/migrations/ that you commit to version control. This gives you a complete, auditable history of every schema change. In CI/CD, prisma migrate deploy applies pending migrations without interactive prompts.

Type-Safe Queries

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

const prisma = new PrismaClient();

// Find with relations -- fully typed, autocompleted
const post = await prisma.post.findUnique({
  where: { slug: 'prisma-in-production' },
  include: {
    author: { select: { name: true, email: true } },
    tags: true,
    comments: {
      orderBy: { createdAt: 'desc' },
      take: 10,
      include: { author: { select: { name: true } } },
    },
  },
});
// TypeScript knows post.author.name is string, post.comments is Comment[]

// Complex filtering
const recentPosts = await prisma.post.findMany({
  where: {
    published: true,
    publishedAt: { gte: new Date('2025-01-01') },
    tags: { some: { name: { in: ['typescript', 'prisma'] } } },
  },
  orderBy: { publishedAt: 'desc' },
  take: 20,
});

// Transaction: create post with tags atomically
const newPost = await prisma.$transaction(async (tx) => {
  const post = await tx.post.create({
    data: {
      title: 'New Post',
      slug: 'new-post',
      content: 'Content here',
      authorId: userId,
      tags: {
        connectOrCreate: [
          { where: { name: 'prisma' }, create: { name: 'prisma' } },
          { where: { name: 'orm' }, create: { name: 'orm' } },
        ],
      },
    },
  });
  return post;
});

Performance Patterns

Avoid the N+1 problem. Always use include or select to load related data in a single query instead of fetching relations in a loop. Prisma translates include into efficient SQL JOINs or batched queries.

Use select to fetch only needed columns. Instead of findMany() which returns all columns, use select: { id: true, title: true, slug: true } to reduce data transfer and memory usage.

Connection pooling. In serverless environments (Lambda, Vercel), use Prisma Accelerate or PgBouncer to pool connections. Serverless functions create a new Prisma Client per invocation; without pooling, you exhaust database connections under load.

// Singleton pattern for non-serverless environments
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;

Use raw SQL when Prisma’s query builder falls short. Aggregations, window functions, and complex CTEs are better expressed in raw SQL. Prisma supports $queryRaw with tagged template literals that are still parameterized and safe from injection:

const topAuthors = await prisma.$queryRaw`
  SELECT u.name, COUNT(p.id)::int AS post_count
  FROM users u
  JOIN posts p ON p."authorId" = u.id
  WHERE p.published = true
  GROUP BY u.id, u.name
  ORDER BY post_count DESC
  LIMIT ${10}
`;

When Prisma Is Not Enough

Prisma is excellent for CRUD-heavy applications with standard relational patterns. It struggles with: very complex queries (multi-level aggregations, recursive CTEs, lateral joins), bulk operations on millions of rows, and fine-grained control over generated SQL. For these cases, complement Prisma with raw queries or consider Drizzle ORM, which gives you a SQL-like query builder with full type safety and no code generation step.

Further reading: Prisma Documentation | Prisma CRUD Operations | Prisma Migrate