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

Name: prisma-orm
Availability: InStock

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.$queryRawSELECT * FROM "User" WHERE email LIKE ${%${search}%};

// Execute await prisma.$executeRawUPDATE "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 (

{u.name} - {u.posts.length} posts

); }

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]

安装