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;
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;
- 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.ymlA 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.ymlThe 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 (tiagoharris@gmail.com)
*
*/
@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 (tiagoharris@gmail.com)
*
*/
@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 (tiagoharris@gmail.com)
*
*/
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 (tiagoharris@gmail.com)
*
*/
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>.ymlThis 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:runTaking 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 lockLet'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 (tiagoharris@gmail.com)
*
*/
@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 droppedLet'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 (tiagoharris@gmail.com)
*
*/
@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.