Comprehensive Guide to Flyway Integration and Configuration

[Dynavy]

Database Migration Management with Flyway

1. Introduction:

This document explains the implementation of Flyway in a Spring Boot application for database migration management. Flyway is a migration tool that handles schema changes using versioned SQL scripts. Different methods for configuring and executing Flyway migrations will be covered, including:

• Using Maven Plugin for running migrations manually when the Spring Boot server is down.

• Configuring Flyway in application.properties.

• Using FlywayConfig and FlywayRunner for automatic migrations.

• Other approaches that could be implemented for more flexibility.

Basic Flyway Commands:

Flyway provides several commands that can be executed from the command line to manage database migrations:

mvn flyway:info // Shows the current state of the migrations and checks if the database connection is correct.

mvn flyway:migrate // Executes the migrations that have not been applied yet, updating the schema to the latest version.

mvn flyway:clean // Drops all the tables, views, and other objects in the database (use with caution).

Migration SQL Files and Folder Structure:

Flyway migrations are typically stored as SQL files within a designated folder in the project. The default location for these files is the src/main/resources/db/migration folder. This folder contains the versioned migration scripts that Flyway will use to update the database schema.

Each SQL migration script should follow a specific naming convention to be recognized by Flyway. The standard naming format is:

• <version>: A unique version number that follows a sequential format, e.g., V1, V2, etc.
• <description>: A brief description of the migration (e.g., add_column, create_table).

For example:

V1__initial_schema.sql 
V2__add_user_table.sql
V3__add_index_to_user_table.sql

The versioning scheme is typically as follows:

• V1, V2, V3, ... for the first migrations.
• V1.1, V1.2, ... for minor updates to an existing migration.

Flyway will apply the migrations in the order of the version numbers.

Flyway’s Internal Table:

Flyway automatically creates a table in the database called flyway_schema_history. This table is used to track the migrations that have been applied to the database. It stores metadata such as:

• Migration version.

• Description of the migration.

• Execution time

• Status of the migration (e.g., applied or pending).

This table ensures that Flyway can keep track of which migrations have been applied and prevent migrations from being applied multiple times.

2. Configuring Flyway with Maven Plugin:

To integrate Flyway into the build process, the Flyway Maven Plugin can be used. This enables the execution of Flyway migrations as part of the Maven lifecycle, even when the Spring Boot server is not running.

Steps:

1. Add the Flyway plugin to the pom.xml file:

   <plugin>
       <groupId>org.flywaydb</groupId>
       <artifactId>flyway-maven-plugin</artifactId>
       <configuration>
           <url>${DB_URL}</url>
           <user>${DB_USERNAME}</user>
           <password>${DB_PASSWORD}</password>
           <cleanDisabled>false</cleanDisabled>
       </configuration>
   </plugin>
   

2.. Run the Flyway migrations manually using the following Maven command:

 mvn flyway:migrate

This is especially useful for running migrations in a local or testing environment where the Spring Boot server might not be running.

3. Configuring Flyway in application.properties:

Flyway can also be configured through the application.properties file. This method allows to easily configure the connection to the database and define Flyway-specific settings.

Example of configuring Flyway in application.properties:

spring.flyway.url=${DB_URL}     // Define the database connection properties.
spring.flyway.user=${DB_USERNAME}     // Define the database connection properties.
spring.flyway.password=${DB_PASSWORD}     // Define the database connection properties.
spring.flyway.enabled=true     // Disable automatic execution of Flyway migrations at startup.
spring.flyway.locations=classpath:db/migration     // Specifies the folder where the migration scripts are stored.
spring.flyway.baseline-on-migrate=true     //  Allows Flyway to run migrations on a database that already contains data.
spring.flyway.validate-on-migrate=true     // Ensures that the migrations are validated before being applied.

4. Custom Flyway Configuration with FlywayConfig

In certain scenarios, greater control over Flyway's configuration and execution may be required. This can be achieved by creating a custom FlywayConfig class, where Flyway beans are defined manually.

Steps:

1. Create a configuration class FlywayConfig:

   package com.copay.app.config.flyway;
   
   import org.flywaydb.core.Flyway;
   import org.springframework.context.annotation.Bean;
   import org.springframework.context.annotation.Configuration;
   
   @Configuration
   public class FlywayConfig {
   
       @Bean
       public Flyway flyway() {
           return Flyway.configure()
                        .dataSource(System.getProperty("DB_URL"), System.getProperty("DB_USERNAME"), System.getProperty("DB_PASSWORD"))
                        .load();
       }
   }

This configuration ensures that Flyway is correctly configured with the environment variables (DB_URL, DB_USERNAME, DB_PASSWORD) and will be injected as a Spring Bean. This approach gives more flexibility in defining the Flyway setup in the application, allowing it to be fully integrated with the Spring Boot context.

