Previous
Next
Prisma Complete Guide | Schema· Queries

Prisma Complete Guide | Schema· Queries

· 24분 읽기 · 수정 intermediate tutorial
Prisma Complete Guide | Schema· Queries

이 글의 핵심

Prisma gives you a type-safe database client generated from your schema ??no more SQL string mistakes or manual type definitions. This guide covers schema design, all query patterns, migrations, relations, and performance best practices.

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.

Real-World Adoption

Prisma has become the de facto ORM for modern TypeScript backends:

  • Over 1 million weekly npm downloads (as of 2026)
  • Used in production by: GitHub (internal tools), Vercel (hosting platform), Cal.com (scheduling), and thousands of startups
  • State of JS 2025: 67% satisfaction rating, highest among Node.js ORMs

Why developers choose Prisma:

  • Developer experience ??Prisma Studio (visual database browser), VS Code extension with autocomplete
  • Migration workflow ??version-controlled SQL migrations, no more manual ALTER TABLE scripts
  • Type safety ??catch database schema mismatches at compile time, not runtime

When NOT to use Prisma:

  • Extreme performance requirements ??Prisma’s query engine adds ~1-2ms overhead vs raw SQL
  • Complex legacy schemas ??if your DB has tons of triggers, stored procedures, or unusual constraints, raw SQL + Kysely or Drizzle might fit better
  • Serverless cold starts ??Prisma Client is ~5MB, which increases Lambda bundle size. Use Prisma Data Proxy or Prisma Accelerate for serverless

Prisma core architecture

Prisma splits database work into three layers you should keep mentally separate: declarative schema, generated client, and migrate. They depend on each other in a fixed order: you change schema.prisma, run migrate (or db push in prototyping), then regenerate the client so TypeScript matches the database.

Schema (schema.prisma)

The schema is the single source of truth for models, enums, relations, indexes, and database provider. It is not TypeScript ??it is its own DSL compiled by the Prisma CLI. That means you cannot import TypeScript types into it; instead, prisma generate emits types that mirror the schema.

Prisma Client (@prisma/client)

The client is generated code (node_modules/.prisma/client). It implements the query API, connection handling, and maps your API calls to SQL through the Prisma query engine (Rust binary). Regenerate after every schema change or you will get runtime errors even when tsc passes (stale client).

Prisma Migrate

Migrate turns schema diffs into versioned SQL files under prisma/migrations/. migrate dev is interactive and development-oriented; migrate deploy is what CI and production should run ??no schema drift, no surprise db push in prod.

Developer edits schema.prisma
        ??        ??   prisma migrate dev     ?�?�?? SQL files in repo  ?�?�?? applied to local DB
        ??        ??   prisma generate        ?�?�?? Prisma Client types + runtime

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 and design patterns

Below is a representative schema. Pay attention to relations (foreign keys and referential actions), indexes for common filters and sorts, and constraints (@unique, composite @@unique, defaults, and cascades).

// 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)
  viewCount   Int      @default(0)
  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[]
}

Relations: practical rules

  • One-to-many: put the foreign key on the ?�many??side (authorId on Post). Use onDelete explicitly ??Cascade for owned data, Restrict or SetNull when you must preserve history.
  • One-to-one: the optional side holds the FK (Profile.userId with @unique). Prisma models this as User.profile? and Profile.user.
  • Many-to-many: implicit join table (as with Post ??Tag) is fine until you need extra columns on the join; then add an explicit PostTag model with a composite primary key or @@id([postId, tagId]).

Indexes and constraints

  • @@index([published, createdAt]): matches WHERE published = true ORDER BY createdAt DESC ??verify with EXPLAIN ANALYZE in PostgreSQL.
  • @unique / @@unique([orgId, slug]): enforce invariants in the database, not only in app code ??Prisma will surface P2002 on violation, which you can map to HTTP 409.
  • String length: Prisma maps String to TEXT on PostgreSQL; if you need VARCHAR(n) for compatibility with other tools, document the migration SQL manually when required.

Migration strategies: development vs production

