With Liquibase, you can easily not only add a column to a table, but you can also do some more complex operations. This time I will show you how to add a column conditionally, if it does not exist, yet. To achieve this task, I will use preConditions tag from Liquibase.

Many databases with inconsistent structure

Probably we all have seen legacy systems. They are a mess by definition. What makes them all special is the way they understand the mess. I have seen a system that was supposed to have several databases with identical structure. At least one in each environment: development, test, production. But that is not all. Each big customer had their own database in each environment for some reasons. Let's say: 50 databases.

The thing is, each database was supposed to differ only by the data inside. The database structure was supposed to be identical. That assumption helped to reduce development effort. There was not 50 code bases, but just one running on 50 databases.

Unfortunately, at some point, we noticed that there were some differences between the databases. Probably due to human mistakes. Some databases already had some columns and some were missing them. The problem had not appeared earlier because not each customer had used each feature.

Nevermind. We decided to make the databases consistent again. But looking at each database manually and fixing them could have introduced more issues. Let me show you how this problem could be tackled automatically with Liquibase.

Add column if not exists

Adding a column with Liquibase is a standard task. Below change set adds test column to test_table table. It uses a well-known to all Liquibase users, addColumn change type.

<changeSet id="Add test column" author="DBA presents">
   <addColumn tableName="test_table">
      <column name="test" type="int" />
   </addColumn>
</changeSet>

However, as I mentioned earlier. There are some cases when you are not sure if the column, that you want to add, already exists or not. Usually, in such cases, if the column exists, there is nothing to be done. But if it does not, it should be added. In short words - the addColumn change must be run only if the column does not exist.

A perfect solution is to use preconditions functionality. It allows to define conditions that will be validated before applying a change set to a database. Liquibase provides us with a list of predefined conditions that we can use. One of them is columnExists condition. As you can easily guess, it checks if the provided column already exists in the table. Knowing that, we can try to build a precondition that checks if test_table contains test column. See the complete change set.

<changeSet id="Add test column if not exists" author="DBA presents">
   <preConditions onFail="MARK_RAN">
      <not>
         <columnExists tableName="test_table" columnName="test" />
      </not>
   </preConditions>
   <addColumn tableName="test_table">
      <column name="test" type="int" />
   </addColumn>
</changeSet>

There are two elements that I have not mentioned, yet. The first one is not operator. We need it, because the change set should be applied only if the column does NOT exist. The second element is the onFail parameter. It defines what should happen when the precondition is not met. It is very important, because we have to set it to MARK_RUN - mark the change set as successfully executed even if the precondition was not met, and of course do NOT apply the change set.

If you still are not sure how it works, here it is:

1. If there is not test column in test_table, the column will be added.
2. If there is, the column will not try to be added, but the change set will still be marked as successfully applied, so Liquibase will not try applying it again next time.