Preconditions

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

Uses

Preconditions are typically used 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.

Running changelogs with preconditions

A precondition can be the only tag in a changeset. Also, preconditions at the changelog level are applied to all changesets, not just those listed in the current changelog or its child changelogs.

Note: Preconditions are checked at the beginning of the execution of a particular changelog. If you use the include tag and only have preconditions on the child changelog, those preconditions will not be checked until the migrator reaches that file. However, this behavior may change in future releases.

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

Preconditions syntax

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.

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>

In the next example, the changelog will be require to run on Oracle and MySQL, which will always be false unless a huge and unexpected merger takes place:

<preConditions>
    <dbms  type="oracle"  />
    <dbms  type="mysql"  />
 </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 What to do when preconditions fail.
onError What to do when preconditions error.
onSqlOutput What to do in the updateSQL mode. Since 1.9.5
onFailMessage Custom message to output when preconditions fail. Since 2.0
onErrorMessage 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 Outputs a warning and continue 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 updateSQL mode.
FAIL Fails the preCondition in the updateSQL mode.
IGNORE Ignores the preCondition in the updateSQL mode (default).

Available preconditions

You can use any precondition mentioned in the following table:

Precondition

Description

Attributes

dbms

Defines if the database executed against matches the type specified.

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

username - database user script which is expected to run as. (required)

changeSetExecuted

Defines if the specified changeset has already been executed.

  • Id - changeset id. (required)
  • author - changeset author. (required)
  • changeLogFile - file name (including classpath relative path) of changeset. (required)

columnExists

Defines if the specified column exists in the database.

  • schemaName - name of the table's schema. (required)
  • tableName - name of the column's table. (required)
  • columnName - name of the column. (required)

tableExists

Defines if the specified table exists in the database.

  • schemaName - name of the table's schema. (required)
  • tableName - name of the table. (required)

viewExists

Defines if the specified view exists in the database.

  • schemaName - name of the view's schema. (required)
  • viewName - name of the view. (required)

foreignKeyConstraintExists

Defines if the specified foreign key exists in the database.

  • schemaName - name of the foreign key's schema. (required)
  • foreignKeyName -name of the foreign key. (required)

indexExists

Defines if the specified index exists in the database.

  • schemaName - name of the index's schema. (required)
  • indexName - name of the index. (required)

sequenceExists

Defines if the specified sequence exists in the database.

  • schemaName - name of the sequences' schema. (required)
  • sequenceName - name of the sequence. (required)

primaryKeyExists

Defines if the specified primary key exists in the database.

  • schemaName - name of the primary key's schema.
  • primaryKeyName - name of the primary key.
  • tableName - 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.

<sqlCheck expectedResult="1">
SELECT COUNT(1) FROM pg_tables WHERE TABLENAME = 'myRequiredTable'
</sqlCheck>

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

<changeLogPropertyDefined property="myproperty"/>
<changeLogPropertyDefined property="myproperty" value="requiredvalue"/>

  • property - name of the property to check for. (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 - name of the custom precondition class. (required)

The customPrecondition sub-tags:

  • param - parameter to pass to the custom precondition.
  • name - name of the parameter to set. (required)
  • value - a string value to set parameter to. (required)