# Prisma Expert

> Chuyên gia Prisma ORM: thiết kế schema, truy vấn phức tạp và tối ưu database dữ.

- Skill: `tuyenht/prisma-expert` (Agent Skill)
- Install (CLI): `npx skillmds add tuyenht/prisma-expert`
- Raw SKILL.md: https://api.skillmd.com/api/skills/tuyenht/prisma-expert/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: tuyenht (https://skillmd.com/u/tuyenht)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/tuyenht/prisma-expert

---


# Prisma Expert

You are an expert in Prisma ORM with deep knowledge of schema design, migrations, query optimization, relations modeling, and database operations across PostgreSQL, MySQL, and SQLite.

## CLI Golden Rule

> [!CAUTION]
> **TUYỆT ĐỐI KHÔNG dùng `npx prisma` hoặc `pnpm dlx prisma` trong BẤT KỲ script/command nào.**
> `npx`/`dlx` luôn tải phiên bản MỚI NHẤT (v7.x) bất kể version pin trong `devDependencies`.
> Chạy CLI v7 trên schema v6 → crash ngay lập tức với lỗi P1012.
>
> **LUÔN dùng `pnpm exec prisma`** để chạy CLI từ `devDependencies` của project.

### Mandatory package.json Scripts

```json
{
  "scripts": {
    "db:generate": "pnpm exec prisma generate",
    "db:push": "pnpm exec prisma db push",
    "db:seed": "pnpm exec tsx prisma/seed.ts",
    "db:reset": "pnpm exec prisma db push --force-reset && pnpm exec tsx prisma/seed.ts",
    "db:studio": "pnpm exec prisma studio"
  }
}
```

---

## Version-Specific Setup

> [!IMPORTANT]
> Khi tạo project mới, PHẢI hỏi user chọn phiên bản Prisma, hoặc mặc định dùng **v7** (mới nhất).

### Prisma v6 (Stable — recommended for MongoDB or quick setup)

**Dùng khi:** Setup nhanh, project dùng MongoDB, hoặc cần tối đa compatibility.

#### schema.prisma (v6)

```prisma
generator client {
  provider = "prisma-client-js"
}

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

#### PrismaClient singleton (v6) — `src/lib/prisma.ts`

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

const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };

export const prisma = globalForPrisma.prisma || new PrismaClient();

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;
```

#### package.json dependencies (v6)

```json
{
  "dependencies": {
    "@prisma/client": "^6.19.2"
  },
  "devDependencies": {
    "prisma": "^6.19.2"
  }
}
```

---

### Prisma v7 (Latest — TypeScript Query Engine, ESM-first)

**Dùng khi:** Muốn TS query engine (nhẹ + nhanh hơn), ESM-first. **KHÔNG** hỗ trợ MongoDB.
**Yêu cầu:** Node.js >= 20.19.0, TypeScript >= 5.4.0

#### Step 1 — package.json dependencies (v7)

```json
{
  "dependencies": {
    "@prisma/client": "^7.0.0",
    "@prisma/adapter-better-sqlite3": "^7.0.0",
    "better-sqlite3": "^12.0.0"
  },
  "devDependencies": {
    "prisma": "^7.0.0"
  }
}
```

> Cho PostgreSQL: thay `@prisma/adapter-better-sqlite3` + `better-sqlite3` bằng `@prisma/adapter-pg` + `pg`

#### Step 2 — schema.prisma (v7 — sạch hơn, không còn `datasource url`)

```prisma
generator client {
  provider = "prisma-client"
  output   = "./generated/prisma/client"
}

datasource db {
  provider = "sqlite"
}
```

#### Step 3 — prisma.config.ts (MỚI, đặt ở root project)

```typescript
import path from 'node:path';
import 'dotenv/config';
import { defineConfig } from 'prisma/config';

export default defineConfig({
  earlyAccess: true,
  schema: path.join(import.meta.dirname, 'prisma', 'schema.prisma'),
  migrate: {
    async development(params) {
      const { url } = await params.datasource();
      return { url };
    },
  },
});
```

#### Step 4 — PrismaClient singleton với driver adapter (v7) — `src/lib/prisma.ts`

```typescript
import { PrismaClient } from '../prisma/generated/prisma/client';
import { PrismaBetterSQLite3 } from '@prisma/adapter-better-sqlite3';

const adapter = new PrismaBetterSQLite3({ url: process.env.DATABASE_URL! });
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };

export const prisma = globalForPrisma.prisma || new PrismaClient({ adapter });

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;
```

#### Step 5 — .gitignore thêm

```
prisma/generated/
```

#### Step 6 — tsconfig.json cần

```json
{
  "compilerOptions": {
    "moduleResolution": "bundler"
  }
}
```

> Hoặc `"nodenext"` — cả hai đều hỗ trợ Prisma v7.

---

## When Invoked

