Prisma ORM Practical Guide - Mastering Type-Safe Database Operations

2025.12.02

DatabaseTypeScriptNode.jsORM

Prisma is a next-generation ORM for Node.js/TypeScript. With type-safe queries, intuitive schema definition, and powerful migration features, it’s ideal for modern application development. This article covers practical Prisma usage.

Prisma Basic Architecture

flowchart TB
    App["Your Application<br/>(TypeScript)"]
    Client["Prisma Client<br/>(Generated Type-Safe API)"]
    Engine["Prisma Engine<br/>(Query Engine + Rust)"]
    DB["Database<br/>(PostgreSQL, MySQL, SQLite, etc.)"]

    App --> Client --> Engine --> DB

Setup

1. Installation and Initialization

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

# Initialize Prisma (for PostgreSQL)
npx prisma init --datasource-provider postgresql

2. Environment Variable Configuration

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

# For production (with connection pool)
DATABASE_URL="postgresql://user:password@db.example.com:5432/mydb?schema=public&connection_limit=10&pool_timeout=30"

Schema Design

Basic Schema Definition

// prisma/schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch", "filteredRelationCount"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// User model
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

  // Relations
  posts     Post[]
  comments  Comment[]
  profile   Profile?
  sessions  Session[]

  @@index([email])
  @@map("users")  // Customize table name
}

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

  @@map("profiles")
}

model Post {
  id          String    @id @default(cuid())
  title       String
  slug        String    @unique
  content     String?
  excerpt     String?
  published   Boolean   @default(false)
  publishedAt DateTime?
  viewCount   Int       @default(0)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  // Relations
  authorId    String
  author      User      @relation(fields: [authorId], references: [id])
  categories  Category[]
  tags        Tag[]
  comments    Comment[]

  @@index([authorId])
  @@index([slug])
  @@index([published, publishedAt])
  @@map("posts")
}

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

  @@map("categories")
}

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

  @@map("tags")
}

model Comment {
  id        String   @id @default(cuid())
  content   String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  postId    String
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  authorId  String
  author    User     @relation(fields: [authorId], references: [id])

  // Self-referential relation (reply feature)
  parentId  String?
  parent    Comment?  @relation("CommentReplies", fields: [parentId], references: [id])
  replies   Comment[] @relation("CommentReplies")

  @@index([postId])
  @@index([authorId])
  @@map("comments")
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  expires      DateTime
  userId       String
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("sessions")
}

// Enum
enum Role {
  USER
  ADMIN
  MODERATOR
}

Advanced Schema Features

// Composite unique key
model OrderItem {
  orderId   String
  productId String
  quantity  Int
  price     Decimal @db.Decimal(10, 2)

  order   Order   @relation(fields: [orderId], references: [id])
  product Product @relation(fields: [productId], references: [id])

  @@id([orderId, productId])  // Composite primary key
  @@map("order_items")
}

// JSON fields and arrays
model Product {
  id          String   @id @default(cuid())
  name        String
  description String?
  price       Decimal  @db.Decimal(10, 2)
  metadata    Json?    // JSON field
  images      String[] // Array (PostgreSQL)

  orderItems  OrderItem[]

  @@map("products")
}

// Full-text search index (PostgreSQL)
model Article {
  id      String @id @default(cuid())
  title   String
  content String

  @@index([title, content], type: Gin)  // GIN index
  @@map("articles")
}

Migrations

Basic Migration Flow

# Development: Detect schema changes and create migration
npx prisma migrate dev --name init

# Create migration only (don't apply)
npx prisma migrate dev --create-only --name add_user_avatar

# Production: Apply migration
npx prisma migrate deploy

# Check migration status
npx prisma migrate status

# Reset database (development only)
npx prisma migrate reset

Customizing Migrations

-- prisma/migrations/20250102_add_full_text_search/migration.sql

-- Add SQL manually
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX posts_title_trgm_idx ON posts USING gin (title gin_trgm_ops);
CREATE INDEX posts_content_trgm_idx ON posts USING gin (content gin_trgm_ops);

-- Create search function
CREATE OR REPLACE FUNCTION search_posts(search_query TEXT)
RETURNS SETOF posts AS $$
  SELECT *
  FROM posts
  WHERE
    title ILIKE '%' || search_query || '%'
    OR content ILIKE '%' || search_query || '%'
  ORDER BY
    CASE
      WHEN title ILIKE search_query THEN 0
      WHEN title ILIKE search_query || '%' THEN 1
      WHEN title ILIKE '%' || search_query || '%' THEN 2
      ELSE 3
    END,
    published_at DESC NULLS LAST;
