Applies to:
- Liquibase Pro
- Liquibase Open Source (Community)
Summary:
Rollbacks are essential in undoing changesets that have been deployed to the database. XML changelogs automatically provide a rollback, but formatted SQL does not automatically create an auto rollback, which must be defined. Therefore, it is strongly recommended that a rollback be included for all changesets.
To write a successful rollback, write the opposite statement for the rollback for what is in the changeset. For example, if the changeset is a CREATE statement, the rollback will be the DROP statement. Below is a list of example changesets with the accompanying rollback defined.
The rollback is typically defined in the SQL formatted file but can be defined in an XML changelog and an external file. It is recommended to write the rollback statement in an external file if it changes periodically or needs to be stored in source control management software.
Environment Conditions:
- Any version of Liquibase Community or Pro
How to:
In Liquibase formatted SQL files, rollbacks are defined as -- rollback {SQL_STATMENT}
. Below are examples of defining a rollback inside a Liquibase formatted SQL file.
For XML changelogs, a tag can reference either the query written in the XML changelog file itself or an external file with the rollback.
CREATE example
-- changeset liquibase:1
CREATE TABLE person (
id int primary key,
name varchar(50) notnull
);
-- rollback DROP TABLE person
INSERT example
-- changeset liquibase:2
INSERT INTO person (id, name) VALUES ('1', 'liquibase');
-- rollback DELETE FROM person WHERE id = 1
ALTER example
-- changeset liquibase:3
ALTER TABLE person ADD COLUMN address varchar(10);
-- rollback ALTER TABLE person DROP COLUMN address
PROCEDURE example
-- changeset liquibase:4
CREATE PROCEDURE person_insert(id NUMERIC, fname CHARACTER)
LANGUAGE'plpgsql'
as $$
BEGIN
INSERT INTO person (id, name) VALUES (id, fname)
commit;
END;
$$
-- rollback DROP PROCEDURE IF EXISTS person_insert
External rollback file example
<changeSet author="liquibase" id="5">
...
<rollback>
<sqlFile
path="filepath/file.sql"
relativeToChangelogFile="true"
splitStatements="true"/>
</rollback>
</changeSet>
No rollback-defined example
There are instances where a changeset doesn't need to have a rollback defined. For example, creating a changeset for a SELECT statement to appear in the log when deploying from Liquibase does not need a rollback defined.
-- changeset liquibase:6 runOnChange:true runAlways:true runWith:psql
SELECT * FROM DATABASECHANGELOG;
-- rollback not required
When performing a Liquibase rollback command involving this changeset, nothing will get executed on the database when rolling back this changeset. However, the entry in the DATABASECHANGELOG table will be removed.
Related Production Doc(s):
Liquibase Rollback Workflow
Rollback Command (has more examples)
Comments
0 comments
Article is closed for comments.