Implement a rollback strategy with modeled changelogs (XML, YAML, JSON) for existing databases

When you have generated modeled changelogs from an existing database using generate-changelog, your changesets represent the current state of your database rather than incremental changes. Unlike clean projects where you define rollback logic as you create each changeset, existing databases require you to add rollback statements to generated changesets that represent tables, constraints, and other objects that already exist.

Your generated changelog contains changesets that recreate your existing database structure. These changesets typically include:

  • Table creation statements

  • Index creation statements

  • Constraint creation statements

  • Initial data insertion statements (if generated with data)

Since these changesets represent existing database objects, you'll need to consider whether rollback operations should:

  • Drop the objects (making the database empty)

  • Preserve certain critical data or structures

  • Handle dependencies between related database objects

Liquibase provides significant value for managing rollbacks by creating a framework that encourages you to consider rollback scenarios during development, simplifies executing rollbacks with straightforward commands, and maintains a complete history of rollback operations for audit and troubleshooting purposes. We recommend that you write rollback logic for every changeset. You can use the RollbackRequired policy check to ensure that every changeset contains rollback logic.

Important: If you need to roll back a changeset that contains an error, perform the rollback before editing the changeset. Editing the changeset first causes a checksum mismatch, which prevents the rollback from executing.

How automatic rollback works

Liquibase automatically generates rollback SQL for many change types in modeled changelogs, including createTable, renameColumn, addColumn, and others. For these change types, you don't need to add any rollback logic to your changesets. Liquibase handles these automatically when you execute rollback commands. See What change types can I use auto rollback with? for a complete list of which change types work with automatic rollback.

When to use manual rollback

However, not all change types support automatic rollback. For these operations, you'll need to provide manual rollback logic to ensure that your database can be reverted to its previous state. This article explains how to identify which change types require a manual rollback, create rollback statements, and utilize the rollback command.

Destructive operations

For rollbacks that reverse destructive operations (DROP, DELETE, TRUNCATE), ensure that you maintain the data and implement logic for its recovery. The operation can be rolled back by recreating the objects, but the original data will not be restored.

To mitigate destructive operations, we recommend using policy checks. Depending on your use case, Liquibase offers several policy checks to assist you:

This article assumes you have already used generate-changelog to create your initial changelog from an existing database and have your Liquibase configuration set up. If you are creating new Liquibase changelogs for the first time, see Implement a rollback strategy with modeled changelogs (XML, YAML, JSON).

Before you begin

Procedure

1

Identify Change types that require manual rollback

The following Change types do NOT support automatic rollback and require manual rollback statements:

  • addAutoIncrement

  • alterSequence

  • createFunction

  • createPackage

  • createPackageBody

  • createProcedure

  • createTrigger

  • customChange

  • delete

  • dropAllForeignKeyConstraints

  • dropCheckConstraint

  • dropColumn

  • dropDefaultValue

  • dropForeignKeyConstraint

  • dropFunction

  • dropIndex

  • dropPackage

  • dropPackageBody

  • dropPrimaryKey

  • dropProcedure

  • dropSequence

  • dropSynonym

  • dropTable

  • dropTrigger

  • dropUniqueConstraint

  • executeCommand

  • insert

  • loadData

  • loadUpdateData

  • markUnused

  • mergeColumns

  • modifyDataType

  • setColumnRemarks

  • setTableRemarks

  • sql

  • sqlFile

  • stop

  • update

2

Review your generated changelog structure

Modeled changelogs use XML, YAML, or JSON formats. Each format requires a specific structure to define changesets and track changes. These identifiers appear in the DATABASECHANGELOGHISTORY table after your change sets have run.

Each changeset will include:

  • your_name— Auto-generated changeset metadata with your system username.

  • unique_id— Timestamp-based unique ID.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                   http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
  <changeSet id="unique_id" author="your_name"></changeSet>
</databaseChangeLog>

3

Add rollback statements to your existing changesets

For each changeset in your changelog, include rollback logic that defines the inverse operations. Your rollback instructions must be specific to the command you wish to roll back. Below we provide three examples: a single rollback statement, multiple rollback statements, and statements written using external rollback files.

Single rollback statement example

