Database Migrations: Flyway vs Liquibase vs Atlas

Database Migrations Done Right

Schema migrations are one of the most dangerous operations in software. A bad migration can corrupt data, cause downtime, or create irreversible damage. Yet many teams still apply schema changes manually or with ad-hoc scripts.

<div style="margin:2.5rem auto;max-width:600px;width:100%;text-align:center;"><svg viewBox="0 0 600 180" xmlns="http://www.w3.org/2000/svg" style="width:100%;height:auto;"><rect width="600" height="180" rx="12" fill="#1a1a2e"/><ellipse cx="150" cy="55" rx="60" ry="18" fill="#6366f1" opacity="0.8"/><rect x="90" y="55" width="120" height="50" fill="#6366f1" opacity="0.8"/><ellipse cx="150" cy="105" rx="60" ry="18" fill="#6366f1" opacity="0.9"/><text x="150" y="85" text-anchor="middle" fill="#ffffff" font-size="12" font-family="system-ui" font-weight="bold">Primary</text><text x="150" y="140" text-anchor="middle" fill="#94a3b8" font-size="10" font-family="system-ui">Read + Write</text><ellipse cx="400" cy="30" rx="50" ry="14" fill="#a855f7" opacity="0.7"/><rect x="350" y="30" width="100" height="35" fill="#a855f7" opacity="0.7"/><ellipse cx="400" cy="65" rx="50" ry="14" fill="#a855f7" opacity="0.8"/><text x="400" y="52" text-anchor="middle" fill="#ffffff" font-size="10" font-family="system-ui">Replica 1</text><ellipse cx="400" cy="110" rx="50" ry="14" fill="#a855f7" opacity="0.7"/><rect x="350" y="110" width="100" height="35" fill="#a855f7" opacity="0.7"/><ellipse cx="400" cy="145" rx="50" ry="14" fill="#a855f7" opacity="0.8"/><text x="400" y="132" text-anchor="middle" fill="#ffffff" font-size="10" font-family="system-ui">Replica 2</text><defs><marker id="arrow8" markerWidth="8" markerHeight="6" refX="8" refY="3" orient="auto"><path d="M0,0 L8,3 L0,6" fill="#2dd4bf"/></marker></defs><path d="M212,65 Q280,30 348,48" stroke="#2dd4bf" stroke-width="1.5" fill="none" marker-end="url(#arrow8)"/><path d="M212,90 Q280,130 348,128" stroke="#2dd4bf" stroke-width="1.5" fill="none" marker-end="url(#arrow8)"/><text x="280" y="55" text-anchor="middle" fill="#2dd4bf" font-size="9" font-family="system-ui">WAL stream</text><text x="280" y="130" text-anchor="middle" fill="#2dd4bf" font-size="9" font-family="system-ui">WAL stream</text><text x="500" y="52" text-anchor="start" fill="#94a3b8" font-size="9" font-family="system-ui">Read-only</text><text x="500" y="132" text-anchor="start" fill="#94a3b8" font-size="9" font-family="system-ui">Read-only</text></svg><p style="margin-top:0.75rem;font-size:0.85rem;color:#94a3b8;font-style:italic;line-height:1.4;">Database replication: the primary handles writes while replicas serve read queries via WAL streaming.</p></div>

A proper migration tool provides:

Flyway: The SQL-First Approach

Flyway uses plain SQL files with a naming convention. It is simple, predictable, and SQL-native.

db/migration/
├── V1__create_users_table.sql
├── V2__add_email_to_users.sql
├── V3__create_orders_table.sql
├── V4__add_index_on_orders_user_id.sql
└── V5__add_status_to_orders.sql

-- V1__create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- V2__add_email_to_users.sql
ALTER TABLE users ADD COLUMN email VARCHAR(255) UNIQUE;
CREATE INDEX idx_users_email ON users (email);

-- V3__create_orders_table.sql
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    total DECIMAL(10, 2) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

# Run migrations
flyway -url=jdbc:postgresql://localhost:5432/myapp \
       -user=postgres \
       -password=secret \
       migrate

# Check current version
flyway info

# Validate migrations match what was applied
flyway validate

Flyway with Docker Compose (run migrations before app starts):

services:
  flyway:
    image: flyway/flyway:10
    command: migrate
    volumes:
      - ./db/migration:/flyway/sql
    environment:
      FLYWAY_URL: jdbc:postgresql://postgres:5432/myapp
      FLYWAY_USER: postgres
      FLYWAY_PASSWORD: secret
    depends_on:
      postgres:
        condition: service_healthy

  app:
    image: my-app:latest
    depends_on:
      flyway:
        condition: service_completed_successfully

Liquibase: The Changelog Approach

Liquibase uses changelog files (XML, YAML, JSON, or SQL) that describe changes abstractly. This abstraction allows database-agnostic migrations.

