name:: prisma-best-practices
description:: Prisma ORM patterns — schema design, migrations, type-safe queries, relations, transactions, connection pooling, error handling, testing, raw SQL safety, and performance. Use when building or reviewing apps with Prisma, when setting up a new database with Prisma, when writing tests for Prisma-backed code, or when debugging query performance.
keywords:: prisma, prisma schema, prisma migrate, prisma client, prisma relations, prisma transaction, prisma typescript, prisma performance, prisma findMany, prisma include, prisma select, prisma testing, prisma seed, prisma raw query, prisma connection pool, prisma error handling, prisma createMany, prisma updateMany, prisma middleware
license:: MIT

Prisma Best Practices

Name: tessl-labs/prisma-best-practices
Rating: 96 (1 reviews)
Author: tessl-labs

Patterns ordered by impact. Every section shows WRONG vs RIGHT code.

1. Schema Design

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"  // or "sqlite", "mysql"
  url      = env("DATABASE_URL")
}

model Product {
  id          Int         @id @default(autoincrement())
  name        String
  description String?
  category    String
  priceCents  Int         // Money as Int (cents), NEVER Float
  orderItems  OrderItem[]
  createdAt   DateTime    @default(now())
  updatedAt   DateTime    @updatedAt

  @@index([category])
}

model Order {
  id           Int         @id @default(autoincrement())
  customerName String
  status       OrderStatus @default(RECEIVED)
  totalCents   Int
  items        OrderItem[]
  createdAt    DateTime    @default(now())
  updatedAt    DateTime    @updatedAt

  @@index([status])
  @@index([createdAt])
}

model OrderItem {
  id         Int     @id @default(autoincrement())
  orderId    Int
  productId  Int
  quantity   Int
  priceCents Int
  order      Order   @relation(fields: [orderId], references: [id], onDelete: Cascade)
  product    Product @relation(fields: [productId], references: [id])
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt

  @@index([orderId])
  @@index([productId])
}

enum OrderStatus {
  RECEIVED
  PREPARING
  READY
  PICKED_UP
  CANCELLED
}

Key schema rules

@updatedAt on EVERY model — auto-updates timestamp on write; include on all models including join/pivot tables
@@index on every foreign key column and every frequently filtered column — Prisma does NOT auto-index foreign keys (unlike some ORMs)
Money as Int (cents), NEVER Float — Float causes rounding errors (e.g., 0.1 + 0.2 !== 0.3)
Enums for finite value sets — provides type safety and schema-level validation; enum values UPPERCASE
DATABASE_URL from env() — never hardcode connection strings
@default(now()) + @updatedAt — include createdAt and updatedAt on every model
onDelete: Cascade on child relations when parent deletion should cascade
generator client block always present with provider = "prisma-client-js"

2. Connection Pool Configuration

WRONG — no connection limit (exhausts database connections)

// Default: no pool settings — can exhaust DB connection limit
const prisma = new PrismaClient();

RIGHT — configure connection pool via DATABASE_URL

// Connection pool is configured via the DATABASE_URL connection string:
// postgresql://user:pass@host:5432/db?connection_limit=10&pool_timeout=30
//
// Key parameters:
//   connection_limit — max connections in pool (default: num_cpus * 2 + 1)
//   pool_timeout     — seconds to wait for a connection from pool (default: 10)
//
// For serverless (AWS Lambda, Vercel), use connection_limit=1 and Prisma Accelerate or PgBouncer

const prisma = new PrismaClient({
  log: process.env.NODE_ENV === 'development' ? ['query', 'warn', 'error'] : ['error'],
});

Singleton pattern (prevents connection leaks in dev with hot reload)

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

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

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development' ? ['query', 'warn', 'error'] : ['error'],
  });

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

3. Queries — select vs include

WRONG — using `include` for list/catalog views (fetches all fields of all relations)

// Fetches ALL fields from Product and all related data — wasteful for a catalog list
const products = await prisma.product.findMany({
  include: { orderItems: true },
});

RIGHT — use `select` for list views to reduce payload

// List/catalog view: select only needed fields
const catalog = await prisma.product.findMany({
  select: { id: true, name: true, category: true, priceCents: true },
  orderBy: [{ category: 'asc' }, { name: 'asc' }],
});

RIGHT — use `include` for detail views that need full relation data

// Detail view: use include to get the order WITH all its items and their products
const orderDetail = await prisma.order.findUnique({
  where: { id: orderId },
  include: {
    items: {
      include: { product: true },
    },
  },
});

When to use which:

select — list views, catalog queries, autocomplete, anywhere you don't need all fields. Reduces query size and response payload.
include — detail views where you need complete related records (e.g., order with all line items and their products). Use include when you need nested relation data.
Never mix select and include at the same level — use select with nested select or include at top level.

