I recently wanted to check a precondition from a Diesel migration (I wanted to ensure that a column I’m dropping is actually empty).
Based on this article I managed to come up with the following code:
-- Ensure that message field is empty
DO $$
BEGIN
ASSERT (SELECT count(*) FROM broadcasts WHERE message is not null) = 0;
END $$;
-- Drop message column
ALTER TABLE broadcasts DROP COLUMN message;
If the precondition fails, the migration is aborted.
$ diesel migration run
Running migration 2020-06-04-113011_drop-message-field
Executing migration script migrations/2020-06-04-113011_drop-message-field/up.sql
Failed with: assertion failed
This is already quite nice, however it might be interesting to formalize this pattern. In Squitch there seems to be a “verify”-step that ensures that a migration was successful.
What if Diesel would support optional pre-check.sql and post-check.sql scripts (next to up.sql and down.sql), to check preconditions and postconditions?
If a precondition is not met (i.e. raises an error), the migration is aborted. If a postcondition is not met, either the migration chain could be aborted with an error, or a rollback could be attempted.