Spring Data : Flyway integration

Till now we queried our database with spring-data using sping boot. In real life our database changes as our application grows. We write scripts and migrate from different environments as we move on. These scripts usually are outside our code base and needs to run manually. Also , if we have a product that has different version installed to differnet clients and a particular client want to jump 2-3 version to we need to find/run each script step by step. Its doable but time consuming.We need to automate this migration.  Spring boot support integration with a couple of option out of the box for this:

  • Flyway
  • Liquibase

These are two of the popular models for migration of DB. Both work in a very similar way.

  • Both have a tracking table to record the version already applied to the database
  • Then if required they run the upgrade scripts

Here we are going to use flyway. We will be using our previous project of Employee table. We will be using mysql as before and not be creating any tables before, just the database – my_company.

pom.xml


<?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>com.mynotes</groupId>
<artifactId>spring-data-boot-jpa-flyway</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>

<name>spring-data-boot-jpa-flyway</name>
<description>Demo project for Spring Data integration with flyways </description>

<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.4.2.RELEASE</version>
</parent>

<properties>
<java.version>1.8</java.version>
</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-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>

Creating our entity class Employee.java:


package com.mynotes.entity;

import java.util.Calendar;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "employee")
public class Employee {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
public int id;

@Column(name = "first_name")
public String fname;

@Column(name = "last_name")
public String lname;

@Column(name = "dob")
public Calendar dob;

@Column(name = "salary")
public Double salary;

@Column(name = "gender")
public String gender;
public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public Calendar getDob() {
return dob;
}

public void setDob(Calendar dob) {
this.dob = dob;
}

public Double getSalary() {
return salary;
}

public void setSalary(Double salary) {
this.salary = salary;
}

public String getFname() {
return fname;
}

public void setFname(String fname) {
this.fname = fname;
}

public String getLname() {
return lname;
}

public void setLname(String lname) {
this.lname = lname;
}

public String getGender() {
return gender;
}

public void setGender(String gender) {
this.gender = gender;
}

@Override
public String toString() {
return "Employee [id=" + id + ", fname=" + fname + ", lname=" + lname + ", dob=" + dob + ", salary=" + salary
+ ", gender=" + gender + "]";
}
}

Creating EmployeeRepository.java for crud operations:


package com.mynotes.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.mynotes.entity.Employee;

@Repository
public interface EmployeeRepository extends CrudRepository<Employee,Integer>{

}

Creating start point for our app Application.java


package com.mynotes;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;

import com.mynotes.entity.Employee;
import com.mynotes.repository.EmployeeRepository;

@SpringBootApplication
public class Application {

public static void main(String[] args) {
ApplicationContext context = SpringApplication.run(Application.class, args);
EmployeeRepository repository = context.getBean(EmployeeRepository.class);
Iterable<Employee> empList = repository.findAll();
empList.forEach((emp) -> System.out.println(emp));
}
}

application.properties


spring.datasource.url=jdbc:mysql://localhost:3306/my_company?autoReconnect=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=admin

spring.jpa.hibernate.ddl-auto=validate
logging.level=DEBUG
spring.jpa.show-sql=true

Notice above that the dll-auto is set to validate which basically means we do not want hibernate to create tables/columns. Hibernate will only check if the java entity matches with the DB or not.

Lets create the flyway script. All flyway related script should be kept in db/migration folder and it will be picked up from this location. Project structure:

spring-data-flyway1

The name of the file should follow some convention in order to tell flyway which order should they be applied.

The name consists of:

  • prefix: Always V for versioned migrations, R for repeatable migrations
  • version: (Versioned migrations only) Underscores separate as many parts as you like
  • separator: Always __ (Two underscores)
  • description: Underscores separate the words

Following this convention I created a file V1__init.sql which contains my table creation and dummy data scripts.


CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`dob` datetime NOT NULL,
`salary` double NOT NULL,
`gender` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO `employee` (`id`,`first_name`,`last_name`,`dob`,`salary`,`gender`) VALUES (1,'John','Doe','1990-07-03 00:00:00',123123,'M');
INSERT INTO `employee` (`id`,`first_name`,`last_name`,`dob`,`salary`,`gender`) VALUES (2,'Jane','Doe','1991-02-03 00:00:00',130000,'F');
INSERT INTO `employee` (`id`,`first_name`,`last_name`,`dob`,`salary`,`gender`) VALUES (3,'Nathan','Drake','1985-02-03 00:00:00',150000,'M');

Lets run Application.java

spring-data-flyway2

Lets see DB. It created the employee table with entries. It also ceated schema_version table for flyway own tracking:

spring-data-flyway3

Lets add another script- V2__add-project-column.sql


ALTER TABLE `employee` ADD COLUMN (project varchar(200));

UPDATE `employee` SET project = 'Unassigned' WHERE project is null;

Run application.java and check the database. New column would have been added in the employee table. Also a new versin row also would have been adden in the schema_version table of flyway.

You can change flyway reated default properties directly in application.properties /yml according to your needs. (Check spring boot docs – http://docs.spring.io/spring-boot/docs/current/reference/html/common-application-properties.html#FLYWAY)

Advertisements
%d bloggers like this: