11

I am looking for a way to execute a hook script before and after migration. I have a bunch of views and stored procedures and would like the process to be:

  1. Drop all views and stored procedures.
  2. Run the migration.
  3. Rebuild views and stored procedures.

This insures that any change to the schema is reflected in related views and stored procedures. Steps (1) and (3) will be bash scripts.

Is this possible in Flyway?

Nathaniel Ford
  • 20,545
  • 20
  • 91
  • 102
Eli
  • 111
  • 1
  • 1
  • 3

3 Answers3

7

Update 2014-04-29: This is now Possible with Flyway 3.0 by implementing the FlywayCallback interface.

Previous answer

The short answer is: no, not at this point.

Here is the reason: I thought about this as well as I was laying down the initial design for Flyway. The more I thought about this aspect though, the more it became clear to me that these pre and post scripts are also an integral part of the migration, or at least something a migration can not do without if it wants to be successful. Therefore I would recommend to either:

  • Merge 1, 2 & 3 in a single migration
  • Have 3 separate migrations x.1 (drop views), x.2 (actual migration), x.3 (rebuild views)

You might even be able to have x.1 and x.3 call stored procedures that do the work for you to avoid code duplication between migrations if these steps are repeating.

Having Flyway take care of performing all changes to the database structure makes the whole thing more straightforward, avoiding a mix of different technologies.

jpmc26
  • 28,463
  • 14
  • 94
  • 146
Axel Fontaine
  • 34,542
  • 16
  • 106
  • 137
  • 1
    I was hoping to have this built in and controlled by Flyway configuration. Basically have a pre, migrate, and post directory structure with Flyway executing whatever is in pre then execute the stuff in migrate followed by the post stuff. I'm still very new at Flyway so I may find out my comment is rubbish. – Kuberchaun Mar 14 '12 at 20:58
  • I would also find this very useful, although I need to use sql instead of sh scripts. – orbfish May 09 '13 at 16:59
  • Still wishing you had something like this. There is always a set of SQL scripts I have to run manually after a migration to, for instance, point synonyms to the correct schema. – orbfish Jan 21 '14 at 18:27
  • I don't understand why you do not make your "point synonyms to schema" a flyway script? For unit testing I typically miss the option to truncate all tables. This would be a usecase for a post migration script (which cannot be a flyway script as it would not run if the schema is latest version). – eckes Apr 28 '14 at 20:57
6

To expand on Axel's response: callbacks with sql scripts simply means putting beforeMigrate.sql (for instance, this is one keyword among others) in the directory containing the migrations, and Flyway will execute beforeMigrate.sql before the other migration scripts. Even before schema_version gets locked.

Other callback names (e.g. afterMigrate) are listed in the documentation for callbacks.

Jacob van Lingen
  • 8,989
  • 7
  • 48
  • 78
elmotec
  • 1,398
  • 1
  • 13
  • 12
0

you should use flyway Callbacks like:

https://flywaydb.org/documentation/concepts/callbacks#beforeEachMigrate https://flywaydb.org/documentation/concepts/callbacks#beforeEachMigrate

see also : https://flywaydb.org/documentation/tutorials/callbacks.html

SQL Callbacks The most convenient way to hook into Flyway’s lifecycle is through SQL callbacks. These are simply sql files in the configured locations following a certain naming convention: the event name followed by the SQL migration suffix.

Using the default settings, Flyway looks in its default locations (<install_dir>/sql) for the Command-line tool) for SQL files like beforeMigrate.sql, beforeEachMigrate.sql, afterEachMigrate.sql, …

Placeholder replacement works just like it does for SQL migrations.

Optionally the callback may also include a description. In that case the callback name is composed of the event name, the separator, the description and the suffix. Example: beforeRepair__vacuum.sql.

Note: Flyway will also honor any sqlMigrationSuffixes you have configured, when scanning for SQL callbacks.

Java Callbacks If SQL Callbacks aren’t flexible enough for you, you have the option to implement the Callback interface yourself. You can even hook multiple Callback implementations in the lifecycle. Java callbacks have the additional flexibility that a single Callback implementation can handle multiple lifecycle events, and are therefore not bound by the SQL callback

Mohsen Bahaloo
  • 257
  • 2
  • 2