$$ LANGUAGE SQL STABLE;

Using Prisma Client

Basic CRUD Operations

// lib/prisma.ts
import { PrismaClient } from '@prisma/client';

// Use global instance (for development hot reload)
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;
}

// services/user.service.ts
import { prisma } from '@/lib/prisma';
import { Prisma, User, Role } from '@prisma/client';
import bcrypt from 'bcryptjs';

// Type definitions
type UserCreateInput = {
  email: string;
  name?: string;
  password: string;
};

type UserWithProfile = Prisma.UserGetPayload<{
  include: { profile: true };
}>;

// Create user
async function createUser(data: UserCreateInput): Promise<User> {
  const hashedPassword = await bcrypt.hash(data.password, 12);

  return prisma.user.create({
    data: {
      email: data.email,
      name: data.name,
      password: hashedPassword,
      profile: {
        create: {},  // Create empty profile simultaneously
      },
    },
  });
}

// Get single user
async function getUserById(id: string): Promise<UserWithProfile | null> {
  return prisma.user.findUnique({
    where: { id },
    include: {
      profile: true,
    },
  });
}

// Get user by email
async function getUserByEmail(email: string): Promise<User | null> {
  return prisma.user.findUnique({
    where: { email },
  });
}

// Get users list (with pagination)
async function getUsers(options: {
  page?: number;
  limit?: number;
  role?: Role;
  search?: string;
}): Promise<{ users: User[]; total: number }> {
  const { page = 1, limit = 10, role, search } = options;
  const skip = (page - 1) * limit;

  const where: Prisma.UserWhereInput = {
    ...(role && { role }),
    ...(search && {
      OR: [
        { email: { contains: search, mode: 'insensitive' } },
        { name: { contains: search, mode: 'insensitive' } },
      ],
    }),
  };

  const [users, total] = await Promise.all([
    prisma.user.findMany({
      where,
      skip,
      take: limit,
      orderBy: { createdAt: 'desc' },
    }),
    prisma.user.count({ where }),
  ]);

  return { users, total };
}

// Update user
async function updateUser(
  id: string,
  data: Prisma.UserUpdateInput
): Promise<User> {
  return prisma.user.update({
    where: { id },
    data,
  });
}

// Delete user (cascade delete)
async function deleteUser(id: string): Promise<void> {
  await prisma.user.delete({
    where: { id },
  });
}

export const userService = {
  createUser,
  getUserById,
  getUserByEmail,
  getUsers,
  updateUser,
  deleteUser,
};

Queries with Relations

// services/post.service.ts
import { prisma } from '@/lib/prisma';
import { Prisma, Post } from '@prisma/client';

// Post type definition (with relations)
const postWithRelations = Prisma.validator<Prisma.PostDefaultArgs>()({
  include: {
    author: {
      select: {
        id: true,
        name: true,
        email: true,
        profile: {
          select: { avatar: true },
        },
      },
    },
    categories: true,
    tags: true,
    _count: {
      select: { comments: true },
    },
  },
});

type PostWithRelations = Prisma.PostGetPayload<typeof postWithRelations>;

// Get published posts
async function getPublishedPosts(options: {
  page?: number;
  limit?: number;
  categorySlug?: string;
  tagName?: string;
}): Promise<{ posts: PostWithRelations[]; total: number }> {
  const { page = 1, limit = 10, categorySlug, tagName } = options;

  const where: Prisma.PostWhereInput = {
    published: true,
    publishedAt: { lte: new Date() },
    ...(categorySlug && {
      categories: {
        some: { slug: categorySlug },
      },
    }),
    ...(tagName && {
      tags: {
        some: { name: tagName },
      },
    }),
  };

  const [posts, total] = await Promise.all([
    prisma.post.findMany({
      where,
      ...postWithRelations,
      skip: (page - 1) * limit,
      take: limit,
      orderBy: { publishedAt: 'desc' },
    }),
    prisma.post.count({ where }),
  ]);

  return { posts, total };
}

// Get post by slug (increment view count)
async function getPostBySlug(slug: string): Promise<PostWithRelations | null> {
  const post = await prisma.post.update({
    where: { slug, published: true },
    data: {
      viewCount: { increment: 1 },
    },
    ...postWithRelations,
  });

  return post;
}

