Liquibase Rollback Workflow

Liquibase provides commands to allow you to undo changes you have made to your database, either automatically or with a custom rollback command. The intention of a rollback script is to return the database to a previous specified point in time.

Note: Rollback support is available in command line, Ant, and Maven.

Rollback commands

There are three rollback modes:

Command Description

rollback This command will revert all changes made to the database after the specified tag. It is used when you want to undo a series of changes related to a specific tag such as a numbered release.

Command	Description
rollback	This command will revert all changes made to the database after the specified tag. It is used when you want to undo a series of changes related to a specific tag such as a numbered release.
rollback-to-date	This command is used to revert all changes made to the database from the current date to the date and time you specify. There are several ways to revert your changes with the `rollback-to-date` command. There are three options: You can use `YYYY-MM-DD`, `HH:MM:SS`, or `YYYY-MM-DD'T'HH:MM:SS` formats to specify the rollback date, time, or date + time. You can also specify date or time only. Examples: `rollback-to-date YYYY-MM-DD`, `rollback-to-date HH:MM:SS`, or `rollback-to-date YYYY-MM-DD’T’HH:MM:SS`
rollback-count	This command is used to roll back a specified number of changesets, where `<value>` is the number of changesets you want to revert sequentially on your database. The command will rollback the changes sequentially starting with the most recent changes.

rollback-to-date

This command is used to revert all changes made to the database from the current date to the date and time you specify. There are several ways to revert your changes with the rollback-to-date command.

There are three options: You can use YYYY-MM-DD, HH:MM:SS, or YYYY-MM-DD'T'HH:MM:SS formats to specify the rollback date, time, or date + time. You can also specify date or time only.

Examples: rollback-to-date YYYY-MM-DD, rollback-to-date HH:MM:SS, or rollback-to-date YYYY-MM-DD’T’HH:MM:SS

rollback-count This command is used to roll back a specified number of changesets, where <value> is the number of changesets you want to revert sequentially on your database. The command will rollback the changes sequentially starting with the most recent changes.

Liquibase Pro rollback commands

Liquibase Pro contains additional built in commands that allow you to perform targeted rollback tasks.

Command	Description
rollback-one-changeset	This command will revert one non-sequential changeset made during a previous change to the database without affecting any other changes made to your database.
rollback-one-update	This command will revert all changesets related to a specific `deploymentId` that was made during a previous database change. The command will undo a series of changes made to the database during a specific deployment and revert those changesets to their previous state without affecting any other changes made to the database.

Rollback on error

In Liquibase Pro 4.18.0 and later, you can use the command parameter --rollback-on-error with the following commands: update, update-count, update-one-changeset, update-testing-rollback, and update-to-tag. If you set --rollback-on-error to true, then Liquibase automatically rolls back any changesets that cause errors when you try to deploy them. See also: failOnError.

Validating rollbacks

Rollback scripts are one of the most difficult aspects of application development to create and maintain, especially when data is modified.

If the database change is simple such as adding a new index, or an update to a stored procedure, then the rollback is straight forward.
Rolling back complex changes that involve structural changes and data migrations are much more difficult.

So before performing a rollback, it is important to validate the changes Liquibase will run prior to making the change to the database. These two commands help in validating and testing rollbacks.

Command	Description
update-testing-rollback	This command is used to test rollback functionality when deploying changesets in your changelog sequentially. It tests the rollback by deploying all pending changesets, executing a sequential rollback in reverse order for the changesets that were deployed, then running the update again.
future-rollback-sql	This command produces the raw SQL Liquibase would use to revert changes associated with undeployed changesets. Used when auditors need to verify that all database changes have a rollback.

Rollbacks are also validated with the help of SQL helper commands:

Command	Description
rollback-sql	This helper command is used in conjunction with the `rollback<tag>` command to inspect the SQL Liquibase will use to revert changes associated with the tag you specify.
future-rollback-from-tag-sql	This helper command produces the raw SQL Liquibase would need to roll back all undeployed changes made up to the specified tag.
rollback-to-date-sql	This helper command is used in conjunction with the `rollback-to-date` command to inspect the SQL that Liquibase will run when using the `rollback-to-date` command.
update-count-sql	This helper command is used in conjunction with the `rollback-count <value>` command to inspect the SQL Liquibase will use to rollback changes based on the value specified.

Pro Command	Description
rollback-one-changeset-sql	This helper command is used in conjunction with the `rollback-one-changeset` command to inspect the SQL Liquibase will use to rollback changes based on the specified changeset.
rollback-one-update-sql	This helper command is used in conjunction with the `rollback-one-update` command to inspect the SQL Liquibase will run to revert all changesets associated with the `deploymentId` specified in the `rollback-one-update` command.

Rollback test cycle

Rollback scripts need to be tested just like application code so we know they work as designed.

You will need to carefully test your rollback scripts. This includes determining who will test the rollback script, how and when they will be tested.

A complete test cycle should include:

Deploying all changes to the database and validating that they were deployed.
Rolling back all changes to the database, validating that all changes were reversed, and verification that the database was brought back to the previous state.
Redeploying all changes to the database. This is required to verify that the rollback did not miss any changes that could impact a future deployment.

Creating rollback statements

Many Change Types such as create table, rename column, and add column can automatically create rollback statements. If your changelog contains only statements that fit into this category, your rollback commands will be generated automatically.

Other Change Types such as drop table and insert data have no corresponding rollback commands that can be automatically generated. Liquibase also cannot automatically generate rollback statements for formatted SQL changesets. In these cases, and cases where you want to override the default generated rollback commands, you can specify the rollback commands via the tag within the changeset tag.

To determine if Liquibase can automatically roll back a specific Change Type and see the examples of the rollback tag, see Automatic and Custom Rollbacks. You can also check the Change Types documentation. Go to the Database Support section to determine whether auto rollback is available for your database platform.

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.

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: "").

{
  "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": "").

<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 empty and output Change Types support automatic rollbacks.