Prisma ORM Skill

Overview

Prisma is a next-generation Node.js and TypeScript ORM that provides:

Prisma Schema: Declarative data modeling language
Prisma Migrate: Database migration system
Prisma Client: Auto-generated, type-safe query builder
Prisma Studio: GUI for database exploration

Quick Start

bash

# Initialize Prisma in a project
npm install prisma --save-dev
npm install @prisma/client
npx prisma init

# Common commands
npx prisma generate      # Generate Prisma Client
npx prisma migrate dev   # Create and apply migrations
npx prisma db push       # Push schema without migrations (dev)
npx prisma studio        # Open database GUI
npx prisma db seed       # Run seed script

Schema Design

Basic Model Structure

prisma

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

datasource db {
  provider = "postgresql" // mysql, sqlite, sqlserver, mongodb
  url      = env("DATABASE_URL")
}

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

  @@index([email])
  @@map("users") // Custom table name
}

Field Types & Modifiers

prisma

model Example {
  // Scalar types
  id          Int       @id @default(autoincrement())
  uuid        String    @id @default(uuid())
  cuid        String    @id @default(cuid())
  name        String    @db.VarChar(255)
  content     String    @db.Text
  count       Int       @default(0)
  price       Decimal   @db.Decimal(10, 2)
  rating      Float
  isActive    Boolean   @default(true)
  data        Json
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  
  // Optional field
  deletedAt   DateTime?
  
  // Unique constraint
  slug        String    @unique
  
  // Composite unique
  @@unique([categoryId, slug])
  
  // Composite index
  @@index([createdAt, isActive])
}

Enums

prisma

enum Role {
  USER
  ADMIN
  MODERATOR
}

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
}

model User {
  id   String @id @default(cuid())
  role Role   @default(USER)
}

Relations

prisma

// 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], onDelete: Cascade)
  userId String @unique
}

// One-to-Many
model User {
  id    String @id @default(cuid())
  posts Post[]
}

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

// Many-to-Many (implicit)
model Post {
  id         String     @id @default(cuid())
  categories Category[]
}

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

// Many-to-Many (explicit - for extra fields)
model Post {
  id       String        @id @default(cuid())
  tags     PostTag[]
}

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

model PostTag {
  post      Post     @relation(fields: [postId], references: [id])
  postId    String
  tag       Tag      @relation(fields: [tagId], references: [id])
  tagId     String
  assignedAt DateTime @default(now())
  assignedBy String

  @@id([postId, tagId])
}

// Self-relation
model Category {
  id       String     @id @default(cuid())
  name     String
  parent   Category?  @relation("CategoryHierarchy", fields: [parentId], references: [id])
  parentId String?
  children Category[] @relation("CategoryHierarchy")
}

Migrations

Development Workflow

bash

# Create migration from schema changes
npx prisma migrate dev --name add_user_table

# Apply migrations without creating new ones
npx prisma migrate deploy

# Reset database (drops all data!)
npx prisma migrate reset

# Check migration status
npx prisma migrate status

# Resolve failed migration
npx prisma migrate resolve --applied "20240115120000_migration_name"

Migration File Structure

prisma/
├── schema.prisma
└── migrations/
    ├── 20240115120000_init/
    │   └── migration.sql
    ├── 20240116080000_add_posts/
    │   └── migration.sql
    └── migration_lock.toml

Custom SQL in Migrations

sql

-- prisma/migrations/20240115120000_custom/migration.sql
-- Add custom SQL after Prisma's generated SQL

-- Create extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Add check constraint
ALTER TABLE "orders" ADD CONSTRAINT "positive_amount" CHECK (amount > 0);

-- Create partial index
CREATE INDEX "active_users_idx" ON "users" (email) WHERE "deletedAt" IS NULL;

Prisma Client Queries

Setup & Instantiation

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: process.env.NODE_ENV === 'development' 
    ? ['query', 'error', 'warn'] 
    : ['error'],
});

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

export default prisma;

CRUD Operations

typescript

import { prisma } from './lib/prisma';

