Flyway with Spring Boot: Best Practices for Safe Database Migrations (2026)

Database migrations are one of the highest-risk operations in production deployments. A migration that locks a large table can take your entire application down for minutes. A migration that can't be rolled back turns a bad deploy into a multi-hour incident. Flyway makes migrations manageable - but only if you use it correctly.

---

Setup and Basic Configuration

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<!-- For PostgreSQL -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

# application.properties
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.flyway.baseline-on-migrate=true  # For existing databases
spring.flyway.validate-on-migrate=true  # Validate checksums on startup
spring.flyway.out-of-order=false        # Enforce version ordering

---

Naming Convention

Flyway's migration naming convention is strict and critical:

V{version}__{description}.sql

Examples:
V1__create_users_table.sql
V2__add_email_index_to_users.sql
V3__create_orders_table.sql
V3.1__add_shipping_address_to_orders.sql

Rules:

• V prefix for versioned migrations (applied once)
• R prefix for repeatable migrations (re-applied when checksum changes)
• Double underscore __ separates version from description
• Version can use dots: V1.2.3__description.sql
• Never modify an applied migration - Flyway will fail with checksum mismatch

---

Zero-Downtime Migrations for Large Tables

Adding a NOT NULL column to a 50-million-row table without a default value locks the entire table during backfill - potentially for minutes. The zero-downtime approach uses three separate deployments:

Step 1 - Add column as nullable (deploy v1)

-- V10__add_phone_to_users.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);  -- No NOT NULL, no lock

Step 2 - Backfill in batches (application code)

@Component
public class PhoneBackfillJob {

    @Scheduled(fixedDelay = 1000)
    @Transactional
    public void backfillPhones() {
        int updated = jdbcTemplate.update(
            "UPDATE users SET phone = 'UNKNOWN' " +
            "WHERE phone IS NULL LIMIT 1000"  // Small batches
        );
        if (updated == 0) log.info("Phone backfill complete");
    }
}

Step 3 - Add NOT NULL constraint (deploy v2, after backfill)

-- V11__make_phone_not_null.sql
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

For PostgreSQL 12+, use NOT VALID to add constraints without scanning existing rows:

-- Add constraint without full table scan
ALTER TABLE orders ADD CONSTRAINT chk_positive_total
    CHECK (total > 0) NOT VALID;

-- Validate in background (doesn't lock)
ALTER TABLE orders VALIDATE CONSTRAINT chk_positive_total;

---

Index Creation Without Locking (PostgreSQL)

-- BLOCKS all writes while building the index (dangerous on large tables)
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- CONCURRENT build - no write locks, takes longer but safe
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

Flyway caveat: CREATE INDEX CONCURRENTLY cannot run inside a transaction. Disable Flyway's transaction wrapping for this migration:

-- V12__create_orders_customer_index.sql
-- flyway:executeInTransaction=false
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id
    ON orders(customer_id);

---

Handling Rollbacks

Flyway Community Edition doesn't support automatic rollbacks. The professional approach: make every migration forward-compatible and write undo scripts manually.

-- V13__add_orders_archived_column.sql (forward)
ALTER TABLE orders ADD COLUMN archived BOOLEAN DEFAULT FALSE;

-- U13__add_orders_archived_column.sql (undo - Flyway Pro)
ALTER TABLE orders DROP COLUMN archived;

For Community Edition, test rollbacks with a separate script:

# rollback_V13.sql - run manually if needed
ALTER TABLE orders DROP COLUMN archived;

---

Separate Flyway for Tests

# application-test.properties
spring.flyway.locations=classpath:db/migration,classpath:db/testdata
spring.flyway.clean-on-validation-error=true  # Reset DB on schema change in tests

-- src/test/resources/db/testdata/R__insert_test_data.sql (Repeatable)
TRUNCATE users, orders CASCADE;
INSERT INTO users (id, name, email) VALUES
    (1, 'Alice', 'alice@test.com'),
    (2, 'Bob', 'bob@test.com');

---

Common Mistakes to Avoid

• Modifying an applied migration - Flyway will refuse to start with a FlywayValidateException; always create a new migration for changes
• Running flyway:clean in production - this drops ALL objects in the schema; never add the clean goal to production pipelines
• Adding NOT NULL columns with no default to large tables - will lock the table; always add as nullable first, backfill, then add the constraint
• One giant migration per release - split migrations into small, independent steps; a failed 20-step migration is a nightmare to recover from

---

Summary

Flyway makes database migrations reliable, but zero-downtime migrations require deliberate patterns: add columns as nullable first, backfill in batches, then add constraints. Create concurrent indexes for large tables. Never modify applied migrations. Keep migrations small and independent. Test your undo scripts before you need them.

---

Detect Migration Anti-Patterns in Your Project

JOptimize flags dangerous migration patterns - NOT NULL columns without defaults, missing CONCURRENTLY on large table indexes, and migrations that will lock tables in production.

• IntelliJ Plugin - highlights risky migration SQL inline as you write it: Install JOptimize for IntelliJ
• Web Dashboard - scan your migration scripts for zero-downtime risks: Analyze your project free ?

Write safe database migrations the first time - free scan, no configuration required.