Flyway with Spring Boot - database migration tool

When we are working with multiple developers, it could be difficult to handle our database schema when the application grows. Keeping track about all these changes and merging those new versions could become very tedious. Flyway is a database migration tool that allows us to have a version control of our database. We could define increasingly new versions of each change so that our co-workers can easily integrate these updates.

Project from scratch

Let's see how can we configure flyway in a new spring boot project. I'm using the option 'Spring initializr' from IntelliJ.

We just add the basic dependencies for this example, flyway and the mysql driver in my case.

If your project is already created, just add the dependency in the pom.xml, you can find it here maven repository

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

before proceeding, I'm going to run a docker-compose with a mysql container.

version: "3"
services:
  db:
    image: mysql:latest
    container_name: mysql-db-flyway
    privileged: true
    ports:
      - 3308:3306
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: "true"
      MYSQL_DATABASE: "demo"

Once we have our container running on port 3308, let's add on the application.properties the following configuration.

spring.datasource.url=jdbc:mysql://localhost:3308/demo
spring.datasource.username=root
spring.datasource.password=

spring.flyway.locations=classpath:db-migration

By default, flyway will try to look for migration files in the following path src/main/resources/db/migration (we can check this in its documentation or by surfing the library code).

To change the location path, we can use spring.flyway.locations

Flyway uses a prefix to identify in which order the scripts will be executed. The scripts should be V1__some_description.sql, V2..., V3 and so on. Let's create now our version 1 sql file. We are going to create a table 'student' with 3 columns, id, name and username.

When we run the first migration, we can see in the console the following message.

Let's enumerate the steps here:

1. As this is the first time we run the migration, flyway will create the flyway_schema_history. This is a table that register the new records for the sql scripts that have been executed. So every time we run a new script, this table will be updated.
2. Flyway is telling us that our current version of schema is empty, that's right! because we have just created the database, so we did not have any data in there (this could be different if we were integrating flyway in a project with an already created database, we would see the example later).
3. The migration script version 1 was successfully applied and we can check now in the history table how this new execution is registered

Let's run a second migration file called V2__professor_table.sql. We can see in the following images how our history table is updated with the new script.

If we restart our application, we will see that no migration is executed as we don't have new scripts to migrate.

Existing project

Let's move on to the next example. Imagine we would like to integrate flyway in a project that already has some tables created. Dropping all the tables, recreate them by flyway and populating the data is not a possibility. To resolve this, we have to add the spring.flyway.baseline-on-migrate=true on the application.properties. When this property is set to true, it creates automatically V1 from our current schema in database and only higher versions will be applied. This means if we want to add a new script, we should start in V2.
As I said, version 1 is for the existing schema, if we instead of creating V2 use V1, that script will be ignored. Try it yourself and check it out.

In this example, let's imagine a project that already has tables professor and student. We add the V2__product_table.sql and run our application. Flyway will create the history table with baseline and then it will migrate the product (V2) script we created.