{{announcement.body}}
{{announcement.title}}

Easy Database Migration With Liquibase

DZone 's Guide to

Easy Database Migration With Liquibase

We will explore some of the features of Liquibase by means of a simple Spring Boot application in combination with a PostgreSQL database.

· Database Zone ·
Free Resource

In this post, we take a look at how we can easily manage our database migration scripts by means of Liquibase. Liquibase will automatically execute necessary database migration scripts during application startup. We will explore some of the features of Liquibase by means of a simple Spring Boot application in combination with a PostgreSQL database.

1. Introduction

When your application makes use of a relational database like PostgreSQL, you inevitably will have to cope with database migration scripts. When your application evolves, new functionality is being added, and this often impacts your database schema. You probably have multiple database instances running for development, test, acceptance, and production. How will you keep track of which scripts have been applied to which instance? 

You can develop something yourself, which will keep track of an internal database schema version and apply the necessary database scripts based on this version, but you can also make use of a tool like Liquibase, which will provide this task for you.

We will create a simple Spring Boot application that makes use of a PostgreSQL database. Docker is a prerequisite for this post. Installation instructions for Docker are available at the Docker website.

The sources being used in this blog are available at GitHub. Beware that the GitHub repository contains the state of the sources as we will reach at the end of this post.

2. Create a Simple Application

Our sample application will have the possibility to add an employee and to retrieve the list of added employees by means of an HTTP request. We start at Spring Initialzr and select the following dependencies: Spring Web, PostgreSQL Driver, and Liquibase Migration. This leads to the following dependencies in our pom:

XML


The domain object, Employee, contains just an id, a first name, and a last name. The getters and setters are left out for brevity.

Java


We create a DAO (Data Access Object), which contains the methods for accessing our database. We use JdbcTemplate for our database access and add two methods: one for adding an employee and one for retrieving the list of employees.

Java


The EmployeeController will take care of processing the HTTP requests in order to add and retrieve employees.

Java


Now, it is time to create our database scripts. By default, Spring expects the Liquibase database changelog file at the following location: src/main/resources/db/changelog/db.changelog-master.yaml

Liquibase expects your database changes in this changelog master file. Inside the file, your database changes must be indicated by different changesets, each with a unique id. The complete list of options can be found here; we will explore some of these options. The syntax is quite easy and recognizable. In our case, we just create the Employee table, the three columns, and add an auto-increment option to our id column.

YAML


The only thing for us to do is to add the database properties to the application.properties file and to set up the database. The application.properties contains the datasource properties in order to be able to connect to the PostgreSQL database, which we will run locally.

Properties files


We make use of the PostgreSQL Docker image to run the database:

Shell


Start the Spring Boot application:

Shell


Let’s add an entry to the list of employees:

Shell


Retrieve the list of employees:

Shell


We now know that our application is up and running and the database table has been created.

3. Inspect the Database

As you probably noticed during the startup of the application, quite some Liquibase log statements passed by. It is also interesting to inspect the database a bit further in order to verify what has happened here. We can make use of the psql command inside the PostgreSQL container in order to do so.

Shell


Now that we have a Postgres command prompt, we can execute database commands. Let’s retrieve a list of tables with the \d command:

Shell


As expected, we see our employee table and the corresponding sequence table. We also notice two tables databasechangelog and databasechangeloglock which are created by Liquibase itself. The databasechangelog table contains a list of all the changes that have been run against the database.

The databasechangeloglock table is used to make sure two machines don’t attempt to modify the database at the same time. Let’s inspect the databasechangelog table:

Shell


The databasechangelog table contains one entry with the details of the changeset, just as we expected.

4. Use XML

By default, Spring Boot expects the changelog to be in YAML format. Personally, we prefer the XML format, which seems to be more readable, but it is mainly a matter of taste. First of all, we need to configure Spring Boot in order to use the XML format. We add the following line to the application.properties file:

Properties files


Besides that, we will refer from our master changelog to version update files. This is a Liquibase best practice. For more best practices, see the Liquibase website. The db.changelog-master.xml file now looks like the following:

XML


In the resources/db/changelog/ directory, we add a file db.changelog-1.0.xml, which contains the same create table entries as in the YAML file we created earlier. We also add a rollback tag. When something goes wrong during the SQL execution, the rollback statement will be executed, leaving your database in a consistent state.

XML


Stop and remove the PostgreSQL container:

Shell


Start the PostgreSQL container with docker run just like we did before and start the application with mvn spring-boot:run.

By means of the psql command, we check the contents of the databasechangelog table and notice that the filename has changed to the XML file.

Shell


5. Add a Migration Script

In the next section, we will add a new column country to the Employee table. We will need to change the Employee, EmployeeDao, and EmployeeController to support the new column.

The Employee is changed as follows:

Java


The EmployeeDao is changed as follows:

Java


The EmployeeController is changed as follows:

Java


Add a db.changelog-2.0.xml file to the resources/db/changelog/ directory:

XML


And add a line to the master file:

XML


Run the application; the log shows us clearly that the migration script is executed:

Shell


Check the schema of the Employee table where we can verify that the country column is added:

Shell


Add an employee via the web request and retrieve all employees:

Shell


6. Something About Contexts

The last feature of Liquibase we will discuss is contexts. A context can be added to a changeset. For example, when a changeset should only be executed on a test environment. First, we will create two Spring Profiles, one for prod, and one for test. We rename the application.properties file to application-prod.properties and add the following line to it:

Properties files


We create an application-test.properties, similar to the application-prod.properties and change the above line to context test.

We add a new changelog db.changelog-3.0.xml, which inserts a record in the Employee table and we add the context test to it:

XML


The other changelogs are given the prod or test contexts and will be executed for context prod as well as for context test.

Run the application again and run it with the prod Spring profile:

Shell


Verify the Employee table or execute the getAllEmployees web request. We notice that it returns an empty list.

Stop the application and run it with the test Spring profile:

Shell


The console log shows us already that the 3.0 changelog is being executed:

Shell


Execute the getAllEmployees web request and you will notice that the record from our changelog is returned.

Shell


7. Conclusion

In this post, we looked at how Liquibase can help you with versioning and migrating your database changes. It is easy to set up and easy to use. We only scratched the surface of what is possible with Liquibase, so take the time to take a look at all the other features it has to offer.

Topics:
database, database migration, java, liquibase, postgres, tutorial

Published at DZone with permission of Gunter Rotsaert , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}