Back to Schema Migration
Q1Question 1 of 2

How do you manage database migrations safely with Flyway

Answer: Database migrations are versioned scripts that evolve the database schema incrementally, and Flyway is a tool that applies these scripts in order, tracks which ones have run, and prevents re-execution.

Key Concepts
  • A database migration is a versioned script that changes the database schema in a controlled, repeatable, and reversible way.
  • Flyway uses SQL files named with a version prefix (V1__, V2__) and tracks applied migrations in a flyway_schema_history table.
  • Migrations run in order and only once because Flyway skips versions that have already been applied.
  • The expand-and-contract pattern adds a new column alongside the old one, migrates data, then removes the old column in a later migration.
  • Never run destructive DDL (DROP COLUMN, DROP TABLE) in the same migration that adds the replacement because a failed deployment leaves the database in a broken state.
  • Spring Boot auto-runs Flyway on startup when the flyway-core dependency is on the classpath.
  • Every migration should be backward-compatible so that the old application version can still work during a rolling deployment.

Definition

Database migrations are versioned scripts that evolve the database schema incrementally, and Flyway is a tool that applies these scripts in order, tracks which ones have run, and prevents re-execution.
  • Database migration is a versioned script (SQL or Java) that makes a specific change to the database schema: creating a table, adding a column, creating an index, or modifying data.
  • Flyway is a migration tool that scans a directory for versioned SQL files, applies them in version order, and records each applied migration in a history table.
  • Migration history table (flyway_schema_history) stores the version number, description, checksum, and execution timestamp of each applied migration.
  • Expand-and-contract is a deployment pattern where you first expand the schema (add the new column), then contract it (remove the old column) after all application instances have migrated.
  • Backward compatibility means the new schema must work with both the current and previous versions of the application during rolling deployments.

Think of updating the paper forms at National Bank of India. Each form revision has a version number (V1, V2, V3). When the bank introduces a new field (email address), they print V2 forms that have both the old layout and the new field. Tellers trained on V1 can still use V2 forms by ignoring the new field. Once all branches are trained on V2, V3 removes the old phone-only layout. The revision log tracks which branches are on which version. That is exactly how Flyway works: versioned changes, applied in order, tracked in a history table.

Understanding the Concepts

What is a database migration?

A database migration is a versioned change to the database schema. Instead of running ad-hoc SQL statements in production, you write each change as a named, versioned file. A migration tool applies these files in order and tracks which ones have already run.

  • This gives you:
  • Version control for the database schema, just like Git for code.
  • Repeatability: run the same migrations on dev, staging, and production.
  • Collaboration: multiple developers can add migrations without conflicts.
  • Audit trail: the history table shows when each change was applied.
TEXT
-- Migration files in src/main/resources/db/migration/
V1__create_customers_table.sql
V2__create_accounts_table.sql
V3__create_transactions_table.sql
V4__add_email_to_customers.sql
V5__rename_phone_to_mobile.sql

How does Flyway work?

Flyway scans the migration directory (default: classpath:db/migration), reads the version number from each filename, and applies them in order. It stores the results in flyway_schema_history.

  1. On startup, Flyway reads flyway_schema_history to see which versions have already been applied.
  2. It scans the migration directory for new files.
  3. It applies unapplied migrations in version order.
  4. For each successful migration, it inserts a row into the history table with the version, description, checksum, and timestamp.
  5. If a migration fails, Flyway stops and does not apply any further migrations.
XML
<!-- pom.xml: Add Flyway dependency --syn-kw">>
syn-kw"><dependencysyn-kw">>
    syn-kw"><groupIdsyn-kw">>org.flywaydbsyn-kw"></groupIdsyn-kw">>
    syn-kw"><artifactIdsyn-kw">>flyway-coresyn-kw"></artifactIdsyn-kw">>
    syn-kw"><versionsyn-kw">>10.8.1syn-kw"></versionsyn-kw">>
syn-kw"></dependencysyn-kw">>
syn-kw"><dependencysyn-kw">>
    syn-kw"><groupIdsyn-kw">>org.flywaydbsyn-kw"></groupIdsyn-kw">>
    syn-kw"><artifactIdsyn-kw">>flyway-database-postgresqlsyn-kw"></artifactIdsyn-kw">>
    syn-kw"><versionsyn-kw">>10.8.1syn-kw"></versionsyn-kw">>
syn-kw"></dependencysyn-kw">>
TEXT
# application.properties
spring.datasource.url=jdbc:postgresql://localhost:5432/bankdb
spring.datasource.username=bank_app
spring.datasource.password=${DB_PASSWORD}
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
TEXT
-- Spring Boot startup log:
Flyway Community Edition 10.8.1
Database: jdbc:postgresql://localhost:5432/bankdb (PostgreSQL 16.1)
Successfully validated 5 migrations
Current version of schema "public": 3
Migrating schema "public" to version "4 - add email to customers"
Migrating schema "public" to version "5 - rename phone to mobile"
Successfully applied 2 migrations to schema "public"

