Skill ファイル

Prisma Orm Patterns

Name: Prisma Orm Patterns
Author: theofernandezz

Production Prisma patterns with PostgreSQL and Neon serverless. Covers schema design, client singleton, service layer, transactions, and migrations. Trigger: Activated when working with Prisma schema, queries, migrations, or database models.

theofernandezz0 スター2026/03/13

職業
カテゴリ: SQL データベース

スキル内容

Core Principle: Prisma is your type-safe interface to PostgreSQL. Keep all database access in a service layer — never write raw Prisma queries inside Server Actions, API routes, or components directly.

🚫 FORBIDDEN PATTERNS

1. Never Import Prisma Directly in Routes or Actions

// ❌ FORBIDDEN - Raw Prisma in a Server Action
'use server'
import { prisma } from '@/lib/db'

export async function createUserAction(data: CreateUserInput) {
  // This bypasses validation, error handling, and the service layer
  return prisma.user.create({ data })
}

// ✅ CORRECT - Use the service layer
'use server'
import { userService } from '@/lib/services/user-service'

export async function createUserAction(data: CreateUserInput) {
  return userService.create(data)
}

2. Never Create Multiple Prisma Client Instances

関連 Skill

Prisma Orm Patterns | Skills Pool

// ❌ FORBIDDEN - New client per file (causes connection pool exhaustion)
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

// ✅ CORRECT - Always import the singleton
import { prisma } from '@/lib/db'

// ❌ FORBIDDEN
async function getUser(id: string): Promise<any> {
  return prisma.user.findUnique({ where: { id } })
}

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

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

// ❌ FORBIDDEN - Returning raw DB model (may expose sensitive fields)
export async function getUserAction(id: string) {
  return prisma.user.findUnique({ where: { id } }) // includes passwordHash!
}

// ✅ CORRECT - Always select explicit fields or use a DTO
export async function getUserAction(id: string) {
  return prisma.user.findUnique({
    where: { id },
    select: {
      id: true,
      name: true,
      email: true,
      createdAt: true,
      // passwordHash: NOT included
    },
  })
}

// lib/db.ts
import { PrismaClient } from '@prisma/client'
import { PrismaNeon } from '@prisma/adapter-neon'
import { neon } from '@neondatabase/serverless'

function createPrismaClient() {
  // Neon serverless adapter — uses HTTP instead of long-lived TCP connections
  const sql = neon(process.env.DATABASE_URL!)
  const adapter = new PrismaNeon(sql)
  return new PrismaClient({ adapter })
}

// Singleton — reuses the instance in dev (avoids hot-reload exhaustion)
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

export const prisma = globalForPrisma.prisma ?? createPrismaClient()

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

// prisma/schema.prisma
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["driverAdapters"]
}

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

// ── Enums (use string unions in TS, but enums are fine in Prisma schema) ──
enum UserRole {
  USER
  ADMIN
  MODERATOR
}

enum PostStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}

// ── Models ────────────────────────────────────────────────────────────────
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  role      UserRole @default(USER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime? // Soft delete

  posts     Post[]
  profile   Profile?

  @@index([email])
  @@index([createdAt])
  @@map("users") // Snake_case table name
}

model Profile {
  id        String  @id @default(cuid())
  bio       String?
  avatarUrl String?
  userId    String  @unique

  user      User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("profiles")
}

model Post {
  id          String     @id @default(cuid())
  title       String
  slug        String     @unique
  content     String
  status      PostStatus @default(DRAFT)
  publishedAt DateTime?
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt
  deletedAt   DateTime?  // Soft delete
  authorId    String

  author      User       @relation(fields: [authorId], references: [id])
  tags        PostTag[]

  @@index([slug])
  @@index([authorId])
  @@index([status, publishedAt])
  @@map("posts")
}

model Tag {
  id    String    @id @default(cuid())
  name  String    @unique
  posts PostTag[]

  @@map("tags")
}

// Explicit many-to-many join table
model PostTag {
  postId String
  tagId  String

  post   Post   @relation(fields: [postId], references: [id], onDelete: Cascade)
  tag    Tag    @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@id([postId, tagId])
  @@map("post_tags")
}

// lib/services/user-service.ts
import { prisma } from '@/lib/db'
import { Prisma, type User } from '@prisma/client'
import { hashPassword } from '@/lib/auth/password'

// ── Types ─────────────────────────────────────────────────────────────────

