How do I use Flyway so that if a table gets mistakenly dropped, on running the application again, the deleted table gets created?

Question

I have a requirement where if a table of a DB gets mistakenly dropped, we need it back, with or without the data. We already use Flyway for migration, is there any way we can achieve this using Flyway or otherwise?

Answer 1

I think you could hack a solution in place using callbacks (SQL or Java) but you've got to ask how can a table get deleted if you are using flyway to control migrations and amendments to your database in the first place.

This is fundamentally what flyway is intended to prevent as the following snippet from the flyway FAQ confirms and the solution may be to close the possibility of external amendments being applied in the first place.

Can I make structure changes to the DB outside of Flyway?

No. One of the prerequisites for being able to rely on the metadata in the database and having reliable migrations is that ALL database changes are made by Flyway. No exceptions. The price for this reliability is discipline. Ad hoc changes have no room here as they will literally sabotage your confidence. Even simple things like adding an index can trip over a migration if it has already been added manually before.

Answer 2

It seems not to be possible with versioned migrations, since they are applied only once, or with repeatable migrations, because they are reapplied only if check sum changes.

Another option - is to create a callback, which will run after migration.

For example, afterMigrate callback could do it, you just need to create a script named afterMigrate.sql in the location, used to load migrations. Now you just need to make a SQL-script to recreate some table if it not exists.

Some vendors support such an options, for example, with PostgreSQL you can use CREATE TABLE query with the IF NOT EXISTS option, to create a table only it doesn't exists.