Skip to main content
Agent skill

Prisma Patterns

Prisma ORM patterns for TypeScript backends — schema design, query optimization, transactions, pagination, and critical traps like updateMany returning count not records, $transaction timeouts, migrate dev resetting the DB, @updatedAt skipped on bulk writes, and serverless connection exhaustion. Use when writing a Prisma schema or query, or debugging transactions, migrations, or serverless connection limits.

Get started

Get the complete skill folder, including its scripts and reference files.

When to use this skill

Use Prisma Patterns when an AI agent needs a reusable SKILL.md workflow for this job: Prisma ORM patterns for TypeScript backends — schema design, query optimization, transactions, pagination, and critical traps like updateMany returning count not records, $transaction timeouts, migrate dev resetting the DB, @updatedAt skipped on bulk writes, and serverless connection exhaustion. Use when writing a Prisma schema or query, or debugging transactions, migrations, or serverless connection limits.

When not to use it

Skip Prisma Patterns when the task is outside the coding category, or when a more specific skill in this directory already covers the same workflow with clearer triggers.

How to install

Where will you use it?

Install scope
Claude Code folder setup
mkdir -p ~/.claude/skills/prisma-patterns
# Save SKILL.md to ~/.claude/skills/prisma-patterns/SKILL.md
  1. Claude Code personal install: place the skill and its bundled files at ~/.claude/skills/prisma-patterns/SKILL.md. For a shared project, commit the folder at .claude/skills/prisma-patterns/ instead.
  2. Current Claude Code detects SKILL.md changes in watched directories during the session. If you just created a top-level skills directory, run /reload-skills. Check that the skill is listed before testing its trigger.
  3. For Claude chat or Cowork in the Desktop app, manage uploaded skills in Customize > Skills; copying files into a local Claude Code folder is not a Desktop install. Other agents may use different install locations.

Full install guide for Claude, Cursor, and Codex →

Try a starter prompt

After installing the skill, paste this starter prompt into your agent to verify it triggers on a small task.

Starter prompt
Use the Prisma Patterns skill for this task: Use Prisma Patterns when this documented workflow matches the task. Confirm any required inputs or files before making changes.

What supports this recommendation?

71/100 provisional content score. This measures documentation, practical detail and source signals. It is not a measured task success rate or a security certification.

Documentation
77/100
Practical detail
68/100
Source evidence
63/100
Source signals
72/100
Model compatibility evidence
  • claudeuntested

    No model-specific signal or recorded compatibility test was found.

  • chatgptuntested

    No model-specific signal or recorded compatibility test was found.

  • geminiuntested

    No model-specific signal or recorded compatibility test was found.

  • copilotuntested

    No model-specific signal or recorded compatibility test was found.

  • llamauntested

    No model-specific signal or recorded compatibility test was found.

  • perplexityuntested

    No model-specific signal or recorded compatibility test was found.

  • mistraluntested

    No model-specific signal or recorded compatibility test was found.

  • grokuntested

    No model-specific signal or recorded compatibility test was found.

Inferred compatibility is based on content signals. Check the app, its available tools and setup requirements before running a skill.

What this skill does

# 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 |

```ts // 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:

```ts // 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 |

```ts // 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.

```ts // 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.

```ts // 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)

```ts 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

```ts // 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

```ts 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" ```

```ts // 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

```ts // 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

```ts // 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 ```

```ts // 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.

```ts // 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.

```ts // 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

```ts // 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

Intended uses

  • Use Prisma Patterns when this documented workflow matches the task.

Implementation notes

  • Review the source instructions and adapt inputs before running the workflow.

Try it, then tell us how it went

A copied prompt does not tell us whether the task worked. Report your result after checking the output. We collect your tool and outcome, never your files or prompt.