Implement a rollback strategy with SQL changelogs for existing databases

When you have generated SQL 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.

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:

NoDataDmlStatements
MultipleDropsNotAllowed
deleteWithoutWhere
ChangeDropColumnWarn
DetectChangeType
ChangeTruncateTableWarn
ChangeDropTableWarn
ModifyDataTypeWarn
MaxAffectedRowsAllowedDelete

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

Before you begin

Ensure you've installed Liquibase and can run liquibase from your command line.
Create a Liquibase project.
Generate your changelog from an existing database.
Connect your database to Liquibase. You'll need to follow the configuration guide specific to your database. You'll also create your first changelog during this process to test your database connection.
Set up your changelog structure.

Procedure

Review your generated changelog structure

Formatted SQL is a changelog format that allows you to write plain SQL while still getting the benefits of Liquibase's database change management. The --liquibase formatted sql header tells Liquibase to treat this file as a formatted SQL changelog, enabling it to track changes and provide rollback capabilities for your raw SQL statements. It also allows you to track who made changes and add a unique ID for each change. These identifiers appear in the DATABASECHANGELOGHISTORY table. See Liquibase Formatted SQL Guide to learn more about Formatted SQL.

For each changeset, you'll see the formatted sql header appear. This will include:

your_name — Auto-generated changeset metadata with your system username.
unique_id — Timestamp-based unique ID.

Your generated changelog will have the formatted SQL header and changesets that recreate your existing database.

--liquibase formatted sql
--changeset your_name:unique_id
CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100)
);


--changeset your_name:unique_id  
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  order_date DATE,
  total DECIMAL(10, 2)
);


--changeset your_name:unique_id
ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id);

Add rollback statements to your existing changesets

For each changeset in your formatted SQL 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.

--liquibase formatted sql
--changeset your_name:unique_id
CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100)
);


--rollback DROP TABLE users;

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 john:2
CREATE TABLE user_roles (
  id INT PRIMARY KEY,
  user_id INT,
  role_name VARCHAR(50)
);


ALTER TABLE user_roles ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users (id);


--rollback ALTER TABLE user_roles DROP CONSTRAINT fk_user;
--rollback DROP TABLE user_roles;

Using external rollback files

For complex rollback logic like stored procedures, you can reference external rollback script files using --rollbackSqlFile. 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 liquibase-user:1
DROP PROCEDURE hello_world;


--rollbackSqlFile path:my_path/my_rollback.sql

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 the creation of a single rollback statement from step 2, where we are dropping the users table that was created. We will use the `rollback` command. This command requires that you create a tag for a database state that you can roll back 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.sql where your_file is the name of a SQL file with changesets marked for rollback.

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.
Check your DATABASECHANGELOGHISTORY table, which was created in your database, to view the rollback history.