Database Schema Versioning: Flyway vs Liquibase vs Prisma Migrate in Production

Schema migrations are the most dangerous operation in production engineering. A bad migration can lock tables, corrupt data, or bring down your application — and unlike a bad code deploy, you can't always just roll back. The tool you choose shapes how your team thinks about schema changes, and each of the major options makes very different tradeoffs.

This post compares Flyway, Liquibase, and Prisma Migrate across the dimensions that matter for production teams: migration authoring, branching support, rollback strategy, CI/CD integration, and zero-downtime patterns.

Tool Overview

---

1. Flyway

Flyway is the simplest tool: numbered SQL files applied in order. Its strength is that your migrations are plain SQL — readable, portable, and debuggable without tooling.

File Naming Convention

db/migrations/
├── V1__create_users.sql
├── V2__add_users_email_index.sql
├── V3__create_accounts.sql
├── V3.1__add_accounts_plan.sql      # Hotfix in same sprint
├── V4__create_subscriptions.sql
├── R__refresh_billing_view.sql       # Repeatable migration (re-runs on change)
└── U4__undo_create_subscriptions.sql # Undo script (Flyway Teams only)

Version format: V{major}.{minor}.{patch}__{description}.sql
Rule: Never edit a migration once applied. Create a new one.

Flyway Configuration

# flyway.toml
[flyway]
url = "jdbc:postgresql://localhost:5432/myapp"
user = "${FLYWAY_DB_USER}"
password = "${FLYWAY_DB_PASSWORD}"
locations = ["filesystem:db/migrations"]
table = "flyway_schema_history"
baselineOnMigrate = false
validateOnMigrate = true
outOfOrder = false          # Fail if applied versions have gaps
cleanDisabled = true        # Never allow flyway clean in production

Docker Compose for Local Dev

# docker-compose.yml
services:
  flyway:
    image: flyway/flyway:10
    command: migrate
    environment:
      FLYWAY_URL: jdbc:postgresql://postgres:5432/myapp
      FLYWAY_USER: postgres
      FLYWAY_PASSWORD: postgres
      FLYWAY_LOCATIONS: filesystem:/flyway/sql
    volumes:
      - ./db/migrations:/flyway/sql
    depends_on:
      postgres:
        condition: service_healthy

Production Migration Script

#!/usr/bin/env bash
# scripts/migrate.sh — runs before app deploy

set -euo pipefail

echo "Running Flyway migrations..."

flyway \
  -url="jdbc:postgresql://${DB_HOST}:${DB_PORT}/${DB_NAME}" \
  -user="${DB_USER}" \
  -password="${DB_PASSWORD}" \
  -locations="filesystem:db/migrations" \
  -outOfOrder=false \
  -validateOnMigrate=true \
  migrate

echo "✅ Migrations complete"
flyway info  # Print migration history

Branching with Flyway

Flyway is linear — it doesn't natively handle parallel feature branches. The community convention is timestamp-based versioning:

V20261103120000__add_billing_portal.sql   # Feature A (merged first)
V20261103143000__add_audit_log.sql         # Feature B (merged second)

This avoids conflicts between feature branches since timestamps are naturally ordered.

# Helper to generate a new migration file with timestamp
new-migration() {
  local description="${1:?Usage: new-migration <description>}"
  local timestamp
  timestamp=$(date +%Y%m%d%H%M%S)
  local filename="db/migrations/V${timestamp}__${description// /_}.sql"
  touch "$filename"
  echo "Created: $filename"
}

---

2. Liquibase

Liquibase uses "changelogs" containing "changesets" — logical units of schema change. The advantage is that Liquibase tracks which changesets have run (by ID, not filename), supports multi-database targeting, and has first-class rollback support.

Changelog Structure

# db/changelog/db.changelog-master.yaml
databaseChangeLog:
  - include:
      file: db/changelog/2026/11/001-create-users.yaml
  - include:
      file: db/changelog/2026/11/002-create-accounts.yaml
  - include:
      file: db/changelog/2026/11/003-add-billing.yaml

# db/changelog/2026/11/001-create-users.yaml
databaseChangeLog:
  - changeSet:
      id: 001-create-users
      author: engineering-team
      labels: v1.0
      context: "!test"  # Don't run in test environments
      changes:
        - createTable:
            tableName: users
            columns:
              - column:
                  name: id
                  type: UUID
                  constraints:
                    primaryKey: true
                    nullable: false
                  defaultValueComputed: gen_random_uuid()
              - column:
                  name: email
                  type: VARCHAR(255)
                  constraints:
                    nullable: false
                    unique: true
              - column:
                  name: created_at
                  type: TIMESTAMPTZ
                  defaultValueComputed: NOW()
                  constraints:
                    nullable: false
        - createIndex:
            tableName: users
            indexName: idx_users_email
            columns:
              - column:
                  name: email
      rollback:
        - dropTable:
            tableName: users

