Prisma ORM — SKILL.md

---
name: "Prisma ORM"
description: "Skill for Prisma ORM — auto-generated from documentation"
version: "1.0.0"
author: "skynet"
category: "dev"
agents: ["claude-code", "codex", "gemini"]
tags: ["prisma", "dev", "auto-generated"]
---

# Prisma ORM

---
name: Prisma ORM
description: Use when working with database operations, schema management, type-safe database queries, and data modeling in JavaScript/TypeScript applications
metadata:
  author: skynet
  version: 1.0.0
category: dev
---

# Prisma ORM

Modern database toolkit providing type-safe database access, declarative migrations, and powerful query capabilities.

## Installation & Setup

```bash
# Install Prisma CLI and client
npm install prisma @prisma/client

# Initialize Prisma in existing project
npx prisma init

# Initialize with specific database provider
npx prisma init --datasource-provider postgresql
npx prisma init --datasource-provider mysql
npx prisma init --datasource-provider sqlite
```

## Schema Definition

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

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@map("users")
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  tags      Tag[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@map("posts")
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String?
  user   User   @relation(fields: [userId], references: [id])
  userId Int    @unique

  @@map("profiles")
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]

  @@map("tags")
}
```

## Database Operations

### Migration Commands
```bash
# Generate and apply migration
npx prisma migrate dev --name init

# Apply pending migrations
npx prisma migrate deploy

# Reset database (dev only)
npx prisma migrate reset

# Generate migration without applying
npx prisma migrate dev --create-only

# Check migration status
npx prisma migrate status
```

### Database Management
```bash
# Generate Prisma Client
npx prisma generate

# Push schema changes without migration
npx prisma db push

# Pull schema from existing database
npx prisma db pull

# Seed database
npx prisma db seed

# Open Prisma Studio
npx prisma studio
```

## Client Usage

### Basic Setup
```typescript
// lib/prisma.ts
import { PrismaClient } from '@prisma/client'

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: ['query'],
  })

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

### CRUD Operations
```typescript
import { prisma } from './lib/prisma'

// Create
const user = await prisma.user.create({
  data: {
    email: 'john@example.com',
    name: 'John Doe',
    posts: {
      create: [
        {
          title: 'My First Post',
          content: 'Hello World!',
          published: true
        }
      ]
    }
  },
  include: {
    posts: true
  }
})

// Read
const users = await prisma.user.findMany({
  where: {
    email: {
      contains: '@example.com'
    }
  },
  include: {
    posts: {
      where: {
        published: true
      }
    },
    profile: true
  },
  orderBy: {
    createdAt: 'desc'
  },
  take: 10,
  skip: 20
})

// Update
const updatedUser = await prisma.user.update({
  where: {
    id: 1
  },
  data: {
    name: 'John Smith',
    posts: {
      updateMany: {
        where: {
          published: false
        },
        data: {
          published: true
        }
      }
    }
  }
})

// Delete
await prisma.user.delete({
  where: {
    id: 1
  }
})

// Upsert
const user = await prisma.user.upsert({
  where: {
    email: 'john@example.com'
  },
  update: {
    name: 'John Updated'
  },
  create: {
    email: 'john@example.com',
    name: 'John New'
  }
})
```

### Advanced Queries
```typescript
// Aggregations
const stats = await prisma.post.aggregate({
  _count: {
    id: true
  },
  _avg: {
    authorId: true
  },
  where: {
    published: true
  }
})

// Group by
const postsByAuthor = await prisma.post.groupBy({
  by: ['authorId'],
  _count: {
    id: true
  },
  having: {
    authorId: {
      _count: {
        gt: 1
      }
    }
  }
})

// Raw queries
const result = await prisma.$queryRaw`
  SELECT u.name, COUNT(p.id) as post_count
  FROM users u
  LEFT JOIN posts p ON u.id = p.author_id
  GROUP BY u.id, u.name
`

// Transactions
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: {
      email: 'tx@example.com',
      name: 'Transaction User'
    }
  })

  await tx.post.create({
    data: {
      title: 'Transaction Post',
      authorId: user.id
    }
  })
})
```

## Decision Tree: Database Provider Selection

```
Need a database provider?
├── Development/Prototyping
│   └── SQLite (file-based, no setup)
├── Production Web App
│   ├── High Performance Required
│   │   └── PostgreSQL (full-featured, ACID)
│   ├── Existing MySQL Infrastructure
│   │   └── MySQL/MariaDB
│   └── Cloud-First
│       ├── AWS → RDS PostgreSQL/MySQL
│       ├── Vercel → PlanetScale/Neon
│       └── Azure → Azure Database
└── Specialized Needs
    ├── Document-like Data → PostgreSQL (JSON)
    ├── Full-text Search → PostgreSQL (built-in)
    └── Legacy System → Match existing DB
```

## Environment Configuration

```bash
# .env
DATABASE_URL="postgresql://username:password@localhost:5432/mydb?schema=public"
# MySQL
# DATABASE_URL="mysql://username:password@localhost:3306/mydb"
# SQLite
# DATABASE_URL="file:./dev.db"

# Optional
PRISMA_QUERY_ENGINE_LIBRARY="query-engine-debian-openssl-3.0.x"
PRISMA_ENGINE_PROTOCOL="graphql"
```

## Common Workflows

### Setting up New Project
```bash
npm init -y
npm install prisma @prisma/client
npx prisma init
# Edit schema.prisma
npx prisma migrate dev --name init
npx prisma generate
```

### Adding New Model
```bash
# 1. Add model to schema.prisma
# 2. Create migration
npx prisma migrate dev --name add_new_model
# 3. Update client
npx prisma generate
```

### Database Seeding
```typescript
// prisma/seed.ts
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  const user = await prisma.user.upsert({
    where: { email: 'admin@example.com' },
    update: {},
    create: {
      email: 'admin@example.com',
      name: 'Admin User',
      posts: {
        create: [
          {
            title: 'Welcome Post',
            content: 'Welcome to our platform!',
            published: true
          }
        ]
      }
    }
  })
  console.log({ user })
}

main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })
```

```json
// package.json
{
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}
```

## Troubleshooting

### Migration Issues
```bash
# Error: "Migration failed to apply cleanly"
npx prisma migrate resolve --applied 20231201000000_migration_name

# Error: "Database is out of sync"
npx prisma migrate reset # Dev only
npx prisma db push # Force schema sync
```

### Client Generation Issues
```bash
# Error: "Cannot find module '@prisma/client'"
npx prisma generate

# Error: "Type definitions out of sync"
rm -rf node_modules/.prisma
npx prisma generate
```

### Connection Issues
```
# Error: "Can't reach database server"
Check DATABASE_URL in .env
Verify database is running
Check network connectivity/firewall
Verify credentials and permissions

# Error: "SSL connection required"
Add ?sslmode=require to DATABASE_URL
For development: ?sslmode=disable
```

### Performance Issues
```typescript
// Enable query logging
const prisma = new PrismaClient({
  log: [
    {
      emit: 'event',
      level: 'query',
    },
  ],
})

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

// Use connection pooling
DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=5"
```

### Schema Validation Errors
```
# Error: "Field not found"
Check field names match schema exactly
Ensure relations are properly defined
Verify @map directives are correct

# Error: "Unique constraint failed"
Check for duplicate data
Add @@ignore to legacy fields
Use upsert instead of create
```