Using Flyway to version your database

When software starts using a database, it's advisable to have version control, just as we have Github to control our source code.
This is all to be sure about what was executed for that specific version.
For Java and Spring boot, we have the Flyway framework that aims to resolve this situation, free of charge.

How works
Flyway will run scripts that have been placed in resources/db/migration/ in order of script version, which follows the nomenclature definition:

• Start with V followed by the version number
• Followed by __ + The description of what will be executed
• With the extension of .sql

We will have files like this:

• V1__Inital_Setup.sql
• V2__Create_table_person.sql
• v3__Create_table_roles.sql
• V4__Fix_table_person.sql

Each file must contain SQL code, it is recommended to separate the content into files that have the same purpose, and once created it is not advisable to keep changing them or their names as this will have a chance of generating an error.

For every file executed, it creates a table in the database called flyway_schema_history to know which files have already been executed and which ones are successful or error.

Dependency
To implement it in your project you must first place it in a Maven or Gradle dependency.
In this post in particular we will do it for Maven and MySQL previously configured in your Spring Boot 3.0.6 project
In the pom.xml file:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
</dependency>

Practical example
After placing the Flyway dependency and having already configured the Mysql database in your project, we will create the 2 files in the folder resources/db/migration/

V1__Create_table_person.sql

CREATE TABLE IF NOT EXISTS `person` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `first_name` varchar(80) NOT NULL,
  `last_name` varchar(80) NOT NULL,
   `address` varchar(255) NOT NULL,
  `gender` varchar(1) NOT NULL,
  PRIMARY KEY (`id`)
);

V2__populate_table_person.sql

INSERT INTO `person` (`id`, `address`, `first_name`, `gender`, `last_name`) VALUES
    (1, 'São Paulo- SP', 'Pedro', 'M', 'Silva'),
    (2, 'Curitiba - PR', 'João', 'M', 'Pereira'),
    (3, 'Belo Horizonte - MG', 'Ana', 'F', 'Souza'),
    (4, 'Salvador - BA', 'Maria', 'F', 'Coelho');

After that, just start your project and check the logs for success:

Successfully validated 12 migrations (execution time 00:00.038s)
Creating Schema History table `your_database_name`.`flyway_schema_history` ...
Current version of schema `your_database_name`: << Empty Schema >>
Migrating schema `your_database_name` to version "1 - Create table person"
Migrating schema `your_database_name` to version "2 - populate table person"
Successfully applied 12 migrations to schema `your_database_name`, now at version v2 (execution time 00:00.492s)

In the flyway_schema_history table it has the value 1 for the succes column.

Conclusion
In this article we learned how to create version control for our SQL scripts using Flyway, so that we are always sure what was executed in the database with each version.

To find out more, I recommend reading the official documentation:
https://flywaydb.org/