// CREATE
const user = await prisma.user.create({
  data: {
    email: 'user@example.com',
    name: 'John Doe',
    profile: {
      create: { bio: 'Hello world' }, // Nested create
    },
  },
});

// Create many
const users = await prisma.user.createMany({
  data: [
    { email: 'user1@example.com', name: 'User 1' },
    { email: 'user2@example.com', name: 'User 2' },
  ],
  skipDuplicates: true,
});

// READ - Find unique
const user = await prisma.user.findUnique({
  where: { id: 'cuid123' },
});

// Find unique or throw
const user = await prisma.user.findUniqueOrThrow({
  where: { email: 'user@example.com' },
});

// Find first
const user = await prisma.user.findFirst({
  where: { role: 'ADMIN' },
  orderBy: { createdAt: 'desc' },
});

// Find many with pagination
const users = await prisma.user.findMany({
  where: { isActive: true },
  orderBy: { name: 'asc' },
  skip: 0,
  take: 10,
});

// UPDATE
const user = await prisma.user.update({
  where: { id: 'cuid123' },
  data: { name: 'Updated Name' },
});

// Upsert (create or update)
const user = await prisma.user.upsert({
  where: { email: 'user@example.com' },
  update: { name: 'Updated Name' },
  create: { email: 'user@example.com', name: 'New User' },
});

// Update many
const result = await prisma.user.updateMany({
  where: { role: 'USER' },
  data: { isActive: true },
});

// DELETE
const user = await prisma.user.delete({
  where: { id: 'cuid123' },
});

// Delete many
const result = await prisma.user.deleteMany({
  where: { deletedAt: { not: null } },
});

Filtering

typescript

// Comparison operators
const users = await prisma.user.findMany({
  where: {
    age: { gt: 18 },           // greater than
    score: { gte: 90 },        // greater than or equal
    price: { lt: 100 },        // less than
    count: { lte: 10 },        // less than or equal
    status: { not: 'DELETED' }, // not equal
    role: { in: ['ADMIN', 'MODERATOR'] },
    type: { notIn: ['SPAM', 'BOT'] },
  },
});

// String filters
const users = await prisma.user.findMany({
  where: {
    email: { contains: '@example.com' },
    name: { startsWith: 'John' },
    bio: { endsWith: 'developer' },
    // Case insensitive (PostgreSQL, MySQL)
    email: { contains: 'JOHN', mode: 'insensitive' },
  },
});

// Logical operators
const users = await prisma.user.findMany({
  where: {
    AND: [
      { isActive: true },
      { role: 'ADMIN' },
    ],
    OR: [
      { email: { contains: '@company.com' } },
      { role: 'ADMIN' },
    ],
    NOT: {
      deletedAt: { not: null },
    },
  },
});

// Relation filters
const posts = await prisma.post.findMany({
  where: {
    author: {
      email: { contains: '@example.com' },
    },
    comments: {
      some: { isApproved: true },  // At least one
      every: { isSpam: false },    // All must match
      none: { isSpam: true },      // None must match
    },
  },
});

// Date filters
const recentPosts = await prisma.post.findMany({
  where: {
    createdAt: {
      gte: new Date('2024-01-01'),
      lt: new Date('2024-02-01'),
    },
  },
});

// Null checks
const users = await prisma.user.findMany({
  where: {
    deletedAt: null,       // IS NULL
    profile: { isNot: null }, // IS NOT NULL (relation)
  },
});

Select & Include (Relations)

typescript

// Select specific fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    name: true,
    // Nested select
    posts: {
      select: { id: true, title: true },
      take: 5,
    },
  },
});

// Include relations
const user = await prisma.user.findUnique({
  where: { id: 'cuid123' },
  include: {
    profile: true,
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 10,
      include: {
        comments: {
          take: 3,
          orderBy: { createdAt: 'desc' },
        },
      },
    },
  },
});

// Count relations
const usersWithCounts = await prisma.user.findMany({
  include: {
    _count: {
      select: { posts: true, followers: true },
    },
  },
});

Aggregations

typescript

// Count
const userCount = await prisma.user.count({
  where: { isActive: true },
});

