Database Migration Using Flyway and Spring Boot

Database Migration Using Flyway and Spring Boot

In this post we will go through the process of database migration using Flyway Plugin for Spring Boot application.

Use Case

We are building an application which needs to be deployed on multiple environments, like: DEV, QA, PROD. We have version control and continuous integration pipeline for our code to be deployed automatically setup, but now we want to go one step further and instead manually updating database every-time data model changes we want to do it automatically.

Stack

  • Spring Boot 2
  • Hibernate
  • Flyway
  • Docker
  • MariaDB

Database Setup

If you have database setup already feel free to skip this step. Otherwise install Docker, docker-compose and let’s pull some images.

First of all lets open up new terminal window and create directory named mariadb:

mkdir mariadb
cd mariadb
vi docker-compose.yml

Content of docker-compose.yml:

version: '3.1'

services:

  db:
    image: mariadb
    restart: always
    ports:
      - "3306:3306"
    container_name: test-mariadb
    environment:
      - MYSQL_ROOT_PASSWORD=test
      - MYSQL_USER=test
      - MYSQL_PASSWORD=test
      - MYSQL_DATABASE=test

To start our mariadb container run:

docker-compose up -d

To validate that our database was created properly run:

codespace:mariadb lab$ docker ps

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
47eb14ef22aa        mariadb             "docker-entrypoint.s…"   8 seconds ago       Up 5 seconds        0.0.0.0:3306->3306/tcp   test-mariadb
codespace:mariadb lab$ docker exec -it test-mariadb bash
root@47eb14ef22aa:/#
root@47eb14ef22aa:/# mysql -u root -p

Enter password: test

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.14-MariaDB-1:10.3.14+maria~bionic mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
MariaDB [(none)]> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> use test;

Database changed
MariaDB [test]> show tables;

Empty set (0.000 sec)

MariaDB [test]>

No tables are created yet, but from what terminal says it looks like our DB has been created successfully.

Spring Boot App

Now we are going to create simple coffee shop Spring Boot application which will perform CRUD operations on our database.

Let’s generate initial application using Spring Initializr.

Download the zip file, extract it and open the project in your favourite editor, for example IntelliJ.

Project structure:

DemoApplication class, starting point of our app:

package com.codespacelab.demo;

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

@SpringBootApplication
public class DemoApplication {

	public static void main(String[] args) {
		SpringApplication.run(DemoApplication.class, args);
	}

}

gradle.build file with required dependancies:

plugins {
	id 'org.springframework.boot' version '2.2.0.RELEASE'
	id 'io.spring.dependency-management' version '1.0.8.RELEASE'
	id 'java'
}

group = 'com.codespacelab'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '1.8'

configurations {
	compileOnly {
		extendsFrom annotationProcessor
	}
}

repositories {
	mavenCentral()
}

dependencies {
	compile("org.springframework.boot:spring-boot-starter-web")
	compile("org.springframework.boot:spring-boot-starter-data-jpa")
	compileOnly("org.projectlombok:lombok:1.18.6")
	annotationProcessor("org.projectlombok:lombok:1.18.6")
	compile("org.flywaydb:flyway-core:6.0.8")
	compile("net.sf.dozer:dozer:5.4.0") {
		exclude group: "org.slf4j", module: "slf4j-log4j12"
	}
	runtime("org.mariadb.jdbc:mariadb-java-client:2.1.0") {
		exclude group: "net.java.dev.jna"
	}
}

test {
	useJUnitPlatform()
}

It’s just a basic gradle.build file, with list of dependancies which we will use in this project.

CoffeeController class:

package com.codespacelab.demo.controller;

import com.codespacelab.demo.model.CoffeeDto;
import com.codespacelab.demo.service.CoffeeService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@Slf4j
@RestController
@RequestMapping("/v1/coffee")
public class CoffeeController {

    private CoffeeService coffeeService;

    public CoffeeController(final CoffeeService coffeeService) {
        this.coffeeService = coffeeService;
    }

    @GetMapping("/all")
    public List<CoffeeDto> getCoffees() {
        return coffeeService.getCoffees();
    }

    @GetMapping
    public CoffeeDto getCoffee(
            @RequestParam(name = "name") String name
    ) {
        return coffeeService.getCoffee(name);
    }

    @PostMapping
    public CoffeeDto addCoffee(
            @Validated @RequestBody CoffeeDto coffeeDto
    ) {
        return coffeeService.addCoffee(coffeeDto);
    }

    @PutMapping
    public CoffeeDto updateCoffee(
            @Validated @RequestBody CoffeeDto coffeeDto
    ) {
        return coffeeService.updateCoffee(coffeeDto);
    }

