Rollback programmatically

I want to make a cmd command to rollback database migrations. To update them I do

 if (args[0].equals("run")) {
            ConfigurableApplicationContext ctx = SpringApplication.run(MigrationsApplication.class, args);
            int exitCode = SpringApplication.exit(ctx, new ExitCodeGenerator() {
                @Override
                public int getExitCode() {
                    // no errors
                    return 0;
                }
            });
            System.exit(exitCode);
}

Is there anyway I can programmatically rollback them?

Hi @fabiopires10 ,

You can find all the details in this page:
Rolling back Changesets | Liquibase Docs

Also, if does not answer your question, could you please elaborate what problem are you having and what exact command are you executing to get the rollback on the changeset.

Thanks!

Best.
@aditi

I want to do a rollback trought a command, like for example “java -jar Myjar.jar run”. To be able to do this I need to have a way of rolling back programmatically

Hi @fabiopires10

You can use Liquibase Java API directly. You can check for different liquibase.rollback methods with Java API, which could be used for programmatic rollback.

Have a look at this blog to read about liquibase.update method with Liquibase Java API.

Refer the question on SO here for an idea to implement rollback.

Hope this helps!

Thanks!

In the rollback method, what is the context?

Contexts in Liquibase are expressions you can add to changeset s to control which will be executed in any particular migration run. Any string can be used for the context name and they are checked case-insensitively.

Read more about it here.

So basically, if you have set property context="dev" as below:

<changeSet id="1" author="bob" context="dev">
        <createTable tableName="department">
           <column name="id" type="int">
           <constraints primaryKey="true"/>
           </column>
           <column name="dept" type="varchar(${dep.size})">
           <constraints nullable="false"/>
           </column>
           <column name="emp_id" type="int">
           <constraints nullable="false"/>
         </column>
       </createTable>
 </changeSet>

and let’s assume you have 2 DB - dev and prod. And out of multiple changesets you want only one specific changeset to run while you are running liuibase update for Dev DB.
while running liquibase (explaining through commandline method):
liquibase --contexts="dev" update

context property will make sure that changesets with context value “dev” will only be executed on Dev DB. Liquibase will not execute the remaining changesets.

If you try and run liquibase update for Prod DB, the changeset with context="dev" will not be executed there and will be skipped.

Hope you got the concept and use of “contexts”. Please let us know in case of doubts.

P.S : Contexts could be any string. It could be your name as well. context “dev” here is just an example.

Thanks!
Rakhi Agrawal

If we talk about implementing it through Java API,
you can do something like this:

private static final Contexts contexts = new Contexts("dev");
private static final String rollbackTag = "TagA";
liquibase.rollback(rollbackTag, contexts);

This will only rollback the changesets with context="dev".

P.S : You can also read a list of contexts from some file or take user input as per requirement.

Hope this helps!

Thanks!
Rakhi Agrawal

So if I don’t want any contexts I should use null?

yes in that case, you should just use null as below:

liquibase.rollback(rollbackTag, null);

Hopefully this should work. Have never used null earlier as using contexts is a good and safe practice.

Please let us know the results once you try this.

Thanks!
Rakhi Agrawal

Using null throws a NullPointerException

ohh… May be try using :

liquibase.rollback(rollbackTag, "");

Just an empty string.

Thanks!

I tried the update method but for some reason it isn’t making my changes

   java.sql.Connection connection = null; //your openConnection logic here
                try {
                    connection = openConnection(dbUrl,dbUsername,dbPassword);
                    Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connection));
                    Liquibase liquibase = new liquibase.Liquibase(changelog, new ClassLoaderResourceAccessor(), database);
                    liquibase.update("");
                } catch (SQLException | IOException | LiquibaseException e) {
                   logger.error(e.getMessage());

                }

This is one of the files

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
  <changeSet author="" id="ff604b8b-fdf7-49af-b84d-3621eaf0ac42" context="">
    <customChange class="com.petapilot.migrations.change.multipleTableChanges.MultipleAdd" suffix="transactions"
                  columnName="GORILA" columnType="varchar(10)"></customChange>
  </changeSet>
</databaseChangeLog>

and this is the message I get

jun. 18, 2021 11:38:06 DA MANH├ liquibase.changelog
INFO: Reading from `saft-demo-dump-cmd`.DATABASECHANGELOG
jun. 18, 2021 11:38:06 DA MANH├ liquibase.lockservice
INFO: Successfully released change log lock
Do you want to see this operation's report in Liquibase Hub, which improves team collaboration?
If so, enter your email. If not, enter [N] to no longer be prompted, or [S] to skip for now, but ask again next time (default "S"):

jun. 18, 2021 11:38:11 DA MANH├ liquibase.lockservice
INFO: Successfully acquired change log lock
Skipping auto-registration
jun. 18, 2021 11:38:11 DA MANH├ liquibase.hub
WARNING: Skipping auto-registration
jun. 18, 2021 11:38:11 DA MANH├ liquibase.lockservice
INFO: Successfully released change log lock

Actually I tried running in Intellij and executed all the migrar íons , even the ones that didn’t have the same context.
In my jar file doesn’t execute any migration

Instead of directly going with customChange, let’s first target normal SQL script.

In my case, when I tried this:

<changeSet author="rakhi" id="test" dbms="${dbType}">
		<sql endDelimiter=";" splitStatements="true" stripComments="true">
			CREATE TABLE message
			(
			   id1 INT NOT NULL,
			   message1 text NOT NULL
			)
			;
		</sql>
	</changeSet>

(note the contexts attribute is not set)

and tried with the similar Java code you provided:

java.sql.Connection connection = null; //your openConnection logic here
                try {
                    connection = openConnection(dbUrl,dbUsername,dbPassword);
                    Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connection));
                    Liquibase liquibase = new liquibase.Liquibase(changelog, new ClassLoaderResourceAccessor(), database);
                    liquibase.update("");
                } catch (SQLException | IOException | LiquibaseException e) {
                   logger.error(e.getMessage());

                }

Liquibase executed the changeset and created a table in DB for me. Pasting partial console statements below:

[DEBUG] 2021-06-18 16:34:30.487 [main] ChangeSet - Reading ChangeSet: com/db/liquibase/changelog/prd.db.changelog-master.xml::test::auth
[DEBUG] 2021-06-18 16:34:30.488 [main] PostgresDatabase - Executing Statement: CREATE TABLE message
			(
			   id1 INT NOT NULL,
			   message1 text NOT NULL
			)
[INFO ] 2021-06-18 16:34:30.488 [main] JdbcExecutor - Executing with the 'jdbc' executor
[INFO ] 2021-06-18 16:34:30.488 [main] JdbcExecutor - CREATE TABLE message
			(
			   id1 INT NOT NULL,
			   message1 text NOT NULL
			)
[DEBUG] 2021-06-18 16:34:30.519 [main] JdbcExecutor - 0 row(s) affected
[INFO ] 2021-06-18 16:34:30.519 [main] ChangeSet - Custom SQL executed
[INFO ] 2021-06-18 16:34:30.520 [main] ChangeSet - ChangeSet com/db/liquibase/changelog/prd.db.changelog-master.xml::test::auth ran successfully in 33ms
[INFO ] 2021-06-18 16:34:30.520 [main] JdbcExecutor - Executing with the 'jdbc' executor
[INFO ] 2021-06-18 16:34:30.521 [main] JdbcExecutor - Executing with the 'jdbc' executor
[INFO ] 2021-06-18 16:34:30.521 [main] JdbcExecutor - SELECT MAX(ORDEREXECUTED) FROM public.databasechangelog
[INFO ] 2021-06-18 16:34:30.522 [main] JdbcExecutor - INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('test', 'auth', 'com/db/liquibase/changelog/prd.db.changelog-master.xml', NOW(), 1, '8:90ae87c0dddab31a70358badc27f0ae9', 'sql', '', 'EXECUTED', NULL, NULL, '3.10.0', '4014270479')
[DEBUG] 2021-06-18 16:34:30.525 [main] JdbcExecutor - 1 row(s) affected
[DEBUG] 2021-06-18 16:34:30.526 [main] JdbcExecutor - Release Database Lock
[INFO ] 2021-06-18 16:34:30.526 [main] JdbcExecutor - Executing with the 'jdbc' executor
[DEBUG] 2021-06-18 16:34:30.526 [main] JdbcExecutor - UPDATE public.databasechangeloglock SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1
[INFO ] 2021-06-18 16:34:30.527 [main] StandardLockService - Successfully released change log lock
[WARN ] 2021-06-18 16:34:30.528 [main] PostgresDatabase - Failed to restore the auto commit to true
[WARN ] 2021-06-18 16:34:30.528 [main] DatabaseGenerator - Unable to close the liquibase/database/connection resource.
[INFO ] 2021-06-18 16:34:30.528 [main] DatabaseGenerator - [DatabaseGenerator][main] end

Tried that but it didn’t work

Not able to get the exact issue here. If it’s working for me it should be working for you too :slight_smile:

FYI: Liquibae jar I’m using is liquibase-core-3.10.0.jar

May be just try starting all over with simple SQL query first.

  1. Try executing the changeset with CLI first.
  2. If point 1 works, then go for Java API implementation (programmatic way)

I am using 4.3.1.Maybe it’s because the version difference?

It’s just a matter of jar right? Can you try with the one I am using and found working?

Will help us confirming the root cause and report an issue if it is actually a one.

In the jar it doesn’t execute any of the migrations
In intelliJ it executes all of them even the ones that don’t have the same context

downgraded the version to 3.10.0 and for some reason now it doesn’t find my changelog file