Repeatable Database Updates via Liquibase

Postat în Știință/Tehnică
la 07-02-2023 15:44
267 vizualizări

by Horatiu Dan

Abstract

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.

 create schema liquirepeat;

At 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 an unique identifier and its name.

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

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

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

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.

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.

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

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.

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

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

+---+---------+---------------------------------------------+--------------------------+-------------+--------+----------------------------------+----------------------------------------------------------+ |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 in the designated table. One straight-forward way is by wiring a CommandLineRunner and provide it with the MinifigRepository.

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

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

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

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.

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

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.

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.

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

Resources

Liquibase Documentation – https://docs.liquibase.com/home.html
Source code for the sample application – https://github.com/horatiucd/liquirepeat
The picture represents Lego figures, designed and built by my son.