Filtering and ordering

// Filter with enum values
const activeOrders = await prisma.order.findMany({
  where: { status: { in: ['RECEIVED', 'PREPARING'] } },
  orderBy: { createdAt: 'asc' },
});

// Pagination
const page = await prisma.product.findMany({
  skip: 20,
  take: 10,
  orderBy: { createdAt: 'desc' },
});

4. N+1 Prevention — Fluent API Gotcha

WRONG — fluent API causes N+1 queries

const orders = await prisma.order.findMany();
// N+1: each call triggers a separate query!
for (const order of orders) {
  const items = await prisma.order.findUnique({ where: { id: order.id } }).items();
  // Another N queries for products...
}

RIGHT — eager load with include

const orders = await prisma.order.findMany({
  include: {
    items: {
      include: { product: true },
    },
  },
});
// All data loaded in a single query — no N+1
for (const order of orders) {
  for (const item of order.items) {
    console.log(item.product.name); // Already loaded
  }
}

5. Transactions

Interactive transactions — use `tx` parameter for ALL operations inside

const order = await prisma.$transaction(async (tx) => {
  // IMPORTANT: use `tx` (not `prisma`) for ALL operations inside the transaction
  const order = await tx.order.create({
    data: {
      customerName: input.customerName,
      totalCents: calculatedTotal,
      status: 'RECEIVED',
      items: {
        create: input.items.map(item => ({
          productId: item.productId,
          quantity: item.quantity,
          priceCents: item.priceCents,
        })),
      },
    },
    include: { items: true },
  });
  return order;
});

Sequential operations (simpler, for independent writes)

// Sequential: runs operations in order, rolls back all if any fails
const [deletedItems, deletedOrder] = await prisma.$transaction([
  prisma.orderItem.deleteMany({ where: { orderId } }),
  prisma.order.delete({ where: { id: orderId } }),
]);

Transaction timeout and isolation

// For long-running transactions, increase timeout (default 5s)
const result = await prisma.$transaction(
  async (tx) => {
    // ... expensive operations
  },
  {
    maxWait: 5000,    // ms to wait to acquire a connection
    timeout: 10000,   // ms for the transaction to complete
  }
);

6. Error Handling

WRONG — no error handling for unique constraint violations

const user = await prisma.user.create({
  data: { email },  // Crashes with P2002 if email already exists!
});

RIGHT — handle Prisma error codes

import { Prisma } from '@prisma/client';

try {
  const user = await prisma.user.create({ data: { email } });
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    if (error.code === 'P2002') {
      // Unique constraint violation — extract the field from error.meta
      const field = (error.meta?.target as string[])?.join(', ');
      throw new ConflictError(`${field} already exists`);
    }
    if (error.code === 'P2025') {
      // Record not found (e.g., update/delete on non-existent record)
      throw new NotFoundError('Record not found');
    }
  }
  throw error;
}

Key Prisma error codes:

P2002 — unique constraint violation
P2025 — record not found (update/delete)
P2003 — foreign key constraint violation
P2014 — relation violation

7. Raw Queries — SQL Injection Prevention

WRONG — string interpolation in raw queries (SQL injection!)

// VULNERABLE: SQL injection via user input!
const users = await prisma.$queryRaw`SELECT * FROM "User" WHERE email = '${email}'`;
const result = await prisma.$queryRawUnsafe(`SELECT * FROM "User" WHERE id = ${id}`);

RIGHT — use tagged template literals (parameterized)

// SAFE: Prisma's tagged template literal auto-parameterizes values
const users = await prisma.$queryRaw`SELECT * FROM "User" WHERE email = ${email}`;

// For dynamic table/column names, use Prisma.sql
import { Prisma } from '@prisma/client';
const table = Prisma.sql`"User"`;
const result = await prisma.$queryRaw`SELECT * FROM ${table} WHERE id = ${id}`;

Critical distinction: $queryRaw with tagged template literals is SAFE (auto-parameterized). $queryRawUnsafe() with string concatenation is VULNERABLE. Never use $queryRawUnsafe with user input.

8. Batch Operations

// createMany — fast bulk insert (single SQL INSERT)
await prisma.product.createMany({
  data: products.map(p => ({
    name: p.name,
    category: p.category,
    priceCents: p.priceCents,
  })),
  skipDuplicates: true,  // Ignore rows that violate unique constraints
});

// updateMany — bulk update matching records
await prisma.order.updateMany({
  where: { status: 'RECEIVED', createdAt: { lt: cutoffDate } },
  data: { status: 'CANCELLED' },
});

