In MySQL, IF statements cannot exist outside of stored procedures. Therefore, to create an idempotent migration for MySQL it's necessary to wrap the migration in a stored procedure and execute that stored procedure against the database to perform the migration.

Idempotent migration in MySQL example

DELIMITER $$

DROP PROCEDURE IF EXISTS add_email_address_column_to_customers_table $$

-- Create the stored procedure to perform the migration
CREATE PROCEDURE add_email_address_column_to_customers_table()

BEGIN
  
  -- Add the email_address column to the customers table, if it doesn't already exist
  IF NOT EXISTS ((SELECT * FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='customers' AND column_name='email_address')) THEN
    ALTER TABLE customers ADD email_address VARCHAR(256);
  END IF;

END $$

-- Execute the stored procedure
CALL add_email_address_column_to_customers_table() $$

-- Don't forget to drop the stored procedure when you're done!
DROP PROCEDURE IF EXISTS add_email_address_column_to_customers_table $$
 
DELIMITER ;

This... This is brilliant.

top man!

Good man

this is great,
is there a way to utilize it in order to create a triggers

i can't inside a stored procedure alter the delimiter as required in order to write a create trigger statement
using prepared statement is also an issue for such actions

thanks

Why we have to drop the store procedure at the end?
I am using a store procedure in a migration script (using flywaydb) and if I don't drop the stored procedure at the end of my script, when I try to re-run the migrations again it fails, do you have any idea why?
Thanks

thanks very much

👍