Version Controlling Database Schemas and Data with Liquibase

Version controlling database schemas facilitates repeatable deployments and consistent environments. The alternative is have a human manually perform database modifications; since humans are human, we tend to make mistakes especially when performing repetitive tasks, and our time is also very expensive compared to that of machines, so automating database schema changes is superior approach.

More than just the schema can and should be automated. For example, if it’s necessary to run a query to convert or fix some data, this approach is superior to writing instructions for a person to manually run that query. This approach is also great for loading initial data, such as sample data, lookup tables, and more.

What is Liquibase?

Liquibase is an open-source database schema change management solution which enables you to manage revisions of your database changes easily. Liquibase makes it easy for anyone involved in the application release process to:

Eliminate errors and delays when releasing databases.
Deploys and Rollback changes for specific versions without needing to know what has already been deployed.
Deploy database and application changes together so they always stay in sync.

https://docs.liquibase.com/home.html

Unlike Flyway, Liquibase can (optionally) specify schema and data changesets in a database agnostic format instead of always using SQL. This approach can be:

• Easier to read and write
• Automatically works between different databases without having to write vendor-specific SQL

Of course, Liquibase can easily execute SQL, too.

Setting up Liquibase in a Spring Boot Project

There are only a few steps involved in setting up Liquibase in a Spring Boot project.

Add the dependency

For Maven’s pom.xml:

<dependency>
	<groupId>org.liquibase</groupId>
	<artifactId>liquibase-core</artifactId>
</dependency>

For Gradle’s build.gradle:

implementation "org.liquibase:liquibase-core"

You’ll also need:

• a dependency on a database driver (such as Postgres, H2, MySQL, etc)
• a dependency on the Spring Boot Data JPA starter: ‘org.springframework.boot:spring-boot-starter-data-jpa’

Create the database schema (optional)

Liquibase requires a database schema to exist when it starts so it can store information about what changesets have already been run. Therefore, this schema must be created if it doesn’t already exists.

Note that this step is not applicable for MySQL due to how it handles schemas.

1. Create a file at src/main/resources/schema.sql with these contents:

BEGIN;
CREATE SCHEMA IF NOT EXISTS YOUR_SCHEMA;
COMMIT;

Replace YOUR_SCHEMA with the name of your schema.

2. The name of your schema must also be specified in these locations:

• In application.{yml,properties}:
  • spring.jpa.properties.hibernate.default_schema
  • spring.liquibase.default-schema If any are missing, make sure to add them.

3. In application.{yml,properties}, set spring.datasource.initialization-mode to always (this ensures that schema.yml is always executed)

Configure Hibernate

In application.{yml,properties}, set spring.jpa.hibernate.ddl-auto to validate.

This setting causes Hibernate to check that its understanding of the schema (which is determines using annotations) matches the actual database schema.

Indicate master changelog

The master changelog is configured in application.{yml,properties} with the spring.liquibase.change-log setting. The default value is db/changelog/db.changelog-master.yaml.

Liquibase supports XML, YAML, and SQL changelogs. If you’re happy using YAML, then leave spring.liquibase.change-log unset to accept the default and create the master changelog file at src/main/resources/db/changelog/db.changelog-master.yaml.

XML has the autocompletion because Liquibase publishes an XML schema. Therefore, XML may be easier to use. To use XML, set spring.liquibase.change-log to db/changelog/db.changelog-master.xml and create the master changelog file at src/main/resources/db/changelog/db.changelog-master.xml.

Start populating the master changelog

At startup, Liquibase executes each changeset the master changelog in the order they appear. For each changeset, liquibase will check to see if that changeset’s id has been executed already. If it has, it ensures that the checksum of the changeset matches; if it does, then liquibase moves on to the next changeset. If it does not, Liquibase throws an exception. This checksum safety measure ensures that the changeset definitions match the state of the database. Therefore, changeset should only ever be added – and never changed or removed.

Note that a changeset’s id doesn’t matter as long as it is unique. The id does not determine execution order like it does in Flyway.

The following is an example master changelog. It does a few things:

• Sets up hibernate_sequence. This sequence is required when using Hibernate’s @GeneratedValue(strategy = GenerationType.SEQUENCE)
• Sets up the spring-session JDBC tables. Note that depending up on the DBMS being used, different scripts are executed. This changeset also uses runOnChange so if the changeset checksum mismatches, instead of throwing an exception, Liquibase will re-run the changeset.
• Creates a table and some indexes on it