// deleteMany — bulk delete
await prisma.orderItem.deleteMany({
  where: { orderId },
});

Use createMany/updateMany/deleteMany instead of loops with individual create/update/delete calls.

9. Migrations

# Development: create and apply migration
npx prisma migrate dev --name add_order_status_index

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

# Reset dev database (drops, recreates, applies all migrations, runs seed)
npx prisma migrate reset

# Prototyping only: push schema without creating migration files
npx prisma db push

Key rules:

prisma migrate dev for development (creates migration files + applies)
prisma migrate deploy for production (applies existing migration files only)
prisma db push for prototyping only — does NOT create migration files
Never edit migration SQL files manually — reset and regenerate instead
Commit migration files to git — they are the source of truth for schema changes

10. Testing — Database Setup, Seeding, and Isolation

Test database configuration

# .env.test — separate database for tests, NEVER use production DB
DATABASE_URL="postgresql://user:pass@localhost:5432/myapp_test"
# Or for SQLite:
# DATABASE_URL="file:./test.db"

// test/setup.ts — shared test Prisma client
import { PrismaClient } from '@prisma/client';

// Test client reads DATABASE_URL from .env.test (configured via dotenv or test runner)
const prisma = new PrismaClient();

export { prisma };

WRONG — no cleanup, no seeding, hardcoded DB URL

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient({
  datasources: { db: { url: 'postgresql://localhost:5432/test' } },  // WRONG: hardcoded!
});

test('creates order', async () => {
  // No cleanup from previous tests — flaky!
  // No seed data — test depends on existing state!
});

RIGHT — clean + seed in beforeEach, env-based DB URL, disconnect in afterAll

import { PrismaClient } from '@prisma/client';

// DB URL comes from environment (.env.test), never hardcoded
const prisma = new PrismaClient();

let testProduct: { id: number; name: string; priceCents: number };

beforeEach(async () => {
  // Clean tables in correct order (children before parents) to respect FK constraints
  await prisma.orderItem.deleteMany();
  await prisma.order.deleteMany();
  await prisma.product.deleteMany();

  // Seed test data — always create known data in beforeEach for test isolation
  testProduct = await prisma.product.create({
    data: {
      name: 'Test Latte',
      category: 'coffee',
      priceCents: 450,
    },
  });
});

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

test('creates an order with items', async () => {
  const order = await prisma.order.create({
    data: {
      customerName: 'Alice',
      totalCents: 450,
      status: 'RECEIVED',
      items: {
        create: [{ productId: testProduct.id, quantity: 1, priceCents: 450 }],
      },
    },
    include: { items: true },
  });

  expect(order.items).toHaveLength(1);
  expect(order.totalCents).toBe(450);
});

Key testing rules:

DATABASE_URL from environment (.env.test) — NEVER hardcode the database URL in test files
deleteMany in beforeEach — clean tables in child-to-parent order before each test
Seed data in beforeEach — create known test data so tests are self-contained and isolated
$disconnect() in afterAll — close the Prisma connection pool after tests complete
Each test must be independent — never depend on data from another test

Prisma seed script (for dev database initialization)

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

const prisma = new PrismaClient();

async function main() {
  await prisma.product.createMany({
    data: [
      { name: 'Espresso', category: 'coffee', priceCents: 350 },
      { name: 'Latte', category: 'coffee', priceCents: 450 },
      { name: 'Muffin', category: 'pastry', priceCents: 300 },
    ],
  });
  console.log('Seed data created');
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

// package.json
{
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}

11. Soft Deletes with Middleware

// Soft delete middleware — filter out deleted records automatically
prisma.$use(async (params, next) => {
  if (params.action === 'findMany' || params.action === 'findFirst') {
    if (!params.args) params.args = {};
    if (!params.args.where) params.args.where = {};
    if (params.args.where.deletedAt === undefined) {
      params.args.where.deletedAt = null;  // Only show non-deleted records
    }
  }
  if (params.action === 'delete') {
    params.action = 'update';
    params.args.data = { deletedAt: new Date() };
  }
  return next(params);
});

Checklist

Verifiers

prisma-schema-design — Define Prisma schema with proper types, indexes, and relations
prisma-queries-transactions — Write efficient queries with proper select/include, transactions, and error handling
prisma-testing — Set up test database with proper cleanup, seeding, and isolation

evals

skills

prisma-best-practices

SKILL.md

verifiers

tile.json

tessl-labs/prisma-best-practices

SKILL.md.css-3qkkll{font-size:var(--chakra-font-sizes-sm);font-weight:var(--chakra-font-weights-normal);color:var(--chakra-colors-gray-300);}skills/prisma-best-practices/