// Aggregate
const stats = await prisma.order.aggregate({
  _sum: { amount: true },
  _avg: { amount: true },
  _min: { amount: true },
  _max: { amount: true },
  _count: true,
  where: { status: 'COMPLETED' },
});

// Group by
const ordersByStatus = await prisma.order.groupBy({
  by: ['status'],
  _count: true,
  _sum: { amount: true },
  having: {
    amount: { _sum: { gt: 1000 } },
  },
});

// Distinct
const uniqueCategories = await prisma.post.findMany({
  distinct: ['categoryId'],
  select: { categoryId: true },
});

Raw Queries

typescript

// Raw query (typed result)
const users = await prisma.$queryRaw<User[]>`
  SELECT * FROM users 
  WHERE email LIKE ${`%@example.com`}
  ORDER BY created_at DESC
  LIMIT 10
`;

// Parameterized queries (safe from SQL injection)
const email = 'user@example.com';
const user = await prisma.$queryRaw`
  SELECT * FROM users WHERE email = ${email}
`;

// Raw execute (for INSERT, UPDATE, DELETE)
const result = await prisma.$executeRaw`
  UPDATE users SET last_login = NOW() WHERE id = ${userId}
`;

// Using Prisma.sql for dynamic queries
import { Prisma } from '@prisma/client';

const orderBy = Prisma.sql`ORDER BY created_at DESC`;
const users = await prisma.$queryRaw`
  SELECT * FROM users ${orderBy}
`;

// Raw with joins
const postsWithAuthors = await prisma.$queryRaw`
  SELECT p.*, u.name as author_name
  FROM posts p
  JOIN users u ON p.author_id = u.id
  WHERE p.published = true
`;

Transactions

typescript

// Sequential operations (auto-rollback on error)
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { email: 'user@example.com' } }),
  prisma.post.create({ data: { title: 'Hello', authorId: 'existing-id' } }),
]);

// Interactive transaction (full control)
const result = await prisma.$transaction(async (tx) => {
  // Decrement sender balance
  const sender = await tx.account.update({
    where: { id: senderId },
    data: { balance: { decrement: amount } },
  });

  if (sender.balance < 0) {
    throw new Error('Insufficient funds');
  }

  // Increment recipient balance
  const recipient = await tx.account.update({
    where: { id: recipientId },
    data: { balance: { increment: amount } },
  });

  // Create transaction record
  const transaction = await tx.transaction.create({
    data: { senderId, recipientId, amount },
  });

  return transaction;
}, {
  maxWait: 5000,    // Max time to acquire connection
  timeout: 10000,   // Max transaction duration
  isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
});

// Nested writes (implicit transaction)
const user = await prisma.user.create({
  data: {
    email: 'user@example.com',
    profile: { create: { bio: 'Hello' } },
    posts: {
      create: [
        { title: 'Post 1' },
        { title: 'Post 2' },
      ],
    },
  },
  include: { profile: true, posts: true },
});

Connection Pooling

Configuration

prisma

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  // Connection pool settings via URL params
  // postgresql://user:pass@host:5432/db?connection_limit=5&pool_timeout=10
}

typescript

// Programmatic pool configuration
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
});

// Graceful shutdown
process.on('beforeExit', async () => {
  await prisma.$disconnect();
});

Serverless / Edge

typescript

// For serverless environments (Vercel, AWS Lambda)
import { PrismaClient } from '@prisma/client';
import { PrismaPg } from '@prisma/adapter-pg';
import { Pool } from 'pg';

// Use connection pooler like PgBouncer or Prisma Accelerate
const connectionString = process.env.DATABASE_URL;

// With Prisma Accelerate
const prisma = new PrismaClient({
  datasourceUrl: process.env.ACCELERATE_URL,
});

TypeScript Integration

Generated Types

typescript

import { 
  User, 
  Post, 
  Prisma,
  Role 
} from '@prisma/client';

// Use generated types
function processUser(user: User): void {
  console.log(user.email);
}

// Input types for create/update
type UserCreateInput = Prisma.UserCreateInput;
type UserUpdateInput = Prisma.UserUpdateInput;

// Where clause types
type UserWhereInput = Prisma.UserWhereInput;
type UserWhereUniqueInput = Prisma.UserWhereUniqueInput;

