Prisma - Next-Generation ORM

Type-safe database access with auto-generated queries and migrations

---

When to Use This Skill

Use this skill when:

• Building type-safe database layers in TypeScript applications
• Working with PostgreSQL, MySQL, SQLite, MongoDB, or SQL Server
• Creating database schemas with migrations
• Implementing complex database relationships
• Generating type-safe database clients
• Migrating from raw SQL or other ORMs

Don't use this skill when:

• Building serverless functions with cold starts (consider PrismaClient connection pooling or lighter ORMs)
• Working with non-supported databases (use appropriate database drivers)
• Simple key-value data (consider simpler solutions like Redis)
• Applications requiring extremely complex raw SQL (though Prisma supports raw queries)

---

Critical Patterns

Pattern 1: Singleton Pattern for Prisma Client

When: Using Prisma in any Node.js application

Good:

// lib/prisma.ts
// ✅ Singleton pattern prevents multiple instances

import { PrismaClient } from '@prisma/client'

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

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

// Prevent multiple instances in development (hot reload)
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

Bad:

// ❌ Creating new client instances everywhere

// services/user.ts
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient() // ❌ New instance

export async function getUser(id: string) {
  return prisma.user.findUnique({ where: { id } })
}

// services/post.ts
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient() // ❌ Another new instance

export async function getPost(id: string) {
  return prisma.post.findUnique({ where: { id } })
}

Why: Multiple PrismaClient instances exhaust database connections, cause connection pool issues, waste resources, and slow down the application. The singleton pattern ensures one client instance per application.

---

Pattern 2: Select and Include for Performance

// ✅ Good: Only select needed fields
async function getUserPreview(id: string) {
  return prisma.user.findUnique({
    where: { id },
    select: { id: true, name: true, email: true }
  })
}

// ❌ Bad: Fetching all fields including sensitive data
async function getUserPreview(id: string) {
  return prisma.user.findUnique({ where: { id } }) // Returns password!
}

Why: Selecting only needed fields reduces data transfer and prevents exposing sensitive data. See queries.md for advanced patterns.

---

Pattern 3: Transactions for Data Integrity

// ✅ Good: Transaction for related operations
async function transferFunds(fromId: string, toId: string, amount: number) {
  return await prisma.$transaction(async (tx) => {
    const sender = await tx.account.update({
      where: { id: fromId },
      data: { balance: { decrement: amount } }
    })
    if (sender.balance < 0) throw new Error('Insufficient funds')

    await tx.account.update({
      where: { id: toId },
      data: { balance: { increment: amount } }
    })
  })
}

// ❌ Bad: Separate operations, not atomic
async function transferFunds(fromId: string, toId: string, amount: number) {
  await prisma.account.update({ where: { id: fromId }, data: { balance: { decrement: amount } } })
  await prisma.account.update({ where: { id: toId }, data: { balance: { increment: amount } } })
  // If second fails, sender already lost money!
}

Why: Transactions ensure atomicity - all operations succeed or all fail. See queries.md for more transaction patterns.

---

Pattern 4: Proper Indexing in Schema

// ✅ Good: Indexes for frequently queried fields
model Post {
  id        String  @id @default(cuid())
  published Boolean @default(false)
  authorId  String

  @@index([authorId])             // Foreign key index
  @@index([published, createdAt]) // Composite for filters
}

// ❌ Bad: No indexes on frequently queried fields
model Post {
  id        String  @id @default(cuid())
  authorId  String  // ❌ No index on foreign key
  published Boolean
  // Queries are slow without indexes
}

Why: Proper indexing dramatically improves query performance. See schema.md for indexing strategies.

---

Anti-Patterns

❌ Anti-Pattern 1: N+1 Query Problem

Don't do this:

// ❌ N+1 queries - fetches users then posts separately
async function getUsersWithPostCounts() {
  const users = await prisma.user.findMany()

  // ❌ Loop makes separate query for each user
  const usersWithCounts = await Promise.all(
    users.map(async (user) => {
      const postCount = await prisma.post.count({
        where: { authorId: user.id }
      })
      return { ...user, postCount }
    })
  )

  return usersWithCounts
  // 1 query for users + N queries for each user's posts = N+1 problem
}

Why it's bad: Makes N+1 database queries instead of 1-2, extremely slow for large datasets, overwhelms database with connections, scales poorly.

Do this instead:

// ✅ Single query with aggregation
async function getUsersWithPostCounts() {
  return await prisma.user.findMany({
    select: {
      id: true,
      name: true,
      email: true,
      _count: {
        select: { posts: true }
      }
    }
  })
  // Single query with COUNT aggregation
}

// ✅ Or use groupBy for more complex cases
async function getPostCountsByUser() {
  return await prisma.post.groupBy({
    by: ['authorId'],
    _count: {
      id: true
    }
  })
}

---

❌ Anti-Pattern 2: Not Using TypeScript Generated Types

Don't do this:

// ❌ Using any loses type safety
async function getUser(id: string): Promise<any> {
  return await prisma.user.findUnique({
    where: { id }
  })
}

