Initializing Our Database With Liquibase

After we have finished this lesson, we

• Know what Liquibase is.
• Understand why we should initialize our database by using a database migration tool.
• Know how we can get the required dependencies with Maven and Gradle.
• Can initialize our database by using Liquibase when our web application is started.

What Is Liquibase?

Liquibase is a database migration tool that allows us to put our database migrations to our version control system and run them in a “stardard” way. Its key features are:

• It supports all common databases.
• It supports change log files that use the XML, YAML, JSON, and SQL format.
• It has a Maven plugin that allows us to control Liquibase and run our database migrations on demand.
• It integrates with Spring Framework and Spring Boot.

Let’s move on and find out why we should initialize our database with Liquibase.

Why Should We Use Liquibase?

There is another database migration tool called Flyway that provides same features as Liquibase. However, this course uses Liquibase because I have a lot of experience from it.

That being said, I admit that the title of this section is a bit misleading because in reality we want to find the answers to these two questions:

• Why do we want to initialize our database by using a database migration tool?
• How should we use the database migration tool?

I will answer to these questions one by one.

First, as you remember, when we wrote integration tests for our application, we initialized our database into a known state by using DbUnit. However, it is not a good idea to use DbUnit when we write our end-to-end tests because of these three reasons:

• Large DbUnit data sets are hard to read, write, and maintain. This is a problem if we want to use large data sets which mimic the data found from our production database.
• One of the benefits of using DbUnit is that we can write assertions for the data found from our database. However, if we use end-to-end tests as smoke tests, there is no need to write assertions for the content of our database because our integration tests already verify that the right information is found from the database.
• If we have to deploy our application to a real servlet container before we can run our end-to-end tests, we cannot use the DbUnit support provided by the Spring Test DbUnit library. This means that using DbUnit becomes quite painful .

So, why should we use a database migration tool instead of writing custom code that inserts the test data into our database? I think that the answer of this question becomes obvious when we find out how we should we use our database migration tool.

Second, when we write integration tests for our application, we initialize our database into a known state before each test method is run. However, if we are writing end-to-end tests, it might not be a good idea to use this technique because of these two reasons:

• If we use large data sets that mimic the data found from our production database, initializing our database before each test method has a huge negative effect to the performance of our test suite. End-to-end tests tend to be slower than unit and integration tests, and we want to pay close attention to the performance of our test suite because we want to keep our feedback loop as short as possible.
• As I mentioned earlier, if we have to deploy our application to a servlet container before we can run our end-to-end tests, it is quite painful to use tools that allow us to initialize our database before each test method is run.

So, if we shouldn’t initialize our database before each test method is run, what should we do, and how our database migration tool can help us to do it? Well, if we use end-to-end tests as smoke tests, we should use the simplest technique that allows us to insert test data into our database before our end-to-end tests are run.

Because we are already using a database migration tool, the easiest option is to create a change set that inserts our test data into our database and invoke that change set automatically before we run our end-to-end tests. Because Liquibase can be integrated with Spring Framework and Spring Boot, we can insert our test data into the used database when our web application is started.

Even though I think that we should use this technique, it has one drawback that we have to take into account when we write our end-to-end tests. The problem is that if we initialize our database only once, we cannot be sure what kind of test data is found from our database when a specific test case is run. This means that:

• We have to design our test cases in a such way that they don’t interfere with each other. For example, let’s assume that we are writing end-to-end tests for two features. The first feature renders the information of a task and the second feature updates the information of a task. Because we cannot specify the order in which our test cases are run, we have to ensure that the test cases of first feature use a different task than the test cases of the second feature.
• We cannot necessarily write assertions for the data that is generated automatically. For example, if we populate column values by using sequences or auto increment columns and our end-to-end tests insert new rows into these database tables, we cannot write assertions for the data that is found from these columns because we cannot predict the order in which our end-to-end tests are run.
• When our end-to-end tests make changes to the data found from our database, we have to make sure that these changes don’t fail our other end-to-end tests. For example, if we have an end-to-end test which verifies that our search function is working as expected, our other end-to-end tests must not change the content of our database in a way that breaks the test which tests the search function.
• If an end-to-end tests fails, it can be hard to reproduce the test failure because we cannot know what kind test data was found from the database when the test failed. Again, the reason for this is that we cannot know the order in which our test cases are run.

It should be clear that writing end-to-end tests for real-life applications can be a quite hard and require a lot of work. That’s one reason why a lot of people tend to use them only for smoke testing.