    @DeleteMapping
    public boolean deleteCoffee(
            @RequestParam(name = "name") String name
    ) {
        return coffeeService.deleteCoffee(name);
    }
}

This generic REST controller class has full RESTful structure and is listening on 5 endpoints:

  • GET /v1/coffee/all – to fetch all coffees from database.
  • GET /v1/coffee?name= – to fetch specific coffee by name.
  • POST /v1/coffee – to add new coffee object.
  • PUT /v1/coffee – to edit coffee object.
  • DELETE /v1/coffee?name= – to remove coffee object from database.

CoffeeDto is an object that carries Coffee object data between processes, post from API and recording in DB:

package com.codespacelab.demo.model;
import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@AllArgsConstructor
@Builder
@Data
@NoArgsConstructor
@JsonInclude(JsonInclude.Include.NON_NULL)
public class CoffeeDto {

    private Long id;
    private String name;
    private String size;
    private double price;
}

Coffee entity class:

package com.codespacelab.demo.model;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@AllArgsConstructor
@Builder
@NoArgsConstructor
@Data
@Entity
@Table(name = "coffee")
public class Coffee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "cup_size")
    private String size;

    @Column(name = "price")
    private double price;
}

Coffee entity just maps our data object to coffee table in a database, generates an ID and stores name, size and price values. For simplicity of model structures we used Lombok annotations to create constructors and builders for coffee data object. More details on Lombok here.

CoffeeMapper class is used for quick object conversion from DTO, with little help from Dozer mapper:

package com.codespacelab.demo.model;

import org.springframework.stereotype.Component;
import org.dozer.DozerBeanMapper;

@Component
public class CoffeeMapper {

    final static DozerBeanMapper mapper = new DozerBeanMapper();

    public Coffee dtoToDomain(CoffeeDto coffeeDto) {
        if (coffeeDto == null) {
            return null;
        }

        return mapper.map(coffeeDto, Coffee.class);
    }

    public CoffeeDto domainToDto(Coffee coffee) {
        return mapper.map(coffee, CoffeeDto.class);
    }
}

CoffeeService is the class where real action happens. Each endpoint in CoffeeController has method in CoffeeService service to do some data processing, as well Service class pulls data from database and passes it to CoffeeMapper to build an object:

package com.codespacelab.demo.service;

import com.codespacelab.demo.model.Coffee;
import com.codespacelab.demo.model.CoffeeDto;
import com.codespacelab.demo.model.CoffeeMapper;
import com.codespacelab.demo.repository.CoffeeRepository;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.server.ResponseStatusException;

import java.util.List;
import java.util.Optional;

import static java.util.stream.Collectors.toList;

@Slf4j
@Service
public class CoffeeService {

    private CoffeeRepository coffeeRepository;
    private CoffeeMapper coffeeMapper;

    public CoffeeService(final CoffeeRepository coffeeRepository,
                             final CoffeeMapper coffeeMapper) {
        this.coffeeRepository = coffeeRepository;
        this.coffeeMapper = coffeeMapper;
    }

    public List<CoffeeDto> getCoffees() {
        return coffeeRepository
                .findAll()
                .stream()
                .map(coffeeList -> coffeeMapper.domainToDto(coffeeList)).collect(toList());
    }

    public CoffeeDto getCoffee(String name) {
        Optional<Coffee> coffeeListOptional = coffeeRepository.findByName(name);

        if(!coffeeListOptional.isPresent()) {
            throw new ResponseStatusException(HttpStatus.BAD_REQUEST, "Bad Request");
        }

        log.debug("Retrieving coffee object: " + name);

        return coffeeMapper.domainToDto(coffeeListOptional.get());
    }

    @Transactional
    public CoffeeDto addCoffee(CoffeeDto coffeeDto) {
        Optional<Coffee> coffeeListOptional = coffeeRepository.findByName(coffeeDto.getName());

        if(coffeeListOptional.isPresent()) {
            throw new ResponseStatusException(HttpStatus.BAD_REQUEST, "Bad Create Request");
        }

        log.debug("Creating coffee object: " + coffeeDto.getName());

        return save(coffeeDto);
    }

    @Transactional
    public CoffeeDto updateCoffee(CoffeeDto coffeeDto) {
        Optional<Coffee> coffeeOptional = coffeeRepository.findByName(coffeeDto.getName());

        if(!coffeeOptional.isPresent()) {
            throw new ResponseStatusException(HttpStatus.BAD_REQUEST, "Bad Update Request");
        }

        log.debug("Updating coffee object: " + coffeeDto.getName());

        return save(coffeeDto);
    }