// Create post
async function createPost(data: {
  title: string;
  content: string;
  authorId: string;
  categoryIds: string[];
  tagNames: string[];
}): Promise<Post> {
  const slug = generateSlug(data.title);

  return prisma.post.create({
    data: {
      title: data.title,
      slug,
      content: data.content,
      excerpt: data.content.slice(0, 200),
      author: { connect: { id: data.authorId } },
      categories: {
        connect: data.categoryIds.map(id => ({ id })),
      },
      tags: {
        connectOrCreate: data.tagNames.map(name => ({
          where: { name },
          create: { name },
        })),
      },
    },
  });
}

// Publish post
async function publishPost(id: string): Promise<Post> {
  return prisma.post.update({
    where: { id },
    data: {
      published: true,
      publishedAt: new Date(),
    },
  });
}

function generateSlug(title: string): string {
  return title
    .toLowerCase()
    .replace(/[^\w\s-]/g, '')
    .replace(/\s+/g, '-')
    .concat('-', Date.now().toString(36));
}

export const postService = {
  getPublishedPosts,
  getPostBySlug,
  createPost,
  publishPost,
};

Transactions

// services/order.service.ts
import { prisma } from '@/lib/prisma';
import { Prisma } from '@prisma/client';

interface OrderItem {
  productId: string;
  quantity: number;
}

// Interactive transaction
async function createOrder(
  userId: string,
  items: OrderItem[]
): Promise<Order> {
  return prisma.$transaction(async (tx) => {
    // 1. Verify product info and stock
    const products = await tx.product.findMany({
      where: {
        id: { in: items.map(item => item.productId) },
      },
    });

    // Stock check
    for (const item of items) {
      const product = products.find(p => p.id === item.productId);
      if (!product) {
        throw new Error(`Product not found: ${item.productId}`);
      }
      if (product.stock < item.quantity) {
        throw new Error(`Insufficient stock for: ${product.name}`);
      }
    }

    // 2. Create order
    const total = items.reduce((sum, item) => {
      const product = products.find(p => p.id === item.productId)!;
      return sum + product.price.toNumber() * item.quantity;
    }, 0);

    const order = await tx.order.create({
      data: {
        userId,
        status: 'PENDING',
        total: new Prisma.Decimal(total),
        items: {
          create: items.map(item => {
            const product = products.find(p => p.id === item.productId)!;
            return {
              productId: item.productId,
              quantity: item.quantity,
              price: product.price,
            };
          }),
        },
      },
      include: {
        items: {
          include: { product: true },
        },
      },
    });

    // 3. Decrease stock
    await Promise.all(
      items.map(item =>
        tx.product.update({
          where: { id: item.productId },
          data: {
            stock: { decrement: item.quantity },
          },
        })
      )
    );

    return order;
  }, {
    maxWait: 5000,  // Maximum wait time
    timeout: 10000, // Timeout
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
  });
}

// Batch transaction (execute multiple operations at once)
async function transferPoints(
  fromUserId: string,
  toUserId: string,
  amount: number
): Promise<void> {
  await prisma.$transaction([
    prisma.user.update({
      where: { id: fromUserId },
      data: { points: { decrement: amount } },
    }),
    prisma.user.update({
      where: { id: toUserId },
      data: { points: { increment: amount } },
    }),
    prisma.pointTransaction.create({
      data: {
        fromUserId,
        toUserId,
        amount,
        type: 'TRANSFER',
      },
    }),
  ]);
}

Advanced Queries

// Aggregation queries
async function getPostStats(authorId: string) {
  return prisma.post.aggregate({
    where: { authorId },
    _count: true,
    _sum: { viewCount: true },
    _avg: { viewCount: true },
    _max: { viewCount: true },
  });
}

// Grouping
async function getPostCountByCategory() {
  return prisma.post.groupBy({
    by: ['published'],
    where: { publishedAt: { not: null } },
    _count: { id: true },
    _avg: { viewCount: true },
    orderBy: { _count: { id: 'desc' } },
  });
}

// Raw SQL queries
async function searchPosts(query: string) {
  return prisma.$queryRaw<Post[]>`
    SELECT p.*,
           ts_rank(to_tsvector('english', title || ' ' || content),
                   plainto_tsquery('english', ${query})) as rank
    FROM posts p
    WHERE to_tsvector('english', title || ' ' || content)
          @@ plainto_tsquery('english', ${query})
    ORDER BY rank DESC
    LIMIT 20
  `;
}

