Easy Database Migration With Liquibase
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.
Join the DZone community and get the full member experience.Join For Free
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.
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
The domain object,
Employee, contains just an id, a first name, and a last name. The getters and setters are left out for brevity.
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.
EmployeeController will take care of processing the HTTP requests in order to add and retrieve employees.
Now, it is time to create our database scripts. By default, Spring expects the Liquibase database changelog file at the following location:
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
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.
We make use of the PostgreSQL Docker image to run the database:
Start the Spring Boot application:
Let’s add an entry to the list of employees:
Retrieve the list of employees:
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.
Now that we have a Postgres command prompt, we can execute database commands. Let’s retrieve a list of tables with the
As expected, we see our employee table and the corresponding sequence table. We also notice two tables
databasechangeloglock which are created by Liquibase itself. The
databasechangelog table contains a list of all the changes that have been run against the database.
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 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
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:
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.
Stop and remove the PostgreSQL container:
Start the PostgreSQL container with
docker run just like we did before and start the application with
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.
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
EmployeeController to support the new column.
Employee is changed as follows:
EmployeeDao is changed as follows:
EmployeeController is changed as follows:
db.changelog-2.0.xml file to the
And add a line to the master file:
Run the application; the log shows us clearly that the migration script is executed:
Check the schema of the
Employee table where we can verify that the
country column is added:
Add an employee via the web request and retrieve all employees:
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:
We create an
application-test.properties, similar to the
application-prod.properties and change the above line to context
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:
The other changelogs are given the
prod or test contexts and will be executed for context
prod as well as for context
Run the application again and run it with the
prod Spring profile:
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:
The console log shows us already that the 3.0 changelog is being executed:
getAllEmployees web request and you will notice that the record from our changelog is returned.
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.
Published at DZone with permission of Gunter Rotsaert , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.