3,321 reads

Integrate Java Database Versioning with Liquibase using MySQL [A Step by Step Guide]

by Tiago MeloFebruary 16th, 2020

Too Long; Didn't Read

Using a database migration tool we can safely manage how the database evolves, instead of running a bunch of non versioned loose SQL files. In some frameworks like Ruby On Rails, database versioning occurs along the development process. In this article we’ll see how to integrate Liquibase with Spring Boot to evolve the database schema of a Java application using MySQL. The most popular database tools are Flyway and Liquibas, which works for all major database vendors. You can specify your changes in XML, YAML, JSON and SQL formats.

People Mentioned

Company Mentioned

featured image - Integrate Java Database Versioning with Liquibase using MySQL [A Step by Step Guide]

Versioning database changes is as important as versioning source code. By using a database migration tool we can safely manage how the database evolves, instead of running a bunch of non versioned loose SQL files. In some frameworks like Ruby On Rails, database versioning occurs along the development. But when it comes to Java world, I don't see it happening so often.

In this article we’ll see how to integrate Liquibase with Spring Boot to evolve the database schema of a Java application using MySQL.

Meet Liquibase

Currently, the most popular database tools are Flyway and Liquibase. I've choose the latter due to these benefits:

- It's database agnostic - it works for all major database vendors;

- You can specify your changes in XML, YAML , JSON and SQL formats.

We'll use YAML format.

Liquibase concepts

These are the key concepts:

- changeLog: a file that keeps track of all changes that need to run to update the DB;

- changeSet: these are atomic changes that would be applied to the database. Each changeSet is uniquely identified by 'id' and 'author'. Each changeset is run as a single transaction.

The Domain Model

This is our initial domain model that will be evolved during this article:

The 'Library' table has a One To Many relationship with 'Book' table.

The Rails way

When I had my first experience with Ruby On Rails, back in 2007, the first feature that caught my attention was Active Record Migrations. Active Record is the ORM framework shipped with Ruby On Rails.

So, for the given domain model above, to generate a migration file that create the two tables:

A migration file is created. Then, we add instructions:

Alright. Let's fire up the server:

Then, using cURL, if we try to access 'Book' resource, for example:

We'll get an error, as we can see at server's log:

Self-explanatory: we need to run the migration that will create the tables. Like this:

Let's check in MySQL. Both tables were created:

OK. suppose that we need to add two fields to 'Books' table: 'isbn' and 'publisher'. To accomplish this, we create another migration file like this:

Then we open the migration file and add the instructions:

Let's run this migration:

If we check the table again, both fields were added:

What if 'publisher' field is not necessary anymore? Let's create a migration to fix this:

This is the migration file:

Let's run this migration:

Now if we check the table, 'publisher' field was removed:

The Java way

How can we do the same in a Java application?

Liquibase can be seamless integrated with Spring Boot, so let's begin.

Creating the project

Spring Initializr is our start point:

We've choose the following dependencies:

- MySQL: to add 'mysql-connector-java' jar to our project;

- JPA: Starter for using Spring Data JPA with Hibernate;

- Liquibase: Starter for using Liquibase;

- Rest Repositories: Starter for exposing Spring Data repositories over REST using Spring Data REST.

Additionally, I'm using Liquibase Maven plugin to ease calling Liquibase from command line as we'll see in the following examples.

This is the dependency:

<dependency>
	<groupId>org.liquibase</groupId>
	<artifactId>liquibase-maven-plugin</artifactId>
	<version>3.6.3</version>
</dependency>

And this is its configuration:

<build>
	<plugins>
		<plugin>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-maven-plugin</artifactId>
		</plugin>
		<plugin>
			<groupId>org.liquibase</groupId>
			<artifactId>liquibase-maven-plugin</artifactId>
			<version>3.6.3</version>
			<configuration>
				<propertyFile>src/main/resources/liquibase.yml</propertyFile>
			</configuration>
		</plugin>
	</plugins>
</build>

As stated in a previous post, I rather not to expose entities or repositories directly, but just for the sake of demonstration, I'll use Rest Repositories this time. It makes it easy to build hypermedia-driven REST web services on top of Spring Data repositories.

Configuration

This is our 'src/main/resources/application.yml' file:

spring:
   datasource:
      url: jdbc:mysql://localhost:3306/liquibase_test?useSSL=false
      username: root
      password:
      
   jpa:
      hibernate:
         dialect: org.hibernate.dialect.MySQL5InnoDBDialect
         ddl-auto: none
   
   liquibase:
      change-log: classpath:db/liquibase-changelog.yml