SQL Changeset with Rollback

  - changeSet:
      id: 003-add-subscription-status-index
      author: devops
      comment: "Performance: filter subscriptions by status + account_id"
      changes:
        - sql:
            sql: |
              CREATE INDEX CONCURRENTLY idx_subscriptions_status_account
              ON subscriptions (account_id, status)
              WHERE status IN ('active', 'trialing');
            splitStatements: false  # Required for CONCURRENTLY
      rollback:
        - sql:
            sql: DROP INDEX IF EXISTS idx_subscriptions_status_account;

Liquibase CI/CD Integration

# .github/workflows/migrate.yml
name: Database Migration

on:
  push:
    branches: [main]
    paths:
      - 'db/changelog/**'

jobs:
  validate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      
      - name: Validate changelog (no DB needed)
        run: |
          docker run --rm \
            -v ${{ github.workspace }}/db:/liquibase/changelog \
            liquibase/liquibase:4.29 \
            --changelog-file=changelog/db.changelog-master.yaml \
            validate

  migrate-staging:
    runs-on: ubuntu-latest
    needs: validate
    environment: staging
    steps:
      - uses: actions/checkout@v4
      
      - name: Run migrations
        env:
          LIQUIBASE_URL: ${{ secrets.STAGING_DB_URL }}
          LIQUIBASE_USERNAME: ${{ secrets.STAGING_DB_USER }}
          LIQUIBASE_PASSWORD: ${{ secrets.STAGING_DB_PASSWORD }}
        run: |
          docker run --rm \
            -v ${{ github.workspace }}/db:/liquibase/changelog \
            -e LIQUIBASE_URL \
            -e LIQUIBASE_USERNAME \
            -e LIQUIBASE_PASSWORD \
            liquibase/liquibase:4.29 \
            --changelog-file=changelog/db.changelog-master.yaml \
            update

      - name: Generate diff report
        run: |
          # Generate a report of what was applied
          docker run --rm \
            -v ${{ github.workspace }}/db:/liquibase/changelog \
            -e LIQUIBASE_URL \
            liquibase/liquibase:4.29 \
            status --verbose

---

3. Prisma Migrate

Prisma Migrate generates SQL migrations automatically by diffing your schema.prisma against the current database state. It's the right choice when your team already uses Prisma as an ORM — the schema is the single source of truth.

Workflow

# 1. Edit schema.prisma (add a column, index, relation, etc.)
# 2. Create a named migration
npx prisma migrate dev --name add_subscription_metadata

# Prisma:
# - Generates db/migrations/20261103120000_add_subscription_metadata/migration.sql
# - Applies it to dev database
# - Regenerates Prisma Client

# 3. Deploy to staging/prod
npx prisma migrate deploy  # Only runs pending migrations — never generates new ones

schema.prisma Best Practices

// prisma/schema.prisma
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins"]
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_DATABASE_URL") // For pgBouncer: migrate uses directUrl
}

model User {
  id        String   @id @default(uuid()) @db.Uuid
  email     String   @unique @db.VarChar(255)
  name      String?  @db.VarChar(100)
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  
  account   Account?

  @@map("users")
  @@index([createdAt(sort: Desc)])
}

model Subscription {
  id                    String   @id @default(uuid()) @db.Uuid
  accountId             String   @unique @map("account_id") @db.Uuid
  status                SubStatus
  stripeSubscriptionId  String?  @unique @map("stripe_subscription_id")
  currentPeriodEnd      DateTime @map("current_period_end")
  cancelAtPeriodEnd     Boolean  @default(false) @map("cancel_at_period_end")
  metadata              Json     @default("{}")
  createdAt             DateTime @default(now()) @map("created_at")
  
  account Account @relation(fields: [accountId], references: [id], onDelete: Cascade)

  @@map("subscriptions")
  @@index([status, currentPeriodEnd], name: "idx_subscriptions_status_period")
}

enum SubStatus {
  trialing
  active
  past_due
  canceled
  paused
  
  @@map("subscription_status")
}

Generated Migration SQL

-- prisma/migrations/20261103120000_add_subscription_metadata/migration.sql
-- CreateEnum
CREATE TYPE "subscription_status" AS ENUM ('trialing', 'active', 'past_due', 'canceled', 'paused');

-- CreateTable
CREATE TABLE "subscriptions" (
    "id" UUID NOT NULL,
    "account_id" UUID NOT NULL,
    "status" "subscription_status" NOT NULL,
    "stripe_subscription_id" TEXT,
    "current_period_end" TIMESTAMPTZ NOT NULL,
    "cancel_at_period_end" BOOLEAN NOT NULL DEFAULT false,
    "metadata" JSONB NOT NULL DEFAULT '{}',
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "subscriptions_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "subscriptions_account_id_key" ON "subscriptions"("account_id");
CREATE UNIQUE INDEX "subscriptions_stripe_subscription_id_key" ON "subscriptions"("stripe_subscription_id");
CREATE INDEX "idx_subscriptions_status_period" ON "subscriptions"("status", "current_period_end");

-- AddForeignKey
ALTER TABLE "subscriptions" ADD CONSTRAINT "subscriptions_account_id_fkey"
FOREIGN KEY ("account_id") REFERENCES "accounts"("id") ON DELETE CASCADE ON UPDATE CASCADE;

Handling Prisma Migrate in CI/CD

# .github/workflows/deploy.yml (relevant section)
- name: Run Prisma migrations
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}
    DIRECT_DATABASE_URL: ${{ secrets.DIRECT_DATABASE_URL }}
  run: |
    # deploy only — never runs migrate dev in production
    npx prisma migrate deploy
    
