prisma-orm

name	prisma-orm
description	Type-safe database access with Prisma ORM. Covers schema design, migrations, relations, queries, and TypeScript integration. Use when working with Prisma, database modeling, or building type-safe data layers for Node.js/TypeScript projects.

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

# 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/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

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

enum Role {
  USER
  ADMIN
  MODERATOR
}

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
}

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

Relations

// 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

# 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

-- 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

// 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

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

// CREATE
const user = await prisma.user.create({
  data: {
    email: '[e-mail]',
    name: 'John Doe',
    profile: {
      create: { bio: 'Hello world' }, // Nested create
    },
  },
});

// Create many
const users = await prisma.user.createMany({
  data: [
    { email: '[e-mail]', name: 'User 1' },
    { email: '[e-mail]', 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: '[e-mail]' },
});

// Find first
const user =  prisma..({
  : { :  },
  : { :  },
});


 users =  prisma..({
  : { :  },
  : { :  },
  : ,
  : ,
});


 user =  prisma..({
  : { :  },
  : { :  },
});


 user =  prisma..({
  : { :  },
  : { :  },
  : { : , :  },
});


 result =  prisma..({
  : { :  },
  : { :  },
});


 user =  prisma..({
  : { :  },
});


 result =  prisma..({
  : { : { :  } },
});

Filtering

// 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', :  },
  },
});


 users =  prisma..({
  : {
    : [
      { :  },
      { :  },
    ],
    : [
      { : { :  } },
      { :  },
    ],
    : {
      : { :  },
    },
  },
});


 posts =  prisma..({
  : {
    : {
      : { :  },
    },
    : {
      : { :  },  
      : { :  },    
      : { :  },      
    },
  },
});


 recentPosts =  prisma..({
  : {
    : {
      :  (),
      :  (),
    },
  },
});


 users =  prisma..({
  : {
    : ,       
    : { :  }, 
  },
});

Select & Include (Relations)

// 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({
  : {
    : {
      : { : , :  },
    },
  },
});

Aggregations

// 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'],
  : { :  },
});

---

Raw Queries

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

// Parameterized queries (safe from SQL injection)
const email = '[e-mail]';
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

// Sequential operations (auto-rollback on error)
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { email: '[e-mail]' } }),
  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..({
    : { senderId, recipientId, amount },
  });

   transaction;
}, {
  : ,    
  : ,   
  : ..,
});


 user =  prisma..({
  : {
    : ,
    : { : { :  } },
    : {
      : [
        { :  },
        { :  },
      ],
    },
  },
  : { : , :  },
});

---

Connection Pooling

Configuration

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
}

// 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

// 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

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;
    : ;
    : ;
    : { : { :  } };
  };
}>;

Type-Safe Service Layer

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 (: , : .): <> {
     prisma..({ : { id }, data });
  }

   (: ): <> {
     prisma..({ : { id } });
  }
}

  userRepository =  ();

Validation with Zod

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

// 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

// 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.(
    .({ : count },  ())
  );
}

Integration Tests

// 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: '[e-mail]' });
      
      const found = await userService.findByEmail('[e-mail]');
      
      expect(found).toMatchObject({
        id: created.id,
        email: '[e-mail]',
      });
    });

    it('returns null when not found', async () => {
      const found = await userService.findByEmail('[e-mail]');
      
      expect(found).toBeNull();
    });
  });

  describe('createWithProfile', () => {
    it('creates user and profile in transaction', async () => {
      const result =  userService.({
        : ,
        : ,
        : ,
      });

      (result.)..();
      (result.?.).();
    });

    (,  () => {
       (
        userService.({
          : ,
          : ,
          :   , 
        })
      )..();

       user =  prisma..({
        : { :  },
      });
      (user).();
    });
  });
});

Mocking Prisma

// 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: '[e-mail]',
      name: 'Test',
      role: 'USER',
      createdAt: new Date(),
      updatedAt:  (),
    });

     result =  userService.();

    (mockPrisma..).({
      : { :  },
    });
    (result?.).();
  });
});

---

Best Practices

Performance

// 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

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

// 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