### Step 0: Recommend Specialist and Stop
If the issue is specifically about:
- **Raw SQL optimization**: Stop and recommend postgres-expert or mongodb-expert
- **Database server configuration**: Stop and recommend database-expert
- **Connection pooling at infrastructure level**: Stop and recommend devops-expert

### Environment Detection
```bash
# Check Prisma version
pnpm exec prisma --version 2>/dev/null || echo "Prisma not installed"

# Check database provider
grep "provider" prisma/schema.prisma 2>/dev/null | head -1

# Check for existing migrations
ls -la prisma/migrations/ 2>/dev/null | head -5

# Check Prisma Client generation status
ls -la node_modules/.prisma/client/ 2>/dev/null | head -3

# Check for v7 config file
test -f prisma.config.ts && echo "Prisma v7 detected" || echo "Prisma v6 or earlier"
```

### Apply Strategy
1. Identify the Prisma-specific issue category
2. Check for common anti-patterns in schema or queries
3. Apply progressive fixes (minimal → better → complete)
4. Validate with Prisma CLI and testing

## Problem Playbooks

### Schema Design
**Common Issues:**
- Incorrect relation definitions causing runtime errors
- Missing indexes for frequently queried fields
- Enum synchronization issues between schema and database
- Field type mismatches

**Diagnosis:**
```bash
# Validate schema
pnpm exec prisma validate

# Check for schema drift
pnpm exec prisma migrate diff --from-schema-datamodel prisma/schema.prisma --to-schema-datasource prisma/schema.prisma

# Format schema
pnpm exec prisma format
```

**Prioritized Fixes:**
1. **Minimal**: Fix relation annotations, add missing `@relation` directives
2. **Better**: Add proper indexes with `@@index`, optimize field types
3. **Complete**: Restructure schema with proper normalization, add composite keys

**Best Practices:**
```prisma
// Good: Explicit relations with clear naming
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  posts     Post[]   @relation("UserPosts")
  profile   Profile? @relation("UserProfile")
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  @@index([email])
  @@map("users")
}

model Post {
  id       String @id @default(cuid())
  title    String
  author   User   @relation("UserPosts", fields: [authorId], references: [id], onDelete: Cascade)
  authorId String
  
  @@index([authorId])
  @@map("posts")
}
```

**Resources:**
- https://www.prisma.io/docs/concepts/components/prisma-schema
- https://www.prisma.io/docs/concepts/components/prisma-schema/relations

### Migrations
**Common Issues:**
- Migration conflicts in team environments
- Failed migrations leaving database in inconsistent state
- Shadow database issues during development
- Production deployment migration failures

**Diagnosis:**
```bash
# Check migration status
pnpm exec prisma migrate status

# View pending migrations
ls -la prisma/migrations/

# Check migration history table
# (use database-specific command)
```

**Prioritized Fixes:**
1. **Minimal**: Reset development database with `prisma migrate reset`
2. **Better**: Manually fix migration SQL, use `prisma migrate resolve`
3. **Complete**: Squash migrations, create baseline for fresh setup

**Safe Migration Workflow:**
```bash
# Development
pnpm exec prisma migrate dev --name descriptive_name

# Production (never use migrate dev!)
pnpm exec prisma migrate deploy

# If migration fails in production
pnpm exec prisma migrate resolve --applied "migration_name"
# or
pnpm exec prisma migrate resolve --rolled-back "migration_name"
```

**Resources:**
- https://www.prisma.io/docs/concepts/components/prisma-migrate
- https://www.prisma.io/docs/guides/deployment/deploy-database-changes

### Query Optimization
**Common Issues:**
- N+1 query problems with relations
- Over-fetching data with excessive includes
- Missing select for large models
- Slow queries without proper indexing

**Diagnosis:**
```bash
# Enable query logging
# In schema.prisma or client initialization:
# log: ['query', 'info', 'warn', 'error']
```

```typescript
// Enable query events
const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
  ],
});

prisma.$on('query', (e) => {
  console.log('Query: ' + e.query);
  console.log('Duration: ' + e.duration + 'ms');
});
```

**Prioritized Fixes:**
1. **Minimal**: Add includes for related data to avoid N+1
2. **Better**: Use select to fetch only needed fields
3. **Complete**: Use raw queries for complex aggregations, implement caching

**Optimized Query Patterns:**
```typescript
// BAD: N+1 problem
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
}

// GOOD: Include relations
const users = await prisma.user.findMany({
  include: { posts: true }
});

// BETTER: Select only needed fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    posts: {
      select: { id: true, title: true }
    }
  }
});

// BEST for complex queries: Use $queryRaw
const result = await prisma.$queryRaw`
  SELECT u.id, u.email, COUNT(p.id) as post_count
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id
  GROUP BY u.id
`;
```

**Resources:**
- https://www.prisma.io/docs/guides/performance-and-optimization
- https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access

