Schema & Migrations

schema.prisma

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

datasource db {
  provider = "postgresql"    // postgresql | mysql | sqlite | mongodb | sqlserver
  url      = env("DATABASE_URL")
}

// Basic model
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String
  bio       String?
  role      Role     @default(USER)
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  posts     Post[]
  profile   Profile?

  @@map("users")            // map to different table name
  @@index([email])
}

// Enum
enum Role {
  USER
  ADMIN
  MOD
}

// One-to-one
model Profile {
  id     String  @id @default(cuid())
  bio    String?
  avatar String?
  userId String  @unique
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)
}

// One-to-many
model Post {
  id        String    @id @default(cuid())
  title     String
  content   String?
  published Boolean   @default(false)
  authorId  String
  author    User      @relation(fields: [authorId], references: [id])
  tags      Tag[]     // many-to-many
  comments  Comment[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  @@index([authorId])
  @@fulltext([title, content])   // MySQL only
}

// Many-to-many (explicit join table)
model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]
}

// Many-to-many with extra fields (explicit relation table)
model UserFollower {
  followerId  String
  followingId String
  createdAt   DateTime @default(now())

  follower  User @relation("following", fields: [followerId], references: [id])
  following User @relation("followers", fields: [followingId], references: [id])

  @@id([followerId, followingId])
}

Field Types & Attributes

model Example {
  // ID strategies
  id     Int    @id @default(autoincrement())
  id     String @id @default(cuid())
  id     String @id @default(uuid())

  // Field attributes
  email  String  @unique
  code   String  @default("N/A")
  price  Float   @default(0.0)
  data   Json?
  bytes  Bytes?

  // Timestamps
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  deletedAt DateTime?

  // DB-native attributes
  name   String @db.VarChar(100)    // PostgreSQL
  body   String @db.Text

  // Model-level attributes
  @@unique([firstName, lastName])
  @@index([email, createdAt(sort: Desc)])
  @@id([tenantId, userId])          // composite primary key
}

Migrations

# Generate and apply migration
npx prisma migrate dev --name add_user_bio

# Apply migrations in production (no schema changes, no data seeding)
npx prisma migrate deploy

# View migration status
npx prisma migrate status

# Reset database (drops, re-creates, re-applies migrations + seed)
npx prisma migrate reset

# Generate Prisma Client after schema changes
npx prisma generate

# Push schema changes without migration (dev/prototyping only — NEVER prod)
npx prisma db push

# Introspect existing database → generate schema
npx prisma db pull

# Open Prisma Studio (GUI)
npx prisma studio

# Seed database
npx prisma db seed

# package.json seed script
{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  }
}

Client Setup

// lib/prisma.ts — singleton pattern (important for hot reloading)
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', 'error', 'warn']
      : ['error'],
  });

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