// ❌ No type safety on returned data
const user = await getUser('123')
console.log(user.emial) // ❌ Typo not caught - runtime error

// ❌ Manual type definitions that get out of sync
interface User {
  id: string
  name: string
  email: string
  // ❌ Schema changes won't update this
}

Why it's bad: Loses Prisma's main benefit (type safety), allows typos and bugs, types get out of sync with schema, no autocomplete.

Do this instead:

// ✅ Use Prisma generated types
import { User } from '@prisma/client'

async function getUser(id: string): Promise<User | null> {
  return await prisma.user.findUnique({
    where: { id }
  })
}

// ✅ Type safety and autocomplete
const user = await getUser('123')
if (user) {
  console.log(user.email) // ✅ Autocomplete and type-safe
  // console.log(user.emial) // ✅ TypeScript error
}

// ✅ Use Prisma types for custom queries
import { Prisma } from '@prisma/client'

type UserWithPosts = Prisma.UserGetPayload<{
  include: { posts: true }
}>

async function getUserWithPosts(id: string): Promise<UserWithPosts | null> {
  return await prisma.user.findUnique({
    where: { id },
    include: { posts: true }
  })
}

---

❌ Anti-Pattern 3: Ignoring Connection Pool Limits

// ❌ Creating new client for every request
export async function GET(request: Request) {
  const prisma = new PrismaClient() // ❌ New instance every request
  return Response.json(await prisma.user.findMany())
}

// ✅ Use singleton client (as shown in Pattern 1)
import { prisma } from '@/lib/prisma'
export async function GET(request: Request) {
  return Response.json(await prisma.user.findMany())
}

Why it's bad: Exhausts database connections, causes "too many connections" errors.

---

❌ Anti-Pattern 4: Raw SQL for Everything

// ❌ Using raw SQL when Prisma query would work
async function getPublishedPosts() {
  return await prisma.$queryRaw`SELECT * FROM "Post" WHERE published = true`
}

// ✅ Use Prisma query builder
async function getPublishedPosts() {
  return await prisma.post.findMany({ where: { published: true } })
}

Why it's bad: Loses type safety, prone to SQL injection, harder to maintain.

For more anti-patterns and best practices, see references/queries.md and references/migrations.md.

---

What This Skill Covers

• Prisma Schema design and relationships
• Prisma Client for type-safe queries
• CRUD Operations (Create, Read, Update, Delete)
• Migrations for schema changes
• Relations and transactions

For advanced queries, migrations, and best practices, see references/.

---

Installation

npm install @prisma/client
npm install -D prisma

# Initialize Prisma
npx prisma init

---

Basic Schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id    String @id @default(cuid())
  email String @unique
  posts Post[]
}

model Post {
  id       String @id @default(cuid())
  title    String
  author   User   @relation(fields: [authorId], references: [id])
  authorId String
  @@index([authorId])
}

For field types, relations, and advanced schema patterns, see schema.md.

---

Prisma Client Setup

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

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

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
const user = await prisma.user.create({
  data: { email: 'alice@example.com', name: 'Alice' },
});

// Read
const user = await prisma.user.findUnique({
  where: { email: 'alice@example.com' },
});

// Update
const user = await prisma.user.update({
  where: { id: userId },
  data: { name: 'Alice Smith' },
});

// Delete
await prisma.user.delete({
  where: { id: userId },
});

For advanced queries, relations, pagination, filtering, aggregations, and transactions, see queries.md.

---

Migrations

# Create migration from schema changes
npx prisma migrate dev --name add_user_role

# Deploy migrations (production)
npx prisma migrate deploy

# Generate Prisma Client
npx prisma generate

# Push schema without migration (prototyping)
npx prisma db push

See migrations.md for migration workflows and seeding.

---

Quick Reference

// Create
await prisma.user.create({ data: { email: 'user@example.com' } });
await prisma.user.createMany({ data: [...] });

// Read
await prisma.user.findUnique({ where: { id: '...' } });
await prisma.user.findMany({ where: { ... }, take: 10 });
await prisma.user.count({ where: { ... } });

// Update
await prisma.user.update({ where: { id: '...' }, data: { ... } });
await prisma.user.updateMany({ where: { ... }, data: { ... } });
await prisma.user.upsert({ where: { ... }, update: { ... }, create: { ... } });

// Delete
await prisma.user.delete({ where: { id: '...' } });
await prisma.user.deleteMany({ where: { ... } });

// Transactions
await prisma.$transaction([...]);
await prisma.$transaction(async (tx) => { ... });

---

Learn More

• Schema & Relations: references/schema.md - Field types, relations (one-to-one, one-to-many, many-to-many)
• Advanced Queries: references/queries.md - Pagination, filtering, aggregations, transactions
• Migrations & Best Practices: references/migrations.md - Migration workflows, seeding, performance optimization

---

External References

• Prisma Documentation
• Prisma GitHub

---

Maintained by dsmj-ai-toolkit