Check database state and conditionally apply changes in the Liquibase

In our previous blog post, we discussed how we can apply different changelogs to different database environments. It is more than often, that when applying a changelog, changeset writer assumes database in a certain state. Like when you are adding a column to the database, you would assume that corresponding table is present. Or when you are dropping a table, it has no data in it. Or we assume that underlying database connection is of a particular nature. We can check for and decide what to do by using the concept of Preconditions in the Liquibase. Using preconditions allows to validate underlying assumption and decide the course of action. Preconditions can be attached to changelogs or changesets to control the execution of an update based on the state of the database.

Types of Preconditions and their meaning

Precondition Name Checks Performed
dbms Checks if the script is running against a specific DBMS (e.g. SQL Server or Oracle)
runningAs Checks if the script is running as a specific database user
changesetExecuted Checks if the changeset has already been executed
tableExists Checks if a table exists in the database
columnExists Checks if a column exists in a table in the database
viewExists Checks if a view exists in the database
indexExists Checks if a index exists in the database
foreignKeyConstraintExists Checks if a foreign key exists in a table
primaryKeyExists Checks if a table has the specified primary key
sequenceExists Checks if a sequence exists in the database
sqlCheck Runs a SQL command and checks the output returned. The SQL command must return a single value (one row and column) like the output of COUNT, SUM, MIN, MAX or AVG.

Each of these preconditions can again have one or more attributes which are necessary to define the precondition.

Handling Failures and Errors

Naturally, you would want to take some actions when a precondition fails. There can be two scenarios:

Failure: When the precondition check fails
Error: When the check itself cannot be performed and an exception is thrown

Both of above situations can be handled by a number of attributes. Some of these include:

Attribute Description
onFail What to do when preconditions fail
onError What to do when preconditions error
onUpdateSQL What to do in updateSQL mode
onFailMessage Custom message to output when preconditions fail
onErrorMessage Custom message to output when preconditions fail

Again, now that the underlying Precondition failed or the check for precondition itself failed, you need to decide the course of action. It can be set using below attributes:

Value Description
HALT Immediately halt the execution of the entire change log. [DEFAULT]
CONTINUE Skip over the change set. Execution of the change set will be attempted again on the next update. Continue with the change log.
MARK_RAN Skip over the change set, but mark it as executed. Continue with the change log.
WARN Output a warning and continue executing the change set/change log as normal.

The possible actions for onUpdateSQL are a little different.

Fitting it all together

In our database, we have a table [dbo].[CustomerDetails] which has only two columns: CustomerTypeID and CustomerDesc. We want to add a new column to this table, say CustomerAddress but we want to make sure that the said table exists before we. For this, we can use below changelog:

Let’s go ahead and apply our change using liquibase update. Since there are no preconditions fails, we’ll not see any indication of same in the logs:

running liquibase update with successful preconditions

Now, let’s try to apply below changelog where we are trying to drop a table:

Here, in the precondition we have specified that table contains no records. However, our table contains 2 records. Let’s run liquibase and see what happens:

running liquibase update with failed preconditions

We can see that liquibase command has failed to proceed as instructed. At this point, if we check records in the DATABASECHANGELOG table, we would see no new entries as no change has been made.

Conditional logic in Preconditions

We can use as many preconditions as we need to make sure underlying assumptions are valid. By default all preconditions are associated by AND. The possible association can be defined using AND, OR and NOT.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s