Database Schema Migration with Flyway

Flyway is an open-source database schema migration tool that allows you to manage and apply database changes in a controlled, versioned, and repeatable way. Without a tool like Flyway, schema changes are applied manually via SQL scripts run by a DBA, with no audit trail, no way to know which scripts have been applied to which environment, and no automatic rollback if something goes wrong. Flyway solves all of these problems by treating database schema changes as code - versioned, committed to source control, and applied automatically during application startup or CI/CD pipeline runs.

Flyway works by maintaining a schema history table (flyway_schema_history) in the database that tracks which migration scripts have been applied, when they were applied, who applied them, and their checksums. Migration scripts follow a strict naming convention: V{version}__{description}.sql for versioned migrations (run once), R__{description}.sql for repeatable migrations (run whenever their checksum changes), and U{version}__{description}.sql for undo migrations. Flyway compares the scripts in your project against the history table and applies any that have not been run yet, in version order.

The below example shows the Flyway migration file structure for a Spring Boot application, including the SQL migration files and how Flyway is configured in application.properties to run automatically on startup.

It gives the following output when the Spring Boot application starts,

Flyway Community Edition 9.22.0 by Redgate
Database: jdbc:mysql://localhost:3306/appdb (MySQL 8.0)
Successfully validated 4 migrations (execution time 00:00.023s)
Creating Schema History table `appdb`.`flyway_schema_history` ...
Current version of schema `appdb`: null
Migrating schema `appdb` to version "1 - Create customers table"
Migrating schema `appdb` to version "2 - Create orders table"
Migrating schema `appdb` to version "3 - Add email index to customers"
Migrating schema `appdb` to version "4 - Add status column to orders"
Successfully applied 4 migrations to schema `appdb` (execution time 00:00.189s)

The below example shows how to add Flyway to a Spring Boot Maven project and configure it, then use the Flyway command-line tool to inspect migration status and run migrations manually outside of application startup.

application.properties configuration,

# application.properties
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.flyway.baseline-on-migrate=true
spring.flyway.validate-on-migrate=true
spring.flyway.out-of-order=false

It gives the following output,

Schema version: 4

+-----------+---------+------------------------------+----------+---------------------+----------+
| Category  | Version | Description                  | Type     | Installed On        | State    |
+-----------+---------+------------------------------+----------+---------------------+----------+
| Versioned | 1       | Create customers table       | SQL      | 2024-01-10 09:00:00 | Success  |
| Versioned | 2       | Create orders table          | SQL      | 2024-01-10 09:00:00 | Success  |
| Versioned | 3       | Add email index to customers | SQL      | 2024-01-12 14:30:00 | Success  |
| Versioned | 4       | Add status column to orders  | SQL      | 2024-01-15 10:00:00 | Success  |
| Versioned | 5       | Migrate order status data    | SQL      |                     | Pending  |
+-----------+---------+------------------------------+----------+---------------------+----------+

Key Flyway Best Practices:

Never modify an applied migration - Flyway checksums every applied script. If you change V3 after it has been applied to any environment, Flyway will refuse to start with a checksum mismatch error. Always create a new migration script instead.

Make migrations backward compatible - In zero-downtime deployments, the new version of the application may run alongside the old version briefly. Schema changes should be compatible with the previous application version. Use the Expand-Contract pattern: first add the new column (Expand), then migrate data, then remove the old column in a later migration (Contract).

Test migrations in CI/CD - Run flyway migrate against a test database in every pull request pipeline. This catches migration errors before they reach production.