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.

A proper migration tool provides:

• Version-controlled schema changes
• Repeatable, idempotent migrations
• Rollback capability
• CI/CD integration
• Schema drift detection

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"

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;

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.