    @Transactional
    public boolean deleteCoffee(String name) {
        Optional<Coffee> coffeeOptional = coffeeRepository.findByName(name);

        if(!coffeeOptional.isPresent()) {
            throw new ResponseStatusException(HttpStatus.BAD_REQUEST, "Bad Delete Request");
        }

        log.debug("Deleting coffee object: " + name);

        return coffeeRepository.deleteByName(name).intValue() == 1;
    }

    private CoffeeDto save(CoffeeDto coffeeDto) {
        Coffee coffee = coffeeRepository.saveAndFlush(coffeeMapper.dtoToDomain(coffeeDto));

        log.debug("Saving coffee object: " + coffeeDto.getName());

        return coffeeMapper.domainToDto(coffee);
    }
}
  • getCoffees – pulls all records from a database, it’s ok for this small application, but if list becomes big it’s better to use paged object.
  • getCoffee – gets record from database by name parameter passed to a method.
  • addCoffee – creates new coffee object in a database.
  • updateCoffee – finds coffee by name and updates it.
  • deleteCoffee – finds and removes object from database by name.

CoffeeRepository class does all heavy lifting related to reading and writing from database:

package com.codespacelab.demo.repository;

import com.codespacelab.demo.model.Coffee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.Optional;

@Repository
public interface CoffeeRepository extends JpaRepository<Coffee, Long> {

    Optional<Coffee> findByName(String name);

    Long deleteByName(String name);
}

Spring Boot application configuration are done in application.yml:

spring:
  datasource:
    url: jdbc:mariadb://localhost:3306/test
    username: test
    password: test
    driverClassName: org.mariadb.jdbc.Driver
    max-active: 100
    hikari:
      maximum-pool-size: 50
      minimum-idle: 10
      connection-test-query: SELECT 1
      data-source-properties:
        connectTimeout: 3000
        socketTimeout: 3000
  flyway:
    schemas: test
    baseline-on-migrate: true #NEVER CHANGE BASELINE TO TRUE
    validate-on-migrate: true #true to populate local DB
    table: schema_version
    enabled: true
  jpa:
    hibernate:
      ddl-auto: validate

server:
  port: 8765

logging:
  level:
    ROOT: INFO
    org.springframework: WARN
    org.springframework.web.filter.CommonsRequestLoggingFilter: DEBUG
    org.hibernate: INFO
  • datasource – in this part of configuration app makes a connection to MariaDB which was started as Docker container.
  • server – tells the app on which port to listen.
  • logging – set’s logging levels.
  • flyway – configures flyway plugin.
    • schema – Comma-separated list of schemas managed by Flyway.
    • baseline-on-migrate – This flag is useful for initial Flyway production deployments on projects with an existing DB. It baseline database with schema set. Note: Be careful when enabling this as it removes the safety net that ensures Flyway does not migrate the wrong database in case of a configuration mistake!
    • validate-on-migrate – Validates database with schema set.
    • table – The name of Flyway’s schema history table.
    • enabled – enables Flyway.

More details on available Flyway migrate options here.

Coffee table initialisation script resources/db/migration/V1.0.0__init.sql:

CREATE TABLE coffee (
  id                SERIAL,
  name              VARCHAR(256),
  cup_size          VARCHAR(256),
  price             DOUBLE,
  PRIMARY KEY (id)
);

On application startup Flyway will go to default directory “resources/db/migration/” and will execute all scripts in it. Script name has to start with V followed by version, two underscores and name. Scripts will be executed by version, higher the version later the script will be executed.

Testing

To test full setup make sure MariaDB Docker container is still running with docker ps command. Then start SpringBoot app.

\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.2.0.RELEASE)

