How does Flyway undo behavior work?

vinceggwp

I've read the documentation on the Flyway undo, and that there are two ways to go about it. One is to provide the SQL file that does the undo you want and the other is a rollback Flyway does to the latest version that was applied.

My question is on the behavior of the second. If I run flyway undo, does it automatically generate SQL statements that will undo what was previously applied automatically? It knows how to undo whatever was done in the latest version in history? Database we're using is Postgresql if that helps with context.

vinceggwp

It seems I have missed a part of the explanation in the tutorial, https://flywaydb.org/documentation/tutorials/undo, and that there only way one to do it, not two. By providing the SQL file to do an undo for the specific version, does it become undoable as shown in the info table

+-----------+---------+---------------------+------+---------------------+---------+----------+
| Category  | Version | Description         | Type | Installed On        | State   | Undoable |
+-----------+---------+---------------------+------+---------------------+---------+----------+
| Versioned | 1       | Create person table | SQL  | 2017-12-17 19:57:28 | Success | No       |
| Versioned | 2       | Add people          | SQL  | 2017-12-17 20:01:13 | Success | No       |
+-----------+---------+---------------------+------+---------------------+---------+----------+

I misunderstood that somehow flyway could automatically do undo for you without you writing out a sql file but that is not the case it seems?

Peter_Laws

Hi vinceggwp,

I suspect what you're thinking of is actually an automatic transaction rollback, but it will look a lot like an undo.
This behaviour is db specific, SQL Server supports it automatically, assuming you're performing a migration on code that can be encapsulated in a transaction. Whereas Oracle can't rollback due to implicit commits. You said you're using PG, it won't do it automatically, reading https://www.postgresql.org/docs/current/tutorial-transactions.html I'm not sure it's possible to do it automatically regardless of flyway. As such, if you flyway migration fails, it will display as failed in the schema history and will require you to run the repair command.