Having automated database migrations will significantly ease managing schema development. With Spring Boot support we can effortlessly handle versioned SQL scripts.
What we are going to build
I have an example Spring Boot project that is configured to connect to a PostgreSQL database run from a docker container. I’m going to add Flyway to this project and use migrations to create tables based on my entities. I will write migrations in SQL. Every file will enclose a distinct set of commands updating the database schema to a newer version.
You can learn how to instantiate a database in a container in the Set up a PostgreSQL database with Docker post.
You can learn how to connect a Spring Boot project with a PostgreSQL database in the Add a PostgreSQL database to your Spring Boot project post.
You can find the example project used in this post in the spring-boot-postgres-flyway repository.
Our example REST API has only one entity – Cookie. The application relays on a PostgreSQL database that can be instantiated using the docker container that is included in the project.
You can see the project directory tree on the image below:
- We are working on a
Spring Boot 2.2.5project with the
JPAdependencies. You can read about setting up a similar project with Spring Initializr in How to create a new Spring Boot Project post.
- A PostgreSQL database. You can use the container that is included in the repository and presented in the Setup a PostgreSQL database with Docker article. You will need Docker Engine – Community with Docker CLI for that.
Include the Flyway dependency
Add the dependency for the Flyway core driver to your
Migration files are detected automatically through filesystem and Java classpath scanning at runtime. You can specify a custom location of those files:
Once you have configured thehttps://flywaydb.org/documentation/migrations#discovery
locationsyou want to use, Flyway will automatically pick up any new SQL migrations as long as they conform to the configured naming convention.
I created the
migration directories in the
resources folder, as that is where Flyway will be looking for my migrations by default:
You don’t have to write the first migration by hand
We can obtain a ready to use SQL by utilizing Hibernate features for saving the DDL script into a file. Read the Save Hibernate DDL schema to a file post to learn how you can save time when creating the first migration.
If you have more entities that one and there are relations between them, writing the script manually may be cumbersome. In my project, the generated file looks like this:
Once you’ve verified the prepared code you can move the file to the
resources/db/migration or any other location that you had specified in application properties.
Furthermore, we have to change the file name so it adheres to the naming convention. I decided to include the date of every migration in the
Version part, so in my case the first migration that was created on March 11th 2020 is called
Description part is optional and I decided to not include it for the sake of brevity. Whatever your strategy is, make sure that it’s uniformly accepted and used by all members of the team.
Flyway allows us to decide how to handle files that breaks the naming strategy:
The configuration optionhttps://flywaydb.org/documentation/migrations#naming
validateMigrationNamingdetermines how Flyway handles files that do not correspond with the naming pattern when carrying out a migration: if
truethen Flyway will simply ignore all such files, if
falsethen Flyway will fail fast and list all files which need to be corrected.
Remove conflicting properties
We no longer want Hibernate to create the tables when the application starts. Remember to remove properties like this one:
or the following one:
In my example I am using a database run in a Docker container, so I removed the volume to make sure that the first migration will generate the schema. Then I started the container with the following command:
If you were using an in-memory database, you don’t have to worry with dropping it.
Start the application
On application startup all new migrations will be executed automatically.
If you rerun the app without any new migrations, you will see:
Verify databse tables
You can see the database schema on the screenshot taken from my IntelliJ on the image below:
Keep track on migrations
Flyway clearly marks which migrations were already applied. Therefore you always know which scripts are going to be recognized as “new” and run against the database. This is the purpose of the
flyway_schema_history table that was automatically created in our database. The table is described in the documentation with these words:
You can think of this table as a complete audit trail of all changes performed against the schema. It also tracks migration checksums and whether or not the migrations were successful.https://flywaydb.org/documentation/migrations#schema-history-table
In my project the table contains the following record:
Verify the status
Migrations that were detected by Flyway’s filesystem are initially
pending. My first migration is marked as
success in the history table shown above. That means it was applied to my database without issues. In case of a failure there are two possible outcomes:
(1) When the migration fails and the database supports DDL transactions, the migration is rolled back and nothing is recorded in the schema history table.https://flywaydb.org/documentation/migrations#migration-states
(2) When the migration fails and the database doesn’t support DDL transactions, the migration is marked as failed in the schema history table, indicating manual database cleanup may be required.
The other available states are:
outdated. Check out the Migration states documentation for more details.
The work done in this project is contained in the commit 7054d3618811346871ba97ad153f7f185d695319.
Now every time my database structure requires modification I can write updates in SQL and save them as a new migration. I always now on which version of the database I’m currently working and which scripts haven’t been applied to my schema. When new developers join my team the up to date version of the database is automatically created on their machines when they start the app.
You can read more about reasoning behind migrations in the Flyway docs.