Spring Boot Flyway DB Version Control Integration

Hey there,

Today, let me talk about integrating our spring boot microservice with Flyway.

Let’s check what flyway is.

Flyway is an open-source database-migration tool, that’s it .

Q: Why database migration is needed?

A: Well, you’ll have a very much explained answer to this here https://flywaydb.org/getstarted/why.

Let’s start integrating.

I’m going to use postgresql here.

First, Go and create a DB in your postgresql server

create database flyway_test;

Now let’s create a spring boot project.

Go to https://start.spring.io/ and give your specific properties and dependencies, and generate your project.

Add the database driver dependencies and configure

Here, I’m adding postgresql dependencies. You may use your preferred.

<dependency>
   <groupId>org.postgresql</groupId>
   <artifactId>postgresql</artifactId>
   <scope>runtime</scope>
</dependency>

And then the jpa dependency

<dependency>
   <groupId>org.postgresql</groupId>
   <artifactId>postgresql</artifactId>
   <scope>runtime</scope>
</dependency>

And the JPA configurations to the application.properties or application.yml

spring.jpa.database=POSTGRESQL
spring.datasource.platform=postgres
spring.datasource.url=jdbc:postgresql://localhost:5432/flyway_test
spring.datasource.username=sajeer
spring.datasource.password=sajeer123
spring.jpa.show-sql=true
spring.jpa.generate-ddl=false
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

Make sure that spring.jpa.generate-ddl=false and spring.jpa.hibernate.ddl-auto=none, otherwise the tables will be automatically generated by JPA. And that won’t be good in many cases.

Add flyway dependencies and configurations

Add the versions to the properties in pom.xml

<flyway-core.version>5.2.3</flyway-core.version>
<flyway-maven-plugin.version>5.0.2</flyway-maven-plugin.version>

Add the dependency

<dependency>
   <groupId>org.flywaydb</groupId>
   <artifactId>flyway-core</artifactId>
   <version>${flyway-core.version}</version>
</dependency>

And then add the flyway plugin

<plugin>
   <groupId>org.flywaydb</groupId>
   <artifactId>flyway-maven-plugin</artifactId>
   <version>${flyway-maven-plugin.version}</version>
</plugin>

My final pom.xml will look like this

pom.xml

Let’s add the configurations for flyway to our application.properties

spring.flyway.baselineOnMigrate=true
spring.flyway.user=sajeer
spring.flyway.password=sajeer123
spring.flyway.schemas=public
spring.flyway.url=jdbc:postgresql://localhost:5432/flyway_test
spring.flyway.locations=classpath:sql-scripts

baselineOnMigrate: Whether to automatically call baseline when migrate is executed against a non-empty schema with no metadata table.

user: postgresql username, must be the owner of your specifying DB

password: Password for the given user

schemas: It can be a schema name or a list of schemas comma separated. If you have a multi tenancy architecture, we need to do flywa migrations in each schema based on the list of schemas fetched from the current schema. I’ll explain it in my new story.

url: URL to your database

locations: This will be the location, that the flyway will look into for the sql scripts to excecute.
If it is a classpath location, it will look into the classpath for the folder sql-scripts which is by default, src/main/resources
If it is a filesystem path, it will look something like this flyway.locations=filesystem:sql-scripts, In this case the folder sql-scripts should be in the same directory as the pom.xml is.

Create sql-scripts Directory and Add sql scripts

Let’s create our script directory inside the classpath as src/main/resources/sql-scripts

And add the first set of scripts

filename: <High-level-version>_<Sub_version>__<Description>.sql

for example: V1_1_0__Create_User_Table.sql

And add the script to create users table, into the sql file

CREATE TABLE IF NOT EXISTS users (
    id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
)

That’s it, you’re done.

Let’s run your microservice

$ mvn clean install -Dmaven.test.skip=true
$ java -jar target/flyway-integration-0.0.1-SNAPSHOT.jar

Once the build is success and the service started, let’s go to your DB and check whether the table is created.

If everything went good, you will see two tables like this

The table flyway_schema_history will have all the versioned details like this

You can add as many as sql files to the sql-scripts directory to create, modify, insert and update operations.

Keep in mind that you can’t edit an already run sql file, it will not be accepted by flyway.

You can get the complete source code from

sajeerzeji/SpringBoot-Flyway-Integration

Buy me a coffee if you like it.

You like it? Then Buy Me A Coffee. I luvv coffee.

Keep in touch.

Sajeer Zeji

sajeerzeji — Overview

sajeer babu — Kerala, India | Professional Profile | LinkedIn

Get me on Facebook

Get me on Instagram

Stack Exchange