Prisma Complete Guide | Schema· Queries

Why Prisma?

// Without Prisma (raw SQL + manual types)
const result = await pool.query<User>(
  'SELECT id, name, email FROM users WHERE id = $1',  // SQL string — no type checking
  [userId]
);
const user: User = result.rows[0];  // Manual type assertion

// With Prisma (generated type-safe client)
const user = await prisma.user.findUnique({ where: { id: userId } });
// user is fully typed: { id: number; name: string; email: string; ... }
// TypeScript catches typos in field names at compile time

Prisma generates a client from your schema — every query is typed, every field is checked at compile time.

---

Setup

npm install prisma @prisma/client
npx prisma init        # Creates prisma/schema.prisma and .env

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

# Start PostgreSQL with Docker
docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_USER=user \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  postgres:16-alpine

---

Schema Definition

// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  role      Role     @default(VIEWER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  posts     Post[]
  profile   Profile?

  @@index([email])
}

enum Role {
  ADMIN
  EDITOR
  VIEWER
}

model Post {
  id          Int      @id @default(autoincrement())
  title       String
  content     String?
  published   Boolean  @default(false)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  authorId    Int
  author      User     @relation(fields: [authorId], references: [id], onDelete: Cascade)

  tags        Tag[]    @relation("PostToTag")
  categories  Category[]

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

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[] @relation("PostToTag")
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String?
  avatar String?

  userId Int    @unique
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

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

---

Migrations

# Development: generate and apply migration
npx prisma migrate dev --name add-user-profile

# This creates: prisma/migrations/20240415_add_user_profile/migration.sql
# And applies it to the database

# Production: apply pending migrations (no prompts)
npx prisma migrate deploy

# Reset database (⚠️ drops all data)
npx prisma migrate reset

# Pull schema from existing database
npx prisma db pull

# Open Prisma Studio (visual DB browser)
npx prisma studio

---

Client Setup

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

// Prevent multiple instances in development (Next.js hot reload)
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };

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

---

CRUD Operations

Create

// Create a single record
const user = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'Alice',
    role: 'ADMIN',
  },
});

// Create with nested relations
const post = await prisma.post.create({
  data: {
    title: 'Hello Prisma',
    content: 'Getting started with Prisma ORM',
    author: {
      connect: { id: 1 },        // Connect to existing user
    },
    tags: {
      connectOrCreate: [          // Create tag if not exists, else connect
        { where: { name: 'typescript' }, create: { name: 'typescript' } },
        { where: { name: 'orm' }, create: { name: 'orm' } },
      ],
    },
  },
  include: { author: true, tags: true },  // Return relations
});

// Create many
await prisma.user.createMany({
  data: [
    { email: '[email protected]', name: 'Bob' },
    { email: '[email protected]', name: 'Carol' },
  ],
  skipDuplicates: true,    // Ignore duplicates (email unique constraint)
});

Read

// Find by unique field
const user = await prisma.user.findUnique({ where: { id: 1 } });
const user2 = await prisma.user.findUnique({ where: { email: '[email protected]' } });

// Find first matching
const latestPost = await prisma.post.findFirst({
  where: { published: true },
  orderBy: { createdAt: 'desc' },
});

// Find many
const publishedPosts = await prisma.post.findMany({
  where: { published: true },
  orderBy: { createdAt: 'desc' },
  take: 10,           // LIMIT
  skip: 20,           // OFFSET (for pagination)
  select: {           // Only fetch needed fields (better performance)
    id: true,
    title: true,
    author: {
      select: { name: true },
    },
  },
});

// Filtering
const results = await prisma.post.findMany({
  where: {
    AND: [
      { published: true },
      { createdAt: { gte: new Date('2026-01-01') } },
      {
        OR: [
          { title: { contains: 'typescript', mode: 'insensitive' } },
          { content: { contains: 'typescript', mode: 'insensitive' } },
        ],
      },
    ],
  },
});

// Count
const count = await prisma.post.count({ where: { published: true } });

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

Update

// Update by unique field
const updated = await prisma.user.update({
  where: { id: 1 },
  data: {
    name: 'Alice Smith',
    updatedAt: new Date(),
  },
});

// Upsert (update or create)
const user = await prisma.user.upsert({
  where: { email: '[email protected]' },
  update: { name: 'Alice Smith' },
  create: { email: '[email protected]', name: 'Alice Smith' },
});

// Update many
await prisma.post.updateMany({
  where: { authorId: 1 },
  data: { published: true },
});

// Atomic increment/decrement
await prisma.post.update({
  where: { id: 1 },
  data: {
    viewCount: { increment: 1 },
  },
});

Delete

// Delete by unique field
await prisma.user.delete({ where: { id: 1 } });

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

---

Relations

Include — Load Nested Data

// include loads related records (JOINs)
const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
      include: {
        tags: true,           // Nested include
      },
    },
    profile: true,
  },
});

// Type: user.posts[0].tags[0].name ✅

Select — Precise Field Selection

// select picks exactly the fields you need
const userNames = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    _count: {
      select: { posts: true },  // Count related records
    },
  },
});
// userNames[0]._count.posts = 5

Many-to-Many

// Connect existing tags to a post
await prisma.post.update({
  where: { id: 1 },
  data: {
    tags: {
      connect: [{ id: 1 }, { id: 2 }],
      disconnect: [{ id: 3 }],
    },
  },
});

