Preconditions

Preconditions are changelog or changeset tags which control the execution of an update based on the state of the database.

Uses

You can use preconditions to:

Document what assumptions the author of the changelog had when creating it.
Enforce that those assumptions are not violated by users running the changelog.
Perform data checks before performing an unrecoverable change such as dropTable.
Control what changesets are run and not run based on the state of the database.

You can use all Liquibase preconditions in XML, YAML, and JSON changelogs. The only supported precondition for SQL changelogs is sqlCheck. For a list of preconditions, see Available preconditions.

Running changelogs with preconditions

There can only be one preconditions element per changelog or changeset. If you need multiple preconditions, you can use nested preconditions. Also, preconditions at the changelog level are applied to all changesets, not just those listed in the current changelog or its child changelogs.

While running the changelog or changeset with the precondition, add it to your changelog file as shown in the examples.

XML example

<?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"
	xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
	xmlns:pro="http://www.liquibase.org/xml/ns/pro"
	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
		http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.9.xsd
		http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
		http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.9.xsd">
    
	<preConditions>
        <dbms  type="oracle"  />
        <runningAs  username="SYSTEM"  />
    </preConditions>

    <changeSet  id="1"  author="Liquibase User">
        <preConditions  onFail="WARN">
            <sqlCheck  expectedResult="0">select count(*) from oldtable</sqlCheck>
        </preConditions>
        <comment>Comments should go after preCondition. If they are located before the precondition, then Liquibase usually gives error.</comment>
        <dropTable  tableName="oldtable"/>
    </changeSet>
</databaseChangeLog>

The sample changelog will only be run if the database executed against is Oracle and the database user executing the script is SYSTEM. Also, it will run the changeset with the sqlCheck precondition and dropTable.

If the preconditions check fails, you will receive a warning and it will continue executing the changeset as normal because of the onFail="WARN" precondition. To prevent the execution of the changeset when the precondition fails, you can set HALT or CONTINUE values. For more information, see onFail/onError values.

SQL example

--liquibase formatted sql
			
--changeset Liquibase User:1
--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM primary_table
--comment: /*comments should go after preCondition. If they are located before the precondition, then Liquibase usually gives error.*/
create table primary_table (  
	id int primary key,
	name varchar(50) not null,
	address1 varchar(50),
	address2 varchar(50),
	city varchar(30)
)

YAML example

databaseChangeLog:  
 - preConditions:
     - onFail: WARN 
     - sqlCheck:
        expectedResult: 0
        sql: select count(*) from DATABASECHANGELOGLOCK
 - changeSet:  
     id:  1  
     author: Liquibase User
     changes:  
     - createTable:   
         columns:  
          - column:  
             name: id  
             type: int  
             autoIncrement: true  
             constraints:  
              primaryKey: true  
              nullable: false  
          - column:  
             name:  firstname  
	         type:  varchar(50)  
          - column:  
             name:  surname  
             type:  varchar(50)  
             constraints:  
              nullable:  false  
          - column:  
             name:  state  
             type:  char(2)
         tableName: example_table

JSON example

{
  "databaseChangeLog": [
    {
      "preConditions": [
        {
         "sqlCheck": {
           "expectedResult": 1,
           "sql": "select count(*) from DATABASECHANGELOGLOCK"
         }
       }
     ],
     "changeSet": {
       "id": "1",
       "author": "Liquibase User",
       "labels": "1.0",
       "changes": [
         {
           "createTable": {
             "tableName": "primary_table",
             "columns": [
                {
                  "column": {
                    "name": "id",
                    "type": "int",
                    "autoIncrement": true,
                    "constraints": {
                      "primaryKey": true,
                      "nullable": false                         
                   }
            	 }
          	   },
           	   {
                 "column": {
                   "name": "firstname",
                   "type": "varchar(50)"
                 }
           	   },
               {
              	 "column": {
              	   "name": "lastname",
             	   "type": "varchar(50)",
              	   "constraints": {
              	     "nullable": false
                   }             
           	     }
         	   },
         	   {
                 "column": {
                   "name": "state",
                   "type": "char(2)"
                  }
                }
              ]
            }
          }
    	]
      }
    }
  ]
}

Preconditions syntax

In XML, JSON, and YAML changelogs, you can apply conditional logic to preconditions using nestable AND, OR, and NOT tags. If no conditional tags are specified, the default value is AND.

