Repeatable Database Updates via Liquibase

Tutorial on how to automatically re-run database scripts that are periodically updated without having to record them in the root migration file.

Horatiu Dan

May. 11, 23 · Tutorial

Like (2)

Save

3.1K Views

The main purpose of this tutorial is to present a way of detecting modifications to a stored Liquibase change set that was previously applied and execute it again automatically. In order to illustrate this, a small proof of concept is constructed gradually. In the first step, the application configures Liquibase as its migration manager and creates the initial database schema. Then, modifications are applied to the running version, and lastly, the repeatable script is introduced and enhanced.

Set-Up

Java 17
Spring Boot v.3.0.2
Liquibase 4.17.2
PostgreSQL 12.11
Maven

Proof of Concept

As PostgreSQL was chosen for the database layer of this service, first, a new schema is created (liquirepeat). This can be easily accomplished by issuing the following SQL command after previously connecting to the database.

    SQL
   
   create schema liquirepeat;

At the application level, the steps are presented below.

The Maven Spring Boot project is created and instructed to use the PostgreSQL Driver, Liquibase, and Spring Data JPA dependencies. This is enough for the current purpose.
A Minifig entity is created, having two attributes - id and name. It represents a mini-figure with a unique identifier and its name.

    Java
   
 

   @Entity
@Table(name = "minifig")
@SequenceGenerator(sequenceName = "minifig_seq", name = "CUSTOM_SEQ_GENERATOR", initialValue = 1, allocationSize = 1)
@Data
public class Minifig {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "CUSTOM_SEQ_GENERATOR")
    @Column(name = "id")
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    public Minifig() {

    }

    public Minifig(String name) {
        this.name = name;
    }
}
  

For convenience, when entities are stored, their unique identifiers are generated using a database sequence called minifig_seq.

A corresponding JPA repository is declared by extending the existing CrudRepository.

    Java
   
   public interface MinifigRepository extends CrudRepository<Minifig, Long> {}

The data source is configured in the usual way in the application.properties file.

    Properties files
   
   spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres?currentSchema=liquirepeat&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=postgres
spring.datasource.password=123456

spring.jpa.hibernate.ddl-auto=none

The previously created schema is referred in the connection URL. DDL handling is disabled, as the infrastructure and the data are intended to be persistent when the application is restarted.

As the database migration manager is Liquibase, the changelog path is configured in the application.properties file as well.

    Properties files
   
   spring.liquibase.change-log=classpath:/db/changelog/db.changelog-root.xml

For now, the db.changelog-root.xml file is empty.

At application start-up, the two Liquibase-specific tables are created — databasechangelog and databasechangeloglock. The former (which records the deployed changes) is empty, as nothing is to be executed yet - db.changelog-root.xml is currently empty.

The logs clearly depict the expected behavior.

    Plain Text
   
 

   INFO 28464 --- [main] liquibase.database      : Set default schema name to liquirepeat
INFO 28464 --- [main] liquibase.lockservice   : Successfully acquired change log lock
INFO 28464 --- [main] liquibase.changelog     : Creating database history table with name: liquirepeat.databasechangelog
INFO 28464 --- [main] liquibase.changelog     : Reading from liquirepeat.databasechangelog
INFO 28464 --- [main] liquibase.lockservice   : Successfully released change log lock
  

In the first version of the application — 1.0.0 — at least the database schema initialization should be fulfilled. According to Liquibase's best practices, a directory for each version is recommended and located under db/changelog, next to db.changelog-root.xml file. Thus, version-1.0.0 folder is created, containing this version change sets - for now, the schema-init.xml file.

    XML
   
 

   <?xml version="1.1" encoding="UTF-8" standalone="no"?>
