rollback feature

[qqcvd]

Hello

Thank you for your very usefull tool. Do you plan to add some rollback feature ?

When we install patches it happens that we need to rollback it. Here is my idea : Let's say that for all Vxxx script, it exists a RB_Vxxx script. Let's add a "Intallation_tag" field in the CHANGE_HISTORY table which would be filled by a new variable "Intallation_tag" if provided in the command line. So, if I ask snowchange to do a rollback on a specific "Intallation_tag", snowchange knows the list of all sql scripts installed during this installation_tag. Thus it can execute the rollback scripts in the inverse order.

[sfc-gh-jhansen]

Thanks for the suggestion @qqcvd! Undo (or rollback) migrations are tricky. And while tools like Flyway to do support them, they usually come with a lot of cautions (see Undo Migrations in the Flyway docs). Honestly I haven't spent much time thinking about the best approach with Snowflake.

What specific use cases do you have in mind? And how would you propose we deal with the issues raised in the Flyway docs?

[qqcvd]

I have 2 use case :

• When, the SQL delivery is part of an bigger delivery including many other application, it happen that , becasue of a problem on another application, you have to rollback all the patches. Yhus you will have to rollback your SQL patch (which was in a successful install state)

• If your installation is in a error state, you have to rollback it. In the pas, while working on SQL server, I could write the rollback command for each installation command. For exemple, in Snowflake, you can use the "IF EXIST" condition in some case... On SQL server, it was possible to test the existence of a fiel before create or delete it.... in Snowflake, it is not possible... that's the point.... :-(

[sfc-gh-jhansen]

Sorry for the super slow follow-up here @qqcvd. The rollback feature is one that I hear about somewhat frequently. If you'd be willing to code it up and submit a PR I'd be happy to review. And if you do, please copy the naming convention and other high level semantics from Flyway.

[iroche]

does anybody have examples of how the do rollback when using schemachange?

[danipisca07]

The way I did it is to add another folder (for example ./rollback instead of ./migrations) and I've put a file A__rollback.sql in it with a rollback instruction, example: create or replace schema demo clone demo at(offset => -{{ ROLLBACK_SECONDS }}); Then when I want to rollback a database I use schemachange with this folder providing the amount of seconds I want to go back.

If you want to do something more advanced you can use a python script to save "checkpoints" in time and when you want to restore you use this checkpoints to calculate the amount of "ROLLBACK_SECONDS". Anyway keep in mind that ROLLBACK_SECONDS must always be lower than the retention period that is set on snowflake. Not exactly a solution but can be a workaround in some cases...

[eaguilera23]

Hi guys. I'm looking into opening a PR for this functionality. I'd like to clarify on its design before jumping into it.

I have checked out the Flyway solution and seems that the way to go is to add U migrations which follow the same versioning as the V ones. This means only versioned migrations would be affected for the rollback.

Having the example of:

• V1.0.0__new_migration.sql
• U1.0.0__new_migration.sql

when running schemachange undo, with a default --step 1 the application will:

1. Fetch all V migrations
2. Fetch all U migrations
3. Get U migration of last V migration. Question: If it doesn't exist, crash or continue?
4. Run U migration
5. Delete V migration related to U

Having the first iteration of this feature only implementing the --step <n> option, for a future development of the --to <version>.

Is this something you would feel comfortable merging to the root project @sfc-gh-jhansen ?

[markfickett]

I would also like a rollback or downgrade feature. I typically use Alembic, and make use of a rollback feature during development, so that I can re-test the upgrade path (which is often more complex, adding tables etc) from a dev database with nontrivial starting contents. Alembic handles this with separate upgrade and downgrade Python functions within each version file. I think the proposed separate files here sound like a good approach.

[vinamra28]

@sfc-gh-jhansen, is the rollback feature live?

[sunando94]

The PRs which was raised was closed. We would really like to have this rollback feature. Please do let us know if roll back will be an feature to the schemachange?