ConcernDevelopmentProduction / CI
Commandprisma migrate dev --name describe_changeprisma migrate deploy
Creates SQLYes, new migration folderNo ??applies existing files only
PromptsMay prompt to reset if driftMust be non-interactive
Data lossAcceptable on local (reset)Never ?�fix??with ad-hoc db push

Development workflow: edit schema ??migrate dev ??commit prisma/migrations/** and updated schema.prisma together. Use prisma db seed after reset for repeatable fixtures.

Production workflow: build your app with prisma generate (often in postinstall or CI). At deploy time, run migrate deploy before or as part of rolling out new code that depends on new columns ??otherwise you get runtime errors between old code and new schema.

Honest caveat: destructive changes (rename column, change type) require hand-written SQL steps or multi-phase migrations. Prisma?�s generated SQL is a starting point; always review migrations that touch large tables (locks, backfill).

# 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

For prototyping only (throwaway DB), prisma db push syncs schema without migration files. Do not rely on it for anything you need to reproduce in staging or production.

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') },
  },
});

Advanced query techniques: aggregation, transactions, raw SQL

groupBy and filtered aggregates

Prisma?�s aggregate is great for global stats; groupBy answers ?�per bucket??questions. You cannot express every SQL HAVING ??when the API fights you, use $queryRaw.

// Posts per author (authorId), only where at least one published post exists
const byAuthor = await prisma.post.groupBy({
  by: ['authorId'],
  where: { published: true },
  _count: { _all: true },
  _sum: { viewCount: true },
  orderBy: { authorId: 'asc' },
});

// When you need SQL features Prisma does not model (e.g. certain HAVING clauses
// on implicit M:N tables), inspect the real table names in generated migrations
// or use prisma.$queryRaw with Prisma.sql for dynamic fragments.
const heavyPosts = await prisma.$queryRaw<{ id: number; viewCount: number }[]>`
  SELECT id, "viewCount"
  FROM "Post"
  WHERE published = true AND "viewCount" > 1000
  ORDER BY "viewCount" DESC
  LIMIT ${20}
`;

Transactions (recap + isolation)

Use sequential array transactions for independent statements. Use interactive transactions when later steps depend on reads from earlier steps (transfers, inventory). For high contention, consider SELECT ??FOR UPDATE via raw SQL inside $transaction(async (tx) => ??.

// 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: power and discipline

Tagged template literals (prisma.$queryRaw\??“) parameterize values and help prevent SQL injection. Never splice unescaped strings for identifiers ??use Prisma.sql or validate allowlists for dynamic column names.

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

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

```typescript
// 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 }],
    },
  },
});

TypeScript: leverage generated types

Prisma?�s real win is end-to-end typing. A few patterns that pay off in larger codebases:

import type { Prisma, User, Post } from '@prisma/client';

// Input types for functions that create partial updates
type UserUpdate = Prisma.UserUpdateInput;
type CreatePost = Prisma.PostCreateInput;

// Reuse where clauses
const publicPosts: Prisma.PostWhereInput = {
  published: true,
  OR: [{ title: { contains: 'prisma' } }, { content: { contains: 'prisma' } }],
};

// Serializable JSON columns (if you use Json in schema)
const meta: Prisma.JsonObject = { source: 'import', version: 1 };

// getPayload for strongly typed include/select results
type UserWithPosts = Prisma.UserGetPayload<{
  include: { posts: true };
}>;

strictNullChecks should be on: findUnique returns User | null ??handle both or use findUniqueOrThrow when absence is exceptional.

Performance: connection pooling, serverless, and caching

Prisma opens a pool of database connections. In long-running Node servers, defaults are usually fine. In serverless (AWS Lambda, Vercel, Cloudflare Workers with adapters), you can exhaust connections because each instance opens its own pool. Mitigations:

  • PgBouncer or a managed pooler (e.g. Neon, Supavisor) in transaction mode for serverless.
  • Prisma Accelerate or Data Proxy: connection pool + optional edge caching; adds latency vs direct TCP but saves your database from connection storms.
  • Separate URLs: DATABASE_URL (direct) for migrations; DATABASE_URL_POOLED for the app in production.
// 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,
    },
  },
});

Application-level caching (Redis, in-memory with TTL) should wrap read-heavy, rarely changing data ??not every Prisma call. Prisma?�s own query result caching is not a full Redis replacement; use it as one layer in a strategy that also invalidates on writes.

Avoid N+1; select what you need; pagination

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

Testing strategies

  1. Integration tests with a real database (Docker Postgres or Testcontainers): run migrations, seed minimal data, assert behavior. This catches SQL mistakes mocks hide.
// vitest + beforeAll migrate + unique DB per test file (simplified)
import { PrismaClient } from '@prisma/client';
import { afterAll, beforeAll, describe, it, expect } from 'vitest';

const prisma = new PrismaClient();

beforeAll(async () => {
  // $executeRaw`?? or migrate deploy against TEST_DATABASE_URL
});

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

it('creates user with unique email', async () => {
  const u = await prisma.user.create({
    data: { email: '[email protected]', name: 'T' },
  });
  expect(u.id).toBeDefined();
});

  1. Mocking Prisma in unit tests: use vi.mock('@prisma/client', ?? or inject a repository interface. Fast, but you must still have a few integration tests for real constraints.

  2. Transactions in tests: wrap each test in a rollback transaction if your DB supports nested transactions, or use truncate between tests ??pick one style and stay consistent to avoid flakiness.

Prisma vs TypeORM vs Drizzle: a straight comparison

PrismaTypeORMDrizzle
Schemaschema.prisma DSLDecorators + entities in TSTypeScript schema (or SQL-like)
MigrationsFirst-class, versioned SQLMigrations, multiple strategiesdrizzle-kit, SQL-first
Type safetyExcellent generated typesGood, occasional loose endsVery close to SQL, excellent inference
DXStudio, great docs, steep CLI learningFamiliar to NestJS usersMinimal magic, steeper for beginners
SQL escape hatch$queryRawQueryBuilder / rawNative SQL feel
RuntimeQuery engine (Rust) + NodePure JS/TS in typical setupsLight, no heavy sidecar

When Prisma fits: new TypeScript service, team values codegen and refactors, PostgreSQL/MySQL, you accept occasional raw SQL for reporting.

When TypeORM fits: large existing codebase already on TypeORM, heavy use of patterns like ActiveRecord across many entities.

When Drizzle fits: you want minimal abstraction, top bundle performance, and schema-as-TypeScript with no separate DSL.

Production experience: honest notes

What went well: onboarding junior developers was fast ??the schema file is a shared picture of the data model, and PrismaClient autocompletion reduced bad column names. Migrations in Git made schema review part of code review. Prisma Studio helped support and product debug data without writing SQL.

What was painful: large reporting queries and complex CTEs were often written in raw SQL anyway; we stopped pretending the ORM would cover everything. The N+1 problem did not ?�go away????include must be designed as carefully as SQL joins. On serverless, connection limits required a pooler; without it, we saw too many connections under load. Upgrading Prisma major versions sometimes required follow-up changes to middleware API or engine binaries ??plan upgrades in a maintenance window.

Bottom line: Prisma is a strong default for CRUD-centric TypeScript services if you invest in indexes, query review, and migration discipline. It is not a reason to stop understanding SQL.

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;
});
  • [Database comparison: SQL vs NoSQL](/en/blog/database-comparison-sql-nosql-guide/
  • [Docker Compose complete guide](/en/blog/docker-compose-complete-guide/
  • [FastAPI complete guide](/en/blog/fastapi-complete-guide/

FAQ

When is this content useful in practice?

Use it when you are designing a TypeScript/Node service backed by PostgreSQL (or another supported database), and you want a single reference for schema design, query patterns, migrations, and production pitfalls. Apply the checklists in migrations and serverless sections before your first production deploy.

What should I read first?

Follow the Related posts links at the end of this article for database fundamentals and local infrastructure. The internal links are written to complement this guide, not to repeat it.

Where can I go deeper?

Read the official Prisma documentation and your database vendor?�s guide to connection pooling. For performance, EXPLAIN ANALYZE in PostgreSQL remains essential ??the ORM does not replace the query planner.

More from this site

Keywords

Prisma, ORM, TypeScript, PostgreSQL, Node.js, Database, Backend, migrations, Prisma Client, type safety