Gyaan

DB Migration: Flyway using SpringBoot

Sujal Maiti's photo
Sujal Maiti
·

3 min read

Introduction

Flyway is a robust database migration tool that allows version control for your database, facilitating consistent and reliable management of database schema changes. When integrated with Spring Boot, Flyway automates the process of database versioning and migration, ensuring that your database schema evolves smoothly alongside your application.

Why Use Flyway?

  1. Version Control: Keeps track of all database changes with version numbers.

  2. Automation: Automatically applies pending migrations on application startup.

  3. Compatibility: Supports a wide range of databases and works seamlessly with Spring Boot.

  4. Reproducibility: Ensures the database schema can be recreated and updated consistently across different environments.

Setting Up Flyway with Spring Boot

To get started with Flyway in a Spring Boot application, follow these steps:

  1. Add Flyway Dependency: Include Flyway in your pom.xml (for Maven) or build.gradle (for Gradle).

Maven

org.flywaydb flyway-core

  1. Configure Data Source: Ensure your application has a configured data source in application.properties or application.yml.

    application.properties

spring.datasource.url=

spring.datasource.username=root spring.datasource.password=

spring.datasource.driver-class-name=

(Pls give necessary info because why shouldI give xD? Kiddding!! Kidding!

  1. Create Migration Scripts: Place your migration scripts in the db/migration directory under src/main/resources.

Writing Migration Scripts

Migration scripts follow a specific naming convention: V<VERSION>__<DESCRIPTION>.sql.

Example Migration Script: V1__create_user_table.sql

CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, password VARCHAR(100) NOT NULL, email VARCHAR(100), PRIMARY KEY (id) );

Example Migration Script: V2__add_role_to_user.sql

ALTER TABLE user ADD COLUMN role VARCHAR(50) NOT NULL DEFAULT 'USER';

Running Flyway Migrations

Flyway migrations are executed automatically when the Spring Boot application starts. Flyway scans the db/migrationdirectory for SQL scripts and applies any pending migrations to the database.

Customizing Flyway

You can customize Flyway's behavior using configuration properties in application.properties or application.yml.

application.properties

flyway.baseline-on-migrate=true flyway.locations=classpath:db/migration,filesystem:/external/migrations

application.yml

flyway: baseline-on-migrate: true locations: - classpath:db/migration - filesystem:/external/migrations

Real-World Examples

1. E-commerce Application

In an e-commerce application, database schema changes are frequent. For example, adding new features like a wishlist, order tracking, or discount codes require changes to the database schema. Flyway helps manage these changes efficiently by versioning each change and applying them seamlessly across different environments (development, testing, production).

Migration Example: Adding a Wishlist Table

-- V3__create_wishlist_table.sql

CREATE TABLE wishlist ( id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, product_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES user(id), FOREIGN KEY (product_id) REFERENCES product(id) );

2. Microservices Architecture

In a microservices architecture, each service may have its own database. Flyway ensures that each microservice's database schema is version-controlled and migrates correctly. This is crucial for maintaining consistency across different services that evolve independently.

Migration Example: Adding a Reviews Service

-- V1__create_reviews_table.sql

CREATE TABLE review ( id INT NOT NULL AUTO_INCREMENT, product_id INT NOT NULL, user_id INT NOT NULL, rating INT NOT NULL, comment TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), FOREIGN KEY (product_id) REFERENCES product(id), FOREIGN KEY (user_id) REFERENCES user(id) );

Conclusion

Flyway is a powerful tool for managing database migrations in a Spring Boot application. It simplifies the process of evolving your database schema, ensures consistency across different environments, and integrates seamlessly with Spring Boot. By following the steps and examples provided, you can effectively use Flyway to handle your application's database migrations, making your development process more robust and manageable.

JavaSpringboot