XML:

<preConditions>
    <or>
        <CONDITION_1>
        <CONDITION_2>
    </or>
<preConditions>

JSON:

{
  "preConditions": [
    {
      "or": [
        {
          {CONDITION_1},
          {CONDITION_2}
        }
      ]
    }
  ]
}

YAML:

- preConditions:
    - or:
        - CONDITION_1
        - CONDITION_2

Examples

The following syntax example will check that the update is running on Oracle and with the SYSTEM user but will only generate a warning message if the precondition fails:

<preConditions  onFail="WARN">
    <dbms  type="oracle"  />
    <runningAs  username="SYSTEM"  />
</preConditions>

The following will require running the changelog on Oracle or MySQL:

<preConditions>
   <or>
      <dbms  type="oracle"  />
      <dbms  type="mysql"  />
   </or>
</preConditions>

You can also see the precondition running as SYSTEM if executing against an Oracle database or running as SA if executing against a MS SQL database.

<preConditions>
    <or>
       <and>
          <dbms  type="oracle"  />  
          <runningAs  username="SYSTEM"  />
       </and>
       <and>
          <dbms  type="mssql"  />
          <runningAs  username="sa"  />
       </and>
     </or>
</preConditions>

Handling failures and errors

Liquibase defines two types of preconditions:

Precondition failures which represent that the check failed
Precondition errors that are the exceptions thrown in the execution of a check

The process of both can be controlled through the onFail and onError attributes on the <preConditions> tag.

Available attributes

Attribute	Description
`onFail`	Controls what happens if the preconditions check fails.
`onError`	Controls what happens if there is an error checking whether the precondition passed or not.
`onSqlOutput`	Controls what to do in the `update-sql` mode. Since 1.9.5
`onFailMessage`	Provides a custom message to output when preconditions fail. Since 2.0
`onErrorMessage`	Provides a custom message to output when preconditions fail. Since 2.0

`onFail`/`onError` values

Value	Description
`HALT`	Halts the execution of the entire changelog (default). `HALT` can be put outside a changeset (e.g. at the beginning of the changelog).
`CONTINUE`	Skips over the changeset. Execution of the changeset will be attempted again on the next update. Continues with the changelog.
`MARK_RAN`	Skips over the changeset but mark it as executed. Continues with the changelog.
`WARN`	Sends a warning and continues executing the changeset / changelog as normal. `WARN` can be put outside a changeset (e.g. at the beginning of the changelog).

`onSqlOutput` values

Value	Description
`TEST`	Runs the changeset in the `update-sql` mode.
`FAIL`	Fails the `preCondition` in the `update-sql` mode.
`IGNORE`	Ignores the `preCondition` in the `update-sql` mode (default).

Available preconditions

The examples of preconditions shown in the table doesn't include the schemaName attribute. It is best practice not to keep the schemaName in the changeset and rely on the default schema except for times you don’t want to create something in the default schema.

Additionally, you can include the onError or onFail attributes with the WARN, HALT, CONTINUE, or MARK_RAN value in the preconditions tag, however, the CONTINUE and MARK_RAN options can only be applied to preconditions inside a changeset.

You can use any precondition mentioned in the following table:

