Spring Boot

Flyway Migration

Flyway is a database migration tool that versions schema changes as SQL or Java migration scripts. Spring Boot auto-configures Flyway when it is on the classpath — migrations run automatically on startup before the application context finishes initialising. This entry covers setup, naming conventions, versioned and repeatable migrations, Java-based migrations, callbacks, multi-environment strategies, and testing.

Setup and Configuration

Add the Flyway dependency and Spring Boot auto-configures it automatically. Flyway reads migrations from classpath:db/migration by default and runs any pending migrations on startup. Set spring.jpa.hibernate.ddl-auto=validate so Hibernate validates the schema Flyway created rather than trying to modify it.

XML

<!-- pom.xml -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

<!-- MySQL requires the MySQL-specific module -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
</dependency>

<!-- PostgreSQL works with flyway-core alone -->

# ── application.yml ────────────────────────────────────────────────────
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration   # default location
    baseline-on-migrate: false          # true only when adding Flyway to
                                        # an existing database
    baseline-version: 0
    validate-on-migrate: true           # fail fast on checksum mismatch
    out-of-order: false                 # reject out-of-order migrations
    table: flyway_schema_history        # default history table name
    schemas:
      - public                          # schemas Flyway manages
    clean-disabled: true                # ALWAYS true in production

  jpa:
    hibernate:
      ddl-auto: validate                # Hibernate validates, Flyway manages

# ── application-dev.yml — relaxed settings for local development ───────
spring:
  flyway:
    clean-disabled: false               # allow flyway:clean in dev only
    out-of-order: true                  # tolerate feature branch migrations

# ── application-test.yml ───────────────────────────────────────────────
spring:
  flyway:
    locations:
      - classpath:db/migration
      - classpath:db/testdata           # seed data for tests only
  jpa:
    hibernate:
      ddl-auto: none

Naming Conventions and File Structure

Flyway derives migration metadata from the filename. The naming pattern is V{version}__{description}.sql for versioned migrations and R__{description}.sql for repeatable migrations. Underscores in the version become dots in the version number. Always use a consistent versioning scheme — timestamps (V20240315143000) are safer than simple integers for team environments.

yaml

src/main/resources/
└── db/
    └── migration/
        ├── V1__create_users_table.sql
        ├── V2__create_products_table.sql
        ├── V3__add_orders_table.sql
        ├── V4__add_user_status_column.sql
        ├── V5__create_indexes.sql
        ├── V6__add_audit_columns.sql
        ├── R__create_views.sql              ← repeatable: runs when content changes
        └── R__seed_reference_data.sql       ← repeatable: runs when content changes

# ── Timestamp-based versioning (recommended for teams) ────────────────
# V20240315143000__create_users_table.sql
# V20240316091500__add_email_index.sql
# V20240317120000__create_products_table.sql

# ── Naming rules ──────────────────────────────────────────────────────
# V      — versioned migration prefix (required)
# 1      — version number (unique, must be greater than previous)
# __     — two underscores separator (required)
# create_users_table — description (underscores become spaces in history)
# .sql   — extension (SQL or Java)
#
# Valid version formats:
# V1, V1.1, V1.2.3, V20240315, V20240315143000
#
# R      — repeatable migration prefix
# __     — two underscores separator
# description.sql

# ── Multiple location directories ────────────────────────────────────
spring:
  flyway:
    locations:
      - classpath:db/migration          # main migrations
      - classpath:db/procedures         # stored procedures
      - filesystem:/opt/migrations      # external migrations

Versioned SQL Migrations

Versioned migrations are the primary migration type. Each file runs exactly once, in version order, and its checksum is stored in the schema history table. Write migrations to be idempotent where possible — use IF NOT EXISTS and IF EXISTS to make re-runs safe. Never modify a migration that has already been applied to any environment.

sql

-- V1__create_users_table.sql
CREATE TABLE users (
    id          BIGSERIAL       PRIMARY KEY,
    name        VARCHAR(100)    NOT NULL,
    email       VARCHAR(255)    NOT NULL,
    username    VARCHAR(50)     NOT NULL,
    status      VARCHAR(20)     NOT NULL DEFAULT 'ACTIVE',
    created_at  TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    version     BIGINT          NOT NULL DEFAULT 0,
    CONSTRAINT uq_users_email    UNIQUE (email),
    CONSTRAINT uq_users_username UNIQUE (username),
    CONSTRAINT chk_users_status
        CHECK (status IN ('ACTIVE','INACTIVE','SUSPENDED'))
);

CREATE INDEX idx_users_email      ON users (email);
CREATE INDEX idx_users_created_at ON users (created_at DESC);

COMMENT ON TABLE  users         IS 'Application users';
COMMENT ON COLUMN users.status  IS 'ACTIVE | INACTIVE | SUSPENDED';