- name: Verify migration status
  run: |
    npx prisma migrate status

Critical: Never run prisma migrate dev in production or CI against a production database. It can reset the shadow database and cause downtime.

---

Zero-Downtime Migration Patterns

These patterns apply regardless of which tool you use.

Pattern 1: Add Column → Backfill → Add NOT NULL Constraint

-- Step 1: Add nullable column (instant — no lock on large tables)
ALTER TABLE users ADD COLUMN subscription_tier VARCHAR(20);

-- Step 2: Backfill in batches (run as separate migration or script)
DO $$
DECLARE
  batch_size INT := 1000;
  last_id UUID := '00000000-0000-0000-0000-000000000000';
  rows_updated INT;
BEGIN
  LOOP
    WITH batch AS (
      SELECT id FROM users
      WHERE id > last_id
        AND subscription_tier IS NULL
      ORDER BY id
      LIMIT batch_size
    )
    UPDATE users u
    SET subscription_tier = 'free'
    FROM batch
    WHERE u.id = batch.id
    RETURNING u.id INTO last_id;
    
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    
    PERFORM pg_sleep(0.05); -- Brief pause between batches
  END LOOP;
END $$;

-- Step 3: Add NOT NULL constraint (deploy after backfill is verified)
ALTER TABLE users ALTER COLUMN subscription_tier SET NOT NULL;
ALTER TABLE users ALTER COLUMN subscription_tier SET DEFAULT 'free';

Pattern 2: Rename Column Without Downtime

Renaming a column while the app is running breaks existing queries. The safe sequence:

-- Migration 1: Add new column (deploy app version reads BOTH)
ALTER TABLE orders ADD COLUMN customer_id UUID;
UPDATE orders SET customer_id = user_id WHERE customer_id IS NULL;
-- Create index on new column
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

-- Migration 2 (after app deployed reading new column): Drop old column
ALTER TABLE orders DROP COLUMN user_id;

Pattern 3: CREATE INDEX CONCURRENTLY

Never use CREATE INDEX on a table with active writes — it takes an exclusive lock. Always use CONCURRENTLY:

-- Runs without blocking reads/writes (takes longer, uses more resources)
CREATE INDEX CONCURRENTLY idx_subscriptions_renewal
ON subscriptions (current_period_end)
WHERE status = 'active';

-- In Prisma: wrap in raw SQL in a separate migration
-- In Flyway: set splitStatements=false
-- In Liquibase: set splitStatements: false on the sql changeset

Detecting Lock Contention

-- Monitor for blocked migrations
SELECT
  pid,
  now() - query_start AS duration,
  state,
  wait_event_type,
  wait_event,
  left(query, 100) AS query_snippet
FROM pg_stat_activity
WHERE state != 'idle'
  AND wait_event_type = 'Lock'
ORDER BY duration DESC;

-- Find what's holding the lock
SELECT
  l.pid,
  l.locktype,
  l.relation::regclass AS table,
  l.mode,
  l.granted,
  a.query,
  now() - a.query_start AS duration
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY duration DESC;

---

Tool Selection Guide

Do you use Prisma as your ORM?
  YES → Prisma Migrate (schema is source of truth, DX is excellent)
  NO  → Continue...
  
Do you need multi-database support (Postgres + MySQL + Oracle)?
  YES → Liquibase (best multi-DB support + enterprise compliance features)
  NO  → Continue...
  
Do you prefer writing plain SQL?
  YES → Flyway (minimal abstraction, SQL-first, easy to debug)
  NO  → Liquibase (XML/YAML changesets with built-in rollback)

---

Cost Reference

Operational cost: The real cost of schema migrations is downtime risk. A migration that locks a 100M-row table for 30 seconds on a busy system costs more than any tooling license.

---

See Also

• Zero-Downtime Database Migrations
• PostgreSQL Performance: Indexes, Query Plans, and Connection Pooling
• Prisma Production Patterns: N+1, Transactions, and Cursor Pagination
• Database Design Patterns for SaaS Applications
• Database Connection Pooling: PgBouncer and RDS Proxy

---

Working With Viprasol

Migrating a production database without a solid versioning strategy is one of the highest-risk operations in software engineering. We design migration workflows that fit your team's toolchain — whether that's Prisma Migrate with zero-downtime patterns, Flyway in CI/CD pipelines, or Liquibase for multi-database enterprise environments.

Talk to our team → | Explore our cloud solutions →