How to rollback stored procedure using SQL format

We are using primarily SQL-formatted changeset and scripting out the rollback statement.
When we rollback a script that has the create/replace support, it does not rollback the previous stored procedure.

The XML-formatted changeset seems to support a changesetid attribute. Is this supported in the SQL-format.
What are other alternatives can we use?

Thanks.

Rollback to a changeset ID is only supported in the XML format.

If you want to rollback to the previous version of the stored procedure, then you would include that prior code version in the --rollback for the changeset.

Otherwise you could use the following if rollback is not required for the code:
–rollback not required

@daryldoak thanks for the reply.

When you refer to including the prior code version, are you referring to a multi-line rollback like this?

– rollback create or replace storedprocedure spname
– rollback begin
– rollback
– rollback …
– rollback end;

If the stored procedure is large, then I will have all this lines of code for rollback purpose only.
This seems to be inefficient. I wonder if anyone has the same problem or do most Liquibase use the XML format?

I do need to have a rollback so it is not an option for us to not have a rollback.

Yes, a multi-line rollback like your example. If you think that is too inefficent then I would recommend using the XML format.

Thanks, @daryldoak.

Do you know if this is a feature that will be supported in the future?

hello,

you can do it in this way :
1- create an sql changelog:
DROP PROCEDURE nameofprocedure;
2- execute the changelog

best practice is to do rollback separatelly and create a changelog for each rollback.
you can do multiple rollback bu using xml one that includes different rollback changelogs.

i hope this may help you

@leovic0101 I do not know if rollback to changeset ID will be supported by the SQL format in the future, but I kind of doubt it. There are a several Liquibase features that are unique to the XML/YAML/JSON formats.

@ahmedabdeljelil
Thank you for your reply.

How is that actually going to work? If I understand correctly, creating a separate changelog for rollback looks like an added overhead to manage. Say for example, if I have I have 3 successful deployment for SP:

  1. SP v1.0
  2. SP v2.0
  3. SP v3.0

If I want to rollback v2.0, using a different changelog, I will have have to make sure that I don’t invoke the rollback for v1.0. Am I understanding it correctly?

Thanks.

Thanks for the info, @daryldoak .
From what I am understanding, I think a hybrid model would probably be an appropriate solution where, components such as SP would be best implemented using XML while those that can have discreet implementation like DMLs or Tables can use SQL format.

1 Like

hello @leovic0101 ,

i mean you can create for example 3 sql files dedicated for rollback.

then create a changelog xml file that will call the required files for rollback.

example: in this case i have 3 sql scripts one.sql, two.sql and three.sql

with this changelog file rollback.xml, i’m going to execute only one and two rollback scripts

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">

    <include file="one.sql"/>
    <include file="two.sql"/>
</databaseChangeLog>

Thanks, @ahmedabdeljelil.

How do you filter out which script to run in the CLI?

I used labels but that is the only way I know.
Also, please keep in mind that I am implementing this via CI/CD pipeline during a rollback scenario.

@leovic0101
You can’t filter which script to run, but you can filter what changesets will run using a liquibase context.

https://docs.liquibase.com/concepts/advanced/contexts.html

Thanks, @daryldoak
Yes, contexts is also an option.