Idempotent Liquibase Change Sets

Postat în Știință/Tehnică
la 30-05-2023 05:50
213 vizualizări

by Horatiu Dan

Abstract

“Idempotence is the property of certain operations in mathematics and computer science whereby they can be applied multiple times without changing the result beyond the initial application” [Resource 3].

The purpose of this article is to outline a few ways of creating idempotent changes when the database modifications are managed with Liquibase. Throughout the life time of a software product that has such tier, various database modifications are being applied as it evolves. The more robust the modifications are, the more maintainable the solution is. In order to accomplish such a way of working, it is usually a good practice to design the executed change sets to have zero side effects, that is to be able to be run as many times as needed with the same end result.

The simple proof of concept built here aims to show case how Liquibase change sets may be written to be idempotent. Moreover, the article explains in more depth what exactly happens when the application starts.

Set-up

Java 17
Spring Boot v.3.1.0
Liquibase 4.20.0
PostgreSQL Driver 42.6.0
Maven 3.6.3

Proof of Concept

As PostgreSQL is the database used here, first and foremost one shall create a new schema – liquidempo. This operation is easy to accomplish by issuing the following SQL command, once connected to the database.

 create schema liquidempo;

At application level:

The Maven Spring Boot project is created and configured to use the PostgreSQL Driver, Spring Data JPA and Liquibase dependencies.

A simple entity is created – Human – with only one attribute, a unique identifier which is also the primary key at database level.

@Entity @Table(name = "human") @SequenceGenerator(sequenceName = "human_seq", name = "CUSTOM_SEQ_GENERATOR", allocationSize = 1) public class Human { @Id @GeneratedValue(strategy = GenerationType.AUTO, generator = "CUSTOM_SEQ_GENERATOR") @Column(name = "id") private Long id; public Long getId() { return id; } public void setId(Long id) { this.id = id; } }

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

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

spring.datasource.type=com.zaxxer.hikari.HikariDataSource spring.datasource.url=jdbc:postgresql://localhost:5432/postgres?currentSchema=liquidempo&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true spring.datasource.username=postgres spring.datasource.password=123456 spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect 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 Liquibase is the database migration manager, 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.

The current state of the project requires a few simple change sets, in order to create the database elements depicted around the Human entity – the table, the sequence and the primary key constraint.

<?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-4.17.xsd"> <changeSet author="horatiucd" id="100"> <createSequence sequenceName="human_seq" startValue="1" incrementBy="1"/> </changeSet> <changeSet author="horatiucd" id="200"> <createTable tableName="human"> <column name="id" type="BIGINT"> <constraints nullable="false"/> </column> </createTable> </changeSet> <changeSet author="horatiucd" id="300"> <addPrimaryKey columnNames="id" constraintName="human_pk" tableName="human"/> </changeSet> </databaseChangeLog>

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-4.17.xsd"> <include file="db/changelog/human_init.xml"/> </databaseChangeLog>

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