<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
                      https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <changeSet author="horatiucd" id="100">
        <createSequence sequenceName="minifig_seq" startValue="1" incrementBy="1"/>
    </changeSet>

    <changeSet author="horatiucd" id="200">
        <createTable tableName="minifig">
            <column name="id" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="name" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet author="horatiucd" id="300">
        <addPrimaryKey columnNames="id" constraintName="minifig_pk" tableName="minifig"/>
    </changeSet>

</databaseChangeLog>
  

minifig table and the corresponding minifig_seq sequence are created in line with the simple entity class. In order for these to be applied, they need to be recorded as part of db.changelog-root.xml file, as indicated below.

    XML
   
 

   <?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
                      https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <!-- Version 1.0.0 -->
    <include file="db/changelog/version-1.0.0/schema-init.xml"/>
</databaseChangeLog>
  

When the application is restarted, the three change sets are executed in the order they are declared.

    Plain Text
   
 

   INFO 44740 --- [main] liquibase.database      : Set default schema name to liquirepeat
INFO 44740 --- [main] liquibase.lockservice   : Successfully acquired change log lock
INFO 44740 --- [main] liquibase.changelog     : Reading from liquirepeat.databasechangelog
Running Changeset: db/changelog/version-1.0.0/schema-init.xml::100::horatiucd
INFO 44740 --- [main] liquibase.changelog     : Sequence minifig_seq created
INFO 44740 --- [main] liquibase.changelog     : ChangeSet db/changelog/version-1.0.0/schema-init.xml::100::horatiucd ran successfully in 15ms
Running Changeset: db/changelog/version-1.0.0/schema-init.xml::200::horatiucd
INFO 44740 --- [main] liquibase.changelog     : Table minifig created
INFO 44740 --- [main] liquibase.changelog     : ChangeSet db/changelog/version-1.0.0/schema-init.xml::200::horatiucd ran successfully in 4ms
Running Changeset: db/changelog/version-1.0.0/schema-init.xml::300::horatiucd
INFO 44740 --- [main] liquibase.changelog     : Primary key added to minifig (id)
INFO 44740 --- [main] liquibase.changelog     : ChangeSet db/changelog/version-1.0.0/schema-init.xml::300::horatiucd ran successfully in 5ms
INFO 44740 --- [main] liquibase.lockservice   : Successfully released change log lock
  

Moreover, they are recorded as separate rows in the databasechangelog database table.

    Plain Text
   
 

   +---+---------+------------------------------------------+--------------------------+-------------+--------+----------------------------------+----------------------------------------------------------+
|id |author   |filename                                  |dateexecuted              |orderexecuted|exectype|md5sum                            |description                                               |
+---+---------+------------------------------------------+--------------------------+-------------+--------+----------------------------------+----------------------------------------------------------+
|100|horatiucd|db/changelog/version-1.0.0/schema-init.xml|2023-02-06 23:15:29.458517|1            |EXECUTED|8:be2c93cdecec258121a0e522cde14dbf|createSequence sequenceName=minifig_seq                   |
|200|horatiucd|db/changelog/version-1.0.0/schema-init.xml|2023-02-06 23:15:29.466702|2            |EXECUTED|8:7083de78675d6af112bec737838e8cbb|createTable tableName=minifig                             |
|300|horatiucd|db/changelog/version-1.0.0/schema-init.xml|2023-02-06 23:15:29.472865|3            |EXECUTED|8:db76242ba57fe4b4883e51313955cae9|addPrimaryKey constraintName=minifig_pk, tableName=minifig|
+---+---------+------------------------------------------+--------------------------+-------------+--------+----------------------------------+----------------------------------------------------------+
  

In version 2.0.0, a new attribute is added to the Minifig entity, its description. In order to reflect it at the database level, a change set is added in a version-specific directory and plugged into the db.changelog-root.xml file.

    XML
   
 

   <?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
                      https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <!-- Version 1.0.0 -->
    <include file="db/changelog/version-1.0.0/schema-init.xml"/>

    <!-- Version 2.0.0 -->
    <include file="db/changelog/version-2.0.0/minifig_update.xml"/>
    
</databaseChangeLog>
  

The minifig_update.xml contains the change set that updates the table.

    XML
   
 

   <?xml version="1.1" encoding="UTF-8" standalone="no"?>
<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
                      https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <changeSet author="horatiucd" id="400">
        <addColumn tableName="minifig">
            <column name="description" type="VARCHAR(500)"/>
        </addColumn>
    </changeSet>
</databaseChangeLog>
  

The entity is enriched with the new attribute as well.

    Java
   
   @Column(name = "description")
private String description;

At application start-up, a new record is added into databasechangelog database table, record that reflects the mentioned change.

    Plain Text
   
 

   +---+---------+---------------------------------------------+--------------------------+-------------+--------+----------------------------------+----------------------------------------------------------+
|id |author   |filename                                     |dateexecuted              |orderexecuted|exectype|md5sum                            |description                                               |
+---+---------+---------------------------------------------+--------------------------+-------------+--------+----------------------------------+----------------------------------------------------------+
|100|horatiucd|db/changelog/version-1.0.0/schema-init.xml   |2023-02-06 23:15:29.458517|1            |EXECUTED|8:be2c93cdecec258121a0e522cde14dbf|createSequence sequenceName=minifig_seq                   |
|200|horatiucd|db/changelog/version-1.0.0/schema-init.xml   |2023-02-06 23:15:29.466702|2            |EXECUTED|8:7083de78675d6af112bec737838e8cbb|createTable tableName=minifig                             |
|300|horatiucd|db/changelog/version-1.0.0/schema-init.xml   |2023-02-06 23:15:29.472865|3            |EXECUTED|8:db76242ba57fe4b4883e51313955cae9|addPrimaryKey constraintName=minifig_pk, tableName=minifig|
|400|horatiucd|db/changelog/version-2.0.0/minifig_update.xml|2023-02-06 23:31:21.146004|4            |EXECUTED|8:0fc33fb9a00f989ed96e3e3af48355c9|addColumn tableName=minifig                               |
+---+---------+---------------------------------------------+--------------------------+-------------+--------+----------------------------------+----------------------------------------------------------+
  

In order to have some data as well, not just the database structure, a few mini-figures may be easily added to the designated table. One straightforward way is by wiring a CommandLineRunner and provide it with the MinifigRepository.

    Java
   
 

   @Bean
public CommandLineRunner init(MinifigRepository repository) {
	return args -> {
		Minifig harry = new Minifig("Harry Potter");
		Minifig ron = new Minifig("Ron Weasley");
		Minifig hermione = new Minifig("Hermione Granger");

		List.of(harry, ron, hermione)
				.forEach(minifig -> log.info("Persisted {}.", repository.save(minifig)));
	};
}
  

The application logs reflect what happens when the application is restarted.

    Plain Text
   
 

   Hibernate: select nextval('minifig_seq')
Hibernate: insert into minifig (description, name, id) values (?, ?, ?)
INFO 10516 --- [main] com.hcd.liquirepeat.config.DataLoader : Persisted Minifig(id=4, name=Harry Potter, description=null).
Hibernate: select nextval('minifig_seq')
Hibernate: insert into minifig (description, name, id) values (?, ?, ?)
INFO 10516 --- [main] com.hcd.liquirepeat.config.DataLoader : Persisted Minifig(id=5, name=Ron Weasley, description=null).
Hibernate: select nextval('minifig_seq')
Hibernate: insert into minifig (description, name, id) values (?, ?, ?)
INFO 10516 --- [main] com.hcd.liquirepeat.config.DataLoader : Persisted Minifig(id=6, name=Hermione Granger, description=null).
  

Handling Repeatable Database Updates

As the plot, let's assume now that as part of application version 2.0.0, a simple Minifig Report is requested to be created, designed as a view - it contains the Id and Name of all mini-figures.

The code for creating it is straightforward.

    SQL
   
 

   DROP VIEW IF EXISTS liquirepeat."Minifig Report" CASCADE;
