When we're working with microservices one of the goals are to have self-container applications. The database in general is one of the items that we have to handle and in a lot of cases It was managed outside of the application.
One framework that allows us to version your database with migrations is Flyway (https://flywaydb.org/).
Flyway help us to bring all the changes of the database for your spring-boot project throw the SQL scripts and some metadata to handle all the changes of the database.
The advantage of this method is that anyone with the project will have the same state of the DB. In general, a copy of the developing or production database.
In this article I'll show how to configure the Flyway in a spring-boot project.
Creating the project
To create the project, we'll use the official site of spring to setup a new project.
First access the website: https://start.spring.io/
When the website is open, you can set the configurations like the following image:
When is done, you can click on the GENERATE button to download the configured project. After that, you can import it to you IDE. I will use Intellij Idea (https://www.jetbrains.com/idea/).
Understanding the Flyway
If you open the file pom.xml, you will see the dependency of flyway-core like this:
In the project structure you will see the folder db.migration, we will save all the SQL files inside this folder.
When we start up the project one of the tasks will see if any new script was included in project, if we have a new one the project will run it on the database.
To create your new script, we have to follow some pattern in the name of the file.
The pattern needs to be a number that will be incremented to help flyway see the sequence of the migration execution.
For this tutorial we will have to create a script like the following example and use V1, V2, V3 to increment the new files:
V1__create_base_tables.sql
Creating the first file
Create the new file called V1_create_base_tables.sql in db.migration folder, following the script below:
Configuring database
To simplify your tutorial I will use the h2 database (a memory DB) to show how the flyway works it. We need to set the project with the H2 parameters.
In the pom.xml file add the following dependency:
And next we will set the login settings in the project, in the application.properties file add the following settings:
After running, you'll see similar logs on console:
2023-04-07 14:12:29.896 INFO 8012 --- [ main] o.f.c.i.database.base.BaseDatabaseType : Database: jdbc:h2:mem:testdb (H2 2.1)
2023-04-07 14:12:30.039 INFO 8012 --- [ main] o.f.core.internal.command.DbValidate : Successfully validated 1 migration (execution time 00:00.037s)
2023-04-07 14:12:30.055 INFO 8012 --- [ main] o.f.c.i.s.JdbcTableSchemaHistory : Creating Schema History table "PUBLIC"."flyway_schema_history" ...
2023-04-07 14:12:30.132 INFO 8012 --- [ main] o.f.core.internal.command.DbMigrate : Current version of schema "PUBLIC": << Empty Schema >>
2023-04-07 14:12:30.143 INFO 8012 --- [ main] o.f.core.internal.command.DbMigrate : Migrating schema "PUBLIC" to version "1 - create base tables"
2023-04-07 14:12:30.177 INFO 8012 --- [ main] o.f.core.internal.command.DbMigrate : Successfully applied 1 migration to schema "PUBLIC", now at version v1 (execution time 00:00.057s)
2023-04-07 14:12:30.477 INFO 8012 --- [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [name: default]
When we insert a new script, like V2__new_tables.sql, the flyway will execute only the new script.
Consideration: in this case we're using a memory database, when the application stops all data will be lost. When we start it again with the second script, flyway will start the database again running all the scripts. For the next posts I will cover a real database and explore some use cases.
Conclusion
Versioning the database from the projects give us some advantages like give to all the developers a mirror of the database. When we have any modifications, the application will handle those modifications and apply to development or production environment.
References
For more details you can see the official Spring documentation: https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto.data-initialization.migration-tool.flyway
Creating migrations:
H2 database