// Include/Select types
type UserWithPosts = Prisma.UserGetPayload<{
  include: { posts: true };
}>;

// Custom payload type
type UserSummary = Prisma.UserGetPayload<{
  select: {
    id: true;
    email: true;
    name: true;
    _count: { select: { posts: true } };
  };
}>;

Type-Safe Service Layer

typescript

import { Prisma, User } from '@prisma/client';
import { prisma } from './lib/prisma';

// Repository pattern with types
class UserRepository {
  async findById(id: string): Promise<User | null> {
    return prisma.user.findUnique({ where: { id } });
  }

  async findMany(
    where?: Prisma.UserWhereInput,
    orderBy?: Prisma.UserOrderByWithRelationInput,
    pagination?: { skip?: number; take?: number }
  ): Promise<User[]> {
    return prisma.user.findMany({
      where,
      orderBy,
      ...pagination,
    });
  }

  async create(data: Prisma.UserCreateInput): Promise<User> {
    return prisma.user.create({ data });
  }

  async update(id: string, data: Prisma.UserUpdateInput): Promise<User> {
    return prisma.user.update({ where: { id }, data });
  }

  async delete(id: string): Promise<User> {
    return prisma.user.delete({ where: { id } });
  }
}

export const userRepository = new UserRepository();

Validation with Zod

typescript

import { z } from 'zod';
import { Prisma } from '@prisma/client';

// Zod schema matching Prisma model
const UserCreateSchema = z.object({
  email: z.string().email(),
  name: z.string().min(2).max(100).optional(),
  role: z.enum(['USER', 'ADMIN', 'MODERATOR']).default('USER'),
}) satisfies z.ZodType<Prisma.UserCreateInput>;

// Usage
function createUser(input: unknown) {
  const validated = UserCreateSchema.parse(input);
  return prisma.user.create({ data: validated });
}

Testing Patterns

Test Setup

typescript

// test/setup.ts
import { PrismaClient } from '@prisma/client';
import { execSync } from 'child_process';
import { randomUUID } from 'crypto';

const prisma = new PrismaClient();

beforeAll(async () => {
  // Push schema to test database
  execSync('npx prisma db push --force-reset', {
    env: { ...process.env, DATABASE_URL: process.env.TEST_DATABASE_URL },
  });
});

beforeEach(async () => {
  // Clean tables before each test
  const tablenames = await prisma.$queryRaw<{ tablename: string }[]>`
    SELECT tablename FROM pg_tables WHERE schemaname='public'
  `;
  
  for (const { tablename } of tablenames) {
    if (tablename !== '_prisma_migrations') {
      await prisma.$executeRawUnsafe(
        `TRUNCATE TABLE "public"."${tablename}" CASCADE;`
      );
    }
  }
});

afterAll(async () => {
  await prisma.$disconnect();
});

export { prisma };

Test Factories

typescript

// test/factories/user.ts
import { faker } from '@faker-js/faker';
import { Prisma } from '@prisma/client';
import { prisma } from '../setup';

export function buildUser(
  overrides?: Partial<Prisma.UserCreateInput>
): Prisma.UserCreateInput {
  return {
    email: faker.internet.email(),
    name: faker.person.fullName(),
    role: 'USER',
    ...overrides,
  };
}

export async function createUser(
  overrides?: Partial<Prisma.UserCreateInput>
) {
  return prisma.user.create({
    data: buildUser(overrides),
  });
}

export async function createUsers(count: number) {
  return Promise.all(
    Array.from({ length: count }, () => createUser())
  );
}

Integration Tests

typescript

// test/user.test.ts
import { prisma, createUser } from './setup';
import { userService } from '../src/services/user';