How do you write Flyway migration scripts for a banking schema?

V1: Create the initial schema

SQL
-- V1__create_banking_schema.sql

CREATE TABLE customers (
    customer_id    BIGSERIAL       PRIMARY KEY,
    full_name      VARCHAR(100)    NOT NULL,
    phone          VARCHAR(15),
    pan_number     CHAR(10)        UNIQUE NOT NULL,
    created_at     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE branches (
    branch_id      SERIAL          PRIMARY KEY,
    branch_name    VARCHAR(100)    NOT NULL,
    ifsc_code      CHAR(11)        UNIQUE NOT NULL,
    city           VARCHAR(50)     NOT NULL
);

CREATE TABLE accounts (
    account_id     VARCHAR(10)     PRIMARY KEY,
    customer_id    BIGINT          NOT NULL REFERENCES customers(customer_id),
    branch_id      INT             NOT NULL REFERENCES branches(branch_id),
    account_type   VARCHAR(10)     NOT NULL
        CHECK (account_type IN ('SAVINGS', 'CURRENT')),
    balance        NUMERIC(15, 2)  NOT NULL DEFAULT 0.00
        CHECK (balance >= 0),
    status         VARCHAR(10)     NOT NULL DEFAULT 'ACTIVE',
    opened_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE transactions (
    txn_id         BIGSERIAL       PRIMARY KEY,
    account_id     VARCHAR(10)     NOT NULL REFERENCES accounts(account_id),
    txn_type       VARCHAR(10)     NOT NULL
        CHECK (txn_type IN ('CREDIT', 'DEBIT')),
    amount         NUMERIC(15, 2)  NOT NULL CHECK (amount > 0),
    description    VARCHAR(200),
    created_at     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_txn_account_date ON transactions (account_id, created_at);
CREATE INDEX idx_accounts_customer ON accounts (customer_id);
TEXT
-- After running V1:
Flyway applied V1__create_banking_schema.sql
Tables created: customers, branches, accounts, transactions

V2: Add a column (non-breaking change)

SQL
-- V2__add_email_to_customers.sql

-- Adding a nullable column is a non-breaking change.
-- The old application ignores the new column.
ALTER TABLE customers ADD COLUMN email VARCHAR(150);

-- Backfill existing rows with a placeholder
UPDATE customers
   SET email = CONCAT(LOWER(REPLACE(full_name, ' ', '.')), '@nationalbank.in')
WHERE email IS NULL;

-- Now make it NOT NULL
ALTER TABLE customers ALTER COLUMN email SET NOT NULL;

-- Add unique constraint
ALTER TABLE customers ADD CONSTRAINT uq_customers_email UNIQUE (email);
TEXT
-- After running V2:
Flyway applied V2__add_email_to_customers.sql
Column added: customers.email
Existing rows backfilled with generated email addresses.

V3: Rename a column using expand-and-contract

Renaming a column directly (ALTER TABLE ... RENAME COLUMN) is a breaking change. The old application still references the old column name. Use expand-and-contract instead.

Phase 1 (expand): Add the new column alongside the old one.

SQL
-- V3__expand_phone_to_mobile.sql

-- Step 1: Add the new column
ALTER TABLE customers ADD COLUMN mobile VARCHAR(15);

-- Step 2: Copy data from old column to new column
UPDATE customers SET mobile = phone WHERE phone IS NOT NULL;

-- Step 3: Create a trigger to keep both columns in sync
-- during the migration period when both old and new app versions
-- are running simultaneously.
CREATE OR REPLACE FUNCTION sync_phone_mobile()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.phone IS DISTINCT FROM OLD.phone THEN
        NEW.mobile := NEW.phone;
    ELSIF NEW.mobile IS DISTINCT FROM OLD.mobile THEN
        NEW.phone := NEW.mobile;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_phone_mobile
    BEFORE UPDATE ON customers
    FOR EACH ROW
    EXECUTE FUNCTION sync_phone_mobile();
TEXT
-- After V3:
-- Both "phone" and "mobile" columns exist and stay in sync.
-- Old app reads/writes "phone". New app reads/writes "mobile".

Phase 2 (contract): Remove the old column after all app instances use the new one.

SQL
-- V4__contract_remove_phone.sql
-- Only run this AFTER all application instances use "mobile"

DROP TRIGGER IF EXISTS trg_sync_phone_mobile ON customers;
DROP FUNCTION IF EXISTS sync_phone_mobile;
ALTER TABLE customers DROP COLUMN phone;
TEXT
-- After V4:
-- "phone" column is gone. "mobile" is the only column.
-- This migration runs only after confirming zero queries use "phone".

How do you compare Flyway and Liquibase?

FeatureFlywayLiquibase
Migration formatPlain SQL filesXML, YAML, JSON, or SQL
Naming conventionV1__description.sqlChangelog with changesets
Rollback supportPaid (Flyway Teams)Built-in (free)
History tableflyway_schema_historydatabasechangelog
Learning curveVery simpleMore complex
Spring Boot integrationAuto-configuredAuto-configured
Best forSQL-first teamsMulti-database teams
JAVA
// Flyway programmatic API (alternative to Spring Boot auto-config)
package com.bank.config;

import org.flywaydb.core.Flyway;
import org.springframework.boot.autoconfigure.flyway.FlywayMigrationStrategy;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.util.logging.Logger;

@Configuration
public class FlywayConfig {

    private static final Logger log =
        Logger.getLogger(FlywayConfig.class.getName());

    @Bean
    public FlywayMigrationStrategy flywayMigrationStrategy() {
        return flyway -> {
            log.info("Starting Flyway migration...");
            var result = flyway.migrate();
            log.info("Applied " + result.migrationsExecuted
                + " migrations. Current version: "
                + result.targetSchemaVersion);
        };
    }
}
TEXT
Starting Flyway migration...
Applied 2 migrations. Current version: 5

What are common mistakes with database migrations?

Mistake 1: Destructive change in a single migration

WRONG: Dropping and recreating a column in one step.

SQL
-- WRONG: V5__rename_balance_column.sql
-- If the deployment fails halfway, the old column is gone
-- and the new column may not have data.
ALTER TABLE accounts DROP COLUMN balance;
ALTER TABLE accounts ADD COLUMN account_balance NUMERIC(15, 2) DEFAULT 0;
-- Data lost! All balances are now 0.

CORRECT: Expand first, contract later.

SQL
-- CORRECT: V5__expand_add_account_balance.sql
ALTER TABLE accounts ADD COLUMN account_balance NUMERIC(15, 2);
UPDATE accounts SET account_balance = balance;
ALTER TABLE accounts ALTER COLUMN account_balance SET NOT NULL;

-- V6__contract_remove_old_balance.sql (later, after all apps migrated)
ALTER TABLE accounts DROP COLUMN balance;

Mistake 2: Modifying an already-applied migration

WRONG: Editing V1 after it has been applied to production.

TEXT
-- WRONG: Editing V1__create_banking_schema.sql after it ran.
-- Flyway computes a checksum of each file. If the checksum changes,
-- Flyway refuses to start.

Flyway validation failed:
Migration checksum mismatch for V1__create_banking_schema
- Applied checksum: 1234567890
- Resolved checksum: 9876543210

CORRECT: Create a new migration to make the change.

SQL
-- CORRECT: Add a new migration instead of editing V1.
-- V7__add_missing_index.sql
CREATE INDEX IF NOT EXISTS idx_accounts_branch
    ON accounts (branch_id);

What about environment-specific migrations?

Use Flyway's locations property to include environment-specific scripts.

TEXT
# application.properties (all environments)
spring.flyway.locations=classpath:db/migration

# application-dev.properties (dev only: seed data)
spring.flyway.locations=classpath:db/migration,classpath:db/seed
SQL
-- db/seed/V100__seed_test_data.sql
-- Only runs in dev environment
INSERT INTO customers (full_name, email, pan_number, date_of_birth)
VALUES
('Rajesh Kumar', 'rajesh@test.com', 'ABCPK1234A', '1990-05-15'),
('Priya Sharma', 'priya@test.com', 'DEFPS5678B', '1992-08-22');

INSERT INTO accounts (account_id, customer_id, branch_id, account_type, balance)
VALUES
('SAV2001', 1, 1, 'SAVINGS', 75000.00),
('SAV2002', 2, 1, 'SAVINGS', 120000.00);
TEXT
-- Dev startup: V100 seed data applied
-- Prod startup: V100 does not exist in the production classpath

What should a senior or principal engineer know?

- Lock-free DDL. In PostgreSQL, ALTER TABLE ... ADD COLUMN with a default is lock-free since PostgreSQL 11. But ALTER TABLE ... ALTER COLUMN ... SET NOT NULL acquires an AccessExclusive lock and blocks all reads and writes. For large tables, use a CHECK constraint instead: ALTER TABLE t ADD CONSTRAINT chk_not_null CHECK (col IS NOT NULL) NOT VALID; ALTER TABLE t VALIDATE CONSTRAINT chk_not_null;

- Migration idempotency. Use IF NOT EXISTS and IF EXISTS in DDL statements so that rerunning a migration does not fail. This is critical for disaster recovery scenarios.

- Blue-green deployments. During a blue-green deployment, both the old (blue) and new (green) application versions run simultaneously against the same database. Every migration must be backward-compatible with the blue version. This is why expand-and-contract is essential.

- Large table migrations. Adding a NOT NULL column with a default to a table with 100 million rows can take minutes if it requires rewriting every row. In PostgreSQL 11+, ADD COLUMN ... DEFAULT is instant for non-volatile defaults. But backfilling data in an UPDATE still rewrites rows. Batch the UPDATE to avoid long-running transactions.

- Flyway callbacks. Use beforeMigrate.sql and afterMigrate.sql for tasks like creating extensions (CREATE EXTENSION IF NOT EXISTS pg_trgm) or refreshing materialized views.

SQL
-- Large table migration: batch updates to avoid long locks
-- V8__backfill_email_batched.sql

DO $$
DECLARE
    batch_size INT := 10000;
    updated INT;
BEGIN
    LOOP
        UPDATE customers
        SET email = CONCAT(LOWER(REPLACE(full_name, ' ', '.')),
            '@nationalbank.in')
        WHERE email IS NULL
          AND customer_id IN (
              SELECT customer_id FROM customers
              WHERE email IS NULL
              LIMIT batch_size
          );
        GET DIAGNOSTICS updated = ROW_COUNT;
        RAISE NOTICE 'Updated % rows', updated;
        EXIT WHEN updated = 0;
        COMMIT;
    END LOOP;
END $$;
TEXT
NOTICE: Updated 10000 rows
NOTICE: Updated 10000 rows
NOTICE: Updated 5432 rows
NOTICE: Updated 0 rows
-- Total: 25432 rows backfilled in 3 batches

What are common interview follow-up questions?

  • What happens if a Flyway migration fails halfway through?
  • How do you roll back a Flyway migration?
  • What is the difference between versioned and repeatable migrations?
  • How do you handle migrations in a microservices architecture where each service has its own database?
  • What is the expand-and-contract pattern?
  • How do you test migrations before applying them to production?
  • What is the difference between Flyway and Liquibase?
  • How do you handle long-running migrations on tables with billions of rows?
  • What is the purpose of the flyway_schema_history table?

Visual Overview

TEXT
  Flyway Migration Flow
  =====================

  src/main/resources/db/migration/
    V1__create_banking_schema.sql
    V2__add_email_to_customers.sql
    V3__expand_phone_to_mobile.sql
    V4__contract_remove_phone.sql

  Application Startup
       |
       v
  Flyway reads flyway_schema_history
       |
       v
  Already applied: V1, V2
  Pending: V3, V4
       |
       v
  Apply V3__expand_phone_to_mobile.sql
       |
       v
  Record V3 in history table
       |
       v
  Apply V4__contract_remove_phone.sql
       |
       v
  Record V4 in history table
       |
       v
  Application Ready
TEXT
  Expand-and-Contract Pattern
  ============================

  V3 (expand)                V4 (contract)
  +-----------+              +-----------+
  | phone     |  both exist  | mobile    |
  | mobile    |  in sync     |           |
  +-----------+              +-----------+
       |                          |
  Old app uses "phone"      Old column gone
  New app uses "mobile"     Only "mobile" remains
  Trigger syncs both        Run after all apps updated

Key Takeaway

Database migrations bring version control to your schema. Instead of running ad-hoc ALTER TABLE statements in production, you write each change as a versioned SQL file. Flyway reads these files in order, applies the unapplied ones, and records each migration in a history table. The same migration files run on every environment, from your laptop to production, guaranteeing that the schema is identical everywhere.

The expand-and-contract pattern is the safe way to make breaking schema changes. When you need to rename a column or change its type, you first expand the schema by adding the new column alongside the old one. A sync trigger or application logic keeps both columns in sync during the migration window. Once all application instances use the new column, you contract by removing the old one. This prevents downtime during rolling deployments.

Never edit an already-applied migration. Flyway checksums every file and refuses to start if a checksum changes. If you need to fix something, write a new migration. Never combine destructive DDL (DROP COLUMN) with additive DDL (ADD COLUMN) in the same migration, because a failure between the two leaves the database in a broken state with data loss.

For production banking systems, batch large data backfills to avoid long-running transactions, use lock-free DDL where possible, and test every migration against a copy of production data before deploying. Flyway callbacks handle pre- and post-migration tasks like creating extensions or refreshing views.

Remember one sentence: migrations are versioned, applied once, never edited, and always backward-compatible.

---

Share
Use ← → arrow keys to navigate