Prisma Cursor Rules: Type-Safe Database ORM

---
description: Enforces Prisma best practices including schema design, relation modeling, migration hygiene, typed Client queries, middleware patterns, and database seeding. Provides guidelines for maintainable, type-safe ORM usage in TypeScript backends.
globs: **/schema.prisma,**/*.ts
---
# Prisma Best Practices

You are an expert in Prisma ORM, database schema design, and TypeScript backend development.
You understand relational data modeling, migration strategies, and query optimization with Prisma Client.

### Schema Design
- Use singular model names: model User, not model Users
- Map table names with @map("users") to follow database naming conventions
- Use camelCase for field names, map to snake_case with @map("created_at")
- Define @id with @default(autoincrement()) or @default(uuid()) for primary keys
- Always add @updatedAt on updatedAt fields
- Use appropriate field types: String, Int, Boolean, DateTime, Decimal, Json, Bytes

### Relations
- Explicitly name relations with @relation(name: "AuthorPosts") when multiple relations exist between the same models
- Define foreign keys explicitly: authorId Int, author User @relation(fields: [authorId], references: [id])
- Use onDelete: Cascade or Restrict explicitly; never rely on database defaults
- Prefer optional relations with ? where the relationship is not guaranteed
- Use @@index on foreign key fields for query performance
- Avoid circular relations; use @@index + manual queries for complex traversals

### Migrations
- Use prisma migrate dev --create-only to generate migration files
- Review generated SQL in migration files before applying
- Never edit migration.sql manually after it's been applied
- Use prisma migrate deploy in CI/CD, never prisma db push in production
- Run prisma migrate status to check for unapplied migrations before deploy
- Name migrations descriptively: add_user_avatar_field, create_post_tags_table

### Client Queries
- Import { PrismaClient } and instantiate once (singleton pattern, avoid new PrismaClient() per request)
- Use findUnique + where for primary-key lookups
- Use findMany with where, orderBy, take, skip for list queries
- Use include or select (never both on the same query) for relation loading
- Use create/update/upsert for mutations; use createMany for bulk inserts
- Wrap Prisma operations in try/catch; handle PrismaClientKnownRequestError for constraint violations
- Use interactive transactions (prisma.$transaction([...])) for multi-table operations

### Middleware & Extensions
- Use prisma.$use() middleware for soft deletes, audit logging, or field encryption
- Use Prisma Client extensions for reusable query patterns (e.g., paginated findMany)
- Add computed fields with result extensions if they depend on existing fields

### Seeding
- Use prisma/seed.ts with a main() function that upserts reference data
- Use upsert (not create) in seed scripts for idempotent re-runs
- Seed with TypeScript (not SQL) to leverage type checking
- Call prisma.$disconnect() in a finally block in seed scripts and standalone tools (not per-request with the singleton client)

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

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  avatar    String?
  posts     Post[]
  comments  Comment[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("users")
}

model Post {
  id        Int       @id @default(autoincrement())
  title     String    @db.VarChar(200)
  body      String    @db.Text
  published Boolean   @default(false)
  author    User      @relation(fields: [authorId], references: [id])
  authorId  Int       @map("author_id")
  comments  Comment[]
  tags      PostTag[]
  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime  @updatedAt @map("updated_at")

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

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

  @@map("tags")
}

model PostTag {
  post   Post @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId Int  @map("post_id")
  tag    Tag  @relation(fields: [tagId], references: [id], onDelete: Cascade)
  tagId  Int  @map("tag_id")

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

model Comment {
  id        Int      @id @default(autoincrement())
  body      String   @db.Text
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId    Int      @map("post_id")
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId  Int      @map("author_id")
  createdAt DateTime @default(now()) @map("created_at")

  @@index([postId])
  @@map("comments")
}

// lib/prisma.ts — Singleton Prisma Client
import { PrismaClient } from '@prisma/client';

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

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

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

// services/postService.ts — Typed queries with Prisma
import { prisma } from '../lib/prisma';
import { Prisma } from '@prisma/client';

export async function getPublishedPosts(page: number, perPage: number) {
  const where: Prisma.PostWhereInput = { published: true };

  const [posts, total] = await prisma.$transaction([
    prisma.post.findMany({
      where,
      include: { author: { select: { id: true, name: true } }, tags: { include: { tag: true } } },
      orderBy: { createdAt: 'desc' },
      skip: (page - 1) * perPage,
      take: perPage,
    }),
    prisma.post.count({ where }),
  ]);

  return { posts, total };
}

export async function createPost(authorId: number, data: { title: string; body: string }) {
  return prisma.post.create({
    data: {
      title: data.title,
      body: data.body,
      author: { connect: { id: authorId } },
    },
    include: { author: { select: { id: true, name: true } } },
  });
}

// prisma/seed.ts — Idempotent database seeding
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  const user = await prisma.user.upsert({
    where: { email: '[email protected]' },
    update: {},
    create: {
      email: '[email protected]',
      name: 'Demo User',
    },
  });

  const post = await prisma.post.upsert({
    where: { id: 1 },
    update: {},
    create: {
      title: 'Getting Started with Prisma',
      body: 'Prisma makes database access type-safe and intuitive.',
      published: true,
      authorId: user.id,
    },
  });

  console.log({ user, post });
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });