Easy Database Migration With Liquibase

Gunter Rotsaert

CORE ·

Apr. 28, 20 · Tutorial

Likes (4)

Comment

Save

21.0K Views

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
   
 

     
    
xxxxxxxxxx

            
          
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

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
   
xxxxxxxxxx

public class Employee {
    private long id;
    private String firstName, lastName;
 
    public Employee(long id, String firstName, String lastName) {
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
    }
    // Getters and setters
}

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
   
xxxxxxxxxx

@Repository
public class EmployeeDao {
 
    @Autowired
    JdbcTemplate jdbcTemplate;
 
    public List getAllEmployees() {
        return jdbcTemplate.query("SELECT * FROM EMPLOYEE", new EmployeeRowMapper());
    }
 
    public void addEmployee(final String firstName, String lastName) {
        jdbcTemplate.update("INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME) VALUES (?, ?)", firstName, lastName);
    }
 
    private static final class EmployeeRowMapper implements RowMapper {
 
        @Override
        public Employee mapRow(final ResultSet rs, final int rowNum) throws SQLException {
            return employee = new Employee(rs.getLong("ID"),
                                           rs.getString("FIRST_NAME"),
                                           rs.getString("LAST_NAME"));
        }
 
    }
 
}

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

    Java
   
 

     
    
xxxxxxxxxx

            
          
@Controller
public class EmployeeController {
 
    @Autowired
    public EmployeeDao employeeDao;
 
    @PostMapping(path = "/addEmployee")
    public @ResponseBody
    String addEmployee(@RequestParam String firstName, @RequestParam String lastName) {
        employeeDao.addEmployee(firstName, lastName);
        return "Saved Employee";
    }
 
    @GetMapping(path = "/getAllEmployees")
    public @ResponseBody
    Iterable getAllEmployees() {
        return employeeDao.getAllEmployees();
    }
 
}

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
   
 

     
    
xxxxxxxxxx

            
          
databaseChangeLog:
  - changeSet:
      id: 1
      author: gunter
      changes:
        - createTable:
            tableName: employee
            columns:
              - column:
                  name: id
                  type: serial
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: first_name
                  type: varchar(255)
                  constraints:
                    nullable: false
              - column:
                  name: last_name
                  type: varchar(255)
                  constraints:
                    nullable: false

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
   
xxxxxxxxxx

spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=root

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

    Shell
   
xxxxxxxxxx

$ docker run -p 5432:5432 --name mypostgres -e POSTGRES_PASSWORD=root -d postgres

Start the Spring Boot application:

    Shell
   
xxxxxxxxxx

$ mvn spring-boot:run

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

    Shell
   
xxxxxxxxxx

$ curl --data "firstName=John" --data "lastName=Doe" http://localhost:8080/addEmployee
Saved Employee

Retrieve the list of employees:

    Shell
   
xxxxxxxxxx

$ curl http://localhost:8080/getAllEmployees
[{"id":1,"firstName":"John","lastName":"Doe"}]

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
   
xxxxxxxxxx

$ docker exec -it mypostgres psql -U postgres
psql (12.2 (Debian 12.2-2.pgdg100+1))
Type "help" for help.
 
postgres=#

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
   
xxxxxxxxxx

postgres=# \d
                  List of relations
 Schema |         Name          |   Type   | Owner 
--------+-----------------------+----------+----------
 public | databasechangelog     | table    | postgres
 public | databasechangeloglock | table    | postgres
 public | employee              | table    | postgres
 public | employee_id_seq       | sequence | postgres
(4 rows)

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
   
 

     
    
xxxxxxxxxx

            
          
postgres=# select * from databasechangelog ;
-[ RECORD 1 ]-+-------------------------------------------------
id            | 1
author        | gunter
filename      | classpath:/db/changelog/db.changelog-master.yaml
dateexecuted  | 2020-03-22 16:14:33.492585
orderexecuted | 1
exectype      | EXECUTED
md5sum        | 8:8d6c01228a9985939df0c3b0b1c4ae38
description   | createTable tableName=employee
comments      | 
tag           | 
liquibase     | 3.8.7
contexts      | 
labels        | 
deployment_id | 4890073466

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
   
xxxxxxxxxx

spring.liquibase.change-log=classpath:/db/changelog/db.changelog-master.xml

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
   
xxxxxxxxxx

<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
 
    <include file="/db/changelog/db.changelog-1.0.xml"/>
 
</databaseChangeLog>

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
   
 

     
    
xxxxxxxxxx

            
          
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
 
    <changeSet author="gunter" id="changelog-1.0">
        <createTable tableName="employee">
            <column name="id" type="serial" autoIncrement="true">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="first_name" type="varchar(255)">
                <constraints nullable="false"/>
            </column>
            <column name="last_name" type="varchar(255)">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <rollback>
            <dropTable tableName="employee"/>
        </rollback>
    </changeSet>
 
</databaseChangeLog>

Stop and remove the PostgreSQL container:

    Shell
   
xxxxxxxxxx

$ docker stop mypostgres
$ docker rm mypostgres

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
   
 

     
    
xxxxxxxxxx

            
          
