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 ;

THAAAAAAAAAANNNNNNNNNNNNNNNNNNNNNNNNNNKSS!!!!!!!!!!!!!!!!!!!!!

Another way of doing this using PREPARE/EXECUTE statement:

SET @sql = (SELECT IF(
    (SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.COLUMNS WHERE
        table_schema='newdb'
         and table_name='persons' and column_name='id'
    ) > 0,
    "SELECT 0",
    "alter table newdb.persons add id int;"
));

PREPARE stmt FROM @sql;
EXECUTE stmt;

similar idea:

set @col_exists = 0;
SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='myTable'
AND column_name='newColumn'
and table_schema = database()
into @col_exists;

set @stmt = case @col_exists
when 0 then CONCAT(
'alter table myTable'
, ' ADD COLUMN newColumn INT NULL DEFAULT 0 AFTER oldColumn'
,';')
else 'select ''column already exists, no op'''
end;

PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

👍