CREATE OR REPLACE VIEW liquirepeat."Minifig Report"
    AS
SELECT m.id AS "Minifig ID",
       m.name AS "Minifig Name"
FROM liquirepeat.minifig m;
  

One option to implement it is to create a new change set file in folder version-2.0.0 and deploy the change. Analyzing a bit more, one may envision that at some point in the future, it's likely for the report to modify, and thus, another change set would be needed in the particular version folder so that the update is deployed as well.

A better solution is to be able to just update the script and the application to execute it automatically at the next restart.

According to Liquibase documentation, change sets have an attribute called runOnChange. When this is true, Liquibase detects a modification to a previously applied update and re-runs it.

With this detail acknowledged, let's add a new change set in the db.changelog-root.xml, having runOnChanged=true and placed in a position where it is always executed the last. The change set runs a plain SQL file - minifig-report.sql - that contains the code for (re)creating the database view.

    XML
   
 

   <?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
                      https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <!-- Version 1.0.0 -->
    <include file="db/changelog/version-1.0.0/schema-init.xml"/>

    <!-- Version 2.0.0 -->
    <include file="db/changelog/version-2.0.0/minifig_update.xml"/>
    
    <changeSet id="repeatable" author="dev-team" runOnChange="true">
        <sqlFile dbms="postgresql" path="db/changelog/run-on-change/minifig-report.sql"/>
    </changeSet>
</databaseChangeLog>
  

At start-up, Liquibase executes the change set, as usual.

    Plain Text
   
 

   INFO 6128 --- [main] liquibase.lockservice   : Successfully acquired change log lock
INFO 6128 --- [main] liquibase.changelog     : Reading from liquirepeat.databasechangelog
Running Changeset: db/changelog/db.changelog-root.xml::repeatable::dev-team
INFO 6128 --- [main] liquibase.changelog     : SQL in file db/changelog/run-on-change/minifig-report.sql executed
INFO 6128 --- [main] liquibase.changelog     : ChangeSet db/changelog/db.changelog-root.xml::repeatable::dev-team ran successfully in 15ms
INFO 6128 --- [main] liquibase.lockservice   : Successfully released change log lock
  

The databasechangelog table records this as well.

    Plain Text
   
 

   +----------+---------+---------------------------------------------+--------------------------+-------------+--------+----------------------------------+----------------------------------------------------------+
|id        |author   |filename                                     |dateexecuted              |orderexecuted|exectype|md5sum                            |description                                               |
+----------+---------+---------------------------------------------+--------------------------+-------------+--------+----------------------------------+----------------------------------------------------------+
|100       |horatiucd|db/changelog/version-1.0.0/schema-init.xml   |2023-02-06 23:15:29.458517|1            |EXECUTED|8:be2c93cdecec258121a0e522cde14dbf|createSequence sequenceName=minifig_seq                   |
|200       |horatiucd|db/changelog/version-1.0.0/schema-init.xml   |2023-02-06 23:15:29.466702|2            |EXECUTED|8:7083de78675d6af112bec737838e8cbb|createTable tableName=minifig                             |
|300       |horatiucd|db/changelog/version-1.0.0/schema-init.xml   |2023-02-06 23:15:29.472865|3            |EXECUTED|8:db76242ba57fe4b4883e51313955cae9|addPrimaryKey constraintName=minifig_pk, tableName=minifig|
|400       |horatiucd|db/changelog/version-2.0.0/minifig_update.xml|2023-02-06 23:31:21.146004|4            |EXECUTED|8:0fc33fb9a00f989ed96e3e3af48355c9|addColumn tableName=minifig                               |
|repeatable|dev-team |db/changelog/db.changelog-root.xml           |2023-02-06 23:51:37.876140|5            |EXECUTED|8:93b422e6004aecce9b67018d6b10bc82|sqlFile                                                   |
+----------+---------+---------------------------------------------+--------------------------+-------------+--------+----------------------------------+----------------------------------------------------------+
  

