Simplifying Database Migrations in Spring Boot with Flyway: A Comprehensive Guide

Maintaining an adaptable database structure is paramount in the ever-growing domain of software development. As applications evolve, so must their underlying databases. However, this process often presents challenges such as manual interventions, inconsistent version control, and the risk of data loss during migrations. These challenges can hinder team collaboration, leading to communication breakdowns, errors, and project delays.

To address these issues, Flyway emerges as a versatile open-source database migration and schema management tool. With Flyway, developers can automate tasks, seamlessly track changes, and facilitate collaboration, ensuring smooth transitions and precise version control of database schemas.

Real-Life Scenario:

Imagine you’re part of a dynamic development team for an e-commerce platform. Initially conceived as a basic online marketplace, your platform quickly adapts to meet user demands. As it gains traction, continuous enhancements become vital. Let’s explore scenarios where database schema changes are necessary:

1. Product Catalog Expansion:

• As the user base grows, demand for more products arises.
• New categories, attributes, and variations need to be incorporated.
• Examples include clothing size options, color variations, and electronic gadget specifications.

2. User Account Enhancements:

• Advanced user functionalities improve the shopping experience.
• Features like user profiles, order history tracking, and wishlist management are introduced.
• These enhancements require modifications in user-related database tables.

Manual database migrations pose challenges:

• Tracking and managing schema changes becomes cumbersome with increasing complexity.
• Coordinating migrations across different environments risks inconsistencies and data loss, hindering efficiency and collaboration.

This is precisely where Flyway shines:

• Flyway offers structured database schema management and version control.
• It automates migration script execution and maintains a version history for seamless migrations across environments.
• Integration with Spring Boot streamlines deployment, allowing developers to focus on feature development rather than migration management.

Step-by-Step Implementation Guide:

In this section, I will show a step-by-step implementation for a Flyway database migration in action using a product entity and pom.xml.

1. Setting Up Your Spring Boot Project:

Begin by creating a new Spring Boot project or using an existing one. Ensure you have the necessary dependencies for Spring Boot and MySQL configured in your pom.xml.

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        
 <!-- flyway dependency -->
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-mysql</artifactId>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

2. Configuring Flyway in application.yml:

Configure Flyway in your application.yml file, specifying the database connection details and the locations of your migration scripts:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/ecommerce
    username: your_username
    password: your_password
    hikari:
      schema: ecommerce
  flyway: #flyway automatically uses the datasource from the application to connect to the DB
    enabled: true # enables flyway database migration
    locations: classpath:db/migration/structure, classpath:db/migration/data # the location where flyway should look for migration scripts
    validate-on-migrate: true
    default-schema: ecommerce

In this configuration:

locations specifies the locations where Flyway should look for migration scripts. You can create as many folders as needed. In our example, we have two folders: data and structure inside the db/migration directory. The data folder contains migration scripts for data manipulation, while the structure folder contains migration scripts for data definition. This organization helps separate scripts based on their specific purposes, making it easier to manage database schema changes. Each folder should contain migration scripts with unique version numbers to ensure proper execution order.

3. Creating Migration Scripts:

Inside the src/main/resources/db/migration directory, create two folders: data and structure. Within each folder, create migration scripts following the naming convention: V<version>__<name>.sql. Flyway automatically ensures that migrations run in order of their versions. Thus, it is crucial to maintain unique version numbers across all folders to avoid conflicts. This ensures that Flyway can accurately track the order of migrations and apply them sequentially.

• create a script named “V1__CREATE_PRODUCT_TABLE.SQL” in the structure folder to create a product table

CREATE TABLE PRODUCT
(
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(255)   NOT NULL,
    description VARCHAR(255),
    price       DECIMAL(10, 2) NOT NULL,
    CREATED_AT  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    Modified_AT TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP
);

• create a script named “V1.1__INSERT_PRODUCT_DATA.SQL” in the data folder to populate the table

INSERT INTO PRODUCT (name, description, price)
VALUES ('Product 1', 'Description of Product 1', 10.99),
       ('Product 2', 'Description of Product 2', 19.99),
       ('Product 3', 'Description of Product 3', 29.99);

N.B. As you can see, the script versions are unique (one after the other); therefore, Flyway executes them in that order.

4. Executing Migrations:

When you run your Spring Boot application, Flyway automatically detects the migration scripts from the specified locations (data and structure) and applies them to the database. It tracks the applied migrations in a metadata table called flyway_schema_history, ensuring they are only executed once.

You can also see Flyway in action in your application logs, indicating the success or failure of each migration.

Validate the creation and population of the product table with the data provided in the script

Closing Remarks:

By adopting Flyway for database migrations within your Spring Boot project, you have streamlined a critical aspect of your development workflow. Here is why this approach stands out:

1. Automated Migration Process: Flyway automates the execution of migration scripts, eliminating the need for manual intervention. This not only saves time but also reduces the risk of human error during database schema changes.
2. Precise Version Control: With Flyway, each migration script is assigned a unique version number. This ensures that migrations are applied in the correct order, maintaining the integrity of your database schema across different environments.
3. Structured Schema Management: By organizing migration scripts into separate folders based on their purpose (e.g., data and structure), Flyway promotes a structured approach to schema management. This enhances clarity and maintainability, especially as your application grows in complexity.
4. Seamless Integration with Spring Boot: Flyway integrates seamlessly with Spring Boot, leveraging its powerful features while simplifying the management of database migrations. This allows developers to focus more on implementing new features and less on the complexities of migration management.

In essence, Flyway empowers development teams to adapt their database schemas efficiently, ensuring alignment with evolving application requirements. By leveraging automation and structured version control, Flyway minimizes the potential for errors, fosters collaboration, and accelerates the pace of development.

If you enjoyed this article, consider giving it a clap 👏, following me for more content, and sharing your thoughts in the comments. Your support means a lot!