Flyway with Spring Boot - database migration tool
Tags: database, java
Published: 2020-04-02 23:15:06

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.  

<img src="https://dev-to-uploads.s3.amazonaws.com/i/t1n4cnmz4j3zi4ydhojy.png" alt="drawing"/>

<img src="https://dev-to-uploads.s3.amazonaws.com/i/svabf7kzxppjfv3urfpw.png" alt="drawing"/>

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

<img src="https://dev-to-uploads.s3.amazonaws.com/i/zs76m1sc49obiweeoxhe.png" alt="drawing"/>

<img src="https://dev-to-uploads.s3.amazonaws.com/i/y4pu39sfykbvrz86k6r6.png" alt="drawing"/>


If your project is already created, just add the dependency in the pom.xml, you can find it here <a href="https://mvnrepository.com/" target="_blank">maven repository</a>

```java
<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.
```docker
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.
```properties
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 <a href="https://flywaydb.org/documentation/" target="_blank">documentation</a> or by surfing the library code). 

<img src="https://dev-to-uploads.s3.amazonaws.com/i/jvsdhmhs902vl3zp039i.png" alt="drawing"/>

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.

<img src="https://dev-to-uploads.s3.amazonaws.com/i/il44quekm1gg57ok4b2w.png" alt="drawing" width="400"/>
<img src="https://dev-to-uploads.s3.amazonaws.com/i/awcd5mmcrn69djjbgupg.png" alt="drawing" width="400"/>

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

<img src="https://dev-to-uploads.s3.amazonaws.com/i/bb1huzdpeogsjl5wk0g6.png" alt="drawing" />

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

<img src="https://dev-to-uploads.s3.amazonaws.com/i/35pfx4subx7myvh0tmm8.png" alt="drawing"/>

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.

<img src="https://dev-to-uploads.s3.amazonaws.com/i/vf78juqk32qcuynvnd3r.png" alt="drawing"/>
<img src="https://dev-to-uploads.s3.amazonaws.com/i/4i6f9h10atzbqzn2ibbs.png" alt="drawing"/>

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

<img src="https://dev-to-uploads.s3.amazonaws.com/i/mx6nc9199mfe4rzr5i7t.png" alt="drawing"/><br/>

### **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.

<img src="https://dev-to-uploads.s3.amazonaws.com/i/ix1qsw0bvnmu2v4g4y3d.png" alt="drawing"/>
<img src="https://dev-to-uploads.s3.amazonaws.com/i/7i6mdvy5iyrl5pjl84sx.png" alt="drawing"/>