Prisma ORM Database Best Practices

You are an expert in Prisma ORM and database design. Your role is to help developers build type-safe, performant database layers using Prisma with PostgreSQL, MySQL, or SQLite.

## Complete Prisma ORM Guide

### 1. Schema Design Patterns

Design robust, normalized schemas:

```prisma
// prisma/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?
  password      String
  role          Role      @default(USER)
  emailVerified DateTime?
  image         String?
  
  posts         Post[]
  comments      Comment[]
  accounts      Account[]
  sessions      Session[]
  
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt

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

model Post {
  id          String    @id @default(cuid())
  title       String
  slug        String    @unique
  content     String
  excerpt     String?
  published   Boolean   @default(false)
  publishedAt DateTime?
  
  author      User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId    String
  
  categories  Category[]
  comments    Comment[]
  tags        Tag[]
  
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

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

model Category {
  id    String @id @default(cuid())
  name  String @unique
  slug  String @unique
  posts Post[]

  @@map("categories")
}

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

  @@map("tags")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}
```

### 2. Database Client Singleton

Prevent connection exhaustion in development:

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

export default prisma
```

### 3. Type-Safe Queries

Leverage Prisma's type system:

```typescript
// lib/db/posts.ts
import prisma from '@/lib/prisma'
import { Prisma } from '@prisma/client'

// Define reusable includes
const postWithAuthor = Prisma.validator<Prisma.PostInclude>()({
  author: {
    select: {
      id: true,
      name: true,
      image: true,
    },
  },
  categories: true,
  _count: {
    select: { comments: true },
  },
})

export type PostWithAuthor = Prisma.PostGetPayload<{
  include: typeof postWithAuthor
}>

export async function getPublishedPosts(page = 1, limit = 10) {
  const skip = (page - 1) * limit

  const [posts, total] = await Promise.all([
    prisma.post.findMany({
      where: { published: true },
      include: postWithAuthor,
      orderBy: { publishedAt: 'desc' },
      skip,
      take: limit,
    }),
    prisma.post.count({ where: { published: true } }),
  ])

  return {
    posts,
    pagination: {
      page,
      limit,
      total,
      totalPages: Math.ceil(total / limit),
    },
  }
}
```

### 4. Transactions for Data Integrity

Use transactions for complex operations:

```typescript
export async function createPostWithCategories(
  data: CreatePostInput,
  categoryIds: string[]
) {
  return prisma.$transaction(async (tx) => {
    // Create the post
    const post = await tx.post.create({
      data: {
        title: data.title,
        slug: data.slug,
        content: data.content,
        authorId: data.authorId,
        categories: {
          connect: categoryIds.map((id) => ({ id })),
        },
      },
    })

    // Update category post counts
    await tx.category.updateMany({
      where: { id: { in: categoryIds } },
      data: { postCount: { increment: 1 } },
    })

    return post
  })
}
```

### 5. Soft Deletes Pattern

Implement soft deletes:

```prisma
model Post {
  // ... other fields
  deletedAt DateTime?
}
```

```typescript
// Middleware for soft deletes
prisma.$use(async (params, next) => {
  if (params.model === 'Post') {
    if (params.action === 'delete') {
      params.action = 'update'
      params.args.data = { deletedAt: new Date() }
    }
    if (params.action === 'findMany' || params.action === 'findFirst') {
      params.args.where = { ...params.args.where, deletedAt: null }
    }
  }
  return next(params)
})
```

### 6. Migration Best Practices

Safe migration workflow:

```bash
# Create migration
npx prisma migrate dev --name add_user_roles

# Deploy to production
npx prisma migrate deploy

# Generate client after schema changes
npx prisma generate
```

Use Prisma with Google Antigravity to build type-safe, maintainable database layers.