Rollback

[jmoont]

Thank you for this great tool.
I can't see a way in your documentation to rollback to a particular point - is that available or something you are adding?
Thanks,
Josh

[victorstanciu]

Hello Josh,

I'm really glad you like the tool. Unfortunately, no, I don't think I will be implementing rollbacks anytime soon. Having a rollback feature would involve having to actually analyze the differences between two schema objects, while this tool is, at its core, nothing more than an SQL script manager. Also, since there is no restriction on what your revision scripts contain (you could write a revision script that inserts records in a table, without altering the schema in any way), it would mean that the app would also have to able to also generate diffs of the actual data inside your database, which is even harder to do, especially if you have tons of records.

[jmoont]

Would it not be possible as you write the revision scripts you write a rollback script that undoes what you are doing. Then if we know where the user is and they want to rollback to a particular version it runs those scripts in reverse.
It obviously wouldn't be able to take into account data but for schema it would work really well.

[victorstanciu]

Yes, flagging rollback scripts as such and running them backwards would probably work, at least from a technical point of view. The more elegant method would be to save snapshots for each schema object involved in a revision script, and then analyze the difference between the snapshots and the current version of a schema object. Writing about it here and thinking about it, the feature starts to seem more and more appealing to me, even though it would complicate each DBMS adapter greatly.
I've added this issue to the 2.0 milestone, thank you very much for suggesting it!

[jmoont]

Cool - looking forward to it!!

[feketegy]

We have a very similar tool (almost exactly the same) and we have a rollback feature too :)

How we did it is that we capture the time stamp of each new schema addition and we only allow upgrade / downgrade linearly either forward or backward in time.

Each developer must provide an upgrade SQL statement and a downgrade one. So each commit will have 2 SQL statements.

For ex. we have 4 commits:

Commit 1
Commit 2
Commit 3
Commit 4

Developer A is at commit 2, therefore he can only upgrade to commit 3 or downgrade to commit 1.

He cannot upgrade from commit 2 directly to commit 4 without running commit 3. Although we have an 'auto upgrade to current' feature, which runs each commit up until the current commit.

Same with downgrades.

We're using our tool for almost a year now, and I can say from experience that the downgrade / rollback was used very rarely, although it's a nice feature to have.

[victorstanciu]

That's cool, so it's pretty much the same approach that @jmoont suggested. Isn't creating two SQL scripts for every database change a hassle? The flow must be something like this: first you write the upgrade script, then you remove / undo every change you made, save the resulting script, then run the upgrade script again on your own database. Am I right?

[feketegy]

Something like that, but the SQL scripts are very granular. We have a rule that each SQL upgrade must contain only 1 SQL statement.

Creating both scripts are easy, especially once you created the upgrade script. Usually it takes less than 5 minute for each commit...

Most of the work are usually when we begin a project, and at the schema design phases. Later on in the project development when the schema is designed, the tool is used more rarely...

[agarrharr]

I'm excited about this feature! Would the rollback feature also work for when you're switching between branches in git? And would you have to write 2 SQL statements by hand each time to be able to rollback?