Prisma ORM

Prisma is a next-generation ORM for Node.js and TypeScript. It provides type-safe database access, auto-generated migrations, and an intuitive data modeling language.

Core Concepts

Schema Definition (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? password String role Role @default(USER) posts Post[] profile Profile? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt

@@index([email]) }

model Profile { id String @id @default(cuid()) bio String? avatar String? user User @relation(fields: [userId], references: [id], onDelete: Cascade) userId String @unique }

model Post { id String @id @default(cuid()) title String content String? published Boolean @default(false) author User @relation(fields: [authorId], references: [id]) authorId String tags Tag[] comments Comment[] createdAt DateTime @default(now()) updatedAt DateTime @updatedAt

@@index([authorId]) @@index([published, createdAt]) }

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

model Comment { id String @id @default(cuid()) content String post Post @relation(fields: [postId], references: [id], onDelete: Cascade) postId String createdAt DateTime @default(now()) }

enum Role { USER ADMIN MODERATOR }

Client Setup

Singleton Pattern (Next.js)

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

With Extensions

const prisma = new PrismaClient().$extends({ result: { user: { fullName: { needs: { firstName: true, lastName: true }, compute(user) { return ${user.firstName} ${user.lastName}; }, }, }, }, });

CRUD Operations

Create

// Single record const user = await prisma.user.create({ data: { email: 'user@example.com', name: 'John Doe', profile: { create: { bio: 'Developer' }, }, }, include: { profile: true }, });

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

// With nested creation const post = await prisma.post.create({ data: { title: 'Hello World', author: { connect: { id: userId } }, tags: { connectOrCreate: [ { where: { name: 'tech' }, create: { name: 'tech' } }, { where: { name: 'news' }, create: { name: 'news' } }, ], }, }, });

Read

// Find unique const user = await prisma.user.findUnique({ where: { email: 'user@example.com' }, include: { posts: true }, });

// Find many with filtering const posts = await prisma.post.findMany({ where: { published: true, author: { email: { contains: '@example.com' } }, OR: [{ title: { contains: 'prisma' } }, { content: { contains: 'prisma' } }], }, orderBy: { createdAt: 'desc' }, take: 10, skip: 0, select: { id: true, title: true, author: { select: { name: true } }, }, });

// Pagination const [posts, total] = await Promise.all([ prisma.post.findMany({ take: 10, skip: (page - 1) * 10, orderBy: { createdAt: 'desc' }, }), prisma.post.count(), ]);

Update

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

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

// Update many const result = await prisma.post.updateMany({ where: { authorId: userId }, data: { published: false }, });

// Atomic operations const post = await prisma.post.update({ where: { id: postId }, data: { views: { increment: 1 }, likes: { decrement: 1 }, }, });

Delete

// Single delete await prisma.user.delete({ where: { id: userId }, });

// Delete many await prisma.post.deleteMany({ where: { published: false, createdAt: { lt: new Date('2024-01-01') }, }, });

Transactions

Sequential Operations

const [posts, totalPosts, users] = await prisma.$transaction([ prisma.post.findMany({ where: { published: true } }), prisma.post.count({ where: { published: true } }), prisma.user.findMany(), ]);

Interactive Transactions

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 receiver balance
const receiver = await tx.account.update({
  where: { id: receiverId },
  data: { balance: { increment: amount } },
});

return { sender, receiver };

}, { maxWait: 5000, timeout: 10000, } );

Relations

One-to-One

model User { id String @id profile Profile? }

model Profile { id String @id user User @relation(fields: [userId], references: [id]) userId String @unique }

One-to-Many

model User { id String @id posts Post[] }

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

Many-to-Many

model Post { id String @id tags Tag[] }

model Tag { id String @id posts Post[] }

Migrations

Create migration

npx prisma migrate dev --name init

Apply migrations (production)

npx prisma migrate deploy

Reset database

npx prisma migrate reset

Generate client

npx prisma generate

Open Prisma Studio

npx prisma studio

Best Practices

• Use cuid() or uuid() for IDs instead of autoincrement

• Always include indexes for frequently queried fields

• Use select/include to avoid over-fetching

• Singleton pattern in Next.js to prevent connection exhaustion

• Interactive transactions for complex operations

• Soft deletes with deletedAt field for important data

References

• references/schema.md - Schema patterns

• references/query-patterns.md - Advanced queries

• references/migrations.md - Migration strategies