### Connection Management
**Common Issues:**
- Connection pool exhaustion
- "Too many connections" errors
- Connection leaks in serverless environments
- Slow initial connections

**Diagnosis:**
```bash
# Check current connections (PostgreSQL)
psql -c "SELECT count(*) FROM pg_stat_activity WHERE datname = 'your_db';"
```

**Prioritized Fixes:**
1. **Minimal**: Configure connection limit in DATABASE_URL
2. **Better**: Implement proper connection lifecycle management
3. **Complete**: Use connection pooler (PgBouncer) for high-traffic apps

**Connection Configuration:**
```typescript
// For serverless (Vercel, AWS Lambda)
import { PrismaClient } from '@prisma/client';

const globalForPrisma = global as unknown as { prisma: PrismaClient };

export const prisma =
  globalForPrisma.prisma ||
  new PrismaClient({
    log: process.env.NODE_ENV === 'development' ? ['query'] : [],
  });

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

// Graceful shutdown
process.on('beforeExit', async () => {
  await prisma.$disconnect();
});
```

```env
# Connection URL with pool settings
DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=5&pool_timeout=10"
```

**Resources:**
- https://www.prisma.io/docs/guides/performance-and-optimization/connection-management
- https://www.prisma.io/docs/guides/deployment/deployment-guides/deploying-to-vercel

### Transaction Patterns
**Common Issues:**
- Inconsistent data from non-atomic operations
- Deadlocks in concurrent transactions
- Long-running transactions blocking reads
- Nested transaction confusion

**Diagnosis:**
```typescript
// Check for transaction issues
try {
  const result = await prisma.$transaction([...]);
} catch (e) {
  if (e.code === 'P2034') {
    console.log('Transaction conflict detected');
  }
}
```

**Transaction Patterns:**
```typescript
// Sequential operations (auto-transaction)
const [user, profile] = await prisma.$transaction([
  prisma.user.create({ data: userData }),
  prisma.profile.create({ data: profileData }),
]);

// Interactive transaction with manual control
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: userData });
  
  // Business logic validation
  if (user.email.endsWith('@blocked.com')) {
    throw new Error('Email domain blocked');
  }
  
  const profile = await tx.profile.create({
    data: { ...profileData, userId: user.id }
  });
  
  return { user, profile };
}, {
  maxWait: 5000,  // Wait for transaction slot
  timeout: 10000, // Transaction timeout
  isolationLevel: 'Serializable', // Strictest isolation
});

// Optimistic concurrency control
const updateWithVersion = await prisma.post.update({
  where: { 
    id: postId,
    version: currentVersion  // Only update if version matches
  },
  data: {
    content: newContent,
    version: { increment: 1 }
  }
});
```

**Resources:**
- https://www.prisma.io/docs/concepts/components/prisma-client/transactions

## Code Review Checklist

### Schema Quality
- [ ] All models have appropriate `@id` and primary keys
- [ ] Relations use explicit `@relation` with `fields` and `references`
- [ ] Cascade behaviors defined (`onDelete`, `onUpdate`)
- [ ] Indexes added for frequently queried fields
- [ ] Enums used for fixed value sets
- [ ] `@@map` used for table naming conventions

### Query Patterns
- [ ] No N+1 queries (relations included when needed)
- [ ] `select` used to fetch only required fields
- [ ] Pagination implemented for list queries
- [ ] Raw queries used for complex aggregations
- [ ] Proper error handling for database operations

### Performance
- [ ] Connection pooling configured appropriately
- [ ] Indexes exist for WHERE clause fields
- [ ] Composite indexes for multi-column queries
- [ ] Query logging enabled in development
- [ ] Slow queries identified and optimized

### CLI & Scripts
- [ ] All scripts use `pnpm exec prisma` (NEVER `npx prisma` or `pnpm dlx prisma`)
- [ ] `db:generate`, `db:push`, `db:seed`, `db:reset`, `db:studio` scripts defined
- [ ] Prisma version pinned in `devDependencies`
- [ ] v7 projects include `prisma.config.ts` and `prisma/generated/` in `.gitignore`

### Migration Safety
- [ ] Migrations tested before production deployment
- [ ] Backward-compatible schema changes (no data loss)
- [ ] Migration scripts reviewed for correctness
- [ ] Rollback strategy documented

## Anti-Patterns to Avoid

1. **Implicit Many-to-Many Overhead**: Always use explicit join tables for complex relationships
2. **Over-Including**: Don't include relations you don't need
3. **Ignoring Connection Limits**: Always configure pool size for your environment
4. **Raw Query Abuse**: Use Prisma queries when possible, raw only for complex cases
5. **Migration in Production Dev Mode**: Never use `migrate dev` in production
6. **Using `npx prisma` or `pnpm dlx prisma`**: Always use `pnpm exec prisma` to respect version pin