Will liquibase auto generate rollback scripts for SQL file types?

General Discussion
1

I use Liquibase community 4.2.2 with PostgreSQL and all my database scripts that I deploy using liquibase are formatted SQL type files.

Eg :

–liquibase formatted sql
–changeset dinesh:facets_config_1

INSERT INTO public.facets_config_facet_fields_config VALUES (81922, 32776, 2);
INSERT INTO public.facets_config_facet_fields_config VALUES (81923, 655367, 2);
INSERT INTO public.facets_config_facet_fields_config VALUES (81923, 655368, 2);
INSERT INTO public.facets_config_facet_fields_config VALUES (81923, 655366, 2);

… and so on.

We usually have INSERT scripts inside the SQL files and we cannot explicitly mention rollback statements for each and every insert scripts since sometimes the number of insert scripts could be even more than 100+.

In this case if I use liquibase , Deploy the scripts and in case rollback to the previous tag, will liquibase auto generate the required rollback statements for the Insert Scripts ?

1 Like
2

According to documentaiton:

Liquibase cannot auto-generate rollbacks for SQL changelogs

So, formatted sql as I understand it won’t autogenerate a rollback.

3

@ronak This means that I have to write a delete statement for each Insert statement to Rollback ? :exploding_head:
Then what about the runInTransaction attribute for the changesets. I am not explicitly setting it. But according to docs by default this will run changeset as a transaction right ? So in case one of my INSERT command fails, will it not fail the transaction and revert the changes automatically ?

Docs for runInTransaction:
https://docs.liquibase.com/concepts/basic/sql-format.html

4

@Dinesh ,

Liquibase is a change management automation tool for the structure of your database. Its not a migration or data handling tool, I think that is why you are having difficulty, because you are working against a paradigm Liquibase leaves to other platform specific tools that have already handled data loading.

Although you can, Liquibase is not meant for bulk insert and rollback of data related rows. You could handle data rollback through your CI/CD orchestration as a step outside liquibase update.

Here’s an example if you are modifying and loading data into a table called someTable:

//Some pipeline script:
...
//pre update prep, do table data load or back up table with data
...
copy someTable to someTableOLD
...
liquibase update
...
if migration is success
   delete someTableOLD
else
   liquibase rollback (for the ddl changes you want to rollback)
   delete someTable //may be handled by the liquibase update depending on your rollback there
   rename someTableOLD to someTable
...
2 Likes