Additionally, configuring Flyway programmatically in the FlywayConfig class eliminates the need to specify database connection details (such as spring.flyway.url, spring.flyway.user, and spring.flyway.password) in the application.properties file. Since the connection details are already provided through FlywayConfig, the properties file can be used exclusively for additional Flyway configurations, including migration locations, validation, or baseline settings.

5. Automatic Migrations with FlywayRunner:

To enable automatic migrations on application startup, a custom FlywayRunner component can be implemented. This component will ensure that Flyway migrations are executed as soon as the Spring Boot application initializes.

Steps:

1. Create a FlywayRunner class that implements CommandLineRunner:

   package com.copay.app.config.flyway;
   
   import org.flywaydb.core.Flyway;
   import org.springframework.boot.CommandLineRunner;
   import org.springframework.stereotype.Component;
   
   @Component
   public class FlywayRunner implements CommandLineRunner {
   
       private final Flyway flyway;
   
       // Constructor with dependency injection.
       public FlywayRunner(Flyway flyway) {
           this.flyway = flyway;
       }
   
       @Override
       public void run(String... args) throws Exception {
           // Automatically runs Flyway migrations at startup.
           flyway.migrate();
       }
   }

6. Other Approaches for Flexibility:

While the methods discussed above (using the Maven plugin, application.properties configuration, and automatic migrations with FlywayRunner) cover most use cases, there are other approaches that can be considered for more flexibility in managing the database migrations.

6.1 Running Migrations with HTTP Requests:

If manual execution of migrations is preferred, a custom REST endpoint can be defined to manually trigger Flyway migrations. This approach provides more control over when the migrations are executed, allowing them to be triggered via HTTP requests.

Example: Running Migrations on a Specific Endpoint

package com.copay.app.controller;

import org.flywaydb.core.Flyway;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class FlywayController {

    @Autowired
    private Flyway flyway;

    @PostMapping("/migrate")
    public String triggerMigrations() {
        try {
            flyway.migrate();
            return "Migrations executed successfully!";
        } catch (Exception e) {
            return "Error executing migrations: " + e.getMessage();
        }
    }
} 

In this example, when the /migrate endpoint is hit, Flyway will execute the migrations. This approach is ideal for scenarios where migrations need to be controlled manually through HTTP requests.

6.2 Using @Scheduled for Periodic Migrations:

If periodic execution of migrations is desired (e.g., daily or weekly), Spring's @scheduled annotation can be used to schedule Flyway migrations at specific intervals.

Example:

package com.copay.app.config;

import org.flywaydb.core.Flyway;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.Scheduled;

@Configuration
public class ScheduledMigrationConfig {

    @Autowired
    private Flyway flyway;

    // Run at 2 AM every day.
    @Scheduled(cron = "0 0 2 * * ?") 
    public void runMigrations() {
        flyway.migrate();
    }
}

This example will automatically run Flyway migrations at 2 AM every day. The cron can be adjusted to fit the correct schedule.

7. Conclusion

Flyway provides an easy and effective way to manage database migrations.
This document has explored multiple ways to integrate Flyway into a Spring Boot application:

• Using Maven plugin for manual migrations when the Spring Boot server is down.

• Configuring Flyway via application.properties for easy setup.

• Writing custom FlywayConfig and FlywayRunner classes for more control over migrations, including automatic execution at startup.

Additionally, other methods have been explored, such as:

• HTTP-based migrations: Triggering migrations via an HTTP request to a dedicated endpoint, allowing migrations to be run externally.

• Scheduled migrations: Using Spring's @scheduled annotation to periodically run migrations at defined intervals, ensuring the database stays up-to-date without manual intervention.

Using these approaches ensures the database remains in sync with the application, whether through automatic, manual, or scheduled execution of migrations.

8 .Interaction between Hibernate and Flyway:

When using both Hibernate and Flyway in a Spring Boot application, ensuring consistency between database schema changes made by Hibernate and migration scripts managed by Flyway is crucial.

If the Hibernate ddl-auto configuration is set to update, Hibernate will automatically update the database schema based on entity class changes (e.g., adding a new column, altering data types, etc.). However, these changes are not versioned or tracked by Flyway, which can lead to inconsistencies between the application code and the database schema over time.

Best Practice:

• After modifying an entity and persisting data with Hibernate, a new Flyway migration script should be created to update the database schema accordingly.

• This ensures that the database schema is properly versioned and managed, preventing issues during deployment or migration across different environments.

Following this practice keeps the database schema in sync with application changes, avoiding inconsistencies when working in various environments or with multiple developers.

9. Mermaid Diagram:

[Dynavy]

Flyway Guide 16/03/2025
@poksyy @krschan

[poksyy]

🤙🤙🤙

[krschan]

https://www.youtube.com/watch?v=EvuL5jyCHOw