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