Reverting the table DDL with new changes

General Discussion
1

Hi here is my situation.
We are using liquibase as our automated deploy for ddl’s.

Day1 - A development team asks for a new table to be created.
Day2 - Data Architect creates it and reviews and sends the ddl to be implemented.
Day3 - Application team adds the ddl to their code and it adds to the changeset and appends into the sql file.
Day4 - Some modifications are needed like column name changes, datatype changes, lengths or dates vs timestamps. They are made in the data model and sent as a new ddl with create steps to the developer.
Day5 - Development team asks for alters instead of new create DDL’s.

So instead of having so many changes in the initial ddl create, can there be a way to over ride the ddl created on Day1 and repoint to Day5 as the base code?
If so please let me know how to do that. I don’t know all the api’s so am learning with this as well.
Thank you,
Sree

2

I can think of many ways to handle this scenario, and it really depends on whether you need to retain the data in the table. But if you don’t need the data here’s what I would do:

  1. Using the existing changeset, remove the table using the Liquibase “rollback” command.
  2. Put new DDL into the existing changeset and run the Liquibase “update” command to create the table with the new structure.
3

Thank you for the reply. I ran this by my DBA and forgot to give the context. We are doing this on AWS postgres.
Also he mentioned this -
There is no rollback, we only roll forward.
Once a changeset has been created and deployed you CAN NOT go back and edit it. Any future updates need to be in a new changeset…

So I was under the impression that rollback is always available as that is what Liquibase is used for. So any one has any comments or would think this is questionable?

4

Hi @database_architect

Can you drop the existing table and recreate one with required structure using a new changeset? I believe this will only be helpful in case you do not have any data in the table.

Please let us know if this could be done?

Thanks!
Rakhi Agrawal

5

@database_architect I’m not really sure why your DBA would tell you that you can’t modify a changeset, but if that is the case then you already have your answer… Any changes to the table structure need to be included as ALTERs in a new changeset.

But on the topic of rollback… Your DBA may be confusing “Postgres rollback” and “Liquibase rollback”, which are two completely different concepts. It doesn’t matter if you are using AWS or not.

A Liquibase rollback uses Liquibase functionality to revert the changes in your changeset. I would suggest that whether to use Liquibase rollback or not should not be a DBA decision since it is only using Liquibase functionality.

1 Like
6

He mentioned that liquibase rollback would work only if changesets are in xml format. We use sql format.

7

That is not true. Liquibase rollback works with all formats. With sql format you will need to specify the rollback instructions, like this:

–liquibase formatted sql
–changeset author:create-table
create table test (col1 number);

–rollback drop table test;