A few observations are useful at this moment:

Since this change set is re-executed if changed, its identifier was chosen to be something that illustrates this aspect - repeatable. Also, it shall designate idempotent operations.
Since the minifig-report.sql file might be updated by a certain developer. The author was set to be a generic one — dev-team.

As a last action, let's imagine the Minifig Report is requested to be enhanced to also contain the description of a mini-figure.

In order to implement this requirement, a developer edits the minifig-report.sql file and modify the script accordingly.

    SQL
   
 

   DROP VIEW IF EXISTS liquirepeat."Minifig Report" CASCADE;
CREATE OR REPLACE VIEW liquirepeat."Minifig Report"
    AS
SELECT m.id AS "Minifig ID",
       m.name AS "Minifig Name",
       m.description AS "Minifig Description"
FROM liquirepeat.minifig m;
  

At start-up, it is re-run, and the report structure is updated. That is, the database view is recreated.

    Plain Text
   
 

   INFO 18796 --- [main] liquibase.lockservice  : Successfully acquired change log lock
INFO 18796 --- [main] liquibase.changelog    : Reading from liquirepeat.databasechangelog
Running Changeset: db/changelog/db.changelog-root.xml::repeatable::dev-team
INFO 18796 --- [main] liquibase.changelog    : SQL in file db/changelog/run-on-change/minifig-report.sql executed
INFO 18796 --- [main] liquibase.changelog    : ChangeSet db/changelog/db.changelog-root.xml::repeatable::dev-team ran successfully in 12ms
INFO 18796 --- [main] liquibase.lockservice  : Successfully released change log lock
  

It is important to note the value in the exectype column of the databasechangelog table has changed from EXECUTED to RERAN. Also, the value of the md5sum has a different value, as the contents of the file was altered, and this triggered the re-execution in the first place.

    Plain Text
   
 

   +----------+---------+---------------------------------------------+--------------------------+-------------+--------+----------------------------------+----------------------------------------------------------+
|id        |author   |filename                                     |dateexecuted              |orderexecuted|exectype|md5sum                            |description                                               |
+----------+---------+---------------------------------------------+--------------------------+-------------+--------+----------------------------------+----------------------------------------------------------+
|100       |horatiucd|db/changelog/version-1.0.0/schema-init.xml   |2023-02-06 23:15:29.458517|1            |EXECUTED|8:be2c93cdecec258121a0e522cde14dbf|createSequence sequenceName=minifig_seq                   |
|200       |horatiucd|db/changelog/version-1.0.0/schema-init.xml   |2023-02-06 23:15:29.466702|2            |EXECUTED|8:7083de78675d6af112bec737838e8cbb|createTable tableName=minifig                             |
|300       |horatiucd|db/changelog/version-1.0.0/schema-init.xml   |2023-02-06 23:15:29.472865|3            |EXECUTED|8:db76242ba57fe4b4883e51313955cae9|addPrimaryKey constraintName=minifig_pk, tableName=minifig|
|400       |horatiucd|db/changelog/version-2.0.0/minifig_update.xml|2023-02-06 23:31:21.146004|4            |EXECUTED|8:0fc33fb9a00f989ed96e3e3af48355c9|addColumn tableName=minifig                               |
|repeatable|dev-team |db/changelog/db.changelog-root.xml           |2023-02-06 23:56:36.516859|6            |RERAN   |8:59be58683050b5ac350494d8bfbad7ac|sqlFile                                                   |
+----------+---------+---------------------------------------------+--------------------------+-------------+--------+----------------------------------+----------------------------------------------------------+
  

Conclusion

The tutorial presented a simple yet useful and convenient way of automatically re-running database scripts that are periodically updated without having each time to record this aspect in the root migration file and reflect the modification.

Database Liquibase

Published at DZone with permission of Horatiu Dan. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

Trending