Forked from jeremyjarrell/Idempotent migration in MySQL example
Created
April 14, 2021 13:16
-
-
Save merikan/c1670423d4d6343974e1f5873ac5edf6 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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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