Migrations: Check Preconditions and Postconditions

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 $$
    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.