spring boot flyway example

1. Overview

In this article, we are going to cover Spring boot flyway example. flyway is a very easy and popular database migration tool. While rapid application development database schema may change based our requirements like adding new tables, modification in an existing table, modifying some data and so many other things that we need to update in the database while upgrading application. We should not like to update database changes manually in production or staging server. So here we will see how to configure flyway with spring boot. In this example our database is empty (no any table available in the database while configuring flyway first time).

Here are some rules to configure flyway tools:

a. Flyway migration script location

Location of .sql files are under the resources/db/migrationdirectory. We can change migration script location using Configuration  flyway.locations in application.properties :

flyway.locations=classpath:db/myfolder # locations of migrations scripts

The migrations are scripts file name like V<VERSION>__<NAME>.sql (with <VERSION> an underscore-separated version, such as ‘1’ or ‘2_1’)

b. Can’t change .sql file once migration performed

Once the migration has been run on the server, .sql file’s content cannot be changed.

c. Flyway history table

In the database, flyway will automatically create a table with name flyway_schema_history where all script’s history will be stored. This table is flyway internal use only.

Spring boot flyway history table

d. Configuration properties of flyway migration

Here is useful properties of flyway migration based on our custom requirements we can change those.

2. Example

Here is complete and well test Spring boot flyway example:

Spring boot flyway example

2.1 pom.xml

flyway-core dependency for flyway migration.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>spring-boot-example</groupId>
    <artifactId>Spring-boot-flyway-database-migration-example</artifactId>
    <version>1.0-SNAPSHOT</version>
    <description>Spring boot flyway example</description>
    <!-- Inherit defaults from Spring Boot -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.8.RELEASE</version>
    </parent>
    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>   <!--It contains database base related classes-->
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
            <version>5.0.6</version>
        </dependency>
    </dependencies>
    <!-- Package as an executable jar -->
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

2.2 application.properties

spring.datasource.* is a database configuration properties. flyway.url, flyway.user, flyway.password are optional. If we have a separate user for database migration then we can specify using those properties. flyway automatically find the default data source when we will not specify flyway.url, flyway.user, flyway.password.

Using flyway.enabled property we can enable or disable flyway migration.

spring.datasource.url=jdbc:mysql://localhost/demo_database_1
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
debug=true
#flyway.baseline-on-migrate=true #used if database has some already table
flyway.enabled=true 
flyway.url=jdbc:mysql://localhost/demo_database_1
flyway.user=root
flyway.password=

2.3 V1__Create_Employee_Table.sql

db/migration/V1__Create_Employee_Table.sql

CREATE TABLE `employee` (
  `employeeId` int(11) NOT NULL AUTO_INCREMENT,
  `employeeName` varchar(255) DEFAULT NULL,
  `employeeRole` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`employeeId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

2.4 V2__Employee_Insert.sql

db/migration/V2__Employee_Insert.sql

INSERT INTO employee
(employeeId, employeeName, employeeRole)
VALUES (1, 'Zone', 'ADMN');

2.5 SpringBootConfig

package com.javadeveloperzone;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;
/**
 * Created by JavaDeveloperZone on 19-07-2017.
 */
@SpringBootApplication
@ComponentScan
// Using a root package also allows the @ComponentScan annotation to be used without needing to specify a basePackage attribute
public class SpringBootConfig {
    public static void main(String[] args) throws Exception {
        SpringApplication.run(SpringBootConfig.class, args);            // it wil start application
    }
}

2.6 Demo:

Build and Run application

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
----
---- 
Spring Boot ::        (v1.5.8.RELEASE)
2018-01-20 11:29:37.527  INFO 123168 --- [           main] o.f.core.internal.util.VersionPrinter    : Flyway Community Edition 5.0.6 by Boxfuse
2018-01-20 11:29:37.777  INFO 123168 --- [           main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:mysql://localhost/demo_database_1 (MySQL 5.5)
2018-01-20 11:29:37.824  INFO 123168 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 2 migrations (execution time 00:00.017s)
2018-01-20 11:29:37.925  INFO 123168 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table: `demo_database_1`.`flyway_schema_history`
2018-01-20 11:29:38.730  INFO 123168 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema `demo_database_1`: << Empty Schema >>
2018-01-20 11:29:38.730  INFO 123168 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema `demo_database_1` to version 1 - Create Employee Table
2018-01-20 11:29:39.037  INFO 123168 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema `demo_database_1` to version 2 - Employee Insert
2018-01-20 11:29:39.131  INFO 123168 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 2 migrations to schema `demo_database_1` (execution time 00:01.199s)
2018-01-20 11:29:39.451  INFO 123168 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup

Output

1. The first time, It will create flyway_schema_history table in the database, flyway_schema_history used by flyway internally manage script migration.
2. It will run migration script available in db/migration directory: V1__Create_Employee_Table.sql, V2__Employee_Insert.sql

Spring boot flyway example – output

3. Conclusion

In this article, we learned about how to configure flyway migration tool with spring boot and perform automatically database migration in spring boot application.

4. References

Spring boot properties

Spring boot document for flyway