A few notes:

- by setting 'spring.jpa.hibernate.ddl-auto' to 'none', the schema generation will be delegated to Liquibase;

- by default, Liquibase's changeLog file is expected to be in 'db/changelog/db.changelog-master.yaml'; but we are changing it by setting 'spring.liquibase.change-log' to put it in 'src/main/resources/db/liquibase-changelog.yml'.

And this is our 'src/main/resources/liquibase.yml' file, which is used by Liquibase Maven plugin:

url: jdbc:mysql://localhost:3306/liquibase_test?useSSL=false
username: root
password:
driver: com.mysql.cj.jdbc.Driver
outputChangeLogFile: src/main/resources/db/liquibase-OutputChangelog.yml

The entities

This is our 'Book' entity:

package com.tiago.entity;


import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;


/**
 * Entity for table "Book" 
 * 
 * @author Tiago Melo ([email protected])
 *
 */
@Entity
public class Book {


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


  @Column(nullable=false)
  private String title;
  
  @ManyToOne
  @JoinColumn(name="library_id")
  private Library library;


  public Long getId() {
    return id;
  }


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


  public String getTitle() {
    return title;
  }


  public void setTitle(String title) {
    this.title = title;
  }


  public Library getLibrary() {
    return library;
  }


  public void setLibrary(Library library) {
    this.library = library;
  }
}

And this is our 'Library' entity:

package com.tiago.entity;


import java.util.List;


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


/**
 * Entity for table "Library" 
 * 
 * @author Tiago Melo ([email protected])
 *
 */
@Entity
public class Library {


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


  @Column(nullable=false)
  private String name;
  
  @OneToMany(mappedBy = "library")
  private List<Book> books;


  public Long getId() {
    return id;
  }


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


  public String getName() {
    return name;
  }


  public void setName(String name) {
    this.name = name;
  }


  public List<Book> getBooks() {
    return books;
  }


  public void setBooks(List<Book> books) {
    this.books = books;
  }
}

The repositories

As mentioned earlier, since we are using Rest Repositories, there's no need to write controllers; the repositories will be exposed directly.

This is our 'BookRepository':

package com.tiago.repository;


import org.springframework.data.repository.CrudRepository;


import com.tiago.entity.Book;


/**
 * Repository for {@link Book} entity.
 * 
 * @author Tiago Melo ([email protected])
 *
*/
public interface BookRepository extends CrudRepository<Book, Long> { }

And this is our 'LibraryRepository':

package com.tiago.repository;


import org.springframework.data.repository.CrudRepository;


import com.tiago.entity.Library;


/**
 * Repository for {@link Library} entity.
 * 
 * @author Tiago Melo ([email protected])
 *
*/
public interface LibraryRepository extends CrudRepository<Library, Long> { }

Organizing our changelogs (migration files)

Let's take a look at our directory structure:

This is 'src/main/resources/db/liquibase-changelog.yml' file:

databaseChangeLog:
- includeAll:
   path: db/changelog/

We are telling Liquibase to execute all changelog files in 'src/main/resources/db/changelog/' directory.

Changelog #1: creating the tables

As a general rule, let's adopt the following naming convention:

<migration_number>_<what_does_this_migration_do>.yml

This way, Liquibase will execute changelogs ordered by its number.

This is our '1_create_book_and_library_tables.yml' file. As the name implies, it creates the tables:

databaseChangeLog:
- changeSet:
   author: "tiago"
   id: "creates_library_table"
   changes:
      - createTable:
         tableName: "library"
         columns:
            - column:
               name: "id"
               type: "BIGINT"
               autoIncrement: "true"
               constraints:
                  primaryKey: "true"
            - column:
               name: "name"
               type: "VARCHAR(255)"
               constraints: 
                  nullable: "false"
                  unique: "true"
 
- changeSet:
   author: "tiago"
   id: "creates_book_table"
   changes:
      - createTable:
         tableName: "book"
         columns:
            - column:
               name: "id"
               type: "BIGINT"
               autoIncrement: "true"
               constraints:
                  primaryKey: "true"
            - column:
               name: "title"
               type: "VARCHAR(255)"
               constraints: 
                  nullable: "false"
                  unique: "true"
            - column:
               name: "library_id"
               type: "BIGINT"
               constraints: 
                  foreignKeyName: "fk_book_library"
                  references: "library(id)"