2019-11-09 21:25:34.630  INFO 27194 --- [           main] com.codespacelab.demo.DemoApplication    : Starting DemoApplication on IE-C02YH04DJGH7 with PID 27194 (/Users/codespacelab/Documents/blog/coffeeShop/out/production/classes started by codespacelab in /Users/codespacelab/Documents/blog/coffeeShop)
2019-11-09 21:25:34.633  INFO 27194 --- [           main] com.codespacelab.demo.DemoApplication    : No active profile set, falling back to default profiles: default
2019-11-09 21:25:35.754  INFO 27194 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2019-11-09 21:25:35.754  INFO 27194 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.27]
2019-11-09 21:25:35.817  INFO 27194 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2019-11-09 21:25:35.918  INFO 27194 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 5.2.4 by Boxfuse
2019-11-09 21:25:35.920  INFO 27194 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2019-11-09 21:25:35.964  INFO 27194 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2019-11-09 21:25:35.970  INFO 27194 --- [           main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:mariadb://localhost:3306/test (MySQL 10.3)
2019-11-09 21:25:36.020  INFO 27194 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.018s)
2019-11-09 21:25:36.032  INFO 27194 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema `test`: 1.0.0
2019-11-09 21:25:36.033  INFO 27194 --- [           main] o.f.core.internal.command.DbMigrate      : Schema `test` is up to date. No migration necessary.
2019-11-09 21:25:36.110  INFO 27194 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2019-11-09 21:25:36.181  INFO 27194 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.4.6.Final}
2019-11-09 21:25:36.348  INFO 27194 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
2019-11-09 21:25:36.432  INFO 27194 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MariaDB103Dialect
2019-11-09 21:25:36.955  INFO 27194 --- [           main] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
2019-11-09 21:25:37.260  INFO 27194 --- [           main] org.dozer.config.GlobalSettings          : Trying to find Dozer configuration file: dozer.properties
2019-11-09 21:25:37.265  WARN 27194 --- [           main] org.dozer.config.GlobalSettings          : Dozer configuration file not found: dozer.properties.  Using defaults for all Dozer global properties.
2019-11-09 21:25:37.267  INFO 27194 --- [           main] org.dozer.DozerInitializer               : Initializing Dozer. Version: 5.4.0, Thread Name: main
2019-11-09 21:25:37.269  INFO 27194 --- [           main] org.dozer.jmx.JMXPlatformImpl            : Dozer JMX MBean [org.dozer.jmx:type=DozerStatisticsController] auto registered with the Platform MBean Server
2019-11-09 21:25:37.270  INFO 27194 --- [           main] org.dozer.jmx.JMXPlatformImpl            : Dozer JMX MBean [org.dozer.jmx:type=DozerAdminController] auto registered with the Platform MBean Server
2019-11-09 21:25:37.273  INFO 27194 --- [           main] org.dozer.DozerBeanMapper                : Initializing a new instance of dozer bean mapper.
2019-11-09 21:25:37.329  WARN 27194 --- [           main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2019-11-09 21:25:37.638  INFO 27194 --- [           main] com.codespacelab.demo.DemoApplication    : Started DemoApplication in 3.359 seconds (JVM running for 3.899)

So far everything seems good: Successfully validated 1 migration (execution time 00:00.018s), let’s make some API calls to add coffees and then connect to a Docker container and check if data is stored in database as expected.

cURLs to add Coffees: small Americano, large Latte, medium Flat White.

curl -X POST \
  http://localhost:8765/v1/coffee \
  -H 'Accept: */*' \
  -H 'Content-Type: application/json' \
  -H 'Host: localhost:8556' \
  -H 'cache-control: no-cache' \
  -d '{
    "name": "Americano",
    "size": "small",
    "price": 2.00
}'

curl -X POST \
  http://localhost:8765/v1/coffee \
  -H 'Accept: */*' \
  -H 'Content-Type: application/json' \
  -H 'Host: localhost:8556' \
  -H 'cache-control: no-cache' \
  -d '{
    "name": "Latte",
    "size": "large",
    "price": 2.45
}'

curl -X POST \
  http://localhost:8765/v1/coffee \
  -H 'Accept: */*' \
  -H 'Content-Type: application/json' \
  -H 'Host: localhost:8556' \
  -H 'cache-control: no-cache' \
  -d '{
    "name": "Flat White",
    "size": "medium",
    "price": 2.45
}'

Now we can connect to MariaDB and check what was stored in there:

docker exec -it test-mariadb bash
root@69b3e2284472:/# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 39
Server version: 10.3.14-MariaDB-1:10.3.14+maria~bionic mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| coffee         |
| schema_version |
+----------------+
2 rows in set (0.000 sec)

MariaDB [test]> select * from coffee;
+----+------------+----------+-------+
| id | name       | cup_size | price |
+----+------------+----------+-------+
|  1 | Americano  | small    |     2 |
|  2 | Latte      | large    |  3.99 |
|  3 | Flat White | medium   |  2.45 |
+----+------------+----------+-------+
3 rows in set (0.000 sec)

MariaDB [test]>

Perfect our app is working as expected and if we would need to deploy it on DEV all it needs is connection to DB to migrate coffee table schema. Feel free to play around with other APIs to delete and update records in DB, add extra scripts to be executed on app startup. Source code.

But maybe you are curious how to add default data after database is migrated or how to use Flyway callback in Spring Boot? If that’s the case take a peek into post: Database Population Using Flyway and Spring Boot

Add Comment

Your email address will not be published. Required fields are marked *