Ever ran into a situation where the migrations would work just as expected in production, but for some reason, are not applicable to other environments? Could be either because the tables exist in production, but for saving space, the other environments use views on it or because you had to manually add the column to set up the new data, so that the application would not come crashing down.

Your approach would really depend on how the migrations are set up. A simple solution no matter what(if you are using straight SQL), is to surround the actual migration sql in a stored procedure and set up continue handlers only for errors that you are already expecting like so:

1
2
3
4
5
6
7
8
9

DELIMITER //
CREATE PROCEDURE AddColumnIfNecessary ()
BEGIN
    DECLARE continue handler for 1060 BEGIN END;
  ALTER TABLE mytable add newcolumn <type and constraints>;
END //
DELIMITER ;
CALL AddColumnIfNecessary();
DROP AddColumnIfNecessary;

If you are gearing up for table not existing too, then add another handler for 1146 as:

1

DECLARE continue handler for 1146 BEGIN END;

If you are working with views by the same name, you’d see “is not BASE TABLE”(error 1347), not table does not exist. So you’d need:

1

DECLARE continue handler for 1347 BEGIN END;

Essentially, its like try - catch for SQL! The above handlers, if you noticed, are not really doing anything between the BEGIN and END. But you can set up any additional SQL in there. However, make sure to declare the handlers before you actually execute the SQL which is going to “throw” these errors

Lesson: Although there are work-arounds like the above, think through why the purpose of “migrations” is defeated in the first place and if the process(whatever triggered the need for altering the table) should even be part of a migration when you are manually meddling with the database.