Design database schemas, create migrations, manage data relationships, and sync with production using Prisma. Apply when designing database schemas, creating migrations, or defining data models.
apm install @thamjiahe/prisma-orm-database
[](https://apm-p1ls2dz87-atlamors-projects.vercel.app/packages/@thamjiahe/prisma-orm-database)---
name: "Prisma ORM Database"
description: "Design database schemas, create migrations, manage data relationships, and sync with production using Prisma. Apply when designing database schemas, creating migrations, or defining data models."
allowed-tools: Read, Write, Edit, Bash
version: 1.1.0
compatibility: Claude Opus 4.5, Claude Code v2.x
updated: 2026-01-24
---
# 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.delete({
where: { id: 'user-123' }
// Posts automatically deleted (onDelete: Cascade)
});
```
## Step 8: Anti-Patterns
```ts
// ❌ BAD: Manual SQL queries (lose type safety)
const result = await prisma.$queryRaw`SELECT * FROM users`;
// ✅ GOOD: Use Prisma query builder
const users = await prisma.user.findMany();
// ❌ BAD: N+1 queries
const users = await prisma.user.findMany();
for (const user of users) {
const posts = await prisma.post.findMany({
where: { authorId: user.id }
});
// Database hit per user!
}
// ✅ GOOD: Query with relations
const users = await prisma.user.findMany({
include: { posts: true }
});
// ❌ BAD: Creating migrations without testing
npx prisma migrate deploy // Without local testing!
// ✅ GOOD: Test locally first
npx prisma migrate dev --name test-migration
npx prisma migrate reset
npx prisma migrate dev
```
## Step 9: Schema Drift Detection
### Detect and Fix Drift
```bash
# Compare migration history with actual database
npx prisma migrate diff
# Generate SQL to fix drift
npx prisma migrate diff \
--from-schema-datamodel prisma/schema.prisma \
--to-schema-datasource
```
## Step 10: Seeding Database
### Create Seed File
Create `prisma/seed.ts`:
```ts
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
async function main() {
// Create users
const user1 = await prisma.user.create({
data: {
email: 'alice@example.com',
name: 'Alice',
password: 'hashedpassword123'
}
});
// Create posts
const post1 = await prisma.post.create({
data: {
title: 'First Post',
content: 'Content here',
authorId: user1.id
}
});
console.log('Database seeded successfully');
}
main()
.catch((e) => {
console.error(e);
process.exit(1);
})
.finally(async () => {
await prisma.$disconnect();
});
```
Configure `package.json`:
```json
{
"prisma": {
"seed": "ts-node prisma/seed.ts"
}
}
```
Run seed:
```bash
npx prisma db seed
```
## Step 11: Production Deployment
### CI/CD Pipeline
```yaml
# .github/workflows/migrations.yaml
name: Deploy Migrations
on:
push:
branches: [main]
jobs:
migrate:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- uses: actions/setup-node@v4
with:
node-version: '20'
- name: Install dependencies
run: npm ci
- name: Deploy migrations
env:
DATABASE_URL: ${{ secrets.DATABASE_URL }}
run: npx prisma migrate deploy
```
## Verification Before Production
- [ ] All schema changes defined in prisma/schema.prisma
- [ ] Migrations created with descriptive names
- [ ] Migrations tested locally (push and reset)
- [ ] No data loss in migrations
- [ ] Rollback plan documented
- [ ] Prisma Client regenerated
- [ ] Type safety verified
- [ ] CI/CD pipeline configured
- [ ] Backups taken before production
- [ ] Team notified of schema changes
## Common Commands
```bash
# Generate Prisma Client
npx prisma generate
# Create migration
npx prisma migrate dev --name migration_name
# Deploy migrations
npx prisma migrate deploy
# Reset database
npx prisma migrate reset
# View Prisma Studio (UI)
npx prisma studio
# Format schema
npx prisma format
# Check migrations status
npx prisma migrate status
# Seed database
npx prisma db seed
# Push without migrations (dev only)
npx prisma db push
```
## Integration with Project Standards
Enforces database best practices:
- D-1: Models defined in organized files
- D-2: Type-safe validation
- D-3: Migrations are reproducible
- Type safety eliminates SQL injection
- No hardcoded secrets (uses .env)
## Resources
- Prisma Documentation: https://www.prisma.io/docs
- Prisma Migrate: https://www.prisma.io/docs/orm/prisma-migrate
- Schema: https://www.prisma.io/docs/orm/prisma-schema
- Relations: https://www.prisma.io/docs/orm/prisma-schema/relations
---
**Last Updated:** January 24, 2026
**Compatibility:** Claude Opus 4.5, Claude Code v2.x
**Status:** Production Ready
> **January 2026 Update:** This skill is compatible with Claude Opus 4.5 and Claude Code v2.x. For complex tasks, use the `effort: high` parameter for thorough analysis.