FreeGitHubCoding

Prisma Patterns

Name: Prisma Patterns
Author: affaan-m

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.

affaan-m on GitHub

Curated by PromptsEdge from a public repo · MIT license. All credit goes to the author.

What is this skill?

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

When to use

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

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

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

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

Install this skill

1
Get the skill — it’s free
Use the Get this skill panel. Unlocked skills stay in My skills.
2
Download or clone the files
Download the zip, or clone the repo and copy the skills/prisma-patterns folder.
3
Put it where your agent looks for skills
For Claude Code, use your personal skills folder (every project) or a project’s own folder:
```
~/.claude/skills/prisma-patterns/SKILL.md      # all projects
.claude/skills/prisma-patterns/SKILL.md        # this project only
```
4
Just ask
No command needed. The agent reads the skill’s description and loads it on its own when your request matches.

SKILL.md frontmatter

What your agent reads to decide when to load this skill.

---
name: prisma-patterns
description: 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.
---

Related skills

View all →

What is this skill?

When to use

What’s inside

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

Install this skill

SKILL.md frontmatter

Files

`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