Differently from Rails, when we fire up the server, the changelogs will be automatically executed. Let's see:

$ mvn spring-boot:run

Taking a look at server's log, we notice that the two tables were created:

2019-03-06 23:17:14.103  INFO 29090 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM liquibase_test.DATABASECHANGELOG
2019-03-06 23:17:14.104  INFO 29090 --- [           main] l.c.StandardChangeLogHistoryService      : Reading from liquibase_test.DATABASECHANGELOG
2019-03-06 23:17:14.105  INFO 29090 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT * FROM liquibase_test.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
2019-03-06 23:17:14.106  INFO 29090 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM liquibase_test.DATABASECHANGELOGLOCK
2019-03-06 23:17:14.123  INFO 29090 --- [           main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE liquibase_test.library (id BIGINT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, CONSTRAINT PK_LIBRARY PRIMARY KEY (id), UNIQUE (name))
2019-03-06 23:17:14.145  INFO 29090 --- [           main] liquibase.changelog.ChangeSet            : Table library created
2019-03-06 23:17:14.145  INFO 29090 --- [           main] liquibase.changelog.ChangeSet            : ChangeSet db/changelog/1_create_book_and_library_tables.yml::creates_library_table::tiago ran successfully in 23ms
2019-03-06 23:17:14.146  INFO 29090 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT MAX(ORDEREXECUTED) FROM liquibase_test.DATABASECHANGELOG
2019-03-06 23:17:14.148  INFO 29090 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO liquibase_test.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('creates_library_table', 'tiago', 'db/changelog/1_create_book_and_library_tables.yml', NOW(), 1, '8:a4b142ffda1ccd5c1840ddad83e249b5', 'createTable tableName=library', '', 'EXECUTED', NULL, NULL, '3.6.3', '1925034107')
2019-03-06 23:17:14.151  INFO 29090 --- [           main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE liquibase_test.book (id BIGINT AUTO_INCREMENT NOT NULL, title VARCHAR(255) NOT NULL, library_id BIGINT NULL, CONSTRAINT PK_BOOK PRIMARY KEY (id), CONSTRAINT fk_book_library FOREIGN KEY (library_id) REFERENCES liquibase_test.library(id), UNIQUE (title))
2019-03-06 23:17:14.173  INFO 29090 --- [           main] liquibase.changelog.ChangeSet            : Table book created
2019-03-06 23:17:14.174  INFO 29090 --- [           main] liquibase.changelog.ChangeSet            : ChangeSet db/changelog/1_create_book_and_library_tables.yml::creates_book_table::tiago ran successfully in 24ms
2019-03-06 23:17:14.175  INFO 29090 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO liquibase_test.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('creates_book_table', 'tiago', 'db/changelog/1_create_book_and_library_tables.yml', NOW(), 2, '8:a54634bf0781ac011818976bf5e36351', 'createTable tableName=book', '', 'EXECUTED', NULL, NULL, '3.6.3', '1925034107')
2019-03-06 23:17:14.186  INFO 29090 --- [           main] l.lockservice.StandardLockService        : Successfully released change log lock

Let's check them in MySQL:

Great.

Now, using cURL, let's access the 'Book' resource:

No books as expected. The same occurs with 'Library' resource:

Changelog #2 and #3: initialization data

Let's see how we can initialize our tables.

This is '2_insert_data_books.yml':

databaseChangeLog:
- changeSet:
   author: "tiago"
   id: "insert_data_books"
   changes:
      - insert:
         tableName: "book"
         columns:
            - column:
               name: "title"
               value: "Test Book 1"

And this is '3_insert_data_library.yml':

databaseChangeLog:
- changeSet:
   author: "tiago"
   id: "insert_data_library"
   changes:
      - insert:
         tableName: "library"
         columns:
            - column:
               name: "name"
               value: "Library 1"

Now let's try something different. Instead of firing up the server to make Liquibase to run these changelogs, we'll do it by using the Maven plugin:

$ mvn liquibase:update -Dliquibase.changeLogFile=db/liquibase-changelog.yml

This is the output:

[INFO] INSERT INTO book (title) VALUES ('Test Book 1')
[INFO] New row inserted into book
[INFO] INSERT INTO book (title) VALUES ('Test Book 2')
[INFO] New row inserted into book

...
[INFO] INSERT INTO library (name) VALUES ('Library 1')
[INFO] New row inserted into library
....

Then, if we fire up the server and access 'Book' resource again...

Take a look at this:

"library" : {
   "href" : "http://localhost:8080/books/1/library"
}

We'll use this URL to associate this book to 'Library 1' soon.

Calling 'Library' resource:

Now we'll associate 'Test Book 1' to 'Library 1':

$ curl -i -X PUT -H "Content-Type:text/uri-list" -d "http://localhost:8080/libraries/1" http://localhost:8080/books/1/library

Then we can check if it worked, by querying the 'library' association of our book:

Changelog #4: adding columns

During the development, we found necessary to add 'isbn' and 'publisher' fields to our 'book' table.

The first step is to create the changelog file. This is '4_add_isbn_and_publisher_to_book.yml':

databaseChangeLog:
- changeSet:
   author: "tiago"
   id: "add_isbn_and_publisher_to_book"
   changes:
   - addColumn:
      columns:
      - column:
          name: "isbn"
          type: "VARCHAR(255)"
          constraints:
            nullable: "false"
      - column:
          name: "publisher"
          type: "VARCHAR(255)"
          constraints:
            nullable: "false"
      tableName: "book"

Then, let's run the changelog:

$ mvn liquibase:update -Dliquibase.changeLogFile=db/liquibase-changelog.yml

This is the output:

[INFO] ALTER TABLE book ADD isbn VARCHAR(255) NOT NULL, ADD publisher VARCHAR(255) NOT NULL
[INFO] Columns isbn(VARCHAR(255)),publisher(VARCHAR(255)) added to book
[INFO] ChangeSet db/changelog/4_add_isbn_and_publisher_to_book.yml::add_isbn::tiago ran successfully in 60ms

Let's check it in MySQL:

Great. The second step is to change our 'Book' entity to add these two new fields:

package com.tiago.entity;


import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;


/**
 * Entity for table "Book" 
 * 
 * @author Tiago Melo ([email protected])
 *
 */
@Entity
public class Book {


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


  @Column(nullable=false)
  private String title;
  
  @ManyToOne
  @JoinColumn(name="library_id")
  private Library library;
  
  @Column(nullable=false)
  private String isbn;
  
  @Column(nullable=false)
  private String publisher;


  public Long getId() {
    return id;
  }


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


  public String getTitle() {
    return title;
  }


  public void setTitle(String title) {
    this.title = title;
  }


  public Library getLibrary() {
    return library;
  }


  public void setLibrary(Library library) {
    this.library = library;
  }


  public String getIsbn() {
    return isbn;
  }


  public void setIsbn(String isbn) {
    this.isbn = isbn;
  }


  public String getPublisher() {
    return publisher;
  }


  public void setPublisher(String publisher) {
    this.publisher = publisher;
  }
}

Now let's update our book to set 'isbn' and 'publisher':

Changelog #5: dropping a column

The 'publisher' field is not necessary anymore.

The first step is to create the changelog file. This is '5_drop_publisher_from_book.yml':

databaseChangeLog:
- changeSet:
   author: "tiago"
   id: "drop_publisher_from_book"
   changes:
   - dropColumn:
      columnName: "publisher"
      tableName: "book"

Then, let's run the changelog:

$ mvn liquibase:update -Dliquibase.changeLogFile=db/liquibase-changelog.yml

This is the output:

[INFO] ALTER TABLE book DROP COLUMN publisher
[INFO] Column book.publisher dropped

Let's check it in MySQL:

OK. The second step is to change our 'Book' entity to remove 'publisher' property:

package com.tiago.entity;


import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;


/**
 * Entity for table "Book" 
 * 
 * @author Tiago Melo ([email protected])
 *
 */
@Entity
public class Book {


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


  @Column(nullable=false)
  private String title;
  
  @ManyToOne
  @JoinColumn(name="library_id")
  private Library library;
  
  @Column(nullable=false)
  private String isbn;
  
  public Long getId() {
    return id;
  }


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


  public String getTitle() {
    return title;
  }


  public void setTitle(String title) {
    this.title = title;
  }


  public Library getLibrary() {
    return library;
  }


  public void setLibrary(Library library) {
    this.library = library;
  }


  public String getIsbn() {
    return isbn;
  }


  public void setIsbn(String isbn) {
    this.isbn = isbn;
  }
}

Now let's fire up the server and check our 'Book' resource:

Great! The 'publisher' property does not exists anymore.

Conclusion

Versioning database changes is as important as versioning source code, and tools like Liquibase makes it possible to do it in a safe and manageable way.

Through this simple example we learnt how we can evolve database in a Java application by integrating Liquibase with Spring Boot.