This is an example of how you would create a rollback for creating a table by setting your rollback to drop the table, which is the reverse operation.

<changeSet id="2" author="your_name">
  <insert tableName="users">
    <column name="id" value="1"/>
    <column name="username" value="admin"/>
    <column name="email" value="admin@example.com"/>
  </insert>
  <rollback>
        DELETE FROM users WHERE id = 1;
    </rollback>
</changeSet>

Multiple rollback statements example

For complex changesets that require multiple rollback operations, you can specify multiple rollback elements.

Understanding rollback order is important:

  • Within a single changeset:

    Rollback statements execute from top to bottom, in the order they appear.

  • Across a changelog file with multiple changesets:

    Changesets roll back from bottom to top—starting with the most recently deployed changeset and working backward to the oldest.

For example, if you deploy changesets 1, 2, and 3 in that order, a rollback operation processes them as 3, 2, 1.

Important: When writing multiple rollback statements within a changeset, list them in the order they should execute. In the example below, the foreign key constraint must be dropped before the table can be dropped, so the constraint rollback appears first.

<changeSet id="3" author="your_name">
  <createTable tableName="user_roles">
    <column name="id" type="int">
      <constraints primaryKey="true"/>
    </column>
    <column name="user_id" type="int"/>
    <column name="role_name" type="varchar(50)"/>
  </createTable>
  <addForeignKeyConstraint baseTableName="user_roles" 
                             baseColumnNames="user_id"
                             referencedTableName="users" 
                             referencedColumnNames="id"
                             constraintName="fk_user"/>
  <rollback>
        ALTER TABLE user_roles DROP CONSTRAINT fk_user;
        DROP TABLE user_roles;
    </rollback>
</changeSet>

Using external rollback files

For complex rollback logic like stored procedures, you can reference external SQL rollback script files using sqlFile. This approach is beneficial when rollback logic is extensive or when you want to keep rollback scripts organized in separate files.

In this example, you would replace my_path/my_rollback.sql with the path to a SQL file containing your rollback instructions for that changeset. 

<changeSet id="4" author="your_name">
  <sql>DROP PROCEDURE hello_world;</sql>
  <rollback>
    <sqlFile path="my_path/my_rollback.sql"/>
  </rollback>
</changeSet>

4

Test your rollback strategy

Before deploying to production, it's essential to verify that your rollback statements function correctly.

For this example, we are using a createTable changeset that Liquibase will automatically roll back. The changelog file containing your changesets is specified in your liquibase.properties file. We will use the rollback command. This command requires that you create a tag for a database state that you can rollback to. Alternatively, you can use rollback-count to roll back a specific number of changesets or rollback-to-date to roll back to a particular date.

These commands will be executed based on the changelog file you have specified in your liquibase.properties file. To specify a different file, use the --changelog-file flag. For example, liquibase tag baseline --changelog-file=your_file.xml where your_file.xml is the name of an XML, YAML, or JSON file with changesets.

1. Tag the current state

This marks your baseline database state as a point from which you can roll back before making any changes.

Example code

liquibase tag baseline

Example output

... Liquibase command 'tag' was executed successfully.

2. Apply the changeset

This creates the users table with the specified columns and constraints.

Example code

liquibase update

Example output

... Running Changeset: users-table.xml::1::your_name Liquibase: Update has been successful. Rows affected: 1 ... Liquibase command 'update' was executed successfully.

3. Preview what rollback would do

This shows you the exact rollback SQL that Liquibase will automatically generate to return to the baseline state.

Example code

liquibase rollback-sql baseline

Example output

-- Rolling Back ChangeSet: example-changelog.xml::1::your_name DROP TABLE public.users;

DELETE FROM public.databasechangelog WHERE ID = '1' AND AUTHOR = 'your_name' AND FILENAME = 'example-changelog.json';

-- Release Database Lock UPDATE public.databasechangeloglock SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1; ... Liquibase command 'rollback-sql' was executed successfully.

4. Execute the rollback

This drops the users table, returning your database to the baseline state before any changes were applied.

Example code

liquibase rollback baseline

Example output

... Rolling Back Changeset: users-table.xml::1::your_name ... Liquibase command 'rollback' was executed successfully.

5. Verify the rollback

Check your database to ensure your rollback worked as expected.