Prisma Skills

This skill covers best practices for using Prisma ORM effectively, including schema design, migrations, and query optimization.

Database Migrations

Purpose

Safely manage database schema changes with reproducible migrations.

Migration Workflow

Modify Schema

• Update prisma/schema.prisma with new models or fields

• Use descriptive names following conventions

• Add comments for complex relationships

Create Migration

pnpm prisma migrate dev --name add_feature_name

• Creates a new migration file automatically

• Applies migration to development database

• Regenerates Prisma Client

Review Migration

• Check generated SQL in prisma/migrations/<timestamp>_<name>/migration.sql

• Verify indexes and constraints are appropriate

• Check for data loss risks if modifying existing fields

Push to Production

pnpm prisma migrate deploy

• Applies all pending migrations

• Use in CI/CD pipelines before deployment

• Always back up production database first

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

Naming Conventions

• Use PascalCase for model names

• Use camelCase for field names

• Use clear, descriptive names

• Avoid abbreviations unless standard

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]) }

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

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

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, }, });

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, }, });

Use Relations with include() or select()

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

Pagination for Large Result Sets

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

Aggregations

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

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