Prisma Schema

Design and configure Prisma schema files with models, relations, and attributes.

After Every Schema Change → Run a Migration

Any change to schema.prisma (adding fields, models, indexes, relations, enums) requires a migration to take effect across environments:

npx prisma migrate dev --name describe_what_changed

Never use prisma db push on a project with existing migrations — it bypasses the migration system and causes schema drift in Docker, CI/CD, and other environments.

Schema File Location

Default: prisma/schema.prisma

Custom location via package.json:

{
  "prisma": {
    "schema": "db/schema.prisma"
  }
}

Schema Structure

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

// Generator configuration
generator client {
  provider = "prisma-client-js"
}

// Models
model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  name  String?
  posts Post[]
}

Field Types

Scalar Types

Prisma Type	PostgreSQL	MySQL	SQLite
String	TEXT	VARCHAR(191)	TEXT
Int	INTEGER	INT	INTEGER
BigInt	BIGINT	BIGINT	BIGINT
Float	DOUBLE PRECISION	DOUBLE	REAL
Decimal	DECIMAL(65,30)	DECIMAL(65,30)	DECIMAL
Boolean	BOOLEAN	BOOLEAN	INTEGER
DateTime	TIMESTAMP(3)	DATETIME(3)	DATETIME
Json	JSONB	JSON	TEXT
Bytes	BYTEA	LONGBLOB	BLOB

Optional and List Types

model Example {
  required   String      // Required field
  optional   String?     // Optional field (nullable)
  list       String[]    // Array/list (not all DBs support)
}

Field Attributes

Primary Key

model User {
  id        Int      @id @default(autoincrement())  // Auto-increment
  uuid      String   @id @default(uuid())           // UUID
  cuid      String   @id @default(cuid())           // CUID
}

Composite Primary Key

model PostTag {
  postId Int
  tagId  Int

  @@id([postId, tagId])
}

Unique Constraints

model User {
  email String @unique                    // Single field unique

  firstName String
  lastName  String
  @@unique([firstName, lastName])         // Composite unique
}

Default Values

model Post {
  id        Int      @id @default(autoincrement())
  uuid      String   @default(uuid())
  createdAt DateTime @default(now())
  status    Status   @default(DRAFT)
  views     Int      @default(0)
}

Database Mapping

model User {
  id Int @id @map("user_id")      // Column name mapping

  @@map("users")                   // Table name mapping
}

Relations

One-to-Many

model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id       Int  @id @default(autoincrement())
  author   User @relation(fields: [authorId], references: [id])
  authorId Int
}

One-to-One

model User {
  id      Int      @id @default(autoincrement())
  profile Profile?
}

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

Many-to-Many (Implicit)

model Post {
  id   Int   @id @default(autoincrement())
  tags Tag[]
}

model Tag {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

Many-to-Many (Explicit)

model Post {
  id       Int        @id @default(autoincrement())
  postTags PostTag[]
}

model Tag {
  id       Int        @id @default(autoincrement())
  postTags PostTag[]
}

model PostTag {
  postId    Int
  tagId     Int
  assignedAt DateTime @default(now())

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

  @@id([postId, tagId])
}

Self-Relations

model User {
  id         Int     @id @default(autoincrement())
  followedBy User[]  @relation("UserFollows")
  following  User[]  @relation("UserFollows")
}

Referential Actions

model Post {
  author   User @relation(fields: [authorId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  authorId Int
}

Options: Cascade, Restrict, NoAction, SetNull, SetDefault

Indexes

model Post {
  id        Int      @id
  title     String
  content   String
  authorId  Int
  createdAt DateTime

  @@index([authorId])                          // Single column
  @@index([authorId, createdAt])               // Composite
  @@index([title], type: Hash)                 // Hash index (PostgreSQL)
  @@index([content], type: Gin)                // GIN index (PostgreSQL)
  @@fulltext([title, content])                 // Full-text (MySQL)
}

Enums

enum Role {
  USER
  ADMIN
  MODERATOR
}

model User {
  id   Int  @id
  role Role @default(USER)
}

Model Attributes

model Post {
  // Fields...

  @@id([field1, field2])           // Composite primary key
  @@unique([field1, field2])       // Composite unique
  @@index([field1, field2])        // Index
  @@map("posts")                    // Table name
  @@schema("blog")                  // Schema (multi-schema)
  @@ignore                          // Ignore model in client
}

Validation Commands

# Validate schema syntax
npx prisma validate

# Format schema file
npx prisma format

# View current database schema
npx prisma db pull