Database Migration Tools: Schema Management for Every Stack

Database Migration Tools: Schema Management for Every Stack

Database migrations are the unsexy backbone of every application. Get them right and deploys are boring (the good kind). Get them wrong and you're restoring from backups at 2 AM. The right tool depends on your language ecosystem, your database, and how much control you need over the SQL.

Why Migration Tools Matter

Without a migration tool, schema changes are ad-hoc -- someone runs SQL manually in production, forgets to update staging, and the next deploy breaks. Migration tools solve this by:

1. Versioning schema changes as code (tracked in git)
2. Applying changes in order across all environments
3. Tracking which migrations have run via a metadata table
4. Enabling rollbacks when a migration goes wrong
5. Supporting team workflows -- multiple developers can write migrations without conflicts

Tool Comparison

Prisma Migrate: Schema-First for TypeScript

Prisma is the most popular ORM in the TypeScript ecosystem, and its migration tool integrates tightly with the Prisma schema. You define your data model, and Prisma generates the SQL migrations.

Defining the Schema

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  role      Role     @default(USER)
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
  @@map("users")
}

model Post {
  id        String   @id @default(uuid())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  String
  tags      Tag[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

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

model Tag {
  id    String @id @default(uuid())
  name  String @unique
  posts Post[]

  @@map("tags")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

Creating and Running Migrations

# Create a migration from schema changes
npx prisma migrate dev --name add_user_roles
# This:
# 1. Diffs your schema against the database
# 2. Generates a SQL migration file
# 3. Applies it to your development database
# 4. Regenerates the Prisma Client

# View the generated migration
cat prisma/migrations/20260209120000_add_user_roles/migration.sql

-- Generated migration
CREATE TYPE "Role" AS ENUM ('USER', 'ADMIN', 'MODERATOR');

ALTER TABLE "users" ADD COLUMN "role" "Role" NOT NULL DEFAULT 'USER';

# Apply migrations in production (no interactive prompts)
npx prisma migrate deploy

# Check migration status
npx prisma migrate status

# Reset database (development only)
npx prisma migrate reset

Prisma Migrate Strengths and Limitations

Strengths:

• Schema is the single source of truth -- generates migrations automatically
• Type-safe client generated from the schema
• Migration files are plain SQL, reviewable in PRs
• Excellent for greenfield TypeScript projects

Limitations:

• No built-in rollback -- you must write a new migration to undo changes
• Limited control over generated SQL (sometimes generates suboptimal migrations)
• Doesn't support all database features (partial indexes, complex constraints require raw SQL)
• Shadow database required for migrate dev (needs permission to create/drop databases)

Handling Complex Migrations in Prisma

When auto-generated migrations aren't enough, edit the SQL before applying:

# Create the migration without applying it
npx prisma migrate dev --name rename_column --create-only

# Edit the generated SQL file
# prisma/migrations/20260209130000_rename_column/migration.sql

-- Prisma would generate DROP + ADD, but we want to preserve data
-- Manually edit to use RENAME instead:
ALTER TABLE "users" RENAME COLUMN "name" TO "display_name";

# Now apply the edited migration
npx prisma migrate dev

Drizzle Kit: SQL Control with TypeScript Safety

Drizzle takes a different approach from Prisma. It's closer to SQL, gives you more control, and generates migrations from TypeScript schema definitions. If you love SQL but want type safety, Drizzle is the answer.

Defining the Schema

// src/db/schema.ts
import {
  pgTable,
  text,
  timestamp,
  uuid,
  boolean,
  pgEnum,
  index,
} from "drizzle-orm/pg-core";

export const roleEnum = pgEnum("role", ["user", "admin", "moderator"]);

export const users = pgTable(
  "users",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    email: text("email").unique().notNull(),
    name: text("name").notNull(),
    role: roleEnum("role").default("user").notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => ({
    emailIdx: index("users_email_idx").on(table.email),
  })
);

export const posts = pgTable(
  "posts",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    title: text("title").notNull(),
    content: text("content"),
    published: boolean("published").default(false).notNull(),
    authorId: uuid("author_id")
      .references(() => users.id)
      .notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => ({
    authorIdx: index("posts_author_idx").on(table.authorId),
  })
);

Drizzle Configuration

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Generating and Running Migrations

# Generate migration from schema diff
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Push schema directly (development -- skips migration files)
npx drizzle-kit push

# Open Drizzle Studio (database browser)
npx drizzle-kit studio

Drizzle Query Examples

import { db } from "./db";
import { users, posts } from "./db/schema";
import { eq, and, desc, sql } from "drizzle-orm";

// Type-safe queries that look like SQL
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.role, "admin"))
  .orderBy(desc(users.createdAt))
  .limit(10);

// Joins
const usersWithPosts = await db
  .select({
    user: users,
    postCount: sql<number>`count(${posts.id})::int`,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.id);

// Insert with returning
const [newUser] = await db
  .insert(users)
  .values({ name: "Alice", email: "[email protected]" })
  .returning();

Drizzle Strengths and Limitations

Strengths:

• SQL-like API -- if you know SQL, you know Drizzle
• Full control over generated SQL
• Lightweight with no code generation step (schema is runtime code)
• Supports complex SQL features (CTEs, subqueries, raw SQL)
• Fast -- minimal abstraction layer

Limitations:

• Smaller ecosystem than Prisma (fewer guides, fewer integrations)
• Migration tooling is newer and less battle-tested
• No built-in seeding mechanism
• Schema changes can sometimes generate unexpected migrations

Flyway: The SQL Purist's Choice

Flyway is a Java-based migration tool that works with plain SQL files. No ORM, no schema DSL -- just versioned SQL scripts. It's the standard in the JVM world and works well for any team that wants full SQL control.

Migration Files

sql/
├── V1__create_users_table.sql
├── V2__create_posts_table.sql
├── V3__add_user_roles.sql
├── V4__add_post_tags.sql
└── R__refresh_materialized_views.sql  # Repeatable migration

-- V1__create_users_table.sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_users_email ON users (email);

-- V3__add_user_roles.sql
CREATE TYPE user_role AS ENUM ('user', 'admin', 'moderator');

ALTER TABLE users ADD COLUMN role user_role NOT NULL DEFAULT 'user';

-- Backfill: promote existing users who were added by admins
UPDATE users SET role = 'admin' WHERE email LIKE '%@company.com';

Running Flyway

# Using the CLI
flyway migrate
flyway info        # Show migration status
flyway validate    # Verify applied migrations match local files
flyway repair      # Fix metadata table after failed migrations

# Using Docker (no Java installation required)
docker run --rm \
  -v $(pwd)/sql:/flyway/sql \
  -e FLYWAY_URL=jdbc:postgresql://localhost:5432/mydb \
  -e FLYWAY_USER=postgres \
  -e FLYWAY_PASSWORD=secret \
  flyway/flyway migrate

Flyway Naming Convention

V{version}__{description}.sql     -- Versioned migration (runs once)
U{version}__{description}.sql     -- Undo migration (paid feature)
R__{description}.sql               -- Repeatable migration (runs when changed)

When to Choose Flyway

• You want full SQL control with no abstraction
• Your team includes DBAs who review migration SQL
• You're in a JVM ecosystem (Java, Kotlin, Scala)
• You need support for many database types
• You want simple, battle-tested tooling

Liquibase: Enterprise Migration Management

Liquibase is similar to Flyway but more feature-rich. It supports multiple changelog formats (XML, YAML, JSON, SQL), has built-in rollback, and offers advanced features for enterprise teams.

Changelog in YAML

# changelog.yaml
databaseChangeLog:
  - changeSet:
      id: 1
      author: alice
      changes:
        - createTable:
            tableName: users
            columns:
              - column:
                  name: id
                  type: uuid
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: email
                  type: text
                  constraints:
                    unique: true
                    nullable: false
              - column:
                  name: name
                  type: text
                  constraints:
                    nullable: false
              - column:
                  name: created_at
                  type: timestamptz
                  defaultValueComputed: now()
      rollback:
        - dropTable:
            tableName: users

  - changeSet:
      id: 2
      author: bob
      changes:
        - addColumn:
            tableName: users
            columns:
              - column:
                  name: role
                  type: text
                  defaultValue: "user"
                  constraints:
                    nullable: false
      rollback:
        - dropColumn:
            tableName: users
            columnName: role

Running Liquibase

liquibase update
liquibase status
liquibase rollback-count 1    # Roll back the last changeset
liquibase diff                # Compare two databases
liquibase generate-changelog  # Reverse-engineer existing DB to changelog

When to Choose Liquibase

• You need built-in rollback support (not a paid add-on)
• Compliance requirements need detailed change tracking
• You prefer declarative changesets over raw SQL
• You need to reverse-engineer an existing database
• Your organization is already invested in the Liquibase ecosystem

golang-migrate: Language-Agnostic SQL Migrations

golang-migrate is a lightweight, CLI-based migration tool. It runs SQL migrations and supports 20+ databases. Despite the name, it works for any project -- it's just a CLI tool.

# Install
go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest
# or use Docker, or download a binary release

# Create a new migration pair
migrate create -ext sql -dir db/migrations -seq add_user_roles

# This creates:
# db/migrations/000003_add_user_roles.up.sql
# db/migrations/000003_add_user_roles.down.sql

-- 000003_add_user_roles.up.sql
ALTER TABLE users ADD COLUMN role TEXT NOT NULL DEFAULT 'user';

-- 000003_add_user_roles.down.sql
ALTER TABLE users DROP COLUMN role;

# Apply all pending migrations
migrate -database "postgres://localhost:5432/mydb?sslmode=disable" -path db/migrations up

# Roll back one migration
migrate -database "..." -path db/migrations down 1

# Go to a specific version
migrate -database "..." -path db/migrations goto 3

When to Choose golang-migrate

• You want a simple, standalone CLI tool
• You write Go backends (integrates as a library too)
• You need up + down migrations for every change
• You want minimal dependencies and maximum portability

Migration Patterns and Best Practices

Always Write Reversible Migrations

Even if your tool doesn't enforce it, think about how to undo every migration:

-- UP: Add a column
ALTER TABLE users ADD COLUMN phone TEXT;

-- DOWN: Remove the column
ALTER TABLE users DROP COLUMN phone;

Non-Destructive Column Renames

Never rename a column directly in production -- it will break running application instances:

-- Step 1: Add new column (deploy migration)
ALTER TABLE users ADD COLUMN display_name TEXT;
UPDATE users SET display_name = name;
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;

-- Step 2: Update application to read from display_name and write to both

-- Step 3: (next deploy) Stop writing to old column

-- Step 4: (next deploy) Drop old column
ALTER TABLE users DROP COLUMN name;

Safe Column Type Changes

-- BAD: Direct type change can lock the table and fail on incompatible data
ALTER TABLE orders ALTER COLUMN total TYPE NUMERIC(10,2);

-- SAFER: Add new column, backfill, swap
ALTER TABLE orders ADD COLUMN total_v2 NUMERIC(10,2);
UPDATE orders SET total_v2 = total::NUMERIC(10,2);
ALTER TABLE orders DROP COLUMN total;
ALTER TABLE orders RENAME COLUMN total_v2 TO total;

Large Table Migrations

For tables with millions of rows, backfill in batches:

-- Don't do this (locks the entire table):
UPDATE users SET normalized_email = LOWER(email);

-- Do this (batch update):
DO $$
DECLARE
  batch_size INT := 10000;
  rows_updated INT;
BEGIN
  LOOP
    UPDATE users
    SET normalized_email = LOWER(email)
    WHERE id IN (
      SELECT id FROM users
      WHERE normalized_email IS NULL
      LIMIT batch_size
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    RAISE NOTICE 'Updated % rows', rows_updated;
    EXIT WHEN rows_updated = 0;
    PERFORM pg_sleep(0.1);  -- Brief pause to reduce lock contention
  END LOOP;
END $$;

Migration Testing

// Test your migrations by running up + down + up
// This catches migrations that are irreversible
describe("database migrations", () => {
  it("should apply all migrations", async () => {
    await migrate("up");
    const tables = await db.query(
      "SELECT tablename FROM pg_tables WHERE schemaname = 'public'"
    );
    expect(tables.rows.map((r) => r.tablename)).toContain("users");
  });

  it("should roll back and reapply cleanly", async () => {
    await migrate("up");
    await migrate("down");
    await migrate("up");
    // If this succeeds without errors, migrations are reversible
  });
});

Decision Framework

Summary

Every project needs versioned, automated database migrations. The choice of tool depends more on your language ecosystem and SQL comfort level than on feature differences. If you're in TypeScript, choose between Prisma (schema-first, auto-generated) and Drizzle (SQL-first, more control). If you want raw SQL with no abstraction, Flyway and golang-migrate are excellent. And if enterprise compliance requires rollback tracking and audit trails, Liquibase has the richest feature set. Whatever you choose, start using it from day one -- retrofitting migrations onto an existing database is painful.