Prisma ORM Patterns

Use this skill when working with Prisma ORM in Node.js/TypeScript projects. Covers schema design, migrations, query optimization, relations, transactions, an...

Security Scan

The skill is an instruction-only Prisma ORM patterns reference whose requirements and instructions match its stated purpose; nothing requests unrelated credentials or installs arbitrary code.

Purpose & Capability

Name/description (Prisma patterns) align with the provided files: schema examples, migration commands, client patterns, query optimization, testing, and migrations. No unrelated binaries, credentials, or config paths are requested.

Instruction Scope

SKILL.md and reference files contain typical runtime examples that reference DATABASE_URL / TEST_DATABASE_URL, child_process execSync for running migrations in tests, and raw SQL ($queryRaw / $executeRaw / $executeRawUnsafe). These are expected for a Prisma guide but are capable of making destructive DB changes or executing raw SQL if copied/run—the docs do not instruct reading unrelated system files or exfiltrating data.

Install Mechanism

No install spec or code to download/execute; this is instruction-only (lowest install risk).

Credentials

The skill does not declare required env vars, but examples implicitly use DATABASE_URL, TEST_DATABASE_URL, and NODE_ENV which are appropriate for Prisma usage. The docs include an example connection string with an api_key for Prisma Accelerate (example only) — not a request for unrelated credentials.

Persistence & Privilege

always is false and the skill is user-invocable/autonomous-invocation is allowed (platform default). The skill does not request persistent system-level privileges or modify other skills.

Assessment

This skill is a code-and-process reference for Prisma and appears coherent. Before using any examples in a real project: (1) never run migration or raw-SQL commands against production without reviewing generated SQL and having backups, (2) set and verify DATABASE_URL / TEST_DATABASE_URL appropriately (use least-privilege DB users for migrations if possible), (3) treat $executeRawUnsafe/$queryRaw carefully — avoid interpolating untrusted input, and (4) the skill is instruction-only (no downloads), so the main risk is copying commands into environments with production credentials. If you want stricter safety, run migrations in a CI pipeline with reviewed SQL and use separate test databases.

When to Use

• Designing or migrating a Prisma schema
• Writing complex queries with relations, filtering, or pagination
• Handling transactions and error scenarios
• Optimizing N+1 queries and performance
• Setting up Prisma in monorepos or serverless environments

Core Workflow

1. Schema Design Principles

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

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

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  posts     Post[]
  profile   Profile?

  @@index([email])
  @@map("users")
}

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  String
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)

  tags      Tag[]    @relation("PostTags")

  @@index([authorId, published])
  @@map("posts")
}

2. Migration Workflow

# Development: auto-apply
npx prisma migrate dev --name add_user_profile

# Production: generate SQL only, review, then deploy
npx prisma migrate deploy

# Reset dev database
npx prisma migrate reset

# Introspect existing DB
npx prisma db pull

3. Client Initialization (Singleton Pattern)

// 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
}

4. Common Query Patterns

// Paginated query with relations
const getPostsPage = async (page: number, limit = 10) => {
  const [posts, total] = await prisma.$transaction([
    prisma.post.findMany({
      where: { published: true },
      include: {
        author: { select: { id: true, name: true } },
        _count: { select: { tags: true } },
      },
      orderBy: { createdAt: 'desc' },
      skip: (page - 1) * limit,
      take: limit,
    }),
    prisma.post.count({ where: { published: true } }),
  ])
  return { posts, total, pages: Math.ceil(total / limit) }
}

// Upsert pattern
const upsertUser = async (email: string, name: string) => {
  return prisma.user.upsert({
    where: { email },
    update: { name },
    create: { email, name },
  })
}

// Avoid N+1: use include vs separate queries
const postsWithAuthors = await prisma.post.findMany({
  include: { author: true }, // Single JOIN query, not N+1
})

5. Transactions

// Interactive transaction (recommended for complex logic)
const transferCredits = async (fromId: string, toId: string, amount: number) => {
  return prisma.$transaction(async (tx) => {
    const from = await tx.user.findUniqueOrThrow({ where: { id: fromId } })
    if (from.credits < amount) throw new Error('Insufficient credits')

    await tx.user.update({
      where: { id: fromId },
      data: { credits: { decrement: amount } },
    })
    await tx.user.update({
      where: { id: toId },
      data: { credits: { increment: amount } },
    })
  })
}

6. Error Handling

import { Prisma } from '@prisma/client'

const safeCreate = async (data: Prisma.UserCreateInput) => {
  try {
    return await prisma.user.create({ data })
  } catch (e) {
    if (e instanceof Prisma.PrismaClientKnownRequestError) {
      if (e.code === 'P2002') {
        throw new Error(`Unique constraint violated: ${e.meta?.target}`)
      }
    }
    throw e
  }
}

Best Practices

• Always use select to limit returned fields in production queries
• Add @@index for frequently filtered/sorted columns
• Use findUniqueOrThrow / findFirstOrThrow to avoid null checks
• Prefer $transaction for multi-step operations
• Enable query logging in development only
• Use connection pooling (PgBouncer / Prisma Accelerate) in serverless