Database Migrations: Prisma vs Drizzle vs Raw SQL (2026 Guide)

Database migrations are the scariest part of shipping. One bad migration can take down your production database. Here's how to handle them safely with Prisma Migrate, Drizzle Kit, or raw SQL migrations.

Quick Comparison

Feature	Prisma Migrate	Drizzle Kit	Raw SQL
Schema definition	Prisma Schema (.prisma)	TypeScript	SQL
Migration format	SQL files (generated)	SQL files (generated)	SQL files (hand-written)
Diffing	Schema → SQL	Schema → SQL	Manual
Rollback	Manual (write down migration)	Manual	Manual
Custom SQL	Supported (edit generated files)	Supported	Native
Data migrations	Separate step	Separate step	Inline
CI/CD integration	prisma migrate deploy	drizzle-kit migrate	Any runner
Learning curve	Low	Low	Higher (SQL knowledge)
Control	Medium	High	Full

Prisma Migrate

Prisma generates SQL migrations from your Prisma schema. Change the schema, run prisma migrate dev, and it creates a migration SQL file.

Workflow

// schema.prisma — add a new field
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  role      String   @default("user")  // ← new field
  createdAt DateTime @default(now())
}

npx prisma migrate dev --name add-user-role
# Creates: prisma/migrations/20260313_add_user_role/migration.sql

Generated SQL:

ALTER TABLE "User" ADD COLUMN "role" TEXT NOT NULL DEFAULT 'user';

Strengths

• Automatic diffing. Change the schema, get the SQL. No manual SQL for common operations.
• Migration history. Tracks applied migrations in a _prisma_migrations table.
• Safe for teams. Multiple developers can create migrations independently; Prisma handles ordering.
• Shadow database. Uses a temporary database to verify migrations before applying.
• Production deploy. prisma migrate deploy applies pending migrations safely.

Weaknesses

• Limited control. Complex migrations (data transformations, multi-step) require editing generated SQL.
• No rollback. Prisma doesn't generate down migrations. You write rollbacks manually.
• Shadow database requirement. Needs permission to create/drop databases in development.
• Lock-in to Prisma. Migrations assume Prisma is your ORM.

Best For

Teams using Prisma as their ORM who want the simplest migration workflow.

Drizzle Kit

Drizzle Kit generates SQL migrations from your Drizzle schema definitions in TypeScript.

Workflow

// schema.ts — add a new field
import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name'),
  role: text('role').notNull().default('user'),  // ← new field
  createdAt: timestamp('created_at').defaultNow(),
})

npx drizzle-kit generate
# Creates: drizzle/0001_add_user_role.sql

Strengths

• TypeScript schema. Your schema is TypeScript — same language as your app. Full IDE support.
• Transparent SQL. Generated migrations are plain SQL files you can review and edit.
• Lightweight. Drizzle Kit is a dev dependency only — not needed in production.
• Database introspection. drizzle-kit introspect generates a Drizzle schema from an existing database.
• Push mode. drizzle-kit push applies changes directly without migration files (great for prototyping).

Weaknesses

• No automatic rollbacks. Like Prisma, down migrations are manual.
• Newer tool. Less documentation and fewer edge cases handled compared to Prisma.
• Custom migration naming. Generated migration names aren't always descriptive.

Best For

Teams using Drizzle ORM who want SQL-transparent migrations with TypeScript schema definitions.

Raw SQL Migrations

Write migration files by hand in pure SQL. Use a runner like node-pg-migrate, graphile-migrate, dbmate, or golang-migrate.

Workflow

-- migrations/001_create_users.up.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- migrations/001_create_users.down.sql
DROP TABLE users;

-- migrations/002_add_user_role.up.sql
ALTER TABLE users ADD COLUMN role TEXT NOT NULL DEFAULT 'user';

-- migrations/002_add_user_role.down.sql
ALTER TABLE users DROP COLUMN role;

Strengths

• Full control. Write exactly the SQL you want. No generated code to review.
• Down migrations. Writing rollbacks is natural (you write both up and down).
• ORM-agnostic. Works with any ORM or no ORM. Switch ORMs without changing migrations.
• Data migrations inline. Combine schema changes with data transformations in one migration.
• No magic. What you write is what runs. No diffing surprises.

Weaknesses

• More work. You write every migration manually. No auto-generation.
• SQL knowledge required. You need to know ALTER TABLE syntax, constraints, and database-specific features.
• Error-prone. Manual SQL means more opportunities for typos and mistakes.
• No schema sync. Your application schema (if using an ORM) can drift from migration state.

Best For

Teams that value full control, use multiple ORMs, or have complex migration requirements (data transformations, stored procedures, custom indexes).

Best Practices (All Approaches)

1. Always Review Generated SQL

Even with Prisma or Drizzle, read the generated SQL before applying. Auto-generated migrations can:

• Drop and recreate columns (losing data) instead of renaming
• Create inefficient indexes
• Miss edge cases in data type changes

2. Never Edit Applied Migrations

Once a migration has been applied to any environment, treat it as immutable. Create a new migration for fixes.

3. Handle Data Migrations Separately

Avoid mixing schema changes and data transformations in one migration when possible:

-- Migration 1: Add new column (nullable)
ALTER TABLE users ADD COLUMN full_name TEXT;

-- Migration 2: Backfill data
UPDATE users SET full_name = first_name || ' ' || last_name;

-- Migration 3: Make non-nullable, drop old columns
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;

4. Test Migrations Against Production-Like Data

A migration that works on an empty dev database may fail on production with millions of rows. Test with realistic data volumes.

5. Use Transactions

Wrap migrations in transactions when your database supports transactional DDL (PostgreSQL does, MySQL doesn't for most DDL).

6. Plan for Zero-Downtime

For production deployments:

• Add columns as nullable first
• Deploy code that handles both old and new schema
• Backfill data
• Make column non-null
• Remove old column references from code

FAQ

Can I switch migration tools?

Yes, but carefully. Export your current schema as SQL, set up the new tool, and mark all existing migrations as applied. Test thoroughly in staging.

Should I check migration files into git?

Absolutely. Migration files are part of your codebase. They should be reviewed in PRs like any other code change.

How do I handle migrations in CI/CD?

Run prisma migrate deploy or drizzle-kit migrate or your SQL runner as part of your deployment pipeline, before the new application code starts.

What about MongoDB migrations?

MongoDB doesn't have schema migrations in the traditional sense. Use application-level migration scripts or tools like migrate-mongo. Prisma supports MongoDB but migrations work differently.

The Verdict

• Prisma Migrate for teams already using Prisma. Simplest workflow for common operations.
• Drizzle Kit for teams using Drizzle. TypeScript-native with transparent SQL output.
• Raw SQL for maximum control, complex migrations, or ORM-agnostic setups.

The right choice depends on your ORM. If you're using Prisma, use Prisma Migrate. If you're using Drizzle, use Drizzle Kit. If you're not using either (or need full control), write raw SQL with a migration runner.