Undo migrations
Published 17 December 2024
EDITION: TEAMS
Undo migrations are the opposite of regular versioned migrations. An undo migration is responsible for undoing the effects of the versioned migration with the same version. Undo migrations are optional and not required to run regular versioned migrations.
Naming
By default, Undo migration scripts start with a U, followed by the same number as the versioned migration script that it corresponds to, two underscores, and then a description (eg., U001.002__UndoNewTwitterColumn.sql).
Relevant configuration:
Undo
Undo migrations are executed via the undo command.
Important notes
Please note, you should take care if you have destructive changes (drop, delete, truncate etc) in your deployment. Undo migrations assume the whole migration succeeded and should now be undone.
This means that failed versioned migrations on databases without DDL transactions may require a different approach. This is because a migration can fail at any point. If you have 10 statements, it is possible for the 1st, the 5th, the 7th or the 10th to fail, whereas undo migrations will undo an entire versioned migration and so will not help under such conditions.
In such circumstances, an alternative approach could be to maintain backwards compatibility between the DB and all versions of the code currently deployed in production. This way the old version of the application is still compatible with the DB, so you can simply roll back the application code, investigate, and take corrective measures.
This should be complemented with a proper, well tested, backup and restore strategy. It is independent of the database structure, and once it is tested and proven to work, no migration script can break it. For optimal performance, and if your infrastructure supports this, we recommend using the snapshot technology of your underlying storage solution. Especially for larger data volumes, this can be several orders of magnitude faster than traditional backups and restores.
For more guidance on how to use undo migrations, see this tutoriaL.
Limitations
Undo migrations form one possible rollback strategy and do have some limitations in practice:
- They work for undoing schema changes but not so well for undoing data changes - If your versioned migration script contains destructive changes (drop, delete, truncate, ...), then restoring both table and data in the undo script can be challenging unless that data is static.
- Undo migrations assume the whole migration succeeded and should now be undone. This does not help with failed versioned migrations on databases without DDL transactions. Why? A migration can fail at any point. If you have 10 statements, it is possible for the 1st, the 5th, the 7th or the 10th to fail. There is simply no way to know in advance. In contrast, undo migrations are written to undo an entire versioned migration and will not help under such conditions.
Example
For a versioned migration V001__addCarTable.sql with contents
CREATE TABLE car (
id INT NOT NULL PRIMARY KEY,
license_plate VARCHAR NOT NULL,
color VARCHAR NOT NULL
);
ALTER TABLE owner ADD driver_license_id VARCHAR;
INSERT INTO brand (name) VALUES ('DeLorean');
the undo migration U001__undoAddCarTable.sql contents could be
DELETE FROM brand WHERE name='DeLorean';
ALTER TABLE owner DROP driver_license_id;
DROP TABLE car;
