Safely Evolving Database with Liquibase, Spring Data, and Spring Boot

Many implementations out there explaining the process to evolve the database with Liquibase and some of them implemented with Spring framework. I couldn’t find a simple and reliable implementation that explains the whole process.

https://www.liquibase.org/

Overview

This blog post explains the process to safely evolve Database with Liquibase, Spring Boot, and Spring Data JPA. Liquibase Maven plugin gives the ability to perform liquibase operations through maven commands and Liquibase Hibernate plugin helps to generate liquibase changesets based on JPA entities. Code uploaded to Github for reference

Technologies Used

  1. Spring Boot 3.x.x
  2. Spring Data JPA 3.x.x
  3. Java 11
  4. Liquibase 4.x.x
  5. Liquibase Hibernate plugin 4.x.x
  6. Liquibase Maven plugin 4.x.x

Instructions

  1. Clone the Github repository into local machine
  2. Add required dependencies for Spring Boot, Spring Data, Liquibase, etc. based on below mentioned pom.xml file
  3. Create Model, Controller and JPA Repository interfaces as shown below

Country.java


package com.liquibasedemo.model;

import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;

import javax.persistence.*;


@Entity
@Table(name = "country")
@Data
public class Country
{
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long Id;

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

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

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


    @ManyToOne
    @JoinColumn(name = "region_id")
    @JsonIgnore
    private Region region;

    public Country()
    {
    }
}
CountryRepository.java
package com.liquibasedemo.repo;

import com.liquibasedemo.model.Country;
import org.springframework.data.jpa.repository.JpaRepository;

public interface CountryRepository extends JpaRepository<Country, Long>
{
}

CountryController.Java

package com.liquibasedemo.web;

import com.liquibasedemo.model.Country;
import com.liquibasedemo.repo.CountryRepository;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/api/v1/country")
public class CountryController
{
    private final CountryRepository countryRepository;

    public CountryController(CountryRepository countryRepository)
    {
        this.countryRepository = countryRepository;
    }

    @GetMapping(path = "/list")
    public List<Country> findAllCountries()
    {
        return countryRepository.findAll();
    }

    @GetMapping(path = "/create")
    public Country createCountry()
    {
        Country country=new Country();
        country.setCode("USD");
        country.setIsoCode("USD");
        country.setName("United States Dollar");


        return countryRepository.saveAndFlush(country);
    }
}

4. Create liquibase.properties under src/main/resources directory with the following content

####  Database properties
url=${liquibase.url}
username=${liquibase.username}
password=${liquibase.password}
driver=com.mysql.cj.jdbc.Driver

#### Reference database properties
referenceUrl=${liquibase.referenceUrl}
referenceDriver=com.mysql.cj.jdbc.Driver
referenceUsername=${liquibase.referenceUsername}
referencePassword=${liquibase.referencePassword}

5. Create four profiles in resources directory application-local.ymlapplication-dev.ymlapplication-test.yml and application.prod.yml for four different environments.

6. First we generate all of our entity changes in local database using hibernate ddl-auto flag and add the following configuration on application-local.yml file. Then we compare development database against local database and generate change log

jpa:
  hibernate:
    ddl-auto: update

7. In src/main/resources directory, create a directory structure as shown below

Directory Structure

In standard liquibase setup we have one db.changelog-master.xml file under resources/db directory and change sets are stored under resources/db/changelog directory.

But in reality we may need to generate separate change sets for each environment as we may not want all the changes in local or development to move to production(or at least that’s how I do it)

8. For this reason I created three separate db.changelog-master.xml files named db.changelog-dev.xmldb.changelog-test.xmldb.changelog-prod.xml. And respective directories are created under resources/db/changelog/

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
    <includeAll path="db/changelog/dev" />
</databaseChangeLog>

9. But if want to use same file for all environments just create one db.changelog-master.xml and nothing under resources/db/changelog directory

10. Run the project on local machine and spring boot should create tables for you on database

Local Database Changes

11. Build the project using maven

$ mvn clean package -DskipTests

12. Now let’s generate change set between local and development database using liquibase:diff command as shown below

$ mvn liquibase:diff -Pdev -Dliquibase.url="jdbc:mysql://localhost:3306/liquibasedemo-dev?serverTimezone=UTC" -Dliquibase.username="root" -Dliquibase.password="Test@2020" -Dliquibase.referenceUrl="jdbc:mysql://localhost:3306/liquibasedemo-local?serverTimezone=UTC" -Dliquibase.referenceUsername="root" -Dliquibase.referencePassword="Test@2020"

13. Above command compares development database to against local database and generates changeset if it finds any difference. Make sure to include -Pdevor -Ptest or -Pprod flag to select correct profile

14. Maven replaces liquibase.properties file place holders with the supplied parameters in step 10

15. Apply change set to development database using liquibase:update command. This will all changes sets present in resources/db/changelog/dev directory

$ mvn liquibase:update -Pdev -Dliquibase.url="dbc:mysql://localhost:3306/liquibasedemo-dev?serverTimezone=UTC" -Dliquibase.username="username" -Dliquibase.password="password"

16. Now go to the database see all the changes applied and tables created

17. Go to http://localhost:8081/api/v1/country/create to create country and http://localhost:8081/api/v1/country/list to see list of countries

18. Repeat steps 11–15 for Test and Production databases. Use following commands for the same

a. Generate changeset between Dev and Test databases

$ mvn liquibase:diff -Ptest -Dliquibase.url="jdbc:mysql://localhost:3306/liquibasedemo-test?serverTimezone=UTC" -Dliquibase.username="root" -Dliquibase.password="bcmc1234" -Dliquibase.referenceUrl="jdbc:mysql://localhost:3306/liquibasedemo-dev?serverTimezone=UTC" -Dliquibase.referenceUsername="root" -Dliquibase.referencePassword="bcmc1234"

b. Apply change sets to Test database

$ mvn liquibase:update -Ptest -Dliquibase.url="dbc:mysql://localhost:3306/liquibasedemo-test?serverTimezone=UTC" -Dliquibase.username="username" -Dliquibase.password="password"

c. Generate changeset between Test and Prod databases

$ mvn liquibase:diff -Pprod -Dliquibase.url="jdbc:mysql://localhost:3306/liquibasedemo?serverTimezone=UTC" -Dliquibase.username="root" -Dliquibase.password="bcmc1234" -Dliquibase.referenceUrl="jdbc:mysql://localhost:3306/liquibasedemo-test?serverTimezone=UTC" -Dliquibase.referenceUsername="root" -Dliquibase.referencePassword="bcmc1234"

d. Apply change sets to Prod database

$ mvn liquibase:update -Pprod -Dliquibase.url="dbc:mysql://localhost:3306/liquibasedemo?serverTimezone=UTC" -Dliquibase.username="username" -Dliquibase.password="password"

``

Code uploaded to Github for reference. Feel free to download and customize it.

Pavan Kumar Jadda
Pavan Kumar Jadda
Articles: 10

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.