-- V2__create_products_table.sql
CREATE TABLE categories (
    id          BIGSERIAL    PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    slug        VARCHAR(100) NOT NULL,
    parent_id   BIGINT       REFERENCES categories(id),
    CONSTRAINT uq_categories_slug UNIQUE (slug)
);

CREATE TABLE products (
    id             BIGSERIAL       PRIMARY KEY,
    name           VARCHAR(200)    NOT NULL,
    description    TEXT,
    sku            VARCHAR(50)     NOT NULL,
    price          NUMERIC(10,2)   NOT NULL,
    stock_qty      INTEGER         NOT NULL DEFAULT 0,
    category_id    BIGINT          NOT NULL
                   REFERENCES categories(id),
    status         VARCHAR(20)     NOT NULL DEFAULT 'DRAFT',
    created_at     TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    updated_at     TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_products_sku    UNIQUE (sku),
    CONSTRAINT chk_products_price CHECK (price >= 0),
    CONSTRAINT chk_products_stock CHECK (stock_qty >= 0)
);

CREATE INDEX idx_products_category ON products (category_id);
CREATE INDEX idx_products_status   ON products (status);
CREATE INDEX idx_products_sku      ON products (sku);

-- V3__add_orders_table.sql
CREATE TABLE orders (
    id           BIGSERIAL       PRIMARY KEY,
    user_id      BIGINT          NOT NULL REFERENCES users(id),
    reference    VARCHAR(30)     NOT NULL,
    status       VARCHAR(20)     NOT NULL DEFAULT 'PENDING',
    total        NUMERIC(10,2)   NOT NULL,
    placed_at    TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    shipped_at   TIMESTAMPTZ,
    version      BIGINT          NOT NULL DEFAULT 0,
    CONSTRAINT uq_orders_reference UNIQUE (reference)
);

