Using Flyway to version your database
Tags: flyway, springboot, java
Published: 2023-12-17 20:30:21


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](https://flywaydb.org/) framework that aims to resolve this situation, free of charge.

**How works**
Flyway will run scripts that have been placed in <u>_resources/db/migration/_</u> 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.

![Table flyway](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hnmnmcjtbexc57wyxox6.png)

**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/