INFO 9092 --- [main] liquibase.database : Set default schema name to liquidempo INFO 9092 --- [main] liquibase.lockservice : Successfully acquired change log lock INFO 9092 --- [main] liquibase.changelog : Creating database history table with name: liquidempo.databasechangelog INFO 9092 --- [main] liquibase.changelog : Reading from liquidempo.databasechangelog Running Changeset: db/changelog/human_init.xml::100::horatiucd INFO 9092 --- [main] liquibase.changelog : Sequence human_seq created INFO 9092 --- [main] liquibase.changelog : ChangeSet db/changelog/human_init.xml::100::horatiucd ran successfully in 6ms Running Changeset: db/changelog/human_init.xml::200::horatiucd INFO 9092 --- [main] liquibase.changelog : Table human created INFO 9092 --- [main] liquibase.changelog : ChangeSet db/changelog/human_init.xml::200::horatiucd ran successfully in 4ms Running Changeset: db/changelog/human_init.xml::300::horatiucd INFO 9092 --- [main] liquibase.changelog : Primary key added to human (id) INFO 9092 --- [main] liquibase.changelog : ChangeSet db/changelog/human_init.xml::300::horatiucd ran successfully in 8ms INFO 9092 --- [main] liquibase : Update command completed successfully. INFO 9092 --- [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/human_init.xml|2023-05-26 16:23:17.184239|1 |EXECUTED|8:db8c5fb392dc96efa322da2c326b5eba|createSequence sequenceName=human_seq | |200|horatiucd|db/changelog/human_init.xml|2023-05-26 16:23:17.193031|2 |EXECUTED|8:ed8e5e7df5edb17ed9a0682b9b640d7f|createTable tableName=human | |300|horatiucd|db/changelog/human_init.xml|2023-05-26 16:23:17.204184|3 |EXECUTED|8:a2d6eff5a1e7513e5ab7981763ae532b|addPrimaryKey constraintName=human_pk, tableName=human| +---+---------+---------------------------+--------------------------+-------------+--------+----------------------------------+------------------------------------------------------+

So far, everything is straight forward, nothing out of the ordinary – a simple Spring Boot application whose database changes are managed with Liquibase.

When examining the above human_init.xml file, one can easily depict the three scripts that result from the three changesets. None is idempotent. It means that if they are executed again (although there is no reason for doing it here) errors will occur because the human_seq sequence, the human table and the human_pk primary key already exist.

Idempotent Change Sets

If the SQL code that results from the XML change sets had been written directly and aimed to be idempotent, it would have read as follows:

CREATE SEQUENCE IF NOT EXISTS human_seq INCREMENT 1 MINVALUE 1 MAXVALUE 99999999999; CREATE TABLE IF NOT EXISTS human ( id SERIAL CONSTRAINT human_pk PRIMARY KEY );

If the two commands are executed several times, no errors occur and the outcome remains the same. After the first run, the sequence, the table and the constraint are created, then every new execution leaves them in the same usable state.

The aim is to accomplish the same in the written Liquibase change sets (change log).

According to the Liquibase documentation [Resource 1] – “Preconditions are tags you add to your changelog or individual changesets to control the execution of an update based on the state of the database. Preconditions let you specify security and standardization requirements for your changesets. If a precondition on a changeset fails, Liquibase does not deploy that changeset.”

These constructs may be configured in various ways, either at change log or change set level. For simplicity, the three change sets of this proof of concept will be made idempotent.

Basically, whenever a change set fails to execute because the entity (sequence, table or primary key) already exists, it would be convenient to continue and not halt the execution of the entire change log and not be able to start the application.

In this direction, Liquibase preconditions provides at least two options:

either skip over the changeset and continue with the change log, or
skip over the change set but mark it as executed and continue with the change log.

Either of the two can be configured by adding a preConditions tag in the change set of interest and setting the onFail attribute as CONTINUE (the former case) or MARK_RAN (the latter case).

In pseudo-code, this looks as below:

<changeSet author="horatiucd" id="100"> <preConditions onFail="CONTINUE or MARK_RAN"> ... </preConditions> ... </changeSet>

This seems in line to the initial desire – execute the change set only if the preconditions are met. Next, each of the two situations is analyzed.

onFail=”CONTINUE”

The change log file – human_init_idempo_continue.xml – becomes as 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-4.17.xsd"> <changeSet author="horatiucd" id="101"> <preConditions onFail="CONTINUE"> <not> <sequenceExists sequenceName="human_seq"/> </not> </preConditions> <createSequence sequenceName="human_seq" startValue="1" incrementBy="1"/> </changeSet> <changeSet author="horatiucd" id="201"> <preConditions onFail="CONTINUE"> <not> <tableExists tableName="human"/> </not> </preConditions> <createTable tableName="human"> <column name="id" type="BIGINT"> <constraints nullable="false"/> </column> </createTable> </changeSet> <changeSet author="horatiucd" id="301"> <preConditions onFail="CONTINUE"> <not> <primaryKeyExists primaryKeyName="human_pk" tableName="human"/> </not> </preConditions> <addPrimaryKey columnNames="id" constraintName="human_pk" tableName="human"/> </changeSet> </databaseChangeLog>

For each item, the precondition checks if it does not exist.

When running the application, the log shows what is executed:

INFO 49016 --- [main] liquibase.database : Set default schema name to liquidempo INFO 49016 --- [main] liquibase.changelog : Reading from liquidempo.databasechangelog INFO 49016 --- [main] liquibase.lockservice : Successfully acquired change log lock Running Changeset: db/changelog/human_init_idempo_continue.xml::101::horatiucd INFO 49016 --- [main] liquibase.changelog : Continuing past: db/changelog/human_init_idempo_continue.xml::101::horatiucd despite precondition failure due to onFail='CONTINUE': db/changelog/db.changelog-root.xml : Not precondition failed Running Changeset: db/changelog/human_init_idempo_continue.xml::201::horatiucd INFO 49016 --- [main] liquibase.changelog : Continuing past: db/changelog/human_init_idempo_continue.xml::201::horatiucd despite precondition failure due to onFail='CONTINUE': db/changelog/db.changelog-root.xml : Not precondition failed Running Changeset: db/changelog/human_init_idempo_continue.xml::301::horatiucd INFO 49016 --- [main] liquibase.changelog : Continuing past: db/changelog/human_init_idempo_continue.xml::301::horatiucd despite precondition failure due to onFail='CONTINUE': db/changelog/db.changelog-root.xml : Not precondition failed INFO 49016 --- [main] liquibase : Update command completed successfully. INFO 49016 --- [main] liquibase.lockservice : Successfully released change log lock

As expected, all three preconditions failed and the execution of the change log continued.

The databasechangelog database table does not have any records in addition to the previous three, which means the change sets will be attempted to be executed again at the next start-up of the application.

onFail=”MARK_RAN”

The change log file – human_init_idempo_mark_ran.xml – is similar to the one in human_init_idempo_continue.xml, the only difference is the onFail attribute, which is set as onFail="MARK_RAN".

The db.changelog-root.xml root change log now looks as 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-4.17.xsd"> <include file="db/changelog/human_init.xml"/> <include file="db/changelog/human_init_idempo_continue.xml"/> <include file="db/changelog/human_init_idempo_mark_ran.xml"/> </databaseChangeLog>

For this proof of concept, all three files were kept on purpose, in order to be able to observe the behavior in detail.

If the application is restarted, no errors are encountered and the log depicts the following:

INFO 38788 --- [main] liquibase.database : Set default schema name to liquidempo INFO 38788 --- [main] liquibase.changelog : Reading from liquidempo.databasechangelog INFO 38788 --- [main] liquibase.lockservice : Successfully acquired change log lock INFO 38788 --- [main] liquibase.changelog : Reading from liquidempo.databasechangelog Running Changeset: db/changelog/human_init_idempo_continue.xml::101::horatiucd INFO 38788 --- [main] liquibase.changelog : Continuing past: db/changelog/human_init_idempo_continue.xml::101::horatiucd despite precondition failure due to onFail='CONTINUE': db/changelog/db.changelog-root.xml : Not precondition failed Running Changeset: db/changelog/human_init_idempo_continue.xml::201::horatiucd INFO 38788 --- [main] liquibase.changelog : Continuing past: db/changelog/human_init_idempo_continue.xml::201::horatiucd despite precondition failure due to onFail='CONTINUE': db/changelog/db.changelog-root.xml : Not precondition failed Running Changeset: db/changelog/human_init_idempo_continue.xml::301::horatiucd INFO 38788 --- [main] liquibase.changelog : Continuing past: db/changelog/human_init_idempo_continue.xml::301::horatiucd despite precondition failure due to onFail='CONTINUE': db/changelog/db.changelog-root.xml : Not precondition failed Running Changeset: db/changelog/human_init_idempo_mark_ran.xml::101::horatiucd INFO 38788 --- [main] liquibase.changelog : Marking ChangeSet: "db/changelog/human_init_idempo_mark_ran.xml::101::horatiucd" as ran despite precondition failure due to onFail='MARK_RAN': db/changelog/db.changelog-root.xml : Not precondition failed Running Changeset: db/changelog/human_init_idempo_mark_ran.xml::201::horatiucd INFO 38788 --- [main] liquibase.changelog : Marking ChangeSet: "db/changelog/human_init_idempo_mark_ran.xml::201::horatiucd" as ran despite precondition failure due to onFail='MARK_RAN': db/changelog/db.changelog-root.xml : Not precondition failed Running Changeset: db/changelog/human_init_idempo_mark_ran.xml::301::horatiucd INFO 38788 --- [main] liquibase.changelog : Marking ChangeSet: "db/changelog/human_init_idempo_mark_ran.xml::301::horatiucd" as ran despite precondition failure due to onFail='MARK_RAN': db/changelog/db.changelog-root.xml : Not precondition failed INFO 38788 --- [main] liquibase : Update command completed successfully. INFO 38788 --- [main] liquibase.lockservice : Successfully released change log lock

The change sets with onFail="CONTINUE" were tried to be re-executed, as this is a new attempt, while the ones with onFail="MARK_RAN" were marked in the databasechangelog and will be passed over at the next star-up.

+---+---------+-------------------------------------------+--------------------------+-------------+--------+----------------------------------+------------------------------------------------------+ |id |author |filename |dateexecuted |orderexecuted|exectype|md5sum |description | +---+---------+-------------------------------------------+--------------------------+-------------+--------+----------------------------------+------------------------------------------------------+ |100|horatiucd|db/changelog/human_init.xml |2023-05-26 16:23:17.184239|1 |EXECUTED|8:db8c5fb392dc96efa322da2c326b5eba|createSequence sequenceName=human_seq | |200|horatiucd|db/changelog/human_init.xml |2023-05-26 16:23:17.193031|2 |EXECUTED|8:ed8e5e7df5edb17ed9a0682b9b640d7f|createTable tableName=human | |300|horatiucd|db/changelog/human_init.xml |2023-05-26 16:23:17.204184|3 |EXECUTED|8:a2d6eff5a1e7513e5ab7981763ae532b|addPrimaryKey constraintName=human_pk, tableName=human| |101|horatiucd|db/changelog/human_init_idempo_mark_ran.xml|2023-05-29 16:40:26.453305|4 |MARK_RAN|8:db8c5fb392dc96efa322da2c326b5eba|createSequence sequenceName=human_seq | |201|horatiucd|db/changelog/human_init_idempo_mark_ran.xml|2023-05-29 16:40:26.463021|5 |MARK_RAN|8:ed8e5e7df5edb17ed9a0682b9b640d7f|createTable tableName=human | |301|horatiucd|db/changelog/human_init_idempo_mark_ran.xml|2023-05-29 16:40:26.475153|6 |MARK_RAN|8:a2d6eff5a1e7513e5ab7981763ae532b|addPrimaryKey constraintName=human_pk, tableName=human| +---+---------+-------------------------------------------+--------------------------+-------------+--------+----------------------------------+------------------------------------------------------+

At the next run of the application, the log will be similar to the one where the onFail was set on "CONTINUE".

One more observation is worth making at this point. In case a change set whose preconditions do not fail, they are executed normally and recorded with exectype = EXECUTED in the databasechangelog table.

Conclusions

This article presented two ways of writing idempotent Liquibase change sets, practice that allows having more robust and easy to maintain applications. This was accomplished by leveraging the change set preConditions tag inside the change log files. While both onFail attribute values – CONTINUE and MARK_RAN – may be used depending on the actual performed operation, the latter seems more appropriate for this proof of concept as it does not attempt to re-run the change sets at every start-up of the application.