describe('UserService', () => {
  describe('findByEmail', () => {
    it('returns user when found', async () => {
      const created = await createUser({ email: 'test@example.com' });
      
      const found = await userService.findByEmail('test@example.com');
      
      expect(found).toMatchObject({
        id: created.id,
        email: 'test@example.com',
      });
    });

    it('returns null when not found', async () => {
      const found = await userService.findByEmail('nonexistent@example.com');
      
      expect(found).toBeNull();
    });
  });

  describe('createWithProfile', () => {
    it('creates user and profile in transaction', async () => {
      const result = await userService.createWithProfile({
        email: 'new@example.com',
        name: 'New User',
        bio: 'Hello world',
      });

      expect(result.profile).not.toBeNull();
      expect(result.profile?.bio).toBe('Hello world');
    });

    it('rolls back on profile creation failure', async () => {
      await expect(
        userService.createWithProfile({
          email: 'test@example.com',
          name: 'Test',
          bio: null as any, // Invalid
        })
      ).rejects.toThrow();

      const user = await prisma.user.findUnique({
        where: { email: 'test@example.com' },
      });
      expect(user).toBeNull();
    });
  });
});

Mocking Prisma

typescript

// test/mocks/prisma.ts
import { PrismaClient } from '@prisma/client';
import { mockDeep, DeepMockProxy } from 'jest-mock-extended';

export type MockPrismaClient = DeepMockProxy<PrismaClient>;

export const createMockPrisma = (): MockPrismaClient => {
  return mockDeep<PrismaClient>();
};

// Usage in tests
import { createMockPrisma } from './mocks/prisma';

describe('UserService (unit)', () => {
  const mockPrisma = createMockPrisma();
  const userService = new UserService(mockPrisma);

  it('calls prisma.user.findUnique', async () => {
    mockPrisma.user.findUnique.mockResolvedValue({
      id: '1',
      email: 'test@example.com',
      name: 'Test',
      role: 'USER',
      createdAt: new Date(),
      updatedAt: new Date(),
    });

    const result = await userService.findById('1');

    expect(mockPrisma.user.findUnique).toHaveBeenCalledWith({
      where: { id: '1' },
    });
    expect(result?.email).toBe('test@example.com');
  });
});

Best Practices

Performance

typescript

// Use select to limit fields
const users = await prisma.user.findMany({
  select: { id: true, email: true }, // Only fetch needed fields
});

// Batch operations
const users = await prisma.user.createMany({
  data: usersToCreate,
  skipDuplicates: true,
});

// Use cursor pagination for large datasets
const users = await prisma.user.findMany({
  take: 10,
  cursor: { id: lastUserId },
  skip: 1, // Skip the cursor
});

Error Handling

typescript

import { Prisma } from '@prisma/client';

try {
  await prisma.user.create({ data });
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    if (error.code === 'P2002') {
      throw new Error('Email already exists');
    }
    if (error.code === 'P2025') {
      throw new Error('Record not found');
    }
  }
  throw error;
}

Soft Deletes

typescript

// Middleware for soft deletes
prisma.$use(async (params, next) => {
  if (params.model === 'User') {
    if (params.action === 'delete') {
      params.action = 'update';
      params.args.data = { deletedAt: new Date() };
    }
    if (params.action === 'findMany' || params.action === 'findFirst') {
      params.args.where = { ...params.args.where, deletedAt: null };
    }
  }
  return next(params);
});

Common Error Codes

Code	Description	Solution
P2002	Unique constraint violation	Handle duplicate entries
P2003	Foreign key constraint	Ensure related records exist
P2025	Record not found	Validate before update/delete
P2024	Connection pool timeout	Increase pool size/timeout
P1001	Can't reach database	Check connection string
P1008	Operations timed out	Optimize query or increase timeout

Search AI Tools

Install this agent skill to your Project

SKILL.md

Prisma ORM Skill

Overview

Quick Start

Schema Design

Basic Model Structure

Field Types & Modifiers

Enums

Relations

Migrations

Development Workflow

Migration File Structure

Custom SQL in Migrations

Prisma Client Queries

Setup & Instantiation

CRUD Operations

Filtering

Select & Include (Relations)

Aggregations

Raw Queries

Transactions

Connection Pooling

Configuration

Serverless / Edge

TypeScript Integration

Generated Types

Type-Safe Service Layer

Validation with Zod

Testing Patterns

Test Setup

Test Factories

Integration Tests

Mocking Prisma

Best Practices

Performance

Error Handling

Soft Deletes

Common Error Codes