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

1. Use cuid() or uuid() for IDs instead of autoincrement
2. Always include indexes for frequently queried fields
3. Use select/include to avoid over-fetching
4. Singleton pattern in Next.js to prevent connection exhaustion
5. Interactive transactions for complex operations
6. 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