Prisma ORM Skill

Expert knowledge for working with Prisma ORM - the next-generation Node.js and TypeScript ORM.

When to Use This Skill

Invoke this skill when working with:

• Database schema design with Prisma
• Prisma Client queries (CRUD operations)
• Database migrations
• Relations and data modeling
• Performance optimization
• Type-safe database access

Documentation Files

This skill includes comprehensive Prisma documentation:

• Schema Reference - Data types, attributes, and schema syntax
• CRUD Operations - Create, Read, Update, Delete operations
• Relations - One-to-one, one-to-many, many-to-many relationships
• Filtering & Sorting - Query filters and ordering
• Transactions - ACID transactions and batch operations
• Migrations - Database migration workflows

Quick Reference

Project Setup

# Install Prisma CLI
npm install prisma --save-dev

# Initialize Prisma in project
npx prisma init

# Install Prisma Client
npm install @prisma/client

# Generate client after schema changes
npx prisma generate

# Create and apply migrations
npx prisma migrate dev --name init

# Open Prisma Studio (GUI)
npx prisma studio

Schema Structure

// prisma/schema.prisma

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

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

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

  @@index([authorId])
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String?
  user   User   @relation(fields: [userId], references: [id])
  userId Int    @unique
}

enum Role {
  USER
  ADMIN
}

Essential Prisma Client Patterns

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

// CREATE
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    posts: {
      create: { title: 'Hello World' }
    }
  },
  include: { posts: true }
})

// READ
const users = await prisma.user.findMany({
  where: {
    email: { contains: '@example.com' }
  },
  include: { posts: true },
  orderBy: { createdAt: 'desc' },
  take: 10
})

// UPDATE
const updated = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Updated' }
})

// DELETE
const deleted = await prisma.user.delete({
  where: { id: 1 }
})

// UPSERT
const upserted = await prisma.user.upsert({
  where: { email: 'bob@example.com' },
  update: { name: 'Bob Updated' },
  create: { email: 'bob@example.com', name: 'Bob' }
})

// TRANSACTION
const [posts, users] = await prisma.$transaction([
  prisma.post.findMany(),
  prisma.user.count()
])

// INTERACTIVE TRANSACTION
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { email: 'new@example.com' } })
  await tx.post.create({ data: { title: 'Post', authorId: user.id } })
})

Best Practices

Schema Design

1. Always define explicit output for generator:

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

2. Use indexes for foreign keys and frequently queried fields:

   model Post {
     authorId Int
     @@index([authorId])
   }

3. Use enums for fixed value sets:

   enum Status {
     DRAFT
     PUBLISHED
     ARCHIVED
   }

Query Optimization

1. Select only needed fields:

   const users = await prisma.user.findMany({
     select: { id: true, email: true }
   })

2. Use pagination for large datasets:

   const users = await prisma.user.findMany({
     skip: 20,
     take: 10
   })

3. Batch operations for bulk updates:

   await prisma.post.updateMany({
     where: { published: false },
     data: { published: true }
   })

Migrations

1. Always name migrations descriptively:

   npx prisma migrate dev --name add_user_profile

2. Review generated SQL before applying:

   npx prisma migrate dev --create-only

3. Use baseline for existing databases:

   npx prisma migrate resolve --applied 0_init

Common Patterns

Soft Delete

model Post {
  id        Int       @id @default(autoincrement())
  deletedAt DateTime?

  @@index([deletedAt])
}

// Soft delete
await prisma.post.update({
  where: { id: 1 },
  data: { deletedAt: new Date() }
})

// Query non-deleted
const posts = await prisma.post.findMany({
  where: { deletedAt: null }
})

Optimistic Locking

model Resource {
  id      Int @id @default(autoincrement())
  data    String
  version Int @default(0)
}

const resource = await prisma.resource.findUnique({ where: { id: 1 } })

const updated = await prisma.resource.updateMany({
  where: { id: 1, version: resource.version },
  data: { data: 'new data', version: { increment: 1 } }
})

if (updated.count === 0) {
  throw new Error('Concurrent modification detected')
}

Connection Pooling

// For serverless environments
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL + '?connection_limit=1'
    }
  }
})