CREATE TABLE order_items (
    order_id     BIGINT          NOT NULL REFERENCES orders(id),
    product_id   BIGINT          NOT NULL REFERENCES products(id),
    quantity     INTEGER         NOT NULL,
    unit_price   NUMERIC(10,2)   NOT NULL,
    line_total   NUMERIC(10,2)   NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

CREATE INDEX idx_orders_user_id   ON orders (user_id);
CREATE INDEX idx_orders_status    ON orders (status, placed_at DESC);

Alter and Data Migrations

Schema changes after the initial creation — adding columns, modifying constraints, backfilling data — are each their own versioned migration. Separate DDL changes from DML data migrations so they can be rolled back or replayed independently. For zero-downtime deploys, use the expand-contract pattern.

sql

-- V4__add_user_profile_columns.sql
-- Add nullable first — backfill — then add NOT NULL constraint
ALTER TABLE users
    ADD COLUMN IF NOT EXISTS avatar_url  VARCHAR(500),
    ADD COLUMN IF NOT EXISTS bio         TEXT,
    ADD COLUMN IF NOT EXISTS phone       VARCHAR(20),
    ADD COLUMN IF NOT EXISTS timezone    VARCHAR(50)
                             NOT NULL DEFAULT 'UTC';

-- V5__backfill_user_display_names.sql
-- Data migration: separate from schema change
UPDATE users
SET    name = CONCAT(first_name, ' ', last_name)
WHERE  name IS NULL
  AND  first_name IS NOT NULL;

-- V6__add_not_null_constraint_to_name.sql
-- Step 3 of expand-contract: enforce constraint after backfill
ALTER TABLE users
    ALTER COLUMN name SET NOT NULL;

-- V7__create_audit_log_table.sql
CREATE TABLE audit_log (
    id          BIGSERIAL    PRIMARY KEY,
    entity_type VARCHAR(50)  NOT NULL,
    entity_id   BIGINT       NOT NULL,
    action      VARCHAR(20)  NOT NULL,   -- INSERT | UPDATE | DELETE
    old_values  JSONB,
    new_values  JSONB,
    changed_by  VARCHAR(100),
    changed_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_audit_log_entity
    ON audit_log (entity_type, entity_id, changed_at DESC);

CREATE INDEX idx_audit_log_changed_at
    ON audit_log (changed_at DESC);

-- V8__rename_column.sql
-- Safe rename: add new column, copy data, drop old column
ALTER TABLE products ADD COLUMN product_name VARCHAR(200);
UPDATE products SET product_name = name;
ALTER TABLE products
    ALTER COLUMN product_name SET NOT NULL,
    DROP  COLUMN name;
ALTER TABLE products RENAME COLUMN product_name TO name;

-- V9__add_foreign_key_with_index.sql
-- Always create index before foreign key on large tables
CREATE INDEX CONCURRENTLY idx_order_items_product_id
    ON order_items (product_id);

ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_product
    FOREIGN KEY (product_id) REFERENCES products(id);

Repeatable Migrations

Repeatable migrations (R__ prefix) run whenever their checksum changes. They are ideal for views, stored procedures, functions, and seed data that is replaced wholesale. Flyway runs all versioned migrations first, then all repeatable migrations in alphabetical order. Repeatable migrations must be idempotent — use CREATE OR REPLACE.

sql

-- R__create_product_views.sql
-- Runs again whenever this file changes

CREATE OR REPLACE VIEW active_products AS
SELECT
    p.id,
    p.name,
    p.sku,
    p.price,
    p.stock_qty,
    c.name  AS category_name,
    p.status,
    p.created_at
FROM  products p
JOIN  categories c ON c.id = p.category_id
WHERE p.status = 'ACTIVE';

CREATE OR REPLACE VIEW order_summary AS
SELECT
    o.id         AS order_id,
    o.reference,
    o.status,
    o.total,
    o.placed_at,
    u.name       AS customer_name,
    u.email      AS customer_email,
    COUNT(oi.product_id) AS item_count
FROM  orders o
JOIN  users  u  ON u.id = o.user_id
JOIN  order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.reference, o.status, o.total,
         o.placed_at, u.name, u.email;

-- R__stored_procedures.sql
CREATE OR REPLACE FUNCTION calculate_order_total(p_order_id BIGINT)
RETURNS NUMERIC AS $$
DECLARE
    v_total NUMERIC(10,2);
BEGIN
    SELECT COALESCE(SUM(line_total), 0)
    INTO   v_total
    FROM   order_items
    WHERE  order_id = p_order_id;

    UPDATE orders SET total = v_total WHERE id = p_order_id;
    RETURN v_total;
END;
$$ LANGUAGE plpgsql;

-- R__seed_reference_data.sql
-- Use INSERT ... ON CONFLICT DO NOTHING for idempotent seeding
INSERT INTO categories (id, name, slug) VALUES
    (1, 'Electronics',  'electronics'),
    (2, 'Clothing',     'clothing'),
    (3, 'Books',        'books'),
    (4, 'Home & Garden','home-garden')
ON CONFLICT (slug) DO NOTHING;

Java-Based Migrations

Java migrations implement SpringJdbcMigration or JavaMigration and are discovered automatically by classpath scanning. Use them for complex data transformations that are difficult in SQL — reading files, calling APIs, hashing passwords, or generating UUIDs. They live in the same db/migration package by default.

Java

// ── V10__migrate_user_passwords.java ─────────────────────────────────
// File location: src/main/java/db/migration/V10__migrate_user_passwords.java

@Component
public class V10__migrate_user_passwords
        implements SpringJdbcMigration {

    private final PasswordEncoder passwordEncoder =
        new BCryptPasswordEncoder(12);

    @Override
    public void migrate(JdbcTemplate jdbcTemplate) {
        // Read all users with plain-text passwords
        List<Map<String, Object>> users = jdbcTemplate.queryForList(
            "SELECT id, password_plain FROM users " +
            "WHERE password_hash IS NULL");

        users.forEach(user -> {
            Long   id    = (Long)   user.get("id");
            String plain = (String) user.get("password_plain");

            String hash = passwordEncoder.encode(plain);

            jdbcTemplate.update(
                "UPDATE users SET password_hash = ?, " +
                "password_plain = NULL WHERE id = ?",
                hash, id);
        });
    }
}

// ── V11__generate_product_slugs.java ──────────────────────────────────
public class V11__generate_product_slugs
        implements SpringJdbcMigration {

    @Override
    public void migrate(JdbcTemplate jdbcTemplate) {
        List<Map<String, Object>> products = jdbcTemplate.queryForList(
            "SELECT id, name FROM products WHERE slug IS NULL");

        products.forEach(product -> {
            Long   id   = (Long)   product.get("id");
            String name = (String) product.get("name");
            String slug = toSlug(name) + "-" + id;

            jdbcTemplate.update(
                "UPDATE products SET slug = ? WHERE id = ?",
                slug, id);
        });
    }

    private String toSlug(String text) {
        return text.toLowerCase()
            .replaceAll("[^a-z0-9\s-]", "")
            .replaceAll("\s+", "-")
            .replaceAll("-+", "-")
            .strip();
    }
}

// ── V12__load_countries_from_csv.java ─────────────────────────────────
public class V12__load_countries_from_csv
        implements SpringJdbcMigration {

    @Override
    public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
        try (InputStream is = getClass()
                .getResourceAsStream("/db/data/countries.csv");
             BufferedReader reader = new BufferedReader(
                 new InputStreamReader(is))) {

            reader.lines()
                .skip(1)                  // skip header
                .map(line -> line.split(","))
                .forEach(parts -> jdbcTemplate.update(
                    "INSERT INTO countries (code, name, dial_code) " +
                    "VALUES (?, ?, ?) ON CONFLICT (code) DO NOTHING",
                    parts[0].trim(),
                    parts[1].trim(),
                    parts[2].trim()));
        }
    }
}

Callbacks

Flyway callbacks execute custom logic at specific points in the migration lifecycle — before and after migration runs, on validation errors, and on clean. Implement FlywayCallback or use SQL callback scripts named with the event name (beforeMigrate.sql, afterMigrate.sql). Common uses include disabling triggers during migration and logging migration events.

Java

// ── Java callback ─────────────────────────────────────────────────────
@Component
@Slf4j
public class MigrationAuditCallback implements Callback {

    @Override
    public boolean supports(Event event, Context context) {
        return event == Event.AFTER_EACH_MIGRATE
            || event == Event.AFTER_EACH_MIGRATE_ERROR
            || event == Event.BEFORE_MIGRATE
            || event == Event.AFTER_MIGRATE;
    }

    @Override
    public boolean canHandleInTransaction(Event event, Context context) {
        return true;
    }

    @Override
    public void handle(Event event, Context context) {
        switch (event) {
            case BEFORE_MIGRATE ->
                log.info("Flyway migration starting...");

            case AFTER_MIGRATE ->
                log.info("Flyway migration completed successfully");

            case AFTER_EACH_MIGRATE -> {
                MigrationInfo info = context.getMigrationInfo();
                log.info("Applied migration {} — {}",
                    info.getVersion(), info.getDescription());
            }

            case AFTER_EACH_MIGRATE_ERROR -> {
                MigrationInfo info = context.getMigrationInfo();
                log.error("Migration {} failed — {}",
                    info.getVersion(), info.getDescription());
            }
        }
    }

    @Override
    public String getCallbackName() {
        return "MigrationAuditCallback";
    }
}

// ── Register callback in Flyway configuration ─────────────────────────
@Configuration
public class FlywayConfig {

    @Bean
    public FlywayConfigurationCustomizer flywayCustomizer(
            MigrationAuditCallback auditCallback) {
        return config -> config.callbacks(auditCallback);
    }
}

-- ── SQL callbacks — placed in db/migration/ ───────────────────────────
-- beforeMigrate.sql — runs before any migration
SET statement_timeout = '120s';
SET lock_timeout      = '30s';

-- afterMigrate.sql — runs after all migrations complete
ANALYZE;    -- update statistics after schema changes

-- beforeEachMigrate.sql — runs before every individual migration
-- afterEachMigrate.sql  — runs after every individual migration

Testing with Flyway

Flyway integrates naturally with Spring Boot tests. Use an in-memory H2 database with Flyway to run migrations in tests, or use Testcontainers to run migrations against a real PostgreSQL or MySQL instance. Always test migrations before applying them to production.

Java

// ── Integration test with H2 ──────────────────────────────────────────
@SpringBootTest
@ActiveProfiles("test")
class FlywayMigrationTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private Flyway flyway;

    @Test
    void migrationsApplyCleanly() {
        // Flyway runs automatically on context load
        // Verify the expected schema version
        MigrationInfo[] applied = flyway.info().applied();
        assertThat(applied).isNotEmpty();
        assertThat(flyway.info().pending()).isEmpty();
    }

    @Test
    void usersTableExists() {
        Integer count = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM users", Integer.class);
        assertThat(count).isNotNull().isGreaterThanOrEqualTo(0);
    }

    @Test
    void schemaHistoryIsPopulated() {
        Integer migrations = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM flyway_schema_history " +
            "WHERE success = true", Integer.class);
        assertThat(migrations).isGreaterThan(0);
    }
}

// ── Testcontainers — run against real PostgreSQL ───────────────────────
@SpringBootTest
@Testcontainers
class FlywayPostgresIntegrationTest {

    @Container
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:16-alpine")
            .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;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void allMigrationsApplyOnPostgres() {
        assertThat(flyway.info().pending()).isEmpty();
        assertThat(flyway.info().applied()).isNotEmpty();
    }

    @Test
    void canInsertAndQueryUser() {
        jdbcTemplate.update(
            "INSERT INTO users (name, email, username) " +
            "VALUES (?, ?, ?)",
            "Alice", "alice@example.com", "alice");

        Map<String, Object> user = jdbcTemplate.queryForMap(
            "SELECT * FROM users WHERE email = ?",
            "alice@example.com");

        assertThat(user.get("name")).isEqualTo("Alice");
        assertThat(user.get("status")).isEqualTo("ACTIVE");
    }
}

Elasticsearch Integration

Liquibase Migration