SKILL.md
Prisma ORM - Type-Safe Database Toolkit
Modern database toolkit for TypeScript with schema-first development, auto-generated type-safe client, and powerful migration system.
Quick Reference
Installation
npm install prisma @prisma/client
npx prisma initBasic Workflow
# 1. Define schema
# Edit prisma/schema.prisma2. Create migration
npx prisma migrate dev --name init
3. Generate client
npx prisma generate
4. Open Studio
npx prisma studio
### Core Schema Pattern
// 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?
posts Post[]
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
model Post {
id String @id @default(cuid())
title String
content String?
published Boolean @default(false)
author User @relation(fields: [authorId], references: [id])
authorId String
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([authorId])
}
### Type-Safe CRUD
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
// Create
const user = await prisma.user.create({
data: {
email: 'alice@example.com',
name: 'Alice',
posts: {
create: { title: 'First Post', content: 'Hello World' }
}
},
include: { posts: true }
});
// Read with filters
const users = await prisma.user.findMany({
where: { email: { contains: '@example.com' } },
include: { posts: { where: { published: true } } },
orderBy: { createdAt: 'desc' },
take: 10
});
// Update
await prisma.user.update({
where: { id: userId },
data: { name: 'Bob' }
});
// Delete
await prisma.user.delete({ where: { id: userId } });
## Schema Design Patterns
### Field Types and Attributes
model Product {
id Int @id @default(autoincrement())
sku String @unique
name String
description String? // Optional field
price Decimal @db.Decimal(10, 2)
inStock Boolean @default(true)
quantity Int @default(0)
tags String[] // Array field (PostgreSQL)
metadata Json? // JSON field
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([sku])
@@index([name, inStock])
}
### Relations
**One-to-Many:**
model User {
id String @id @default(cuid())
posts Post[]
}
model Post {
id String @id @default(cuid())
author User @relation(fields: [authorId], references: [id])
authorId String
@@index([authorId])
}
**Many-to-Many:**
model Post {
id String @id @default(cuid())
categories Category[] @relation("PostCategories")
}
model Category {
id String @id @default(cuid())
name String @unique
posts Post[] @relation("PostCategories")
}
**One-to-One:**
model User {
id String @id @default(cuid())
profile Profile?
}
model Profile {
id String @id @default(cuid())
bio String
user User @relation(fields: [userId], references: [id])
userId String @unique
}
**Self-Relations:**
model User {
id String @id @default(cuid())
following User[] @relation("UserFollows")
followers User[] @relation("UserFollows")
}
## Client Operations
### Nested Writes
// Create with nested relations
const user = await prisma.user.create({
data: {
email: 'bob@example.com',
profile: {
create: { bio: 'Software Engineer' }
},
posts: {
create: [
{ title: 'Post 1', content: 'Content 1' },
{ title: 'Post 2', content: 'Content 2' }
]
}
}
});
// Update with nested operations
await prisma.user.update({
where: { id: userId },
data: {
posts: {
create: { title: 'New Post' },
update: {
where: { id: postId },
data: { published: true }
},
delete: { id: oldPostId }
}
}
});
### Transactions
**Sequential (Interactive):**
await prisma.$transaction(async (tx) => {
const user = await tx.user.create({
data: { email: 'alice@example.com' }
});
await tx.post.create({
data: { title: 'Post', authorId: user.id }
});
// Rollback if error thrown
if (someCondition) {
throw new Error('Rollback transaction');
}
});
**Batch (Parallel):**
const [deletedPosts, updatedUser] = await prisma.$transaction([
prisma.post.deleteMany({ where: { published: false } }),
prisma.user.update({
where: { id: userId },
data: { name: 'Updated' }
})
]);
### Advanced Queries
**Aggregations:**
const result = await prisma.post.aggregate({
_count: { id: true },
_avg: { views: true },
_sum: { likes: true },
_max: { createdAt: true },
where: { published: true }
});
const grouped = await prisma.post.groupBy({
by: ['authorId'],
_count: { id: true },
_avg: { views: true },
having: { views: { _avg: { gt: 100 } } }
});
**Raw SQL:**
// Raw query
const users = await prisma.$queryRaw<User[]>
SELECT * FROM "User" WHERE email LIKE ${%${search}%}
;
// Execute
await prisma.$executeRaw
UPDATE "Post" SET views = views + 1 WHERE id = ${postId}
;
## Migrations
### Development Workflow
Create and apply migration
npx prisma migrate dev --name add_user_role
Reset database (WARNING: deletes all data)
npx prisma migrate reset
View migration status
npx prisma migrate status
### Production Deployment
Apply pending migrations
npx prisma migrate deploy
Generate client (in CI/CD)
npx prisma generate
### Schema Prototyping
Push schema without migrations (dev only)
npx prisma db push
Pull schema from existing database
npx prisma db pull
## Integration Patterns
### Next.js App Router
// lib/prisma.ts
import { PrismaClient } from '@prisma/client';
const globalForPrisma = globalThis as unknown as {
prisma: PrismaClient | undefined;
};
export const prisma = globalForPrisma.prisma ?? new PrismaClient();
if (process.env.NODE_ENV !== 'production') {
globalForPrisma.prisma = prisma;
}
**Server Component:**
// app/users/page.tsx
import { prisma } from '@/lib/prisma';
export default async function UsersPage() {
const users = await prisma.user.findMany({
include: { posts: { take: 5 } }
});
return (
<ul>
{users.map(u => (
<li key={u.id}>{u.name} - {u.posts.length} posts</li>
))}
</ul>
);
}
**Server Action:**
// app/actions.ts
'use server';
import { prisma } from '@/lib/prisma';
import { revalidatePath } from 'next/cache';
export async function createPost(formData: FormData) {
const title = formData.get('title') as string;
const authorId = formData.get('authorId') as string;
await prisma.post.create({
data: { title, authorId }
});
revalidatePath('/posts');
}
### Node.js Middleware
import { PrismaClient } from '@prisma/client';
import express from 'express';
const app = express();
const prisma = new PrismaClient();
app.get('/users/:id', async (req, res) => {
const user = await prisma.user.findUnique({
where: { id: req.params.id },
include: { posts: true }
});
if (!user) return res.status(404).json({ error: 'Not found' });
res.json(user);
});
app.listen(3000);
## Performance Optimization
### Query Optimization
// ❌ N+1 queries
const users = await prisma.user.findMany();
for (const user of users) {
const posts = await prisma.post.findMany({
where: { authorId: user.id }
});
}
// ✅ Single query with include
const users = await prisma.user.findMany({
include: { posts: true }
});
// ✅ Select specific fields
const users = await prisma.user.findMany({
select: { id: true, email: true, posts: { select: { title: true } } }
});
### Pagination
// Cursor-based (recommended for large datasets)
const posts = await prisma.post.findMany({
take: 10,
cursor: lastPostId ? { id: lastPostId } : undefined,
skip: lastPostId ? 1 : 0,
orderBy: { createdAt: 'desc' }
});
// Offset-based (simple but slower)
const posts = await prisma.post.findMany({
skip: (page - 1) * pageSize,
take: pageSize
});
### Connection Pooling
// schema.prisma
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
// Connection pool settings
directUrl = env("DIRECT_URL")
// Serverless connection limit
relationMode = "prisma" // For PlanetScale, Neon
}
.env
DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=10&pool_timeout=20"
## Prisma Studio
Launch visual database browser
npx prisma studio
**Features:**
- Visual data browser and editor
- Create, read, update, delete records
- Filter and search data
- View relations visually
- Runs on `localhost:5555`
## Prisma vs Drizzle
Feature
**Prisma**
**Drizzle**
Schema Definition
Custom DSL
TypeScript code
Type Safety
Generated types
Inferred types
Migrations
Built-in (migrate)
drizzle-kit
Query Builder
Fluent API
SQL-like builders
Relations
Automatic
Manual joins
Studio
Built-in GUI
No GUI
Bundle Size
~300kB
~50kB
Raw SQL
Supported
First-class
Edge Runtime
Limited
Full support
Learning Curve
Moderate
Steeper
**Best For**
Full-stack apps, rapid development, teams
Edge functions, SQL experts, bundle-sensitive
**Choose Prisma when:**
- Team prefers schema-first development
- Need visual database tools (Studio)
- Want automatic relation handling
- Building full-stack monoliths
- Rapid prototyping and migrations
**Choose Drizzle when:**
- Need minimal bundle size (edge functions)
- Prefer SQL-like syntax
- Edge runtime deployment (Cloudflare Workers)
- Want full control over SQL generation
- Team has strong SQL expertise
## Best Practices
- **Singleton Pattern** - Reuse `PrismaClient` instance (especially in dev)
- **Connection Management** - Configure pool size for serverless
- **Select Specific Fields** - Use `select` to reduce payload size
- **Use Transactions** - For multi-step operations requiring atomicity
- **Index Strategically** - Add `@@index` on frequently queried fields
- **Migration Discipline** - Never edit migrations after deployment
- **Schema Versioning** - Use descriptive migration names
- **Soft Deletes** - Add `deletedAt` field instead of hard deletes
- **Validate Before Saving** - Use Zod schemas before Prisma operations
- **Monitor Queries** - Use `prisma.$on('query')` for logging
## Common Pitfalls
❌ **Creating multiple PrismaClient instances:**
// WRONG - creates connection leak
function getUser() {
const prisma = new PrismaClient(); // New instance every call
return prisma.user.findMany();
}
// CORRECT - singleton pattern
const prisma = new PrismaClient();
function getUser() {
return prisma.user.findMany();
}
❌ **N+1 queries:**
// WRONG - multiple queries
const users = await prisma.user.findMany();
for (const user of users) {
user.posts = await prisma.post.findMany({ where: { authorId: user.id } });
}
// CORRECT - single query with include
const users = await prisma.user.findMany({ include: { posts: true } });
❌ **Missing transaction for multi-step operations:**
// WRONG - not atomic, can leave inconsistent state
await prisma.user.delete({ where: { id: userId } });
await prisma.post.deleteMany({ where: { authorId: userId } }); // May fail
// CORRECT - atomic transaction
await prisma.$transaction([
prisma.post.deleteMany({ where: { authorId: userId } }),
prisma.user.delete({ where: { id: userId } })
]);