Automatic and Custom Rollbacks

Liquibase supports multiple ways to revert your database to a previous state. For many Change Types, such as createTable, addColumn, and renameColumn, Liquibase automatically generates the SQL statements necessary to complete the rollback, so you can simply run a command like rollback on your desired changeset(s) and be done.

However, Liquibase cannot automatically generate rollback SQL for Change Types like dropTable and insert. To roll back database updates that include these changesets, you must write custom rollback statements in your desired changeset(s) and then run the rollback command. You must write custom rollback statements for all formatted SQL changelogs, as they do not support auto rollback, regardless of the Change Type. You can also write your own rollbacks if you want to override the default rollback statement for a Change Type that supports auto rollback.

For a complete list of Change Types that allow auto rollback, see the "Auto rollback Change Types" section.

Automatic rollback statements

When you run the update command on a createTable changeset, Liquibase executes the SQL statement CREATE TABLE myTable. When you run the rollback command to revert that changeset, Liquibase recognizes that the inverse of the original change is DROP TABLE myTable and executes that statement. In this case, you aren't required to add anything special to your changelog.

Liquibase command 'rollback' was executed successfully.
Liquibase: Rollback has been successful.

However, if you try to run rollback on dropTable, Liquibase cannot automatically generate the rollback SQL:

Unexpected error running Liquibase: No inverse to liquibase.change.core.DropTableChange created

There is only one state the database could be in right before a createTable statement—a state in which the table does not exist. However, there are multiple states the database could be in right before a dropTable statement, so Liquibase cannot reliably look through your changelog to "recreate" a table you dropped. This is because getting the table to its final state before being dropped may have involved a large number of changesets, raw SQL statements in sql or modifySql tags, Contexts, or manual changes to the changelog.

To roll back your dropTable statement, you have to specify custom logic in your changelog so that Liquibase knows how to correctly restore your database.

Custom rollback statements

Formatted SQL

Liquibase does not support an automatic rollback for formatted SQL changesets. Also, some Change Types have no corresponding rollback commands that can be automatically generated by Liquibase. In these cases, you should add custom rollback statements to the changesets if you want to use rollback commands. For example, in formatted SQL:

--changeset liquibaseuser:1
create table testTable ( id int primary key, name varchar(255) );
--rollback drop table testTable;
				
--changeset liquibaseuser:2
insert into testTable values ('1','The First', 'Country')
insert into testTable values ('2','The Second', 'Country2')
--rollback delete from testTable where id='1'
--rollback delete from testTable where id='2'

In Liquibase 4.26.0 and later, you can also specify rollback SQL for a changeset in a separate file using the --rollbackSqlFile statement:

--changeset liquibase-user:1
DROP PROCEDURE hello_world;
--rollbackSqlFile:release_1.0/rollback_45895.sql

For more information, see Example Changelogs: SQL Format.

Override default rollback commands

The <rollback> tag describes how to roll back a change using SQL statements, Change Types, or a reference to a previous changeset. You can use any Change Type in the <rollback> element, such as dropTable, sql, and sqlFile:

XML

<changeSet id="1" author="liquibaseuser">
    <createTable tableName="testTable">
        <column name="id" type="int"/>
    </createTable>
    <rollback>
        <dropTable tableName="testTable"/>
    </rollback>
</changeSet>

Alternatively, you can have raw SQL in the content part of the <rollback> element. For example:

<changeSet id="1" author="liquibaseuser">
    <createTable tableName="testTable">
        <column name="id" type="int"/>
    </createTable>
    <rollback>
        drop table testTable
    </rollback>
</changeSet>

Liquibase treats the raw SQL within <rollback> the same as in the <sql> Change Type, with stripComments set to true, splitStatements set to true, and endDelimiter set to ;. For more details, see the XML example from the sql documentation.

YAML

- changeSet:
    id: 1
    author: liquibaseuser
    changes:
      - createTable:
          tableName: testTable
          columns:
            - column:
                name: id
    rollback:
      - dropTable:
          tableName: testTable

JSON

{
  "changeSet": {
    "id": 1,
    "author": "liquibaseuser",
    "changes": [
      {
        "createTable": {
          "tableName": "testTable"
        }
      }
    ],
    "rollback": [
      {
        "dropTable": {
          "tableName": "testTable"
        }
      }
    ]
  }
}

Multiple rollbacks

You can also specify multiple Change Types within a single <rollback> statement or across multiple <rollback> statements:

XML

Change Type syntax:

<changeSet id="multiRollbackTest" author="liquibaseuser">
    <createTable tableName="multiRollback1">
        <column name="id" type="int"/>
    </createTable>
    <createTable tableName="multiRollback2">
        <column name="id" type="int"/>
    </createTable>
    <createTable tableName="multiRollback3">
        <column name="id" type="int"/>
    </createTable>
    <rollback>
        <dropTable tableName="multiRollback1"/>
        <dropTable tableName="multiRollback2"/>
    </rollback>
    <rollback>
      <dropTable tableName="multiRollback3"/>
    </rollback>
</changeSet>

Raw SQL:

<changeSet id="multiRollbackTest" author="liquibaseuser">
    <createTable tableName="multiRollback1">
        <column name="id" type="int"/>
    </createTable>
    <createTable tableName="multiRollback2">
        <column name="id" type="int"/>
    </createTable>
    <createTable tableName="multiRollback3">
        <column name="id" type="int"/>
    </createTable>
    <rollback>
        drop table multiRollback1;
        drop table multiRollback2;
    </rollback>
    <rollback>drop table multiRollback3</rollback>
</changeSet>

YAML

- changeSet:
    id: multiRollbackTest
    author: liquibaseuser
    changes:
      - createTable:
          tableName: multiRollback1
          columns:
            - column:
                name: id
      - createTable:
          tableName: multiRollback2
          columns:
            - column:
                name: id
      - createTable:
          tableName: multiRollback3
          columns:
            - column:
                name: id
    rollback:
      - dropTable:
          tableName: multiRollback1
      - dropTable:
          tableName: multiRollback2
    rollback:
      - dropTable:
          tableName: multiRollback3

JSON

{
  "changeSet": {
    "id": multiRollbackTest,
    "author": "liquibaseuser",
    "changes": [
      {
        "createTable": {
          "tableName": "multiRollback1",
          "columns": [
            {
              "column": {
                "name": "id"
              }
            }
          ]
        },
        "createTable": {
          "tableName": "multiRollback2"
          "columns": [
            {
              "column": {
                "name": "id"
              }
            }
          ]
        },
        "createTable": {
          "tableName": "multiRollback3"
          "columns": [
            {
              "column": {
                "name": "id"
              }
            }
          ]
        }
      }
    ],
    "rollback": [
      {
        "dropTable": {
          "tableName": "multiRollback1"
        },
        "dropTable": {
          "tableName": "multiRollback2"
        }
      }
    ],
    "rollback": [
      {
        "dropTable": {
          "tableName": "multiRollback3"
        }
      }
    ],
  }
}

Directly reference changeset

The following example shows how you can use a <rollback> tag to reference the changeset that originally created a statement. This example uses changeset 2 to implement rollback logic against changeset 1:

SQL

--changeset liquibaseuser:1
create table testTable ( id int primary key, name varchar(255) );

--changeset liquibaseuser:2
--rollback drop table testTable;
--rollback changesetId:1 changesetAuthor:liquibaseuser changesetPath:optional/path/to/myChangeLog.sql

XML

<changeSet id="1" author="liquibaseuser">
    <createTable tableName="testTable"/>
        <column name="id" type="varchar(255)"/>
    </createTable>
</changeSet>

<changeSet id="2" author="liquibaseuser">
    <dropTable tableName="testTable"/>
    <rollback changesetId="1" changesetAuthor="liquibaseuser" changesetPath="optional/path/to/myChangeLog.xml"/>
</changeSet>

YAML

- changeSet:
    id: 1
    author: liquibaseuser
    changes:
      - createTable:
          tableName: testTable
          columns:
            - column:
                name: id
- changeSet:
    id: 2
    author: liquibaseuser
    rollback:
      - dropTable:
          tableName: testTable
      changesetId: 1
      changesetAuthor: liquibaseuser
      changesetpath: optional/path/to/myChangeLog.yaml

JSON

{
  "changeSet": {
    "id": 1,
    "author": "liquibaseuser",
    "changes": [
      {
        "createTable": {
          "tableName": "testTable",
          "columns": [
            {
              "column": {
                "name": "id",
                "type": "varchar(255)"
              }
            }
          ]
        }
      }
    ]
{
  "changeSet": {
    "id": 2,
    "author": "liquibaseuser",
    "rollback": [
      {
        "dropTable": {
          "tableName": "testTable"
        }
      },
      "changesetId": "1",
      "changesetAuthor": "liquibaseuser",
      "changesetPath": "optional/path/to/myChangeLog.json"
    ]
  }
}

Empty rollback statements

If you do not want to revert a change in a rollback mode, use either the keyword empty or the keyword not required inside the rollback tag. In XML, YAML, and JSON changelogs, you can also use an empty string inside the rollback tag.

SQL

--changeset liquibaseuser:1
create table testTable ( id int primary key, name varchar(255) );
--rollback empty

XML

<changeSet id="3" author="liquibaseuser">
    <createTable tableName="testTable">
        <column name="id" type="int"/>
    </createTable>
    <rollback>empty</rollback>
</changeSet>

You can also use an empty string (<rollback></rollback>) or a self-closing-tag (<rollback/>).

YAML

- changeSet:
    id: 1
    author: liquibase
    changes:
      - createTable:
          tableName: testTable
          columns:
            - column:
                name: id
                type: int
    rollback: empty

You can also use an empty string (rollback: "").

JSON

{
  "changeSet": {
    "id": 1,
    "author": "example",
    "changes": [
      {
        "createTable": {
          "tableName": "testTable",
          "columns": [
            {
              "column": {
                "name": "id",
                "type": "int"
              }
            }
          ]
        }
      }
    ],
    "rollback": "empty"
  }
}

You can also use an empty string ("rollback": "").

Note: The empty and output Change Types support automatic rollbacks.

Auto rollback Change Types

You can use an automatic rollback with XML, JSON, and YAML changelogs for any Change Type marked as "Supported" in the following table:

Change Type	Supported	Behavior
`addAutoIncrement`	Not Supported
`addCheckConstraint`	Supported	Drop check constraint
`addColumn`	Supported	Drop column
`addDefaultValue`	Supported	Drop default value
`addForeignKeyConstraint`	Supported	Drop foreign key constraint
`addLookupTable`	Supported	Drop foreign key constraint and drop table
`addNotNullConstraint`	Supported	Drop not null constraint
`addPrimaryKey`	Supported	Drop primary key
`addUniqueConstraint`	Supported	Drop unique constraint
`alterSequence`	Not Supported
`createFunction`	Not Supported
`createIndex`	Supported	Drop index
`createPackage`	Not Supported
`createPackageBody`	Not Supported
`createProcedure`	Not Supported
`createSequence`	Supported	Drop sequence
`createSynonym`	Supported	Drop synonym
`createTable`	Supported	Drop table
`createTrigger`	Not Supported
`createView`	Supported	Drop view
`customChange`	Not Supported
`delete`	Not Supported
`disableCheckConstraint`	Supported	Enable check constraint
`disableTrigger`	Supported	Enable trigger
`dropAllForeignKeyConstraints`	Not Supported
`dropCheckConstraint`	Not Supported
`dropColumn`	Not Supported
`dropDefaultValue`	Not Supported
`dropForeignKeyConstraint`	Not Supported
`dropFunction`	Not Supported
`dropIndex`	Not Supported
`dropNotNullConstraint`	Supported	Add not null constraint
`dropPackage`	Not Supported
`dropPackageBody`	Not Supported
`dropPrimaryKey`	Not Supported
`dropProcedure`	Not Supported
`dropSequence`	Not Supported
`dropSynonym`	Not Supported
`dropTable`	Not Supported
`dropTrigger`	Not Supported
`dropUniqueConstraint`	Not Supported
`dropView`	Not Supported
`empty`	Supported	Return empty change
`enableCheckConstraint`	Supported	Disable check constraint
`enableTrigger`	Supported	Disable trigger
`executeCommand`	Not Supported
`insert`	Not Supported
`loadData`	Not Supported
`loadUpdateData`	Not Supported
`markUnused`	Not Supported
`mergeColumns`	Not Supported
`modifyDataType`	Not Supported
`output`	Supported	Return empty change
`renameColumn`	Supported	Rename with `oldColumnName`
`renameSequence`	Supported	Rename with `oldSequenceName`
`renameTable`	Supported	Rename with `oldTableName`
`renameTrigger`	Supported	Rename with `oldTriggerName`.
`renameView`	Supported	Rename with `oldViewName`
`setColumnRemarks`	Not Supported
`setTableRemarks`	Not Supported
`sql`	Not Supported
`sqlFile`	Not Supported
`stop`	Not Supported
`tagDatabase`	Supported	Deletes the tag
`update`	Not Supported