// Use Prisma.validator for type-safe input shapes
const userWithProfile = Prisma.validator<Prisma.UserDefaultArgs>()({
  include: { profile: true },
})
export type UserWithProfile = Prisma.UserGetPayload<typeof userWithProfile>

const userPublicSelect = {
  id: true,
  name: true,
  email: true,
  role: true,
  createdAt: true,
} satisfies Prisma.UserSelect

export type UserPublic = Prisma.UserGetPayload<{ select: typeof userPublicSelect }>

// ── Service ───────────────────────────────────────────────────────────────

export const userService = {
  async findById(id: string): Promise<UserPublic | null> {
    return prisma.user.findUnique({
      where: { id, deletedAt: null }, // Soft delete filter
      select: userPublicSelect,
    })
  },

  async findByEmail(email: string): Promise<User | null> {
    return prisma.user.findUnique({
      where: { email, deletedAt: null },
    })
  },

  async findWithProfile(id: string): Promise<UserWithProfile | null> {
    return prisma.user.findUnique({
      where: { id, deletedAt: null },
      ...userWithProfile,
    })
  },

  async create(data: {
    email: string
    name: string
    password: string
  }): Promise<UserPublic> {
    const passwordHash = await hashPassword(data.password)

    return prisma.user.create({
      data: {
        email: data.email,
        name: data.name,
        passwordHash,
        profile: {
          create: {}, // Create empty profile automatically
        },
      },
      select: userPublicSelect,
    })
  },

  async update(id: string, data: Prisma.UserUpdateInput): Promise<UserPublic> {
    return prisma.user.update({
      where: { id },
      data,
      select: userPublicSelect,
    })
  },

  async softDelete(id: string): Promise<void> {
    await prisma.user.update({
      where: { id },
      data: { deletedAt: new Date() },
    })
  },
}

// lib/services/post-service.ts
import { prisma } from '@/lib/db'

export const postService = {
  // Use $transaction for multi-step operations that must succeed together
  async publish(postId: string, tagNames: string[]): Promise<void> {
    await prisma.$transaction(async (tx) => {
      // Step 1: Update post status
      await tx.post.update({
        where: { id: postId },
        data: { status: 'PUBLISHED', publishedAt: new Date() },
      })

      // Step 2: Upsert tags and create join records
      const tags = await Promise.all(
        tagNames.map((name) =>
          tx.tag.upsert({
            where: { name },
            create: { name },
            update: {},
          })
        )
      )

      // Step 3: Link tags to post
      await tx.postTag.createMany({
        data: tags.map((tag) => ({ postId, tagId: tag.id })),
        skipDuplicates: true,
      })
    })
  },

  // Interactive transaction with manual commit/rollback control
  async transferCredits(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')
        }

        const [updatedFrom, updatedTo] = await Promise.all([
          tx.user.update({
            where: { id: fromId },
            data: { credits: { decrement: amount } },
          }),
          tx.user.update({
            where: { id: toId },
            data: { credits: { increment: amount } },
          }),
        ])

        return { from: updatedFrom, to: updatedTo }
      },
      { isolationLevel: Prisma.TransactionIsolationLevel.Serializable }
    )
  },
}

// lib/services/post-service.ts
interface PaginationArgs {
  cursor?: string
  limit?: number
  orderBy?: 'newest' | 'oldest'
}

interface PaginatedResult<T> {
  items: T[]
  nextCursor: string | null
  hasMore: boolean
}

export const postService = {
  async paginate(args: PaginationArgs): Promise<PaginatedResult<PostPublic>> {
    const limit = Math.min(args.limit ?? 20, 100) // Cap at 100
    const take = limit + 1 // Fetch one extra to detect hasMore

    const items = await prisma.post.findMany({
      where: { status: 'PUBLISHED', deletedAt: null },
      orderBy: { publishedAt: args.orderBy === 'oldest' ? 'asc' : 'desc' },
      take,
      ...(args.cursor && {
        cursor: { id: args.cursor },
        skip: 1, // Skip the cursor item itself
      }),
      select: postPublicSelect,
    })

    const hasMore = items.length > limit
    if (hasMore) items.pop() // Remove the extra item

    return {
      items,
      hasMore,
      nextCursor: hasMore ? (items.at(-1)?.id ?? null) : null,
    }
  },
}

// lib/db/selects.ts - Centralize your reusable select/include shapes

import { Prisma } from '@prisma/client'

export const postWithAuthor = Prisma.validator<Prisma.PostDefaultArgs>()({
  include: {
    author: {
      select: { id: true, name: true, profile: { select: { avatarUrl: true } } },
    },
    tags: { include: { tag: true } },
  },
})