Enough with theory. Let’s move on and find out how we can initialize the used database with Liquibase.

Getting the Required Dependencies

The first thing that we have to do is to get the required dependencies. We can do this by declaring the liquibase-core dependency in our build script.

If we are using Maven, we have to add the following snippet to our pom.xml file:

1<dependency>
2	<groupId>org.liquibase</groupId>
3    <artifactId>liquibase-core</artifactId>
4	<version>3.5.3</version>
5</dependency>

If we are using Gradle, we have to add the following snippet to our build.gradle file:

1dependencies {
2    compile(
3            'org.liquibase:liquibase-core:3.5.3'
4    )
5}

Next, we will create the SQL scripts that insert the test data into our database.

Creating the SQL Scripts

We will insert the test data into our database by using SQL scripts. Because these files must be found by Liquibase, we have to create a new directory to the directory that contains our Liquibase change log file and put our SQL scripts to the created directory.

In other words, because our Liquibase change log file is found from the src/main/resources/db.changelog directory, we will put our SQL scripts to the src/main/resources/db.changelog/test-data directory.

We can create the required SQL scripts by following these steps:

First, we have to create the SQL script that contains all task related data. This script inserts two rows into the tasks, tags, and tasks_tags tables.

After we have created this SQL script, the source code of the tasks.sql file looks as follows:

 1-- Tasks
 2INSERT INTO tasks
 3  (id, assignee_id, closer_id, creation_time, creator_id, description, modification_time, modifier_id, resolution, status, title, version)
 4VALUES
 5  (1, 1, 1, now(), 1, 'This example contains end-to-end tests', now(), 1, 'DONE', 'CLOSED', 'Write example application', 0);
 6INSERT INTO tasks
 7(id, assignee_id, closer_id, creation_time, creator_id, description, modification_time, modifier_id, resolution, status, title, version)
 8VALUES
 9  (2, null, null, now(), 1, 'This lesson talks about end-to-end testing', now(), 1, null, 'OPEN', 'Write lesson', 0);
10
11-- Tags
12INSERT INTO tags
13(id, creation_time, modification_time, name, version)
14VALUES
15  (1, now(), now(), 'example', 0);
16INSERT INTO tags
17(id, creation_time, modification_time, name, version)
18VALUES
19  (2, now(), now(), 'lesson', 0);
20
21INSERT INTO tasks_tags (task_id, tag_id) VALUES (1, 1);
22INSERT INTO tasks_tags (task_id, tag_id) VALUES (2, 2);

Second, we have to create the SQL script that inserts user data into our database. Because we want to create one normal user and one administrator, this script inserts two rows into the user_accounts table.

After we have created this SQL scripts, the source code of the users.sql file looks as follows:

1INSERT INTO user_accounts
2(id, creation_time, email_address, is_enabled, modification_time, name, password, role, version)
3VALUES
4  (1, now(), 'john.doe@gmail.com', true, now(), 'John Doe', 'user', 'ROLE_USER', 0);
5INSERT INTO user_accounts
6(id, creation_time, email_address, is_enabled, modification_time, name, password, role, version)
7VALUES
8(2, now(), 'anne.admin@gmail.com"', true, now(), 'Anne Admin', 'admin', 'ROLE_ADMIN', 0);

Let’s move on and create the Liquibase change set that runs these SQL scripts.

Creating the Liquibase Change Set

We can create the Liquibase change set that runs our SQL scripts by following these steps:

1. Add a new change set to our change log file and ensure that this change set is invoked only when the ’endToEndTest’ Liquibase context is used.
2. Ensure that the created change set inserts the user data into our database.
3. Ensure that the created change set inserts the task data into our database.

After we have created the required Liquibase change set, the source code of the db.changelog.xml file looks as follows:

 1<databaseChangeLog
 2        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
 3        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 4        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
 5        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">
 6
 7	<!-- Other change sets have been emitted -->
 8
 9    <changeSet id="10-insert-e2e-test-data" author="PK" context="endToEndTest">
10        <sqlFile path="test-data/users.sql"
11				 relativeToChangelogFile="true"
12				 encoding="UTF-8"/>
13        <sqlFile path="test-data/tasks.sql"
14			     relativeToChangelogFile="true"
15				 encoding="UTF-8"/>
16    </changeSet>
17</databaseChangeLog>

Next, we will learn to configure the used Liquibase contexts.

Configuring the Used Liquibase Contexts

