Prisma ORM: Modern Database Toolkit for Node.js

What is Prisma?

Prisma is a next-generation ORM for Node.js and TypeScript. It provides type-safe database access, auto-generated queries, and a powerful migration system.

Why Prisma?

Traditional ORM	Prisma
Manual type definitions	Auto-generated types
String-based queries	Type-safe queries
Runtime errors	Compile-time safety
Complex syntax	Intuitive API

Getting Started

Installation

npm install prisma @prisma/client
npx prisma init

Schema Definition

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

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
  categories  Category[]
  tags        Tag[]
  comments    Comment[]
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt

  @@index([authorId])
}

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

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
  authorId  String
  createdAt DateTime @default(now())
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Migrations

# Create migration
npx prisma migrate dev --name init

# Apply to production
npx prisma migrate deploy

# Reset database
npx prisma migrate reset

# Generate client
npx prisma generate

CRUD Operations

Create

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

const prisma = new PrismaClient();

// Create single record
const user = await prisma.user.create({
  data: {
    email: 'john@example.com',
    name: 'John Doe',
    password: hashedPassword
  }
});

// Create with relations
const userWithProfile = await prisma.user.create({
  data: {
    email: 'jane@example.com',
    name: 'Jane Doe',
    password: hashedPassword,
    profile: {
      create: {
        bio: 'Software developer',
        avatar: 'https://example.com/avatar.jpg'
      }
    }
  },
  include: {
    profile: true
  }
});

// Create multiple
const users = await prisma.user.createMany({
  data: [
    { email: 'user1@example.com', password: 'hash1' },
    { email: 'user2@example.com', password: 'hash2' }
  ],
  skipDuplicates: true
});

// Create post with categories
const post = await prisma.post.create({
  data: {
    title: 'My First Post',
    content: 'Hello World!',
    author: { connect: { id: userId } },
    categories: {
      connectOrCreate: [
        {
          where: { name: 'Tech' },
          create: { name: 'Tech' }
        }
      ]
    }
  }
});

Read

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

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

// Find many
const users = await prisma.user.findMany({
  where: {
    role: 'USER',
    email: { contains: '@gmail.com' }
  },
  orderBy: { createdAt: 'desc' },
  skip: 0,
  take: 10
});

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

// Select specific fields
const userEmail = await prisma.user.findUnique({
  where: { id: userId },
  select: {
    email: true,
    name: true,
    posts: {
      select: { title: true }
    }
  }
});

// Complex filters
const posts = await prisma.post.findMany({
  where: {
    OR: [
      { title: { contains: 'prisma', mode: 'insensitive' } },
      { content: { contains: 'prisma', mode: 'insensitive' } }
    ],
    AND: [
      { published: true },
      { author: { role: 'ADMIN' } }
    ]
  }
});

// Count
const userCount = await prisma.user.count({
  where: { role: 'USER' }
});

// Aggregate
const stats = await prisma.post.aggregate({
  _count: { id: true },
  _avg: { views: true },
  where: { published: true }
});

// Group by
const postsByAuthor = await prisma.post.groupBy({
  by: ['authorId'],
  _count: { id: true },
  orderBy: { _count: { id: 'desc' } }
});

Update

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

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

// Upsert
const user = await prisma.user.upsert({
  where: { email: 'john@example.com' },
  update: { name: 'John Updated' },
  create: {
    email: 'john@example.com',
    name: 'John Doe',
    password: hashedPassword
  }
});

// Update relations
const post = await prisma.post.update({
  where: { id: postId },
  data: {
    categories: {
      set: [],  // Remove all
      connect: [{ id: categoryId }]  // Add new
    }
  }
});

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

Delete

// Delete single
const user = await prisma.user.delete({
  where: { id: userId }
});

// Delete many
const result = await prisma.user.deleteMany({
  where: {
    createdAt: { lt: new Date('2023-01-01') },
    role: 'USER'
  }
});

// Soft delete (using middleware)
const user = await prisma.user.update({
  where: { id: userId },
  data: { deletedAt: new Date() }
});

Transactions

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

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

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

  return { sender, recipient };
});

// Sequential transaction
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: userData }),
  prisma.post.create({ data: postData })
]);

// Transaction options
const result = await prisma.$transaction(
  async (tx) => {
    // ...
  },
  {
    maxWait: 5000,
    timeout: 10000,
    isolationLevel: 'Serializable'
  }
);

Raw Queries

// Raw query
const users = await prisma.$queryRaw`
  SELECT * FROM "User"
  WHERE email LIKE ${`%@gmail.com`}
`;

// Raw execute
await prisma.$executeRaw`
  UPDATE "User"
  SET "updatedAt" = NOW()
  WHERE id = ${userId}
`;

// Tagged template (safe)
const email = 'john@example.com';
const user = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE email = ${email}
`;

Middleware

// Logging middleware
prisma.$use(async (params, next) => {
  const before = Date.now();
  const result = await next(params);
  const after = Date.now();

  console.log(`Query ${params.model}.${params.action} took ${after - before}ms`);
  return result;
});

// Soft delete middleware
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.args.where = {
        ...params.args.where,
        deletedAt: null
      };
    }
  }
  return next(params);
});

Pagination

// Offset pagination
async function getUsers(page: number, pageSize: number) {
  const [users, total] = await Promise.all([
    prisma.user.findMany({
      skip: (page - 1) * pageSize,
      take: pageSize,
      orderBy: { createdAt: 'desc' }
    }),
    prisma.user.count()
  ]);

  return {
    data: users,
    meta: {
      total,
      page,
      pageSize,
      totalPages: Math.ceil(total / pageSize)
    }
  };
}

// Cursor pagination
async function getPosts(cursor?: string, take: number = 10) {
  const posts = await prisma.post.findMany({
    take: take + 1,
    cursor: cursor ? { id: cursor } : undefined,
    orderBy: { createdAt: 'desc' }
  });

  const hasMore = posts.length > take;
  const data = hasMore ? posts.slice(0, -1) : posts;

  return {
    data,
    nextCursor: hasMore ? data[data.length - 1].id : null
  };
}

Best Practices

Connection Management

// Singleton pattern
import { PrismaClient } from '@prisma/client';

declare global {
  var prisma: PrismaClient | undefined;
}

export const prisma = globalThis.prisma ?? new PrismaClient({
  log: ['query', 'error', 'warn']
});

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

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

Error Handling

import { PrismaClientKnownRequestError } from '@prisma/client/runtime/library';

try {
  const user = await prisma.user.create({
    data: { email: 'john@example.com', password: 'hash' }
  });
} catch (error) {
  if (error instanceof PrismaClientKnownRequestError) {
    if (error.code === 'P2002') {
      throw new Error('Email already exists');
    }
  }
  throw error;
}

Summary

Operation	Method
Create	create, createMany
Read	findUnique, findMany, findFirst
Update	update, updateMany, upsert
Delete	delete, deleteMany
Aggregate	count, aggregate, groupBy
Transaction	$transaction

Prisma provides a powerful, type-safe way to work with databases in Node.js applications.