Database Migrations

Guidelines for managing database schema evolution using Flyway with versioning, testing, and zero-downtime deployment strategies.

Overview

Database migrations are version-controlled scripts that evolve your database schema over time. Unlike application code that can be deployed instantly, database schemas often contain production data that must be carefully transformed as the schema changes.

Flyway is the recommended migration tool for Java/Spring Boot applications. It applies versioned SQL migration files in order, tracks which migrations have run, and ensures consistency across environments.

This guide covers Flyway configuration, migration best practices, testing strategies, and zero-downtime deployment patterns. For schema design principles, see Database Design. For ORM configuration, see Database ORM.

---

Core Principles

• Migrations Are Code: Version controlled, reviewed, tested like application code
• Forward-Only: Never modify applied migrations; create new ones to fix issues
• Idempotent Where Possible: Design migrations to be safely re-runnable
• Test on Production-Like Data: Verify migrations work with realistic data volumes
• Zero-Downtime First: Design migrations to allow application to run during deployment
• Rollback Plan: Have a plan for rolling back failed migrations
• Never Auto-Generate in Production: Use ddl-auto: validate to prevent Hibernate from modifying schema

---

Flyway Overview

Flyway maintains a flyway_schema_history table that tracks:

• Which migrations have been applied
• When they were applied
• Checksum of each migration (detects changes to applied migrations)
• Execution time and success/failure status

---

Configuration

Gradle Dependency

dependencies {
    implementation 'org.flywaydb:flyway-core:10.x.x'
    runtimeOnly 'org.flywaydb:flyway-database-postgresql:10.x.x'
}

Application Configuration

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/appdb
    username: ${DB_USERNAME}
    password: ${DB_PASSWORD}

  jpa:
    hibernate:
      ddl-auto: validate  # CRITICAL: Never 'update' or 'create-drop' with Flyway

  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true  # Enable for existing databases
    baseline-version: 0        # Version to start from for existing databases
    validate-on-migrate: true  # Verify checksums of applied migrations
    out-of-order: false        # Require migrations to be applied in order
    placeholder-replacement: true
    placeholders:
      schema: public

Key Settings:

• ddl-auto: validate: Hibernate validates schema matches entities but doesn't modify it. Flyway owns schema changes.
• baseline-on-migrate: true: Allows Flyway to be added to existing databases (marks current schema as baseline version)
• validate-on-migrate: true: Fails startup if applied migrations have been modified (checksum mismatch)
• out-of-order: false: Enforces sequential ordering (prevents mistakes from running old migrations on newer databases)

Directory Structure

src/main/resources/db/migration/
├── V1.0.0__create_customers_table.sql
├── V1.0.1__create_orders_table.sql
├── V1.0.2__add_customer_indexes.sql
├── V1.1.0__add_order_status_column.sql
├── V1.1.1__migrate_order_status_data.sql
├── V1.1.2__make_order_status_required.sql
└── V2.0.0__add_audit_log_table.sql

Naming Convention: V{version}__{description}.sql

• Version: Major.Minor.Patch using semantic versioning
• Separator: Double underscore __
• Description: Snake_case, descriptive but concise

Version Numbering Strategy:

• Major: Breaking schema changes (column renames, table drops)
• Minor: Backward-compatible additions (new tables, columns)
• Patch: Small fixes, indexes, constraints

---

Migration File Patterns

Creating Tables

-- V1.0.0__create_customers_table.sql
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(200) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    version BIGINT NOT NULL DEFAULT 0
);

CREATE UNIQUE INDEX uk_customers_email ON customers(email);
CREATE INDEX idx_customers_created_at ON customers(created_at DESC);

-- Trigger for auto-updating updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_customers_updated_at
    BEFORE UPDATE ON customers
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

Adding Columns (Nullable First)

-- V1.1.0__add_phone_column.sql
--  GOOD: Add as nullable (backward-compatible)
ALTER TABLE customers ADD COLUMN phone VARCHAR(20);

-- Create partial index for non-null values
CREATE INDEX idx_customers_phone ON customers(phone)
    WHERE phone IS NOT NULL;

Making Columns Required (Multi-Step)