// Full-text search (preview feature)
async function searchPostsFullText(query: string) {
  return prisma.post.findMany({
    where: {
      OR: [
        { title: { search: query } },
        { content: { search: query } },
      ],
    },
    orderBy: {
      _relevance: {
        fields: ['title', 'content'],
        search: query,
        sort: 'desc',
      },
    },
  });
}

Performance Optimization

Solving N+1 Problem

// Bad: Code that causes N+1 problem
async function getPostsWithAuthorsBad() {
  const posts = await prisma.post.findMany();

  // Query occurs for each post (N times)
  for (const post of posts) {
    const author = await prisma.user.findUnique({
      where: { id: post.authorId },
    });
    console.log(post.title, author?.name);
  }
}

// Good: Fetch relations in bulk with include
async function getPostsWithAuthorsGood() {
  const posts = await prisma.post.findMany({
    include: {
      author: {
        select: { name: true },
      },
    },
  });

  for (const post of posts) {
    console.log(post.title, post.author.name);
  }
}

// Good: Fetch only required fields with select
async function getPostTitlesWithAuthors() {
  return prisma.post.findMany({
    select: {
      id: true,
      title: true,
      author: {
        select: { name: true },
      },
    },
  });
}

Connection Pool Configuration

// Production configuration
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
  // Connection pool settings specified via URL parameters
  // ?connection_limit=10&pool_timeout=30
});

// For serverless environments (Prisma Accelerate)
// Configure in schema.prisma
// datasource db {
//   provider = "postgresql"
//   url      = env("DATABASE_URL")
//   directUrl = env("DIRECT_URL")  // For migrations
// }

Query Logging and Debugging

// Detailed query logging
const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
    { emit: 'stdout', level: 'error' },
    { emit: 'stdout', level: 'warn' },
  ],
});

// Listen to query events
prisma.$on('query', (e) => {
  console.log('Query:', e.query);
  console.log('Params:', e.params);
  console.log('Duration:', e.duration, 'ms');
});

// Detect slow queries
prisma.$on('query', (e) => {
  if (e.duration > 100) {
    console.warn(`Slow query (${e.duration}ms):`, e.query);
  }
});

Testing Strategy

Test Setup

// tests/helpers/prisma.ts
import { PrismaClient } from '@prisma/client';
import { execSync } from 'child_process';

const TEST_DATABASE_URL = process.env.TEST_DATABASE_URL ||
  'postgresql://test:test@localhost:5432/test_db';

export function createTestPrismaClient(): PrismaClient {
  return new PrismaClient({
    datasources: {
      db: { url: TEST_DATABASE_URL },
    },
  });
}

export async function resetDatabase(prisma: PrismaClient): Promise<void> {
  // Clear tables considering deletion order
  const tablenames = await prisma.$queryRaw<
    Array<{ tablename: string }>
  >`SELECT tablename FROM pg_tables WHERE schemaname='public'`;

  for (const { tablename } of tablenames) {
    if (tablename !== '_prisma_migrations') {
      await prisma.$executeRawUnsafe(
        `TRUNCATE TABLE "public"."${tablename}" CASCADE;`
      );
    }
  }
}

// Test factory
export function createUserFactory(prisma: PrismaClient) {
  return {
    create: async (overrides: Partial<Prisma.UserCreateInput> = {}) => {
      return prisma.user.create({
        data: {
          email: `test-${Date.now()}@example.com`,
          name: 'Test User',
          password: 'hashed_password',
          ...overrides,
        },
      });
    },
  };
}

Integration Tests

// tests/integration/user.test.ts
import { describe, it, expect, beforeAll, afterAll, beforeEach } from 'vitest';
import { PrismaClient } from '@prisma/client';
import { createTestPrismaClient, resetDatabase, createUserFactory } from '../helpers/prisma';
import { userService } from '@/services/user.service';

