Prisma

Common Patterns

// Adding a new model
model Quiz {
  id        String   @id @default(cuid())
  title     String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

// Adding a relationship
model Question {
  id    String @id @default(cuid())
  quiz  Quiz   @relation(fields: [quizId], references: [id], onDelete: Cascade)
  quizId String
}

// Adding a unique constraint
model User {
  id    String @id @default(cuid())
  email String @unique
  name  String
}

// Adding an index for query performance
model Interview {
  id        String   @id @default(cuid())
  candidateId String
  createdAt DateTime @default(now())

  @@index([candidateId])
  @@index([createdAt])
}

Rollback Strategies

• Never delete migrations - they form the audit trail
• Create a new migration to undo changes if needed
• Test migrations locally before deploying
• Keep migration files small and focused

Data Modeling

Purpose

Design effective database schemas that support application requirements efficiently.

Schema Design Principles

1. Naming Conventions

   • Use PascalCase for model names
   • Use camelCase for field names
   • Use clear, descriptive names
   • Avoid abbreviations unless standard

2. Relationships

   // One-to-Many
   model User {
     id String @id @default(cuid())
     interviews Interview[]
   }
   
   model Interview {
     id String @id @default(cuid())
     user User @relation(fields: [userId], references: [id])
     userId String
   }
   
   // Many-to-Many (with join table)
   model Quiz {
     id String @id @default(cuid())
     questions QuizQuestion[]
   }
   
   model Question {
     id String @id @default(cuid())
     quizzes QuizQuestion[]
   }
   
   model QuizQuestion {
     id String @id @default(cuid())
     quiz Quiz @relation(fields: [quizId], references: [id], onDelete: Cascade)
     quizId String
     question Question @relation(fields: [questionId], references: [id], onDelete: Cascade)
     questionId String
     order Int
   
     @@unique([quizId, questionId])
   }
   

3. Field Types

   • Use appropriate types (String, Int, Boolean, DateTime, Json)
   • Use @db.Text for large text fields
   • Use Json type for flexible data structures
   • Consider enum types for fixed values

4. Timestamps and Metadata

   model Entity {
     id String @id @default(cuid())
     createdAt DateTime @default(now())
     updatedAt DateTime @updatedAt
     createdBy String?
   }
   

Indexes and Performance

model Candidate {
  id String @id @default(cuid())
  email String @unique
  name String
  createdAt DateTime @default(now())

  // Single field index
  @@index([email])

  // Composite index for common queries
  @@index([createdAt, id])

  // Full-text search index (PostgreSQL)
  @@fulltext([name])
}

Query Optimization

Purpose

Write efficient database queries that minimize load and improve application performance.

Query Patterns

1. Use select() to Fetch Only Needed Fields

   // Bad - fetches all fields
   const user = await prisma.user.findUnique({
     where: { id: "user-1" },
   });
   
   // Good - fetch only needed fields
   const user = await prisma.user.findUnique({
     where: { id: "user-1" },
     select: {
       id: true,
       email: true,
       name: true,
     },
   });
   

2. Batch Queries Efficiently

   // Avoid N+1 queries
   const users = await prisma.user.findMany();
   for (const user of users) {
     const interviews = await prisma.interview.findMany({
       where: { userId: user.id },
     }); // N+1 problem!
   }
   
   // Solution - use include or nested queries
   const users = await prisma.user.findMany({
     include: {
       interviews: true,
     },
   });
   

3. Use Relations with include() or select()

   const quiz = await prisma.quiz.findUnique({
     where: { id: "quiz-1" },
     include: {
       quizQuestions: {
         include: {
           question: true,
         },
         orderBy: { order: "asc" },
       },
     },
   });
   

4. Pagination for Large Result Sets

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

5. Aggregations

   const stats = await prisma.interview.aggregate({
     where: { candidateId: "candidate-1" },
     _count: true,
     _avg: { score: true },
   });
   

6. Raw Queries for Complex Operations

   const results = await prisma.$queryRaw`
     SELECT u.*, COUNT(i.id) as interview_count
     FROM User u
     LEFT JOIN Interview i ON u.id = i.userId
     GROUP BY u.id
   `;
   

Performance Tips

• Always use indexes on frequently queried fields
• Use select() instead of fetching entire records
• Implement pagination for large datasets
• Use distinct() to avoid duplicate results
• Consider denormalization for frequently accessed aggregations
• Monitor slow queries in production
• Use Prisma Studio to analyze query patterns

Caching Strategies

Combine Prisma queries with cache components in server components, and use server actions for mutations:

// Data fetching (in server component or cached function)
"use cache";
import { cacheLife, cacheTag } from "next/cache";

export async function getCachedQuiz(id: string) {
  cacheLife({ max: 3600 });
  cacheTag("quizzes");

  return await prisma.quiz.findUnique({
    where: { id },
    include: {
      quizQuestions: {
        include: { question: true },
        orderBy: { order: "asc" },
      },
    },
  });
}

// Mutations (in server action, NOT API route)
("use server");
import { updateTag } from "@/lib/utils/cache-utils";

export async function updateQuizAction(id: string, data: QuizInput) {
  const user = await requireUser();

  const quiz = await prisma.quiz.update({
    where: { id },
    data,
  });

  // Invalidate cache after mutation
  updateTag("quizzes");

  return { success: true, data: quiz };
}

Development Checklist

When working with Prisma:

• Schema follows naming conventions
• Relationships are properly defined
• Indexes are added for query performance
• Timestamps (createdAt, updatedAt) are included
• Migrations are reviewed before deployment
• Queries use select() or include() appropriately
• N+1 query problems are avoided
• Pagination is implemented for large datasets
• Mutations are in server actions, not API routes
• Cache invalidation (updateTag) is called after mutations in server actions