Idempotent migrations when using Flyway

Question

I recently started to use Flyway to control a SQL database. I have been reading that it is generally a good practice to write idempotent migrations and set OutOfOrder to true in Flyway, when working in a continuous integration environment, where branches might be merged and deployed independently.

My understanding is that an idempotent migration, for instance to create an index on a table, needs to operate as follows: 1) check if the table exists, if not, create the table. 3) check if the column exists, if not, create column. 2) check if the index exists, if not, create it.

That results in a large procedure and leads to the fact that each time that a table is altered this procedure needs to be copied over and over again.

So now I am bit perplex. Is this is really the best way to implement Flyway in a continuous integration environment? Does anybody have better suggestions?

Thank you very much for the help!

Answer 1

1) An idempotent migration is a migration that can be ran more than once, but still have the same effect as if it was ran only once. To achieve this, you don't need to go as far as to write table creation code every time that you want to modify a database table. Trying to do this will also become impossible to maintain very quickly.

Basically, when writing a statement in your migration script, you need to know what the state of your database will be before this statement is ran for the first time. Now, think of what modifications must be made to the statement to ensure that it can be ran without error even if it has been ran before.

Eg: If your database is in state A and you run migration x to get it to B:

M(A, x) -> B 

Then you need to write x so that the database state is still B even if x is ran again:

M(B, x) -> B

This will allow you to run the same migration script more than once (handy for instance, if one of the statements in the script failed but previous ones succeeded).

2) Next up, setting outOfOrder=true will let flyway run any migrations that has not yet been run, even if the migration is older than the latest one that was ran. So if you have three migrations, x, y and z (in this order), and x and z is ran against the database then without outOfOrder set, flyway will not run y once it is available, because z was already ran. However with the flag set to true, y will basically now be ran out of order - after z.

Now if you set outOfOrder to true, you will need to be aware of this possibility and that the starting state of your database before the in your database can now have two different values:

Where A is the initial state:

M(A, x) -> B
M(B, y) -> C
M(C, z) -> E
(Taking the state from A -> B -> C -> E)

M(A, x) -> B
M(B, z) -> D
M(D, y) -> E
(Taking the state from A -> B -> D -> E)

So before y is ran, the state can either be B or D and before z is ran, the state can either be B or C. Due to this, migration script y needs to be written so that, in addition to being idempotent, it will also function for both of its starting state and allow z to still function after it has ran (z must function correctly for both states B and C).

Answer 2

The theory behind how to write idempotent scripts is nicely explained by @mdewit. Generally this is achieved in MYSQL using procedures as explained in https://dbabulletin.com/index.php/2018/03/29/best-practices-using-flyway-for-database-migrations/.

MySQL, for example, does not allow IF ELSE statements outside of the procedures, and though workarounds exist, they don’t play well with H2 unit tests (I explain it later in this article).

The common idempotent workaround for MySQL is creating a stored procedure that performs the migration, then dropping this stored procedure afterwards. Stored procedure will use IF-ELSE to check for existing objects. Example is below:

DELIMITER $$

DROP PROCEDURE IF EXISTS upgrade_database_1_0_to_2_0 $$

CREATE PROCEDURE upgrade_database_1_0_to_2_0()

BEGIN

-- rename a table safely

IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()

AND TABLE_NAME='my_old_table_name') ) THEN

RENAME TABLE

my_old_table_name TO my_new_table_name,

END IF;

-- add a column safely

IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()

AND COLUMN_NAME='my_additional_column' AND TABLE_NAME='my_table_name') ) THEN

ALTER TABLE my_table_name ADD my_additional_column varchar(2048) NOT NULL DEFAULT '';

END IF;

END $$

CALL upgrade_database_1_0_to_2_0() $$

DELIMITER ;