describe('UserService', () => {
  let prisma: PrismaClient;
  let userFactory: ReturnType<typeof createUserFactory>;

  beforeAll(async () => {
    prisma = createTestPrismaClient();
    userFactory = createUserFactory(prisma);
  });

  beforeEach(async () => {
    await resetDatabase(prisma);
  });

  afterAll(async () => {
    await prisma.$disconnect();
  });

  describe('createUser', () => {
    it('should create a user with profile', async () => {
      const user = await userService.createUser({
        email: 'new@example.com',
        name: 'New User',
        password: 'password123',
      });

      expect(user.email).toBe('new@example.com');
      expect(user.name).toBe('New User');

      const userWithProfile = await prisma.user.findUnique({
        where: { id: user.id },
        include: { profile: true },
      });

      expect(userWithProfile?.profile).toBeDefined();
    });

    it('should throw on duplicate email', async () => {
      await userFactory.create({ email: 'exists@example.com' });

      await expect(
        userService.createUser({
          email: 'exists@example.com',
          password: 'password123',
        })
      ).rejects.toThrow();
    });
  });

  describe('getUsers', () => {
    it('should return paginated users', async () => {
      // Create 15 users
      await Promise.all(
        Array.from({ length: 15 }, (_, i) =>
          userFactory.create({ email: `user${i}@example.com` })
        )
      );

      const result = await userService.getUsers({ page: 1, limit: 10 });

      expect(result.users).toHaveLength(10);
      expect(result.total).toBe(15);
    });
  });
});

Production Best Practices

Error Handling

// lib/prisma-errors.ts
import { Prisma } from '@prisma/client';

export class DatabaseError extends Error {
  constructor(
    message: string,
    public code: string,
    public originalError?: Error
  ) {
    super(message);
    this.name = 'DatabaseError';
  }
}

export function handlePrismaError(error: unknown): never {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    switch (error.code) {
      case 'P2002':
        throw new DatabaseError(
          `Unique constraint violation on ${error.meta?.target}`,
          'UNIQUE_CONSTRAINT',
          error
        );
      case 'P2025':
        throw new DatabaseError(
          'Record not found',
          'NOT_FOUND',
          error
        );
      case 'P2003':
        throw new DatabaseError(
          'Foreign key constraint failed',
          'FOREIGN_KEY_CONSTRAINT',
          error
        );
      default:
        throw new DatabaseError(
          `Database error: ${error.message}`,
          error.code,
          error
        );
    }
  }

  if (error instanceof Prisma.PrismaClientValidationError) {
    throw new DatabaseError(
      'Invalid data provided',
      'VALIDATION_ERROR',
      error
    );
  }

  throw error;
}

// Usage example
async function safeCreateUser(data: UserCreateInput) {
  try {
    return await userService.createUser(data);
  } catch (error) {
    handlePrismaError(error);
  }
}

Health Check and Monitoring

// lib/health.ts
import { prisma } from './prisma';

export async function checkDatabaseHealth(): Promise<{
  healthy: boolean;
  latency: number;
  error?: string;
}> {
  const start = Date.now();

  try {
    await prisma.$queryRaw`SELECT 1`;
    return {
      healthy: true,
      latency: Date.now() - start,
    };
  } catch (error) {
    return {
      healthy: false,
      latency: Date.now() - start,
      error: error instanceof Error ? error.message : 'Unknown error',
    };
  }
}

// Metrics collection
export async function getDatabaseMetrics() {
  const [
    userCount,
    postCount,
    activeConnections,
  ] = await Promise.all([
    prisma.user.count(),
    prisma.post.count(),
    prisma.$queryRaw<[{ count: bigint }]>`
      SELECT count(*) FROM pg_stat_activity
      WHERE datname = current_database()
    `.then(r => Number(r[0].count)),
  ]);

  return {
    userCount,
    postCount,
    activeConnections,
    timestamp: new Date().toISOString(),
  };
}

Summary

Prisma is a modern ORM that emphasizes type safety and developer experience.

Prisma Strengths

FeatureBenefit
Type-safe queriesCompile-time error detection
Intuitive schemaCentralized management with Prisma Schema
Auto migrationsSafe schema changes
Relation handlingEasy N+1 problem avoidance

Considerations for Adoption

  • Use $queryRaw when complex raw SQL is needed
  • Use createMany/updateMany for bulk data processing
  • Be careful with connection pools in serverless environments

Leveraging Prisma enables type-safe and maintainable database operations.

Recommended Articles

Tutorials

Drizzle ORM Introduction - Lightweight TypeScript ORM

DrizzleORM
Tutorials

Drizzle ORM Practical Guide - Leveraging a Type-Safe SQL-Like ORM

Drizzle ORMTypeScript
News

Drizzle ORM - Lightweight ORM Made with TypeScript

DatabaseTypeScript
Tutorials

Prisma ORM Introduction - Type-Safe Database Operations

PrismaTypeScript
News

TypeScript 5 New Features - Faster and More User-Friendly Evolution

TypeScriptJavaScript
Tutorials

VS Code Extension Development Introduction

VS CodeExtension
← Back to list