# changelog.yaml
databaseChangeLog:
  - changeSet:
      id: 1
      author: yash
      changes:
        - createTable:
            tableName: users
            columns:
              - column:
                  name: id
                  type: bigint
                  autoIncrement: true
                  constraints:
                    primaryKey: true
              - column:
                  name: name
                  type: varchar(255)
                  constraints:
                    nullable: false
              - column:
                  name: created_at
                  type: timestamp with time zone
                  defaultValueComputed: NOW()

  - changeSet:
      id: 2
      author: yash
      changes:
        - addColumn:
            tableName: users
            columns:
              - column:
                  name: email
                  type: varchar(255)
                  constraints:
                    unique: true
        - createIndex:
            tableName: users
            indexName: idx_users_email
            columns:
              - column:
                  name: email
      rollback:
        - dropIndex:
            tableName: users
            indexName: idx_users_email
        - dropColumn:
            tableName: users
            columnName: email

# Run migrations
liquibase --url=jdbc:postgresql://localhost:5432/myapp \
          --username=postgres \
          --password=secret \
          --changelog-file=changelog.yaml \
          update

# Generate rollback SQL
liquibase rollback-sql --count=1

# Diff between database and changelog
liquibase diff

Atlas: The Declarative Approach

Atlas by Ariga takes a different approach. Instead of writing migration scripts, you declare the desired schema and Atlas computes the diff.

# schema.hcl - Declare desired state
schema "public" {}

table "users" {
  schema = schema.public
  column "id" {
    type = bigserial
  }
  column "name" {
    type = varchar(255)
    null = false
  }
  column "email" {
    type = varchar(255)
    null = true
  }
  column "created_at" {
    type    = timestamptz
    default = sql("NOW()")
  }
  primary_key {
    columns = [column.id]
  }
  index "idx_users_email" {
    columns = [column.email]
    unique  = true
  }
}