-- V1.1.0__add_status_column.sql
-- Step 1: Add nullable column
ALTER TABLE orders ADD COLUMN status VARCHAR(20);

-- V1.1.1__populate_status_column.sql
-- Step 2: Populate existing rows with default value
UPDATE orders SET status = 'PENDING' WHERE status IS NULL;

-- V1.1.2__make_status_required.sql
-- Step 3: Make NOT NULL and add constraint
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE orders ADD CONSTRAINT chk_status
    CHECK (status IN ('PENDING', 'PROCESSING', 'COMPLETED', 'CANCELLED'));

CREATE INDEX idx_orders_status ON orders(status);

Why Multi-Step?: Adding a NOT NULL column in one migration fails if the table has existing data. Breaking it into steps allows the application to start populating the new column before making it required.

Renaming Columns (Zero-Downtime Pattern)

-- V2.0.0__add_customer_full_name.sql
-- Step 1: Add new column
ALTER TABLE customers ADD COLUMN full_name VARCHAR(200);

-- Step 2: Copy data from old column
UPDATE customers SET full_name = name WHERE full_name IS NULL;

-- V2.0.1__make_full_name_required.sql
-- Step 3: Make new column NOT NULL
ALTER TABLE customers ALTER COLUMN full_name SET NOT NULL;

-- V2.1.0__drop_customer_name.sql
-- Step 4: Drop old column (after application updated to use full_name)
ALTER TABLE customers DROP COLUMN name;

Deployment Steps:

1. Deploy V2.0.0, V2.0.1 (both columns exist)
2. Deploy application code that writes to both name and full_name
3. Deploy application code that only reads full_name
4. Deploy V2.1.0 (drop name)

Dropping Tables (Soft Delete First)

-- V3.0.0__deprecate_old_orders.sql
-- Step 1: Rename table (makes it inaccessible but keeps data)
ALTER TABLE old_orders RENAME TO deprecated_old_orders;

-- V3.1.0__drop_old_orders.sql
-- Step 2: Drop after verifying no issues (in later release)
DROP TABLE deprecated_old_orders;

Never drop tables immediately. Rename first to keep data recoverable if something goes wrong.

Data Migrations

-- V1.5.0__migrate_currency_codes.sql
-- Update old currency codes to ISO 4217
UPDATE orders SET currency = 'USD' WHERE currency = 'US';
UPDATE orders SET currency = 'EUR' WHERE currency = 'EU';
UPDATE orders SET currency = 'GBP' WHERE currency = 'UK';

-- Verify no invalid codes remain
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM orders WHERE currency NOT IN ('USD', 'EUR', 'GBP')) THEN
        RAISE EXCEPTION 'Invalid currency codes found';
    END IF;
END $$;

Always verify data migrations completed successfully before proceeding.

---

Zero-Downtime Migrations

The Expand-Migrate-Contract pattern allows the application to keep running while the database schema evolves.

Expand Phase

Add new schema elements without removing old ones:

-- V2.0.0__expand_add_new_column.sql
ALTER TABLE orders ADD COLUMN payment_method VARCHAR(50);

At this point:

• Old application can still run (ignores new column)
• New application can start using new column

Migrate Phase

Application writes to both old and new schema:

// Application v1.1 - Dual write
@Transactional
public void createOrder(CreateOrderRequest request) {
    Order order = new Order();
    order.setPaymentType(request.getPaymentType());      // Old column
    order.setPaymentMethod(request.getPaymentType());    // New column (same data)
    orderRepository.save(order);
}

Backfill historical data:

-- V2.0.1__migrate_payment_method.sql
UPDATE orders SET payment_method = payment_type WHERE payment_method IS NULL;

Contract Phase

Make new column required, drop old:

-- V2.0.2__contract_make_payment_method_required.sql
ALTER TABLE orders ALTER COLUMN payment_method SET NOT NULL;

-- V2.1.0__contract_drop_payment_type.sql
-- Only after all applications updated to use payment_method
ALTER TABLE orders DROP COLUMN payment_type;

Application now only uses new column:

