Prisma ORM Database

# Prisma ORM Database

Systematic Prisma workflow ensuring type-safe database operations with zero migration errors.

## Overview

This Skill enforces:
- Prisma schema definition (source of truth)
- Model-first migration pattern
- Safe migrations with rollback testing
- Type-safe database queries
- Environment-specific workflows
- Schema drift detection
- Production deployment safety

Apply when designing database schemas, creating migrations, or generating Prisma Client.

## Prisma Workflow

**Every schema change follows this process**:

```
Step 1: Update schema.prisma
  ↓
Step 2: Create migration
  ↓
Step 3: Test migration locally
  ↓
Step 4: Deploy to preview
  ↓
Step 5: Merge and deploy production
```

## Step 1: Setup

### Install Prisma

```bash
npm install @prisma/client
npm install -D prisma

# Initialize Prisma
npx prisma init
```

### Configure Database Connection

Create `.env.local`:

```
DATABASE_URL="postgresql://user:password@host:5432/dbname"
```

For Neon:

```
DATABASE_URL="postgresql://user:password@host-pooler.neon.tech/dbname?sslmode=require"
```

## Step 2: Schema Definition

### Create Models

```prisma
// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String
  password  String   @db.VarChar(255)
  role      Role     @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
}

model Profile {
  id     String @id @default(cuid())
  bio    String?
  avatar String?
  userId String @unique
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String   @db.Text
  published Boolean  @default(false)
  authorId  String
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  tags      Tag[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([authorId])
  @@index([published])
}

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]
}

enum Role {
  ADMIN
  USER
  GUEST
}
```

## Step 3: Relationships

### One-to-Many Relationship

```prisma
model Author {
  id    String @id @default(cuid())
  name  String
  books Book[]
}

model Book {
  id       String @id @default(cuid())
  title    String
  authorId String
  author   Author @relation(fields: [authorId], references: [id])
}
```

### One-to-One Relationship

```prisma
model User {
  id      String  @id @default(cuid())
  email   String  @unique
  profile Profile?
}

model Profile {
  id     String  @id @default(cuid())
  userId String  @unique
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)
}
```

### Many-to-Many Relationship

```prisma
model Student {
  id       String   @id @default(cuid())
  name     String
  courses  Course[]
}

model Course {
  id       String    @id @default(cuid())
  name     String
  students Student[]
}
```

## Step 4: Create Migrations

### LOCAL DEVELOPMENT Workflow

```bash
# 1. Update schema.prisma
# (Add, modify, or remove models)

# 2. Create migration
npx prisma migrate dev --name add-user-model

# 3. Migration file created in prisma/migrations/
# 4. Database updated automatically
# 5. Prisma Client regenerated
```

### Check Migration Status

```bash
# View migration history
npx prisma migrate status

# Show which migrations are pending
npx prisma migrate status --verbose
```

### Rollback Migration

```bash
# Reset database (careful! loses all data)
npx prisma migrate reset

# This:
# 1. Deletes database
# 2. Recreates from scratch
# 3. Applies all migrations
# 4. Seeds data (if seed.ts exists)
```

## Step 5: Push vs Migrate

### npx prisma db push (Prototyping)

**Use for**: Rapid development, testing, no production

```bash
npx prisma db push
```

**Pros**:
- Fast
- No migration files created
- Good for early stages

**Cons**:
- No migration history
- Can't reproduce changes
- Not safe for production

### npx prisma migrate dev (Production-Safe)

**Use for**: Everything! Development, preview, production

```bash
npx prisma migrate dev --name descriptive_name
```

**Pros**:
- Creates migration files (version control)
- Reproducible on any environment
- Safe rollback capability
- Production-ready

## Step 6: Deploy Migrations

### Preview/Staging Environment

```bash
# GitHub Actions workflow
npx prisma migrate deploy
```

### Production Environment

```bash
# Automated deployment (never manual!)
npx prisma migrate deploy
```

**Checklist**:
- [ ] All migrations tested locally
- [ ] No destructive changes without data migration
- [ ] Rollback plan documented
- [ ] Backups taken
- [ ] Team notified of changes

## Step 7: Querying Data

### Type-Safe Queries

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

const prisma = new PrismaClient();

// ✅ GOOD: Type-safe query
const user = await prisma.user.findUnique({
  where: { id: 'user-123' }
});
// user is fully typed: { id: string, email: string, name: string, ... }

// ✅ GOOD: Create with relations
const newUser = await prisma.user.create({
  data: {
    email: 'test@example.com',
    name: 'Test User',
    profile: {
      create: {
        bio: 'My bio',
        avatar: 'https://...'
      }
    }
  },
  include: { profile: true }
});

// ✅ GOOD: Query with relations
const users = await prisma.user.findMany({
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' }
    },
    profile: true
  }
});

// ✅ GOOD: Update with nested operations
const updated = await prisma.user.update({
  where: { id: 'user-123' },
  data: {
    email: 'newemail@example.com',
    profile: {
      update: { bio: 'Updated bio' }
    }
  }
});

// ✅ GOOD: Delete with cascading
await prisma.user