<?xml version="1.0" encoding="UTF-8"?>
<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 id="hibernate_sequence" author="candrews">
    <!-- When liquibase inserts data, hibernate_sequence isn't used.
      Since Hibernate will use the sequence when generating ids when data is inserted,
      if the sequence starts below the range used by liquibase changesets,
      there could be collisions. So avoid that, set the startValue to a value greater than
      the greatest value used in the liquibase changesets. -->
    <dropSequence sequenceName="hibernate_sequence"/>
    <createSequence sequenceName="hibernate_sequence" startValue="100000"/>
  </changeSet>
  <changeSet id="spring-session" runOnChange="true" author="candrews">
    <sqlFile path="org/springframework/session/jdbc/schema-drop-h2.sql" dbms="h2"/>
    <sqlFile path="org/springframework/session/jdbc/schema-drop-hsqldb.sql" dbms="hsqldb"/>
    <sqlFile path="org/springframework/session/jdbc/schema-drop-mysql.sql" dbms="mysql"/>
    <sqlFile path="org/springframework/session/jdbc/schema-drop-postgresql.sql" dbms="postgresql"/>
    <sqlFile path="org/springframework/session/jdbc/schema-drop-sqlite.sql" dbms="sqlite"/>
    <sqlFile path="org/springframework/session/jdbc/schema-drop-sqlserver.sql" dbms="sqlserver"/>
    <sqlFile path="org/springframework/session/jdbc/schema-h2.sql" dbms="h2"/>
    <sqlFile path="org/springframework/session/jdbc/schema-hsqldb.sql" dbms="hsqldb"/>
    <sqlFile path="org/springframework/session/jdbc/schema-mysql.sql" dbms="mysql"/>
    <sqlFile path="org/springframework/session/jdbc/schema-postgresql.sql" dbms="postgresql"/>
    <sqlFile path="org/springframework/session/jdbc/schema-sqlite.sql" dbms="sqlite"/>
    <sqlFile path="org/springframework/session/jdbc/schema-sqlserver.sql" dbms="sqlserver"/>
  </changeSet>
  <changeSet id="1" author="candrews">
    <createTable tableName="monitor_result">
      <column name="id" type="bigint">
        <constraints nullable="false" primaryKey="true"/>
      </column>
      <column name="performed" type="datetime">
        <constraints nullable="false"/>
      </column>
      <column name="health_url" type="varchar(255)">
        <constraints nullable="false"/>
      </column>
      <column name="response_time" type="bigint">
        <constraints nullable="false"/>
      </column>
      <column name="success" type="boolean">
        <constraints nullable="false"/>
      </column>
    </createTable>
    <createIndex tableName="monitor_result" indexName="ix_hh_1">
      <column name="performed"/>
    </createIndex>
    <createIndex tableName="monitor_result" indexName="ix_hh_2">
      <column name="health_url"/>
    </createIndex>
    <createIndex tableName="monitor_result" indexName="ix_hh_3">
      <column name="success"/>
    </createIndex>
  </changeSet>
</databaseChangeLog>

Setup Hibernate entities

Hibernate entity classes are set up as usual.

In the changeset, a table named monitor_result was created. The corresponding Hibernate entity is:

@Entity
public class MonitorResult {

	@Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE)
	@Column
	private Long id;

	/**
	 * Time take before a response was received.
	 */
	@NotNull
	private Duration responseTime;

	/**
	 * When the test was performed.
	 */
	@NotNull
	private Instant performed;

	/**
	 * Result of the test.
	 */
	@NotNull
	private boolean success;

	/**
	 * URL monitored.
	 */
	@URL(protocol = "https")
	@NotNull
	@Size(min = 1, max = 255)
	private String healthUrl;
}

Contexts

Contexts allow changesets to be run or skipped based on a condition.

Example scenarios for which conditional changesets are useful include:

• Sample data for integration testing. Load test data into the database then run standard junit integration tests against it
• Environment specific data. If one environment needs different lookup tables or other data than another environment, conditional changesets can do that.

To use contexts in a changeset, set the context attribute. For example:

  <changeSet id="testdata" author="candrews" context="test">
       <loadData tableName="monitor_result" relativeToChangelogFile="true" file="monitor_result.csv" />
  </changeSet>

By default, liquibase will run changesets for all contexts. Since we only want to run changesets with context="test" in integration tests, we need to configure Spring Boot accordingly. To do that:

1. In src/main/resources/application.yml, set spring.liquibase.contexts: "!test"
2. In src/test/resources/application.yml, set spring.liquibase.contexts: "test"

Links, references, and additional examples

• baeldung: Use Liquibase to Safely Evolve Your Database Schema
• Spring Boot Liquibase documentation
• Liquibase

Version Controlling Database Schemas and Data with Liquibase by Craig Andrews is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.