prisma-orm-patterns-and-migrations

Prisma ORM Patterns & Migrations Architecture Guide

Production reference for building scalable Node.js and TypeScript applications using Prisma ORM, optimizing database access, executing zero-downtime migrations, and managing connections.

---

1. Schema Modeling & Relationship Patterns

1.1 Explicit Many-to-Many vs. Implicit Many-to-Many

• Implicit: Prisma manages join table automatically. Use when join table needs no additional metadata.
• Explicit: User defines join table model. Use when storing timestamps or custom payload on relation records (e.g., role, assignedAt).

// Explicit Many-to-Many with Relation Attributes
model User {
  id        String         @id @default(uuid())
  email     String         @unique
  memberships UserOrganization[]
}

model Organization {
  id        String         @id @default(uuid())
  name      String
  members   UserOrganization[]
}

model UserOrganization {
  userId         String
  organizationId String
  role           String       @default("MEMBER")
  assignedAt     DateTime     @default(now())

  user         User         @relation(fields: [userId], references: [id], onDelete: Cascade)
  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  @@id([userId, organizationId])
  @@index([organizationId])
}

1.2 Referential Actions & Performance Indexing

• Always explicitly define foreign key indexes using @@index([foreignKeyField]) to optimize join performance and cascading updates/deletes.
• Set appropriate onDelete actions (Cascade, SetNull, Restrict, NoAction).

---

2. Query Optimization & Preventing N+1 Problems

2.1 Fine-Grained Projection (select vs include)

Avoid blind include queries that fetch all fields from related tables. Use explicit select payloads to reduce payload over-fetching.

// ❌ ANTI-PATTERN: Over-fetching full nested objects
const posts = await prisma.post.findMany({
  include: { author: true, comments: true }
});

// ✅ PRODUCTION PATTERN: Explicit field selection
const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    author: {
      select: {
        id: true,
        name: true
      }
    },
    _count: {
      select: { comments: true }
    }
  }
});