There are two different techniques that we can use when we configure the used Liquibase contexts. Let’s start by finding out how we can configure the used Liquibase contexts when we are using Spring Framework.

Using Spring Framework

If we are using Spring Framework, we can configure the used Liquibase contexts by following these steps:

First, we have to add the liquibase.contexts property to our profile specific properties files. The value of this property specifies the used Liquibase contexts. If we want to use multiple Liquibase contexts, we can specify them by separating the context names with a comma.

Because we want to run both application and end-to-end test specific change sets when we run our end-to-end tests, we have to ensure that our end-to-end-tests use both ‘application’ and ’endToEndTest’ Liquibase contexts.

After we have added this property to the properties file that is used when our end-to-end-tests are run, the source code of the end-to-end-test.properties file looks as follows:

1#Database Configuration
2db.driver=org.h2.Driver
3db.url=jdbc:h2:mem:testwithspring-e2e
4db.username=sa
5db.password=
6
7#Liquibase Configuration
8liquibase.contexts=application,endToEndTest

Second, we have to ensure that the used Liquibase contexts are configured when we integrate Liquibase with Spring Framework. We can do this by making the following changes to the configuration of the SpringLiquibase bean:

1. We have to add a new method parameter to the method that configures the SpringLiquibase bean. The added Environment object allows us to access the property values found from our profile specific properties files.
2. Get the property value of the liquibase.contexts property and configure the used Liquibase contexts.

After we have made these changes to the configuration of the SpringLiquibase bean, the source code of our configuration class looks as follows:

 1import com.zaxxer.hikari.HikariConfig;
 2import com.zaxxer.hikari.HikariDataSource;
 3import liquibase.integration.spring.SpringLiquibase;
 4import org.springframework.context.annotation.Bean;
 5import org.springframework.context.annotation.Configuration;
 6import org.springframework.core.env.Environment;
 7
 8import javax.sql.DataSource;
 9
10@Configuration
11public class PersistenceContext {
12
13    @Bean
14    DataSource dataSource(Environment env) {
15        HikariConfig config = new HikariConfig();
16        config.setDriverClassName(env.getRequiredProperty("db.driver"));
17        config.setJdbcUrl(env.getRequiredProperty("db.url"));
18        config.setUsername(env.getRequiredProperty("db.username"));
19        config.setPassword(env.getRequiredProperty("db.password"));
20
21        return new HikariDataSource(config);
22    }
23
24    @Bean
25    SpringLiquibase liquibase(DataSource dataSource, Environment env) {
26        SpringLiquibase liquibase = new SpringLiquibase();
27        liquibase.setContexts(env.getRequiredProperty("liquibase.contexts"));
28        liquibase.setChangeLog("classpath:/db/changelog/db.changelog.xml");
29        liquibase.setDataSource(dataSource);
30        return liquibase;
31    }
32}

Next, we will find out how we can configure the used Liquibase contexts when we are using Spring Boot.

Using Spring Boot

If we are using Spring Boot, we can configure the used Liquibase contexts by setting the value of the liquibase.contexts property to our profile specific properties files. Because we want to run both application and end-to-end test specific change sets when we run our end-to-end tests, we have to ensure that our end-to-end tests use both ‘application’ and ’endToEndTest’ Liquibase contexts.

After we have configured the used Liquibase contexts, the content of the application-endToEndTest.properties file looks as follows:

 1#Database Configuration
 2spring.datasource.type=com.zaxxer.hikari.HikariDataSource
 3spring.datasource.url=jdbc:h2:mem:testwithspring-e2e
 4spring.datasource.driver-class-name=org.h2.Driver
 5spring.datasource.username=sa
 6spring.datasource.password=
 7
 8#Liquibase Configuration
 9liquibase.change-log=classpath:/db/changelog/db.changelog.xml
10liquibase.contexts=application,endToEndTest

We are done. When we start our web application that uses either Spring Framework or Spring Boot, Liquibase initializes our database by using our SQL scripts. Let’s summarize what we learned from this lesson.

Summary

This lesson has taught us five things:

• Liquibase is a database migration tool that supports all common databases.
• Liquibase supports change log files which use the XML, YAML, JSON, and SQL format.
• We should initialize our database by using a database migration tool because it is the easiest and the least worst choice.
• We can get the Liquibase dependency by declaring the liquibase-core dependency in our build script.
• If we want to initialize our database with Liquibase, we have to use its Spring integration and configure the context of the change set that contains our test data.

Get the source code from Github