Prisma ORM Tutorial: The Complete Guide for Modern Database Development

Prisma has revolutionized how developers interact with databases. After using it extensively in production applications like InsureSignal and Liquidity Hunters, I can confidently say it's the best ORM for TypeScript projects. This tutorial will take you from zero to production-ready.

Why Prisma Over Other ORMs?

Traditional ORMs like Sequelize or TypeORM require you to define models twice: once in code and once in your database. Prisma flips this with a schema-first approach:

• Type-safe queries - Generated types match your schema exactly
• Auto-completion - Your IDE knows every field and relation
• Migrations - Database changes tracked in version control
• Performance - Optimized queries with intelligent batching
• Developer experience - Clear error messages and intuitive API

Getting Started

Installation

# Initialize a new Node.js project
npm init -y

# Install Prisma
npm install prisma --save-dev
npm install @prisma/client

# Initialize Prisma
npx prisma init

This creates a prisma folder with a schema.prisma file and a .env file for your database URL.

Configure Your Database

Update your .env file:

# PostgreSQL
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

# MySQL
# DATABASE_URL="mysql://user:password@localhost:3306/mydb"

# SQLite (great for development)
# DATABASE_URL="file:./dev.db"

Schema Design

The Prisma schema is the heart of your database layer. Here's a real-world example:

// 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)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  posts     Post[]
  profile   Profile?
  orders    Order[]

  @@index([email])
}

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

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

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

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

model Order {
  id        String      @id @default(cuid())
  userId    String
  user      User        @relation(fields: [userId], references: [id])
  status    OrderStatus @default(PENDING)
  total     Decimal     @db.Decimal(10, 2)
  items     OrderItem[]
  createdAt DateTime    @default(now())

  @@index([userId])
  @@index([status])
}

model OrderItem {
  id        String @id @default(cuid())
  orderId   String
  order     Order  @relation(fields: [orderId], references: [id], onDelete: Cascade)
  productId String
  quantity  Int
  price     Decimal @db.Decimal(10, 2)
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
}

Schema Best Practices

1. Use cuid() or uuid() for IDs - More secure than auto-increment
2. Add indexes - For frequently queried fields
3. Use enums - For fixed sets of values
4. Set onDelete behavior - Prevent orphaned records
5. Add timestamps - createdAt and updatedAt for auditing

Migrations

Create and Apply Migrations

# Create a migration
npx prisma migrate dev --name init

# Apply migrations in production
npx prisma migrate deploy

# Reset database (development only!)
npx prisma migrate reset

Generate the Client

npx prisma generate

This creates type-safe TypeScript types from your schema.

CRUD Operations

Create Records

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

const prisma = new PrismaClient();

// Create a single user
const user = await prisma.user.create({
  data: {
    email: 'justin@example.com',
    name: 'Justin Malinow',
    password: hashedPassword,
  },
});

// Create with relations
const userWithProfile = await prisma.user.create({
  data: {
    email: 'artist@example.com',
    name: 'Music Producer',
    password: hashedPassword,
    profile: {
      create: {
        bio: 'Electronic music producer',
        avatar: '/avatars/producer.jpg',
      },
    },
  },
  include: {
    profile: true,
  },
});

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

Read Records

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

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

// Find many with filtering
const publishedPosts = await prisma.post.findMany({
  where: {
    published: true,
    author: {
      role: 'ADMIN',
    },
  },
  orderBy: { createdAt: 'desc' },
  take: 10,
  skip: 0,
});

// Select specific fields
const userEmails = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    name: true,
  },
});

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

Update Records

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

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

// Upsert (update or create)
const user = await prisma.user.upsert({
  where: { email: 'justin@example.com' },
  update: { name: 'Justin M.' },
  create: {
    email: 'justin@example.com',
    name: 'Justin M.',
    password: hashedPassword,
  },
});

// Update relations
const post = await prisma.post.update({
  where: { id: postId },
  data: {
    categories: {
      connect: [{ id: categoryId1 }, { id: categoryId2 }],
    },
  },
});

Delete Records

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

// Delete many records
await prisma.post.deleteMany({
  where: {
    published: false,
    createdAt: {
      lt: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000), // Older than 30 days
    },
  },
});

Advanced Queries

Filtering

// Complex filters
const posts = await prisma.post.findMany({
  where: {
    OR: [
      { title: { contains: 'Prisma', mode: 'insensitive' } },
      { content: { contains: 'database', mode: 'insensitive' } },
    ],
    AND: [
      { published: true },
      { createdAt: { gte: new Date('2025-01-01') } },
    ],
    NOT: {
      author: { role: 'USER' },
    },
  },
});

// Relation filters
const usersWithOrders = await prisma.user.findMany({
  where: {
    orders: {
      some: {
        status: 'DELIVERED',
        total: { gte: 100 },
      },
    },
  },
});

Aggregations

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

// Aggregate
const orderStats = await prisma.order.aggregate({
  where: { status: 'DELIVERED' },
  _sum: { total: true },
  _avg: { total: true },
  _count: true,
});

// Group by
const ordersByStatus = await prisma.order.groupBy({
  by: ['status'],
  _count: true,
  _sum: { total: true },
});

Transactions

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

// Interactive transaction
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUnique({ where: { id: userId } });

  if (!user) {
    throw new Error('User not found');
  }

  const order = await tx.order.create({
    data: {
      userId: user.id,
      total: 99.99,
      items: {
        create: orderItems,
      },
    },
  });

  return order;
});

Production Best Practices

Connection Management

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

Error Handling

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

try {
  await prisma.user.create({ data: userData });
} 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

model User {
  id        String    @id @default(cuid())
  email     String    @unique
  deletedAt DateTime?
  // ... other fields
}

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

Prisma with Next.js API Routes

// app/api/users/route.ts
import { prisma } from '@/lib/prisma';
import { NextResponse } from 'next/server';

export async function GET() {
  const users = await prisma.user.findMany({
    select: { id: true, name: true, email: true },
  });
  return NextResponse.json(users);
}

export async function POST(request: Request) {
  const data = await request.json();

  const user = await prisma.user.create({
    data: {
      email: data.email,
      name: data.name,
      password: await hash(data.password),
    },
  });

  return NextResponse.json(user, { status: 201 });
}

Conclusion

Prisma transforms database development from a chore into a joy. The type safety, auto-completion, and clean API make it indispensable for modern TypeScript projects.

I've used Prisma across multiple production applications, and it consistently delivers on its promise of making database work simpler and safer. Start with the basics covered here, and you'll have a solid foundation for any data-driven application.

For more database and backend content, check out my other blog posts or see Prisma in action on my portfolio.