- Task
- Version · 6.0.2
- Create
Add rollback support to raw SQL scripts
Last updated: November 17, 2026
Add --rollbackSQL markers to a raw SQL script so that the changelog decorate-sql generates from it can be rolled back. Without a marker, a generated changeset has no rollback, because Liquibase cannot automatically invert raw SQL.
Before you begin
Liquibase Secure must be installed, with your license key applied. See Apply your license key.
To test the rollback, Liquibase needs a connection to a test database. Set
url,username, andpasswordinliquibase.properties, asLIQUIBASE_COMMAND_*environment variables, or with--url,--username, and--password. See Connect Liquibase to your database.You need a raw SQL script written for Oracle or SQL Server.
If your tool writes a Down script that reverses the whole Up script, use Decorate paired Up and Down migration scripts instead. Liquibase does not read
--rollbackSQLmarkers in that mode.
Procedure
Add a marker after each statement
To roll back a statement, add a --rollbackSQL comment on the line after it, with the rollback SQL on the same line. Each marker attaches to the statement immediately above it.
Each marker has four parts. For example, --rollbackSQL DROP TABLE orders;:
Part | In the example | Rule |
|---|---|---|
Dashes |
| Start the line with two dashes, like any SQL comment. A space after them is allowed, as in |
Keyword |
| The keyword is not case-sensitive, so |
Separator | The space after the keyword | Use whitespace, not a colon. A marker written as |
Rollback SQL |
| Liquibase stores this text as written and runs it on rollback, even when it is not SQL. It warns when the text does not start with a SQL keyword, so remove any marker that is a note rather than SQL. |
Keep these rules in mind as well:
Plain rollback comments. A comment such as
-- rollback DROP TABLE orders;is not a marker, because it has noSQLin the keyword. Raw SQL cannot tell it apart from an ordinary note, so Liquibase keeps it as a comment, captures no rollback from it, and prints a NOTE naming the line. A bare-- rollbackline does not start a multi-line rollback block. Any SQL after it deploys as a normal statement, and Liquibase prints a warning.Several statements on Oracle. On Oracle, a marker that holds more than one statement, such as
--rollbackSQL DELETE FROM orders; DELETE FROM customers;, becomes one rollback change per statement, so each statement runs on its own. A marker that contains a PL/SQL block stays whole.
Check markers for a formatted SQL changelog
This step is for you if you write a .sql changelog without --executor. Skip it if you write XML, YAML, or JSON, which store each rollback separately, or if you pass --executor, because sqlplus or sqlcmd then runs the changelog.
When you run rollback, Liquibase reads each rollback back out of the .sql file. Two things in a marker can make it read differently than you wrote it:
A string literal that ends in a backslash, such as
'C:\'orESCAPE '\', does not end at its closing quote when the changelog is read back.A statement followed by a comment with no
;before it loses its terminator to the comment.
Liquibase warns only when this changes how a rollback runs, for example when a ; follows the literal in the same marker, or another rollback follows in the same changeset. It still writes the changelog, and the warning names the line where each affected changeset starts. To avoid the warning:
Rewrite the literal. For example, use
'C:' || CHR(92)on Oracle or'C:' + CHAR(92)on SQL Server instead of'C:\', and useESCAPE '!'instead ofESCAPE '\', with!in place of\in theLIKEpattern.End each
--rollbackSQLstatement with;before any comment on the same line.
Run decorate-sql
If Liquibase prints a warning about a marker, fix the marker, then run the command again with --overwrite-output-file=true, because the changelog was already written. For example, a marker written with a colon, --rollbackSQL: DROP TABLE orders;, prints this warning:
If a marker has no statement above it, the run fails with LB-CHG-0040 and writes nothing. Move the marker below the statement it undoes, or remove it, then run the command again.
Check the rollbacks in the changelog
Open changelog.xml and check that each changeset has the rollback you expect, in a rollback element.
Liquibase groups back-to-back DML statements into one changeset. The two INSERT statements from step 1 become one changeset, and their rollbacks run in reverse order, so the second insert is undone first:
If you write a .sql changelog instead, each rollback is a -- rollback line.
Test the rollback
Run update-testing-rollback against a test database. It deploys the changelog, rolls it back, and deploys it again, so every rollback runs once.
liquibase update-testing-rollback --changelog-file=changelog.xmlThe rollbacks work if Liquibase rolls back every changeset, in reverse order, and the command ends successfully. The output includes:
If the test fails
The test stops at the first rollback that fails, and the changelog stays deployed on the test database. The error tells you what to fix:
LB-RLB-0001No inverse to liquibase.change.core.RawSQLChange created. The changeset has no rollback, because its statement has no--rollbackSQLmarker. Liquibase cannot automatically invert raw SQL. Add a marker after that statement in your script.LB-DBX-0004followed by a database error, such asORA-00950: invalid DROP option. The database rejected the rollback SQL.decorate-sqldoes not check that your SQL or your rollback SQL is valid, so a typo in a marker first shows up here. Fix the marker in your script.
Then test again:
Decorate the script again with
--overwrite-output-file=true.Undo the deploy from the failed test with
rollback-count. Set--countto the number of changesets in the changelog.Run
update-testing-rollbackagain.
If you skip rollback-count, the test reports success without rolling anything back, because the changesets are already deployed.