table "orders" {
  schema = schema.public
  column "id" {
    type = bigserial
  }
  column "user_id" {
    type = bigint
    null = false
  }
  column "total" {
    type = decimal(10, 2)
    null = false
  }
  column "status" {
    type    = varchar(50)
    default = "pending"
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "fk_orders_user" {
    columns     = [column.user_id]
    ref_columns = [table.users.column.id]
  }
}

# Inspect current database schema
atlas schema inspect -u "postgresql://postgres:secret@localhost:5432/myapp?sslmode=disable"

# Compute diff between desired and actual
atlas schema diff \
  --from "postgresql://postgres:secret@localhost:5432/myapp?sslmode=disable" \
  --to "file://schema.hcl"

# Apply changes (Atlas generates migration SQL automatically)
atlas schema apply \
  -u "postgresql://postgres:secret@localhost:5432/myapp?sslmode=disable" \
  --to "file://schema.hcl"

# Or generate versioned migration files
atlas migrate diff add_orders_table \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://postgres/16/dev"

<div style="margin:2.5rem auto;max-width:600px;width:100%;text-align:center;"><svg viewBox="0 0 600 170" xmlns="http://www.w3.org/2000/svg" style="width:100%;height:auto;"><rect width="600" height="170" rx="12" fill="#1a1a2e"/><path d="M80,90 Q80,50 120,50 Q130,30 160,35 Q190,25 200,50 Q230,45 230,70 Q240,90 210,95 L100,95 Q70,95 80,90 Z" fill="none" stroke="#3b82f6" stroke-width="1.5"/><text x="155" y="75" text-anchor="middle" fill="#3b82f6" font-size="11" font-family="system-ui">Cloud</text><text x="155" y="120" text-anchor="middle" fill="#94a3b8" font-size="9" font-family="system-ui">$5,000/mo</text><defs><marker id="arrow9" markerWidth="10" markerHeight="7" refX="10" refY="3.5" orient="auto"><path d="M0,0 L10,3.5 L0,7" fill="#2dd4bf"/></marker></defs><line x1="245" y1="70" x2="340" y2="70" stroke="#2dd4bf" stroke-width="2.5" marker-end="url(#arrow9)"/><text x="293" y="60" text-anchor="middle" fill="#2dd4bf" font-size="10" font-family="system-ui" font-weight="bold">Migrate</text><rect x="355" y="35" width="180" height="70" rx="8" fill="none" stroke="#6366f1" stroke-width="2"/><rect x="365" y="45" width="160" height="15" rx="3" fill="#6366f1" opacity="0.7"/><rect x="365" y="65" width="160" height="15" rx="3" fill="#a855f7" opacity="0.7"/><rect x="365" y="85" width="100" height="10" rx="2" fill="#2dd4bf" opacity="0.5"/><text x="445" y="57" text-anchor="middle" fill="#ffffff" font-size="9" font-family="system-ui">Bare Metal</text><text x="445" y="77" text-anchor="middle" fill="#ffffff" font-size="9" font-family="system-ui">Docker + LXC</text><text x="445" y="120" text-anchor="middle" fill="#94a3b8" font-size="9" font-family="system-ui">$200/mo</text><text x="300" y="150" text-anchor="middle" fill="#2dd4bf" font-size="11" font-family="system-ui" font-weight="bold">96% cost reduction</text></svg><p style="margin-top:0.75rem;font-size:0.85rem;color:#94a3b8;font-style:italic;line-height:1.4;">Cloud to self-hosted migration can dramatically reduce infrastructure costs while maintaining full control.</p></div>

Comparison

|---------|--------|-----------|-------|

Safe Migration Patterns

Regardless of which tool you use, follow these patterns for zero-downtime migrations:

1. Expand-and-Contract

Never rename or remove columns directly. Instead:

-- Step 1: Add new column (expand)
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Step 2: Backfill data
UPDATE users SET full_name = name;

-- Step 3: Deploy app code that reads from both, writes to both

-- Step 4: Deploy app code that reads from full_name only

-- Step 5: Drop old column (contract) -- separate migration, days later
ALTER TABLE users DROP COLUMN name;

2. Never Lock Large Tables

-- BAD: Locks the entire table while adding default
ALTER TABLE orders ADD COLUMN priority INTEGER DEFAULT 0;

-- GOOD: Add column nullable first, then backfill
ALTER TABLE orders ADD COLUMN priority INTEGER;
-- Backfill in batches
UPDATE orders SET priority = 0 WHERE id BETWEEN 1 AND 10000;
UPDATE orders SET priority = 0 WHERE id BETWEEN 10001 AND 20000;
-- Then add default for new rows
ALTER TABLE orders ALTER COLUMN priority SET DEFAULT 0;

<div style="margin:2.5rem auto;max-width:600px;width:100%;text-align:center;"><svg viewBox="0 0 600 180" xmlns="http://www.w3.org/2000/svg" style="width:100%;height:auto;"><rect width="600" height="180" rx="12" fill="#1a1a2e"/><rect x="30" y="55" width="90" height="50" rx="8" fill="#6366f1" opacity="0.9"/><text x="75" y="85" text-anchor="middle" fill="#ffffff" font-size="12" font-family="system-ui">Code</text><rect x="150" y="55" width="90" height="50" rx="8" fill="#3b82f6" opacity="0.9"/><text x="195" y="85" text-anchor="middle" fill="#ffffff" font-size="12" font-family="system-ui">Build</text><rect x="270" y="55" width="90" height="50" rx="8" fill="#a855f7" opacity="0.9"/><text x="315" y="85" text-anchor="middle" fill="#ffffff" font-size="12" font-family="system-ui">Test</text><rect x="390" y="55" width="90" height="50" rx="8" fill="#2dd4bf" opacity="0.9"/><text x="435" y="85" text-anchor="middle" fill="#1a1a2e" font-size="12" font-family="system-ui">Deploy</text><rect x="510" y="55" width="60" height="50" rx="8" fill="#f59e0b" opacity="0.9"/><text x="540" y="85" text-anchor="middle" fill="#1a1a2e" font-size="12" font-family="system-ui">Live</text><path d="M122,80 L148,80" stroke="#e2e8f0" stroke-width="2" marker-end="url(#arrow1)"/><path d="M242,80 L268,80" stroke="#e2e8f0" stroke-width="2" marker-end="url(#arrow1)"/><path d="M362,80 L388,80" stroke="#e2e8f0" stroke-width="2" marker-end="url(#arrow1)"/><path d="M482,80 L508,80" stroke="#e2e8f0" stroke-width="2" marker-end="url(#arrow1)"/><defs><marker id="arrow1" markerWidth="8" markerHeight="6" refX="8" refY="3" orient="auto"><path d="M0,0 L8,3 L0,6" fill="#e2e8f0"/></marker></defs><text x="300" y="145" text-anchor="middle" fill="#94a3b8" font-size="11" font-family="system-ui">Continuous Integration / Continuous Deployment Pipeline</text></svg><p style="margin-top:0.75rem;font-size:0.85rem;color:#94a3b8;font-style:italic;line-height:1.4;">A typical CI/CD pipeline: code flows through build, test, and deploy stages automatically.</p></div>

3. Create Indexes Concurrently

-- BAD: Blocks writes
CREATE INDEX idx_orders_status ON orders (status);

-- GOOD: Non-blocking (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

At TechSaaS, we use Flyway for most projects because SQL-first means no abstraction layer between you and the database. The naming convention is intuitive, and the Docker integration makes it trivial to run migrations as part of a compose stack. For teams that want a declarative approach, Atlas is impressive and growing fast.