postgres=# select * from databasechangelog;
-[ RECORD 1 ]-+-----------------------------------
id            | changelog-1.0
author        | gunter
filename      | /db/changelog/db.changelog-1.0.xml
dateexecuted  | 2020-03-28 11:13:20.163418
orderexecuted | 1
exectype      | EXECUTED
md5sum        | 8:8d6c01228a9985939df0c3b0b1c4ae38
description   | createTable tableName=employee
comments      | 
tag           | 
liquibase     | 3.8.7
contexts      | 
labels        | 
deployment_id | 5390400140

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
   
 

     
    
xxxxxxxxxx

            
          
public class Employee {
    private long id;
    private String firstName, lastName, country;
 
    public Employee(long id, String firstName, String lastName, String country) {
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
        this.country = country;
    }
    // Getters and setters
}

The EmployeeDao is changed as follows:

    Java
   
xxxxxxxxxx

public void addEmployee(final String firstName, String lastName, String country) {
    jdbcTemplate.update("INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, COUNTRY) VALUES (?, ?, ?)",
            firstName, lastName, country);
}
 
private static final class EmployeeRowMapper implements RowMapper {
 
    @Override
    public Employee mapRow(final ResultSet rs, final int rowNum) throws SQLException {
        return new Employee(rs.getLong("ID"),
                            rs.getString("FIRST_NAME"),
                            rs.getString("LAST_NAME"),
                            rs.getString("COUNTRY"));
    }
 
}

The EmployeeController is changed as follows:

    Java
   
xxxxxxxxxx

@PostMapping(path = "/addEmployee")
public @ResponseBody
String addEmployee(@RequestParam String firstName, @RequestParam String lastName, @RequestParam String country) {
    employeeDao.addEmployee(firstName, lastName, country);
    return "Saved Employee";
}

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

    XML
   
 

     
    
xxxxxxxxxx

            
          
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
 
    <changeSet author="gunter" id="changelog-2.0">
        <addColumn tableName="employee">
            <column name="country" type="varchar(255)"/>
        </addColumn>
        <rollback>
            <dropColumn tableName="employee">
                <column name="country" type="varchar(255)"/>
            </dropColumn>
        </rollback>
    </changeSet>
 
</databaseChangeLog>

And add a line to the master file:

    XML
   
xxxxxxxxxx

<include file="/db/changelog/db.changelog-2.0.xml"/>

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

    Shell
   
xxxxxxxxxx

2020-03-28 11:33:12.775 INFO 11117 --- [ main] liquibase.executor.jvm.JdbcExecutor : ALTER TABLE public.employee ADD country VARCHAR(255) 
2020-03-28 11:33:12.776 INFO 11117 --- [ main] liquibase.changelog.ChangeSet : Columns country(varchar(255)) added to employee 
2020-03-28 11:33:12.779 INFO 11117 --- [ main] liquibase.changelog.ChangeSet : ChangeSet /db/changelog/db.changelog-2.0.xml::changelog-2.0::gunter ran successfully in 4ms

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

    Shell
   
 

     
    
xxxxxxxxxx

            
          
postgres=# \d employee
Table "public.employee"
   Column   |          Type          | Collation | Nullable | Default 
------------+------------------------+-----------+----------+----------------------------------
 id         | integer                |           | not null | generated by default as identity
 first_name | character varying(255) |           | not null | 
 last_name  | character varying(255) |           | not null | 
 country    | character varying(255) |           |          | 
Indexes:
"employee_pkey" PRIMARY KEY, btree (id)

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

    Shell
   
xxxxxxxxxx

$ curl --data "firstName=John" --data "lastName=Doe" --data "country=The Netherlands" http://localhost:8080/addEmployee
Saved Employee
$ curl http://localhost:8080/getAllEmployees
[{"id":1,"firstName":"John","lastName":"Doe","country":"The Netherlands"}]

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
   
xxxxxxxxxx

spring.liquibase.contexts=prod

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
   
 

     
    
xxxxxxxxxx

            
          
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
 
    <changeSet author="gunter" id="changelog-3.0" context="test">
        <insert tableName="employee">
            <column name="first_name" value="Foo"/>
            <column name="last_name" value="Bar"/>
            <column name="country" value="Sweden"/>
        </insert>
    </changeSet>
 
</databaseChangeLog>

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
   
xxxxxxxxxx

$ mvn spring-boot:run -Dspring-boot.run.profiles=prod

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
   
xxxxxxxxxx

$ mvn spring-boot:run -Dspring-boot.run.profiles=test

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

    Shell
   
xxxxxxxxxx

2020-03-29 11:22:23.806 INFO 9277 --- [ main] liquibase.executor.jvm.JdbcExecutor : INSERT INTO public.employee (first_name, last_name, country) VALUES ('Foo', 'Bar', 'Sweden')
2020-03-29 11:22:23.807 INFO 9277 --- [ main] liquibase.changelog.ChangeSet : New row inserted into employee
2020-03-29 11:22:23.815 INFO 9277 --- [ main] liquibase.changelog.ChangeSet : ChangeSet /db/changelog/db.changelog-3.0.xml::changelog-3.0::gunter ran successfully in 10ms

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

    Shell
   
          x
         
$ curl http://localhost:8080/getAllEmployees
[{"id":1,"firstName":"Foo","lastName":"Bar","country":"Sweden"}]

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.

Database Relational database Liquibase Spring Framework application shell Spring Boot PostgreSQL XML

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

Opinions expressed by DZone contributors are their own.

Related

Trending