---

Transactions

// Batch operations (atomic — all succeed or all fail)
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { email: '[email protected]', name: 'User' } }),
  prisma.post.create({ data: { title: 'First post', authorId: 1 } }),
]);

// Interactive transaction (use the transaction client)
const result = await prisma.$transaction(async (tx) => {
  const sender = await tx.user.update({
    where: { id: 1 },
    data: { balance: { decrement: 100 } },
  });

  if (sender.balance < 0) {
    throw new Error('Insufficient balance');  // Rolls back the transaction
  }

  const receiver = await tx.user.update({
    where: { id: 2 },
    data: { balance: { increment: 100 } },
  });

  return { sender, receiver };
});

---

Raw Queries

For complex queries that Prisma can’t express:

// Type-safe raw query
const users = await prisma.$queryRaw<{ id: number; name: string }[]>`
  SELECT id, name
  FROM users
  WHERE created_at > ${new Date('2026-01-01')}
  ORDER BY name
  LIMIT ${10}
`;

// Raw execute (for mutations — returns affected row count)
const count = await prisma.$executeRaw`
  UPDATE posts SET view_count = view_count + 1 WHERE id = ${postId}
`;

---

Performance Patterns

Avoid N+1 Queries

// ❌ N+1: 1 query for posts + N queries for authors
const posts = await prisma.post.findMany();
for (const post of posts) {
  const author = await prisma.user.findUnique({ where: { id: post.authorId } });
}

// ✅ 1 query with include
const posts = await prisma.post.findMany({
  include: { author: true },
});

Select Only What You Need

// ❌ Fetches all columns (profile image, bio, settings...)
const users = await prisma.user.findMany();

// ✅ Only fetch name and email
const users = await prisma.user.findMany({
  select: { id: true, name: true, email: true },
});

Pagination

// Cursor-based pagination (better performance than offset for large datasets)
async function getPosts(cursor?: number, limit = 20) {
  return prisma.post.findMany({
    where: { published: true },
    take: limit,
    skip: cursor ? 1 : 0,         // Skip the cursor itself
    cursor: cursor ? { id: cursor } : undefined,
    orderBy: { id: 'asc' },
  });
}

// Offset pagination (simpler, fine for small datasets)
async function getPostsPage(page: number, limit = 20) {
  const [posts, total] = await prisma.$transaction([
    prisma.post.findMany({
      where: { published: true },
      skip: (page - 1) * limit,
      take: limit,
      orderBy: { createdAt: 'desc' },
    }),
    prisma.post.count({ where: { published: true } }),
  ]);
  return { posts, total, totalPages: Math.ceil(total / limit) };
}

Connection Pooling (PgBouncer / Prisma Accelerate)

// For serverless environments (many short-lived connections)
const prisma = new PrismaClient({
  datasources: {
    db: {
      // Use connection pool URL (PgBouncer or Prisma Accelerate)
      url: process.env.DATABASE_URL_POOLED,
    },
  },
});

---

Middleware (Logging, Soft Delete)

// Soft delete middleware
prisma.$use(async (params, next) => {
  if (params.model === 'Post') {
    // Redirect deletes to updates
    if (params.action === 'delete') {
      params.action = 'update';
      params.args.data = { deletedAt: new Date() };
    }

    // Exclude soft-deleted records from queries
    if (params.action === 'findMany' || params.action === 'findFirst') {
      params.args.where = { ...params.args.where, deletedAt: null };
    }
  }
  return next(params);
});

// Query timing middleware
prisma.$use(async (params, next) => {
  const start = Date.now();
  const result = await next(params);
  const duration = Date.now() - start;

  if (duration > 1000) {
    console.warn(`Slow query: ${params.model}.${params.action} took ${duration}ms`);
  }

  return result;
});

Related posts:

• Database Comparison: SQL vs NoSQL
• Docker Compose Complete Guide
• FastAPI Complete Guide

---

자주 묻는 질문 (FAQ)

Q. 이 내용을 실무에서 언제 쓰나요?

A. Master Prisma ORM for Node.js and TypeScript. Covers schema definition, CRUD operations, relations, migrations, transact… 실무에서는 위 본문의 예제와 선택 가이드를 참고해 적용하면 됩니다.

Q. 선행으로 읽으면 좋은 글은?

A. 각 글 하단의 이전 글 또는 관련 글 링크를 따라가면 순서대로 배울 수 있습니다. C++ 시리즈 목차에서 전체 흐름을 확인할 수 있습니다.

Q. 더 깊이 공부하려면?

A. cppreference와 해당 라이브러리 공식 문서를 참고하세요. 글 말미의 참고 자료 링크도 활용하면 좋습니다.

---

같이 보면 좋은 글 (내부 링크)

이 주제와 연결되는 다른 글입니다.

• 데이터베이스 선택 가이드 | SQL vs NoSQL 완벽 비교
• FastAPI 완벽 가이드 — Python 최고 성능 웹 프레임워크, Django·Flask 대체
• Docker Compose 완벽 가이드 | 멀티 컨테이너·네트워크·볼륨·환경 변수

---

이 글에서 다루는 키워드 (관련 검색어)

Prisma, ORM, TypeScript, PostgreSQL, Node.js, Database, Backend 등으로 검색하시면 이 글이 도움이 됩니다.