Precondition	Description	Attribute
`dbms`	Defines if the database executed against matches the type specified. `<preConditions onFail="WARN"> <or> <dbms type="oracle" /> <dbms type="mysql" /> </or> </preConditions>`	type – the type of database expected. Multiple dbms values can be specified using comma-separated values. (required)
`runningAs`	Defines if the database user executed under matches the username specified. `<preConditions onFail="WARN"> <dbms type="oracle" /> <runningAs username="SYSTEM" /> </preConditions>`	username – the database user script which is expected to run as. (required)
`changeSetExecuted`	Defines if the specified changeset has already been executed. `<preConditions onFail="HALT"> <changeSetExecuted id="1" author="liquibase" changelog-file="changelog.xml" /> </preConditions>`	Id – the changeset id. (required) author – the changeset author. (required) changelog-file – the file name (including classpath relative path) of the changeset. (required)
`columnExists`	Defines if the specified column exists in the database. `<preConditions onFail="WARN"> <columnExists tableName="table1" columnName="column1" /> </preConditions>`	schemaName – the name of the table's schema. tableName – the name of the column's table. (required) columnName – the name of the column. (required)
`tableExists`	Defines if the specified table exists in the database. `<changeSet author="author" id="1"> <preConditions onFail="MARK_RAN"> <not> <tableExists tableName="MY_NEW_TABLE"/> </not> </preConditions> <createTable tableName="MY_NEW_TABLE"> <column name="IDX" type="${integer.type}"> <constraints nullable="false"/> </column> <column name="INTVAL" type="${integer.type}"/> </createTable> </changeSet>`	schemaName – the name of the table's schema. tableName – the name of the table. (required)
`viewExists`	Defines if the specified view exists in the database. `<preConditions onFail="HALT"> <viewExists viewName="liquibase" /> </preConditions>`	schemaName – the name of the view's schema. viewName – the name of the view. (required)
`foreignKeyConstraintExists`	Defines if the specified foreign key exists in the database. `<preConditions onFail="WARN"> <foreignKeyConstraintExists foreignKeyName="PK_2354" /> </preConditions>`	schemaName – the name of the foreign key's schema. foreignKeyName – the name of the foreign key. (required)
`indexExists`	Defines if the specified index exists in the database. You can either specify the `indexName` attribute or `tableName` and `columnNames` attributes. `<preConditions onFail="WARN"> <indexExists indexName="originindex"/> </preConditions>` `<preConditions onFail="WARN"> <indexExists tableName="MY_NEW_TABLE" columnNames="ID"/> </preConditions>` Note: There are a few databases where the `indexName` is not unique, that's why both `indexName` and `tableName` can be used. `<preConditions onFail="WARN"> <indexExists indexName="originindex" tableName="MY_NEW_TABLE"/> </preConditions>`	schemaName – the name of the index's schema. indexName – the name of the index. tableName – the name of the table. columnNames – the name of the column.
`sequenceExists`	Defines if the specified sequence exists in the database. `<preConditions onFail="WARN"> <sequenceExists sequenceName="idnumber" /> </preConditions>`	schemaName – the name of the sequences' schema. sequenceName – the name of the sequence. (required)
`primaryKeyExists`	Defines if the specified primary key exists in the database. `<preConditions onFail="HALT"> <primaryKeyExists primaryKeyName="ID_2354" tableName="table1" /> </preConditions>`	schemaName – the name of the primary key's schema. primaryKeyName – the name of the primary key constraint. tableName – the name of the table containing primary key. (`tableName` or `primaryKeyName` is required)
`sqlCheck`	Executes an SQL string and checks the returned value. The SQL must return a single row with a single value. To check a number of rows, use the `count` SQL function. To check for ranges of values, perform the check in the SQL and return a value that can be easily compared against. `<preConditions onFail="WARN"> <sqlCheck expectedResult="1"> SELECT COUNT(1) FROM pg_tables WHERE TABLENAME = 'myRequiredTable' </sqlCheck> </preConditions>`	expectedResult – the value to compare the SQL result to. (required)
`changeLogPropertyDefined`	Checks whether given changelog attribute is present. It fails if the value is not the same as given. `<preConditions onFail="WARN"> <changeLogPropertyDefined property="myproperty"/> <changeLogPropertyDefined property="myproperty" value="requiredvalue"/> </preConditions>`	property – the name of the property to check. (required) value – the required value for a given property.
`customPrecondition`	Can be created by adding a class that implements the `liquibase.precondition.CustomPrecondition` interface. Parameters on custom classes are set through reflection based on the `<param>` sub-tags. Pass parameters as strings to the custom precondition. `<customPrecondition className="com.example.CustomTableCheck"> <param name="tableName" value="our_table"/> <param name="count" value="42"/> </customPrecondition>`	className – the name of the custom precondition class. (required) The customPrecondition sub-tags: param – the parameter to pass to the custom precondition. name – the name of the parameter to set. (required) value – a string value to set parameter to. (required)
`uniqueConstraintExists`	Checks for the existence of unique constraints before running the update. (since Liquibase 4.9.0) `<preConditions> <uniqueConstraintExists constraintName="uq_constraint_name" tableName="test_table"/> </preConditions> <preConditions> <uniqueConstraintExists tableName="test_table" columnNames="first_name, last_name"/> </preConditions>`	constraintName – the name of the unique constraint tableName – the name of the column's table (required) columnNames – the name of the column