Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save foopang/2c1472f8ba1d14bf9b63 to your computer and use it in GitHub Desktop.
Save foopang/2c1472f8ba1d14bf9b63 to your computer and use it in GitHub Desktop.
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.
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 ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment