Converting a Complete Prisma Schema to SQL Migration Script

Full Schema Conversion

This example demonstrates converting a complete Prisma schema into a production-ready SQL migration script with all table definitions, constraints, and indexes.

Example Prisma Schema

enum Role {
  USER
  ADMIN
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  role      Role     @default(USER)
  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
  content   String?
  published Boolean   @default(false)
  authorId  Int       @map("author_id")
  author    User      @relation(fields: [authorId], references: [id])
  comments  Comment[]
  tags      PostTag[]
  createdAt DateTime  @default(now()) @map("created_at")

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

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

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

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

  @@map("tags")
}

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

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

Generated SQL (PostgreSQL)

The converter produces a complete migration script with:

1. CREATE TYPE for the Role enum
2. CREATE TABLE for each model, in dependency order
3. FOREIGN KEY constraints for all relations
4. PRIMARY KEY (including composite keys for PostTag)
5. CREATE INDEX for all @@index definitions

Order of Operations

When running the generated SQL as a migration:

1. Create enum types first (CREATE TYPE role AS ENUM ...)
2. Create tables without foreign key dependencies first (users, tags)
3. Create tables with foreign keys next (posts, comments, post_tags)
4. Create indexes last

The converter outputs statements in a logical order so you can typically run the entire script sequentially. For circular dependencies (rare but possible), you may need to create tables first and add foreign keys via ALTER TABLE afterwards.

Production Considerations

Before running the generated SQL in production:

• Add IF NOT EXISTS to avoid errors on re-runs
• Wrap in a transaction for atomic deployment
• Add ON DELETE and ON UPDATE cascade rules to foreign keys as needed
• Review index choices based on your actual query patterns