Implement a rollback strategy with SQL changelogs

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.

Be sure to:

• Replace your_name with your name.

• Replace unique_id with a unique_id.

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.

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.

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.

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

1. Check your database to ensure your rollback worked as expected.

2. Check your DATABASECHANGELOGHISTORY table, which was created in your database, to view the rollback history.