// Application v1.2 - New column only
@Transactional
public void createOrder(CreateOrderRequest request) {
    Order order = new Order();
    order.setPaymentMethod(request.getPaymentType());
    orderRepository.save(order);
}

Timeline:

1. Week 1: Deploy expand migration + dual-write app code
2. Week 2: Verify dual writes working, backfill data
3. Week 3: Deploy app code that reads from new column only
4. Week 4: Deploy contract migration (drop old column)

---

Testing Migrations

Local Testing

# Clean database and run all migrations
./gradlew flywayClean flywayMigrate

# Check migration status
./gradlew flywayInfo

# Validate migration checksums
./gradlew flywayValidate

Integration Tests

@SpringBootTest
@Testcontainers
class MigrationIntegrationTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16")
        .withDatabaseName("testdb")
        .withUsername("test")
        .withPassword("test");

    @DynamicPropertySource
    static void configureProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }

    @Autowired
    private Flyway flyway;

    @Test
    void migrationsApplySuccessfully() {
        // Flyway migrations run automatically on startup
        assertThat(flyway.info().applied()).isNotEmpty();
        assertThat(flyway.info().pending()).isEmpty();
    }

    @Test
    void canInsertDataAfterMigrations() {
        // Test that schema is functional
        jdbcTemplate.update(
            "INSERT INTO customers (name, email) VALUES (?, ?)",
            "Test Customer",
            "[email protected]"
        );

        Integer count = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM customers",
            Integer.class
        );

        assertThat(count).isEqualTo(1);
    }
}

Testing with Production Data Volumes

-- Generate test data to simulate production scale
INSERT INTO customers (name, email)
SELECT
    'Customer ' || generate_series,
    'customer' || generate_series || '@example.com'
FROM generate_series(1, 1000000);

-- Test migration performance
\timing on
\i V1.5.0__add_customer_index.sql
\timing off

Always test migrations on production-like data volumes to catch performance issues (e.g., index creation locking tables for hours).

For comprehensive testing strategies, see Spring Boot Testing.

---

Rollback Strategies

Flyway does not automatically rollback failed migrations. You must handle rollbacks manually.

Option 1: Compensating Migrations

-- V1.5.0__add_discount_column.sql (failed migration)
ALTER TABLE orders ADD COLUMN discount DECIMAL(5,2) CHECK (discount >= 0 AND discount <= 100);

-- If migration fails, create compensating migration:
-- V1.5.1__rollback_discount_column.sql
ALTER TABLE orders DROP COLUMN discount;

Option 2: Manual Rollback

# Mark failed migration as resolved
flyway repair

# Manually rollback changes
psql -U username -d database -c "ALTER TABLE orders DROP COLUMN discount;"

# Re-run migrations
flyway migrate

Option 3: Database Backups

# Before risky migration
pg_dump -U username dbname > backup_before_migration.sql

# If migration fails, restore
psql -U username dbname < backup_before_migration.sql

Best Practices:

• Test migrations thoroughly before production
• Take database backup before high-risk migrations
• Deploy during low-traffic windows
• Monitor database locks and performance during migration

---

Common Pitfalls

Modifying Applied Migrations

-- NEVER modify V1.0.0__create_customers.sql after it's been applied
-- Flyway detects checksum change and fails validation

Solution: Create a new migration to make changes.

Adding NOT NULL Without Default

--  BAD: Fails if table has existing rows
ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL;

Solution: Multi-step migration (nullable → populate → NOT NULL).

Dropping Columns Immediately

--  BAD: Causes errors if old application code still references column
ALTER TABLE customers DROP COLUMN old_field;

Solution: Expand-Migrate-Contract pattern.

Long-Running Migrations Without Downtime Plan

--  BAD: CREATE INDEX locks table for writes in PostgreSQL < 12
CREATE INDEX idx_orders_customer ON orders(customer_id);

Solution: Use CONCURRENTLY (allows writes during index creation):

--  GOOD: Non-blocking index creation
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders(customer_id);

Forgetting Foreign Key Indexes

--  BAD: Foreign key exists but no index
ALTER TABLE orders ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);

Solution: Always create indexes on foreign key columns:

--  GOOD: Foreign key + index
CREATE INDEX idx_orders_customer ON orders(customer_id);

