Correcting SQL Code Mistakes with Liquibase

No one is perfect. SQL code mistakes happen. Let’s say you just checked your database code into source control. The CI/CD automation job ran a Liquibase update to deploy the code to the development database… and then you realize that there was a mistake in your code. How do you fix the mistake? First, let’s do a quick review of how Liquibase works and we’ll break down your options for correcting the bad change.

How Liquibase works

For each change that is applied to the database, Liquibase inserts a record in the DATABASECHANGELOG table. Each change is uniquely identified by the ID+Author+Filename. The record also contains the checksum of the changeset. Any modification of the changeset results in a different checksum.

Each time an update runs, Liquibase checks to see if the changeset has been applied to the database. If the it has been applied, Liquibase calculates the checksum of the changeset and determines if it is the same as the stored checksum. If the checksums are the same, the changeset is ignored. If the checksums are different, it raises an error. This ensures that a changeset only gets applied once and that one that’s already been applied has not been modified.

Rerunnable vs. non-rerunnable changesets

Many database changes can only be run once because if they are rerun, they will throw errors and fail. If you try to execute a CREATE TABLE statement more than once, for example, it will fail. However, there are other changes that can be rerun. An example of a rerunnable change is CREATE OR REPLACE VIEW. Your approach to fixing database code will vary depending upon whether the change is rerunnable.

Fixing rerunnable changesets

Liquibase makes fixing a rerunnable changeset easy. Simply update the code and have Liquibase reapply the changeset. The key is applying the runOnChange attribute to the changeset to let Liquibase know that it is safe to rerun. Here’s an example:

--changeset your.name:changeset123 runOnChange:”true”
CREATE or REPLACE VIEW . . .

Fixing non-rerunnable changesets

You have two choices when it comes to correcting a non-rerunnable change:

1. Create a new changeset to modify the database object.
2. Rollback the changeset, correct the code, then reapply the changeset.

The safest approach is the first option: Create a new changeset to modify the database object. In fact, for changes that have already been deployed to Production, this is probably the only viable option. You don’t want to be the developer who dropped the CUSTOMERS table on Production in order to add a new column to the table.

However, there are circumstances where rolling back and correcting the changeset is a reasonable option. For example, you are creating a new table and it has only been deployed to the Dev environment. You want the first deployment to Prod to be a single CREATE TABLE statement instead of a CREATE TABLE statement followed by ALTER TABLE statements. In this case, dropping the table in Dev and recreating it in the new format is a reasonable solution. Just remember that you are responsible for ensuring that the changeset has only been deployed to non-Prod environments and that the rollback will not disrupt other activities such as UAT.

To correct a non-rerunnable changeset, start by defining a rollback for the changeset:

--changeset your.name:changeset456
CREATE TABLE . . .
--rollback DROP TABLE . . .

Next, rollback the changeset from all the environments where it was applied. The rollback will remove the associated changeset records from the DATABASECHANGELOG table so that Liquibase can later reapply the changeset and there won’t be any checksum errors.

There are a number of ways to perform a Liquibase rollback. See the Liquibase documentation for a complete list of rollback commands. If you have a Liquibase Pro license, you can use the rollbackOneChangeSet command to selectively remove a single change. (Try it for free with a Pro trial.) Finally, modify the changeset as needed and run Liquibase update on all the environments where the rollback was performed. The update will apply the modified changeset to the database and insert a row in the DATABASECHANGELOG table with the new checksum.

Summing it up

Mistakes happen. Fortunately, Liquibase gives you the tools you need to easily correct them. If you have questions or run into issues, be sure to check out our forum or our chat room. If you’re looking for more dedicated support options, consider starting a free trial of Liquibase Pro to try out our support (and more advanced features).