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 init

Basic Workflow

# 1. Define schema
# Edit prisma/schema.prisma

# 2. 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 } })
]);

Red Flags

Stop and reconsider if:

Creating new PrismaClient in request handlers
Not using transactions for multi-step operations
Missing indexes on foreign keys or frequently queried fields
Using findMany without pagination on large tables
Fetching entire objects when only specific fields needed
Not handling connection errors in production
Using migrate dev in production (use migrate deploy)

Integration with Other Skills

typescript-core: Zod validation, type safety patterns
nextjs-core: Server Actions, Server Components integration
nextjs-v16: App Router data fetching, caching
database-migration: Safe schema evolution patterns

Resources

Documentation: https://www.prisma.io/docs
Schema Reference: https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference
Client API: https://www.prisma.io/docs/reference/api-reference/prisma-client-reference
Examples: https://github.com/prisma/prisma-examples

Related Skills

When using Prisma, these skills enhance your workflow:

drizzle: Drizzle ORM as lightweight alternative to Prisma
typescript: TypeScript best practices for Prisma generated types
nextjs: Prisma with Next.js: connection pooling, edge runtime considerations
test-driven-development: Testing Prisma models, migrations, and queries

[Full documentation available in these skills if deployed in your bundle]

prisma-orm