prisma-patterns

사용 방법

설치 명령을 복사하고 자신의 에이전트 환경에 설치하기 전에 소스 패키지를 확인하세요.
필요한 도구, 인증 정보, 지원 환경은 원본 문서에서 확인하세요.
입력과 원하는 산출물을 설명하고 출처와 결과를 대조하세요.

npx skills add https://github.com/affaan-m/ecc --skill prisma-patterns

시작 전 확인

설치에는 모델 구독, API 한도, 파일 접근 권한이 포함되지 않습니다.
별점 수와 설치 수는 활동 지표이며 독립적인 품질·보안 검증이 아닙니다.

원본 Skill 문서

원본 저장소의 제목, 예시, 코드, 표, 링크, 이미지를 유지해 표시합니다.

Prisma Patterns

Production patterns and non-obvious traps for Prisma ORM in TypeScript backends.

Check your version before applying patterns. The Prisma API surface has evolved across major releases: ``bash npx prisma --version Notable API differences across versions: - relationJoins can load relations via JOIN rather than separate queries, but may cause row explosion on large 1:N relations or deep include — benchmark both approaches - omit field modifier and prisma.$extends Client Extensions API were added - **Newer installs**: the package may be named prisma instead of @prisma/client; PrismaClient may require a driver adapter (e.g. @prisma/adapter-pg); datasource.url may live in prisma.config.ts instead of schema.prisma - CLI commands (migrate dev, migrate deploy, generate`) are unchanged across versions

When to Activate

Designing or modifying Prisma schema models and relations
Writing queries, transactions, or pagination logic
Using updateMany, deleteMany, or any bulk operation
Running or planning database migrations
Deploying to serverless environments (Vercel, Lambda, Cloudflare Workers)
Implementing soft delete or multi-tenant row filtering

Core Concepts

ID Strategy

Strategy	Use When	Avoid When
`@default(cuid())`	Default choice — URL-safe, sortable, no collisions	Sequential IDs needed for external systems
`@default(uuid())`	Interoperability with non-Prisma systems required	High-write tables (random UUIDs fragment B-tree indexes)
`@default(autoincrement())`	Internal join tables, audit logs	Public-facing IDs (exposes record count)

Schema Defaults

prisma

model User {
  id        String    @id @default(cuid())
  email     String    @unique  // @unique already creates an index — no @@index needed
  name      String
  role      Role      @default(USER)
  posts     Post[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  deletedAt DateTime?

  @@index([createdAt])
  @@index([deletedAt, createdAt]) // composite for soft-delete + sort queries
}

Add @@index on every foreign key and column used in WHERE or ORDER BY.
Declare deletedAt DateTime? upfront when soft delete is a foreseeable requirement — adding it later requires a migration on a live table.
updatedAt @updatedAt is set automatically by Prisma on update and upsert only (see Anti-Patterns for bulk update trap).

`include` vs `select`

	`include`	`select`
Returns	All scalar fields + specified relations	Only specified fields
Use when	You need most fields plus a relation	Hot paths, large tables, avoiding over-fetch
Performance	May over-fetch on wide tables	Minimal payload, faster on large datasets
Prisma 5 note	Uses JOIN by default (`relationJoins`)	Same

// include — all columns + relation
const user = await prisma.user.findUnique({
  where: { id },
  include: { posts: { select: { id: true, title: true } } },
});

// select — explicit allowlist
const user = await prisma.user.findUnique({
  where: { id },
  select: { id: true, email: true, name: true },
});

Never return raw Prisma entities from API responses — map to response DTOs to control exposed fields:

// BAD: leaks passwordHash, deletedAt, internal fields
return await prisma.user.findUniqueOrThrow({ where: { id } });

// GOOD: explicit DTO mapping
const user = await prisma.user.findUniqueOrThrow({ where: { id } });
return { id: user.id, name: user.name, email: user.email };

Transaction Form Selection

Situation	Use
Independent operations, no inter-dependency	Array form
Later step depends on earlier result	Interactive form
External calls (email, HTTP) involved	Outside transaction entirely

// Array form — batched in one round trip
const [user, post] = await prisma.$transaction([
  prisma.user.update({ where: { id }, data: { name } }),
  prisma.post.create({ data: { title, authorId: id } }),
]);

// Interactive form — use tx client only, never the outer prisma client
const post = await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUniqueOrThrow({ where: { id } });
  if (user.role !== 'ADMIN') throw new Error('Forbidden');
  return tx.post.create({ data: { title, authorId: user.id } });
});

PrismaClient Singleton

Each PrismaClient instance opens its own connection pool. Instantiate once.

// lib/prisma.ts

// Option A — adapter-based initialization (required by newer Prisma installs)
import { PrismaClient } from '@prisma/client'; // or the generated client path for your setup
import { PrismaPg } from '@prisma/adapter-pg';

function createPrismaClient() {
  const adapter = new PrismaPg({
    connectionString: process.env.DATABASE_URL!,
  });
  return new PrismaClient({
    adapter,
    log: process.env.NODE_ENV === 'development' ? ['query', 'error'] : ['error'],
  });
}

const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient };

export const prisma = globalForPrisma.prisma ?? createPrismaClient();

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

// Option B — direct initialization (older installs, no adapter needed)
// import { PrismaClient } from '@prisma/client';
// export const prisma = globalForPrisma.prisma ?? new PrismaClient({ ... });

Use Option A if your Prisma install requires an adapter argument in the PrismaClient constructor. Use Option B if new PrismaClient() works without arguments. Let the compiler tell you which is correct.

The globalThis pattern prevents duplicate instances during hot reload (Next.js, nodemon, ts-node-dev).

N+1 Problem

Loading relations inside a loop issues one query per row.

// BAD: N+1 — one extra query per user
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
}

// GOOD: single query
const users = await prisma.user.findMany({ include: { posts: true } });

With Prisma 5+ relationJoins, the include form uses a single JOIN. On large 1:N sets this may increase result set size — benchmark both approaches if the relation can return many rows per parent.

Code Examples

Cursor Pagination (preferred for feeds and large datasets)

async function getPosts(cursor?: string, limit = 20) {
  const items = await prisma.post.findMany({
    where: { published: true },
    orderBy: [
      { createdAt: 'desc' },
      { id: 'desc' }, // secondary sort prevents unstable pagination on duplicate timestamps
    ],
    take: limit + 1,
    ...(cursor && { cursor: { id: cursor }, skip: 1 }),
  });

  const hasNextPage = items.length > limit;
  if (hasNextPage) items.pop();

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

Fetch limit + 1 and pop — canonical way to detect hasNextPage without an extra count query. Always include a unique field (e.g. id) as a secondary orderBy to prevent unstable pagination when multiple rows share the same timestamp. Use offset pagination only when users need to jump to arbitrary pages (admin tables).

Soft Delete

// Always filter explicitly — do not rely on middleware (hides behavior, hard to debug)
const activeUsers = await prisma.user.findMany({ where: { deletedAt: null } });

await prisma.user.update({ where: { id }, data: { deletedAt: new Date() } });
await prisma.user.update({ where: { id }, data: { deletedAt: null } }); // restore

Error Handling

import { Prisma } from '@prisma/client'; // or the generated client path for your setup

try {
  await prisma.user.create({ data: { email } });
} catch (e) {
  if (e instanceof Prisma.PrismaClientKnownRequestError) {
    if (e.code === 'P2002') throw new ConflictError('Email already exists');
    if (e.code === 'P2025') throw new NotFoundError('Record not found');
    if (e.code === 'P2003') throw new BadRequestError('Referenced record does not exist');
  }
  throw e;
}

Common codes: P2002 unique violation · P2025 not found · P2003 foreign key violation.

Catch at the service boundary and translate to domain errors. Never expose raw Prisma messages to API consumers.

Connection Pool — Serverless

Embed connection params directly in DATABASE_URL — string concatenation breaks if the URL already has query parameters (e.g. ?schema=public):

bash

# .env — preferred: embed params in the URL
DATABASE_URL="postgresql://user:pass@host/db?connection_limit=1&pool_timeout=20"

# With an external pooler (PgBouncer, Supabase pooler)
DATABASE_URL="postgresql://user:pass@host/db?pgbouncer=true&connection_limit=1"

// Vercel, AWS Lambda, and similar serverless runtimes:
// cap pool to 1 per instance; connection_limit and pool_timeout controlled via DATABASE_URL

// Adapter-based setup (if your Prisma install requires an adapter):
import { PrismaClient } from '@prisma/client';
import { PrismaPg } from '@prisma/adapter-pg';

const prisma = new PrismaClient({
  adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }),
});

// Direct setup (if your Prisma install does not require an adapter):
// const prisma = new PrismaClient();

Anti-Patterns

`updateMany` returns a count, not records

// BAD: result is { count: 2 } — users[0] is undefined
const users = await prisma.user.updateMany({ where: { role: 'GUEST' }, data: { role: 'USER' } });

// GOOD: capture IDs first, then update, then fetch only the affected rows
const targets = await prisma.user.findMany({
  where: { role: 'GUEST' },
  select: { id: true },
});
const ids = targets.map((u) => u.id);
await prisma.user.updateMany({ where: { id: { in: ids } }, data: { role: 'USER' } });
const updated = await prisma.user.findMany({ where: { id: { in: ids } } });

Same applies to deleteMany — returns { count: n }, never the deleted rows.

`$transaction` interactive form times out after 5 seconds

// BAD: external call inside transaction exceeds 5s default → "Transaction already closed"
await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUniqueOrThrow({ where: { id } });
  await sendWelcomeEmail(user.email); // external call
  await tx.user.update({ where: { id }, data: { emailSent: true } });
});

// GOOD: external calls outside the transaction
const user = await prisma.user.findUniqueOrThrow({ where: { id } });
await sendWelcomeEmail(user.email);
await prisma.user.update({ where: { id }, data: { emailSent: true } });

// Only raise timeout when bulk processing genuinely needs it
await prisma.$transaction(async (tx) => { ... }, { timeout: 30_000 });

`migrate dev` can reset the database

migrate dev detects schema drift and may prompt to reset the DB, dropping all data.

bash

# NEVER on shared dev, staging, or production
npx prisma migrate dev --name add_column

# Safe everywhere except local solo dev
npx prisma migrate deploy

# Check drift without applying
npx prisma migrate diff \
  --from-migrations ./prisma/migrations \
  --to-schema-datamodel ./prisma/schema.prisma \
  --shadow-database-url "$SHADOW_DATABASE_URL"

Manually editing a migration file breaks future deploys

Prisma checksums every migration file. Editing after apply causes P3006 checksum mismatch on every environment where the original already ran. Create a new migration instead.

Breaking schema changes require multi-step migration

Adding NOT NULL to an existing column or renaming a column in one migration will lock the table or drop data. Use expand-and-contract:

bash

# Step 1: create migration locally, then deploy
npx prisma migrate dev --name add_new_column   # local only
npx prisma migrate deploy                       # staging / production

// Step 2: backfill data (run in a script or migration job, not in the shell)
await prisma.user.updateMany({ data: { newColumn: derivedValue } });

bash

# Step 3: create the NOT NULL constraint migration locally, then deploy
npx prisma migrate dev --name make_new_column_required  # local only
npx prisma migrate deploy                               # staging / production

`@updatedAt` does not fire on `updateMany`

@updatedAt is set automatically only on update and upsert. Bulk writes leave it stale.

// BAD: updatedAt stays at its old value
await prisma.post.updateMany({ where: { authorId }, data: { published: true } });

// GOOD
await prisma.post.updateMany({
  where: { authorId },
  data: { published: true, updatedAt: new Date() },
});

Soft delete + `findUniqueOrThrow` leaks deleted records

findUniqueOrThrow throws P2025 only when the row does not exist in the DB. Soft-deleted rows still exist and are returned without error.

findUniqueOrThrow requires a unique constraint field in where — adding deletedAt: null alongside id breaks the type because { id, deletedAt } is not a compound unique constraint. Use findFirstOrThrow instead.

// BAD: returns soft-deleted user
const user = await prisma.user.findUniqueOrThrow({ where: { id } });

// BAD: Prisma type error — { id, deletedAt } is not a unique constraint
const user = await prisma.user.findUniqueOrThrow({ where: { id, deletedAt: null } });

// GOOD: findFirstOrThrow supports arbitrary where conditions
const user = await prisma.user.findFirstOrThrow({ where: { id, deletedAt: null } });

`deleteMany` without `where` deletes every row

// BAD: silently wipes the table
await prisma.post.deleteMany();

// GOOD
await prisma.post.deleteMany({ where: { authorId: userId } });

Best Practices

Rule	Reason
`migrate deploy` in CI/CD, `migrate dev` only locally	`migrate dev` can reset the DB on drift
Map entities to response DTOs	Prevents leaking internal fields
Catch `PrismaClientKnownRequestError` at service boundary	Translate to domain errors
Prefer `*OrThrow` methods over manual null checks	Throws P2025 automatically; use `findFirstOrThrow` when filtering non-unique fields
`connection_limit=1` + external pooler in serverless	Prevents connection exhaustion
Always provide `where` on `deleteMany`	Prevents accidental table wipe
Set `updatedAt: new Date()` manually in `updateMany`	`@updatedAt` skips bulk writes

Related Skills

nestjs-patterns — NestJS service layer that integrates Prisma
postgres-patterns — PostgreSQL-level indexing and connection tuning
database-migrations — multi-step migration planning for production
backend-patterns — general API and service layer design

사용 방법

시작 전 확인

Prisma Patterns

When to Activate

Core Concepts

ID Strategy

Schema Defaults

`include` vs `select`

Transaction Form Selection

PrismaClient Singleton

N+1 Problem

Code Examples

Cursor Pagination (preferred for feeds and large datasets)

Soft Delete

Error Handling

Connection Pool — Serverless

Anti-Patterns

`updateMany` returns a count, not records

`$transaction` interactive form times out after 5 seconds

`migrate dev` can reset the database

Manually editing a migration file breaks future deploys

Breaking schema changes require multi-step migration

`@updatedAt` does not fire on `updateMany`

Soft delete + `findUniqueOrThrow` leaks deleted records

`deleteMany` without `where` deletes every row

Best Practices

Related Skills

관련 작업

더 많은 Skills

scientific-thinking-literature-review

docker-patterns

agentic-engineering

backend-patterns

prisma-patterns

사용 방법

시작 전 확인

Prisma Patterns

When to Activate

Core Concepts

ID Strategy

Schema Defaults

include vs select

Transaction Form Selection

PrismaClient Singleton

N+1 Problem

Code Examples

Cursor Pagination (preferred for feeds and large datasets)

Soft Delete

Error Handling

Connection Pool — Serverless

Anti-Patterns

updateMany returns a count, not records

$transaction interactive form times out after 5 seconds

migrate dev can reset the database

Manually editing a migration file breaks future deploys

Breaking schema changes require multi-step migration

@updatedAt does not fire on updateMany

Soft delete + findUniqueOrThrow leaks deleted records

deleteMany without where deletes every row

Best Practices

Related Skills

관련 작업

backend-patterns

rea-analysis-optimization

python-patterns

rust-patterns

더 많은 Skills

scientific-thinking-literature-review

docker-patterns

agentic-engineering

backend-patterns

`include` vs `select`

`updateMany` returns a count, not records

`$transaction` interactive form times out after 5 seconds

`migrate dev` can reset the database

`@updatedAt` does not fire on `updateMany`

Soft delete + `findUniqueOrThrow` leaks deleted records

`deleteMany` without `where` deletes every row