8

Easy Database Migration With Liquibase

 3 years ago
source link: https://mydeveloperplanet.com/2020/04/21/easy-database-migration-with-liquibase/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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 which 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 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:

<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.

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.

@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.

@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.

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.

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:

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

Start the Spring Boot application:

$ mvn spring-boot:run

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

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

Retrieve the list of employees:

$ 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 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.

$ 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:

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:

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:

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:

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.

<databaseChangeLog
<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:

$ 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.

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:

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:

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:

@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:

<databaseChangeLog
<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:

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

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

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:

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:

$ 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:

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:

<databaseChangeLog
<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:

$ 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:

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

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

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.

$ 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.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK