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

Custom rollbacks

Liquibase cannot automatically roll back all Change Types. For example, Liquibase cannot automatically roll back dropTable changes because the inverse SQL could be more than one statement.

In these cases, you must specify custom rollback syntax in your changelog for every changeset that you might want to roll back. This way, when you run the rollback command, Liquibase knows what to do.

Note: When rolling back stored logic, Liquibase does not restore the previously stored version. Instead, Liquibase rolls back to the exact file/code specified in the custom rollback.

If you'd like to create a rollback that points to logic stored in an SQL file, use the sqlFile Change Type. For more information and example, see the sqlFile article.

Note: Liquibase does not support automatic rollback for any Formatted SQL changesets. To roll back Formatted SQL changes, you must always specify a custom rollback.

--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.19.0 and later, you can specify multiple rollback statements in a block comment:

--changeset liquibaseuser:2
insert into testTable values ('1','The First', 'Country')
insert into testTable values ('2','The Second', 'Country2')
/* liquibase rollback
rollback delete from testTable where id='1'
rollback delete from testTable where id='2'
*/

In Liquibase 4.26.0 and later, you can use a rollbackSqlFile statement to specify rollback SQL for a changeset in a separate file:

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

In your rollbackSqlFile statement, you can specify parameters to change the behavior of your rollback, such as a unique end delimiter. For more information, see Example Changelogs: SQL Format.

databaseChangeLog:
- changeSet:
    id: 2
    author: liquibaseuser
    changes:
    - dropTable:
        tableName: person
    rollback:
      createTable:
        catalogName: cat
        columns:
        - column:
          name: address
          type: varchar(255)
        remarks: A String
        schemaName: public
        tableName: person

For more information, see Example Changelogs: YAML Format.

{
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "2",
        "author": "liquibaseuser",
        "changes": [
          {
            "dropTable": {
              "tableName": "person"
            }
          }
        ],
        "rollback": [
          {
            "createTable": {
              "catalogName": "cat",
              "columns": [
                {
                  "column": {
                    "name": "address",
                    "type": "varchar(255)"
                  }
                }
              ],
              "remarks": "A String",
              "schemaName": "public",
              "tableName": "person"
            }
          }
        ]
      }
    }
  ]
}

For more information, see Example Changelogs: JSON Format.

<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-latest.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-latest.xsd">

    <changeSet author="liquibaseuser" id="2">
        <dropTable tableName="person"/>

        <rollback>
            <createTable catalogName="department"
                  remarks="A String"
                  schemaName="public"
                  tableName="person"
                <column name="address" type="varchar(255)"/>
            </createTable>
        </rollback>
  </changeSet>

</databaseChangeLog>

For more information, see Example Changelogs: XML 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:

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

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

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

Multiple rollbacks

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

- 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

{
  "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"
        }
      }
    ],
  }
}

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>

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:

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

- 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

{
  "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"
    ]
  }
}

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

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.

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

- 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: "") or an empty array (rollback: []).

{
  "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": "") or an empty array ("rollback": []).

<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/>).

Note: The output Change Type 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
`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