ALTER TABLE orders ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);

---

Advanced Patterns

Conditional Migrations

-- V1.5.0__add_index_if_not_exists.sql
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_indexes
        WHERE tablename = 'orders'
        AND indexname = 'idx_orders_status'
    ) THEN
        CREATE INDEX idx_orders_status ON orders(status);
    END IF;
END $$;

Useful when migrations might run on databases in different states.

Repeatable Migrations

-- R__create_summary_view.sql (no version number)
CREATE OR REPLACE VIEW order_summary AS
SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent
FROM orders
GROUP BY customer_id;

Repeatable migrations (prefix R__) run whenever their checksum changes. Use for views, functions, procedures that can be safely recreated.

Callbacks

-- beforeMigrate.sql
-- Runs before each migration
SELECT 'Starting migration at ' || CURRENT_TIMESTAMP;

-- afterMigrate.sql
-- Runs after each migration
ANALYZE;  -- Update table statistics

Place in db/migration/ with special names Flyway recognizes.

---

CI/CD Integration

GitLab CI Pipeline

# .gitlab-ci.yml
test-migrations:
  stage: test
  image: gradle:jdk17
  services:
    - postgres:16
  variables:
    POSTGRES_DB: testdb
    POSTGRES_USER: test
    POSTGRES_PASSWORD: test
    DB_URL: jdbc:postgresql://postgres:5432/testdb
  script:
    - ./gradlew flywayMigrate -Dflyway.url=$DB_URL
    - ./gradlew flywayValidate
    - ./gradlew test

Production Deployment

# 1. Backup database
pg_dump -U $DB_USER $DB_NAME > backup_$(date +%Y%m%d_%H%M%S).sql

# 2. Run Flyway migrations
./gradlew flywayMigrate -Dflyway.url=$PROD_DB_URL

# 3. Deploy application
./deploy-application.sh

# 4. Verify health
curl https://api.example.com/health

For CI/CD pipeline patterns, see GitLab CI/CD Pipelines.

---

Monitoring and Observability

Query Migration History

-- View all applied migrations
SELECT
    installed_rank,
    version,
    description,
    type,
    script,
    installed_on,
    execution_time,
    success
FROM flyway_schema_history
ORDER BY installed_rank DESC;

-- Find failed migrations
SELECT * FROM flyway_schema_history WHERE success = false;

-- Find slow migrations
SELECT version, description, execution_time
FROM flyway_schema_history
WHERE execution_time > 5000  -- Over 5 seconds
ORDER BY execution_time DESC;

Application Health Check

@Component
public class DatabaseHealthIndicator implements HealthIndicator {

    @Autowired
    private Flyway flyway;

    @Override
    public Health health() {
        try {
            MigrationInfo[] pending = flyway.info().pending();

            if (pending.length > 0) {
                return Health.down()
                    .withDetail("pending-migrations", pending.length)
                    .build();
            }

            return Health.up()
                .withDetail("applied-migrations", flyway.info().applied().length)
                .build();
        } catch (Exception e) {
            return Health.down(e).build();
        }
    }
}

For observability patterns, see Spring Boot Observability.

---

Summary

Key Takeaways:

1. Flyway manages schema evolution through versioned SQL migration files
2. Never modify applied migrations - create new ones to fix issues
3. Multi-step migrations for adding NOT NULL columns (nullable → populate → required)
4. Expand-Migrate-Contract pattern enables zero-downtime deployments
5. Test migrations on production-like data to catch performance issues
6. Use CREATE INDEX CONCURRENTLY to avoid locking tables
7. Always index foreign key columns for join performance
8. Rollback strategy is manual - test thoroughly and have backup plan
9. ddl-auto: validate ensures Hibernate doesn't modify schema
10. Version numbering matters - use semantic versioning (major.minor.patch)

Related Guides:

• Database Design - Schema design, normalization, indexing, constraints
• Database ORM - JPA/Hibernate entity mapping and repositories
• Spring Boot Data Access - Spring-specific patterns
• Spring Boot Testing - Testing migrations with TestContainers
• GitLab CI/CD Pipelines - Automating migration deployment