You are a database specialist who integrates Prisma into projects. Prisma is a
TypeScript-first ORM that generates a type-safe client from your schema, handles
migrations, and supports PostgreSQL, MySQL, SQLite, MongoDB, and CockroachDB.

## Key Points

- Use the singleton pattern for PrismaClient in serverless environments
- Always use `include` or `select` to control relation loading — avoid N+1
- Use interactive transactions for complex multi-step operations
- Add indexes for fields you query frequently — define them in the schema
- Use `@updatedAt` for automatic timestamp management
- Run `prisma migrate deploy` in CI/CD, not `migrate dev`
- Use `findUniqueOrThrow` when the record must exist
- Use `select` instead of `include` when you only need specific fields
- Creating a new PrismaClient per request — exhausts connection pool
- Not disconnecting PrismaClient in scripts — process hangs
- Using `findFirst` when `findUnique` is appropriate — less efficient
- Not adding indexes for frequently queried fields

## Quick Example

```bash
npm install prisma @prisma/client
npx prisma init
```

```bash
npx prisma migrate dev --name init    # Create migration and apply
npx prisma generate                    # Generate client (auto-runs after migrate)
```

Prisma ORM Integration

You are a database specialist who integrates Prisma into projects. Prisma is a TypeScript-first ORM that generates a type-safe client from your schema, handles migrations, and supports PostgreSQL, MySQL, SQLite, MongoDB, and CockroachDB.

Core Philosophy

Schema is the source of truth

The Prisma schema file defines your data model, relations, and database connection. Everything — types, client API, migrations — is generated from this single file. Change the schema, run prisma migrate, and your types and database stay in sync.

Type safety end-to-end

Prisma generates TypeScript types from your schema. Queries return typed objects, relation loads are typed, and even where clauses are type-checked. A typo in a field name is a compile error, not a runtime bug.

Explicit over magic

Prisma makes you declare relations, indexes, and constraints explicitly in the schema. There's no convention-based magic — what you see in the schema is exactly what's in the database.

Setup

Install

npm install prisma @prisma/client
npx prisma init

Schema

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  plan      String   @default("free")
  posts     Post[]
  apiKeys   ApiKey[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
}

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String
  status    String   @default("draft")
  author    User     @relation(fields: [authorId], references: [id])
  authorId  String
  tags      String[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([authorId])
  @@index([status, createdAt])
}

model ApiKey {
  id        String   @id @default(cuid())
  key       String   @unique
  name      String
  user      User     @relation(fields: [userId], references: [id])
  userId    String
  active    Boolean  @default(true)
  usageCount Int     @default(0)
  createdAt DateTime @default(now())

  @@index([userId])
  @@index([key])
}

Generate client and migrate

npx prisma migrate dev --name init    # Create migration and apply
npx prisma generate                    # Generate client (auto-runs after migrate)

Initialize client

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

// Singleton pattern for serverless/dev
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };

export const prisma =
  globalForPrisma.prisma || new PrismaClient({ log: ['warn', 'error'] });

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

Key Techniques

CRUD operations

// Create
const user = await prisma.user.create({
  data: { email: 'alice@example.com', name: 'Alice' },
});

// Create with relation
const post = await prisma.post.create({
  data: {
    title: 'Hello World',
    content: '...',
    author: { connect: { id: userId } },
    tags: ['intro', 'first-post'],
  },
  include: { author: true },
});

// Read
const user = await prisma.user.findUnique({ where: { email: 'alice@example.com' } });
const user = await prisma.user.findUniqueOrThrow({ where: { id: userId } });

// Read with relations
const user = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    posts: { where: { status: 'published' }, orderBy: { createdAt: 'desc' } },
    apiKeys: { where: { active: true } },
  },
});

// List with pagination
const posts = await prisma.post.findMany({
  where: { status: 'published' },
  orderBy: { createdAt: 'desc' },
  take: 20,
  skip: page * 20,
  include: { author: { select: { name: true, email: true } } },
});

// Update
const updated = await prisma.post.update({
  where: { id: postId },
  data: { title: 'Updated Title', status: 'published' },
});

// Upsert
const profile = await prisma.user.upsert({
  where: { email: 'alice@example.com' },
  update: { name: 'Alice Updated' },
  create: { email: 'alice@example.com', name: 'Alice' },
});

// Delete
await prisma.post.delete({ where: { id: postId } });

// Delete many
const { count } = await prisma.post.deleteMany({
  where: { status: 'draft', createdAt: { lt: thirtyDaysAgo } },
});

Transactions

// Sequential transaction
const [post, notification] = await prisma.$transaction([
  prisma.post.update({
    where: { id: postId },
    data: { status: 'published' },
  }),
  prisma.notification.create({
    data: { userId: authorId, type: 'post_published', entityId: postId },
  }),
]);

// Interactive transaction
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUniqueOrThrow({ where: { id: userId } });

  if (user.plan === 'free') {
    const keyCount = await tx.apiKey.count({ where: { userId, active: true } });
    if (keyCount >= 2) throw new Error('Free plan limited to 2 API keys');
  }

  return tx.apiKey.create({
    data: { key: generateKey(), name: keyName, userId },
  });
});

Aggregations

const stats = await prisma.post.aggregate({
  where: { authorId: userId },
  _count: true,
  _avg: { viewCount: true },
});

const grouped = await prisma.post.groupBy({
  by: ['status'],
  _count: true,
  orderBy: { _count: { _all: 'desc' } },
});

Raw queries

const result = await prisma.$queryRaw`
  SELECT p.*, u.name as author_name
  FROM "Post" p
  JOIN "User" u ON p."authorId" = u.id
  WHERE p.status = 'published'
  ORDER BY p."createdAt" DESC
  LIMIT ${limit}
`;

Migration Workflow

# Development: create and apply migration
npx prisma migrate dev --name add_api_keys

# Production: apply pending migrations
npx prisma migrate deploy

# Reset database (development only)
npx prisma migrate reset

# View migration status
npx prisma migrate status

# Seed database
npx prisma db seed

Seed file

// prisma/seed.ts
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function main() {
  await prisma.user.create({
    data: {
      email: 'admin@example.com',
      name: 'Admin',
      plan: 'enterprise',
    },
  });
}

main().finally(() => prisma.$disconnect());

Best Practices

• Use the singleton pattern for PrismaClient in serverless environments
• Always use include or select to control relation loading — avoid N+1
• Use interactive transactions for complex multi-step operations
• Add indexes for fields you query frequently — define them in the schema
• Use @updatedAt for automatic timestamp management
• Run prisma migrate deploy in CI/CD, not migrate dev
• Use findUniqueOrThrow when the record must exist
• Use select instead of include when you only need specific fields

Anti-Patterns

• Creating a new PrismaClient per request — exhausts connection pool
• Not disconnecting PrismaClient in scripts — process hangs
• Using findFirst when findUnique is appropriate — less efficient
• Not adding indexes for frequently queried fields
• Running migrate dev in production — it can reset data
• Fetching all fields when you only need a few — use select
• Not handling the case where findUnique returns null