This tutorial will show you how to perform FlyWay database migrations using Spring Boot and PostgreSQL Database.
Flyway is an open-source library that lets you to automate version-based database migrations. Flyway records of all applied migrations into your RDBMs so that it can detect and execute the required migration steps to update your database to the latest version. You can trigger Flyway in several ways: either use its command line client or integrate it into your Java application. In this post, I will show you how to integrate a migration in your Spring Boot application.
Starting PostgreSQL Database
We will be using PostgreSQL for our Migration examples. The simplest way to get started, is to launch it as Docker container:
$ docker run --rm=true --name flyway_test -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres -p 5432:5432 postgres:10.5 Check that the PostgreSQL process is active:
$ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 3c22fb3e2adb postgres:10.5 "docker-entrypoint..." 21 minutes ago Up 21 minutes 0.0.0.0:5432->5432/tcp flyway_test Create the Spring Boot project
Now it’s time to create the Spring Boot project that will perform the migration. From the shell (or using the Spring Boot Initializr) execute:
$ spring init -dweb,data-jpa,postgresql,flyway spring-flyway-demo Ok, you will end with a nice project which contains all the required dependencies:
<?xml version="1.0" encoding="UTF-8"?><project>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
</project>
Configuring the Spring Boot Project
Next step will be adding the Datasource settings in the application.properties file which will be read by FlyWay to perform the migration:
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres spring.datasource.username=postgres spring.datasource.password=postgres spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect spring.jpa.hibernate.ddl-auto=create spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
Notice the last parameter spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true which is required to avoid the annoying error:
Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
Ok, now let’s add Entity classes that are part of our Database schema. In our case, just this class:
package com.example.springflywaydemo;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
@Entity
public class Account {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
String userId;
String username;
String password;
String country;
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
}
Add Migration files
Migration files are searched by FlyWay, by default, in this path src/main/resources/db/migration so let’s create it from the root of our project:
$ mkdir -p src/main/resources/db/migration Within the migration folder, we will add a subfolder for each new migration we want to add:
├── src │ ├── main │ │ ├── java │ │ │ └── com │ │ │ └── example │ │ │ └── springflywaydemo │ │ │ ├── Account.java │ │ │ └── DemoApplication.java │ │ └── resources │ │ ├── application.properties │ │ ├── db │ │ │ └── migration │ │ │ ├── release_1 │ │ │ │ └── V1_1__create_account_table.sql │ │ │ └── release_2 │ │ │ └── V1_2__insert_account_table.sql │ │ ├── static
The first SQL script (V1_1__create_account_table.sql) contains the DDL of a table:
CREATE TABLE account( user_id VARCHAR PRIMARY KEY, username VARCHAR (50) UNIQUE NOT NULL, password VARCHAR (50) NOT NULL, country VARCHAR (355) NOT NULL ); The second script (V1_2__insert_account_table.sql), executes a couple of inserts in the Table:
INSERT into account ( user_id, username, password, country) VALUES ('a1234567','frank','smith','usa'); INSERT into account ( user_id, username, password, country) VALUES ('bc123454','mark','twain','usa'); To trigger the migration it’s enough to execute the install phase, that will trigger our tests:
mvn clean install You should see in your Console logs:
o.f.c.internal.license.VersionPrinter : Flyway Community Edition 5.2.4 by Boxfuse com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. o.f.c.internal.database.DatabaseFactory : Database: jdbc:postgresql://localhost:5432/postgres (PostgreSQL 10.5) o.f.core.internal.command.DbValidate : Successfully validated 2 migrations (execution time 00:00.013s) o.f.c.i.s.JdbcTableSchemaHistory : Creating Schema History table: "public"."flyway_schema_history" o.f.core.internal.command.DbMigrate : Current version of schema "public": << Empty Schema >> o.f.core.internal.command.DbMigrate : Migrating schema "public" to version 1.1 - create account table o.f.core.internal.command.DbMigrate : Migrating schema "public" to version 1.2 - insert account table o.f.core.internal.command.DbMigrate : Successfully applied 2 migrations to schema "public" (execution time 00:00.132s)
Now, if we check into the Docker process and we log into the Database, we can see that the migration was completed successfully:
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
3c22fb3e2adb postgres:10.5 "docker-entrypoint..." 31 minutes ago Up 31 minutes 0.0.0.0:5432->5432/tcp flyway_test
$ docker exec -it 3c22fb3e2adb /bin/bash
root@3c22fb3e2adb:/# psql -U postgres
psql (10.5 (Debian 10.5-2.pgdg90+1))
Type "help" for help.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------------+-------+----------
public | account | table | postgres
public | flyway_schema_history | table | postgres
(2 rows)
postgres=# select * from account;
user_id | username | password | country
----------+----------+----------+-----------------
a1234567 | frank | smith | usa
bc123454 | mark | twain | usa
(2 rows)
So What happened?
When the migration process starts, Flyway tries to locate its schema history table. As the database is empty, Flyway won’t find it and will create it instead.
You now have a database with a single empty table called flyway_schema_history.
Important! This table will be used to track the state of the database.
Immediately afterwards Flyway will scan the filesystem or the classpath to find migration files which can be written in either Sql or Java.
The migrations are then sorted based on their version number and applied in order, as you can see from the following picture
You can find the source code for both examples here: https://github.com/fmarchioni/masterspringboot/tree/master/flyway/spring-flyway-demo
FlyWay in Java Enterprise Applications
If you are looking for a tutorial of FlyWay with an Enterprise Application you can check this tutorial: http://www.mastertheboss.com/other/flyway/getting-started-with-flyway
Found the article helpful? if so please follow us on Socials