export type PostWithAuthor = Prisma.PostGetPayload<typeof postWithAuthor>

// Usage in service
const post = await prisma.post.findUnique({
  where: { id },
  ...postWithAuthor,
})
// `post` is fully typed as PostWithAuthor

// prisma/seed.ts
import { PrismaClient } from '@prisma/client'
import { hashPassword } from '../src/lib/auth/password'

const prisma = new PrismaClient()

async function main() {
  console.log('🌱 Seeding...')

  // Upsert (safe to re-run)
  const admin = await prisma.user.upsert({
    where: { email: '[email protected]' },
    update: {},
    create: {
      email: '[email protected]',
      name: 'Admin User',
      role: 'ADMIN',
      passwordHash: await hashPassword('password123'),
      profile: { create: { bio: 'Platform admin' } },
    },
  })

  await prisma.post.upsert({
    where: { slug: 'hello-world' },
    update: {},
    create: {
      title: 'Hello World',
      slug: 'hello-world',
      content: 'First post!',
      status: 'PUBLISHED',
      publishedAt: new Date(),
      authorId: admin.id,
    },
  })

  console.log('✅ Done')
}

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

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

# 1. Edit prisma/schema.prisma

# 2. Create and apply migration (dev)
npx prisma migrate dev --name add_posts_table

# 3. Generate Prisma Client after schema changes
npx prisma generate

# 4. Apply migrations in production (no dev scaffolding)
npx prisma migrate deploy

# 5. Inspect DB in browser
npx prisma studio

# 6. Reset DB + re-run all migrations + seed (dev only)
npx prisma migrate reset

# Core
npm install prisma @prisma/client

# Neon serverless adapter
npm install @neondatabase/serverless @prisma/adapter-neon

# Dev
npm install -D prisma tsx

# .env.local
DATABASE_URL="postgresql://user:[email protected]/neondb?sslmode=require"

prisma/
├── schema.prisma          # Single schema file
├── migrations/            # Auto-generated migration files
│   └── 20260225_init/
│       └── migration.sql
└── seed.ts                # Seed script

lib/
├── db.ts                  # Prisma singleton
├── db/
│   └── selects.ts         # Shared Prisma.validator shapes
└── services/
    ├── user-service.ts    # One file per model
    ├── post-service.ts
    └── tag-service.ts

Prisma Orm Patterns

🚫 FORBIDDEN PATTERNS

1. Never Import Prisma Directly in Routes or Actions

2. Never Create Multiple Prisma Client Instances

Prisma Orm Patterns

🚫 FORBIDDEN PATTERNS

1. Never Import Prisma Directly in Routes or Actions

2. Never Create Multiple Prisma Client Instances

3. Never Use `any` with Prisma Types

4. Never Expose Database Models Directly to the Client

✅ REQUIRED PATTERNS

1. Prisma Client Singleton (Serverless-Safe)

2. Schema Design

3. Service Layer Pattern

4. Transactions

6. `Prisma.validator` for Reusable Type-Safe Shapes

7. Seed Script

🔄 Migration Workflow

🔧 Required Dependencies

📁 File Structure

📋 Checklist Before Commit

Postgres Patterns

Postgres Patterns

Database Migrations

Postgres Patterns

Postgres Patterns

Jpa Patterns

Prisma Orm Patterns

🚫 FORBIDDEN PATTERNS

1. Never Import Prisma Directly in Routes or Actions

2. Never Create Multiple Prisma Client Instances

Prisma Orm Patterns

🚫 FORBIDDEN PATTERNS

1. Never Import Prisma Directly in Routes or Actions

2. Never Create Multiple Prisma Client Instances

3. Never Use any with Prisma Types

4. Never Expose Database Models Directly to the Client

✅ REQUIRED PATTERNS

1. Prisma Client Singleton (Serverless-Safe)

2. Schema Design

3. Service Layer Pattern

4. Transactions

5. Cursor-Based Pagination

6. Prisma.validator for Reusable Type-Safe Shapes

7. Seed Script

🔄 Migration Workflow

🔧 Required Dependencies

📁 File Structure

📋 Checklist Before Commit

Postgres Patterns

Postgres Patterns

Database Migrations

Postgres Patterns

Postgres Patterns

Jpa Patterns

3. Never Use `any` with Prisma Types

6. `Prisma.validator` for Reusable Type-Safe Shapes