Recommendations when reversions aren't possible without data loss?

[shajra]

Hi.

Somehow, all the migration frameworks I've used have been forward-only. I've never had first-class support for reversion, so I'm only now considering how this affects my workflow.

I think reversions can fall into the following categories:

1. no associated data (like a view), so it is easy to revert with no lost data
2. has associated data but only relevant to the change being reverted; reversion is semantically the same as if the change was never introduced in the first place
3. data relevant to the prior schema is lost with the reversion, degrading users' experience.

We can try to have 1 and 2 as common cases with some care, but I can't say that 3 can/should always be avoided. In that case, I could imagine doing one of the following:

1. Throw an exception and disallow the reversion.
2. Allow the reversion and accept that data loss might happen.

I'm guessing that when you have reversion as a first-class feature, you get used to using it when developing against a test database. You just bring in changes and revert them iteratively until you have confidence in their correctness. You don't worry much about lost data because you're working against a test database.

One way to go is to take advantage of the reversion mechanism while developing but then comment it all out and put in an exception before committing it to production. That way, you have some protection against accidental data loss beyond the reversion confirmation prompt. Otherwise, it might be hard to tell a safe reversion from an unsafe one.

Am I too stuck in the forwards-only way of thinking of migrations?

[theory]

Hi. My recommendation is to avoid reverting changes in production, FULL STOP. If there's an issue after the migration and a reversion would delete data, don't delete, but fix forward. Of course this can cause issues to last a bit longer, but it's the only solution that would ensure you don't lose any data.

To enforce this, if you can structure a revert script to raise an exception if it can detect data that would be deleted, or perhaps if it can check the target name and raise an exception if it's a production target, for example, that could be quite helpful, yes. To do it based on the target, add a variable to the target for which you'd like to prevent reverts, something like:

sqitch target alter prd.example.com --set revert_ok=0
sqitch target alter dev.example.com --set revert_ok=1

Then in the revert script, do something like this:

CREATE OR REPLACE FUNCTION check_revert(
    ok int
) RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
    IF ok == 0 THEN 
        RAISE EXCEPTION 'Revert forbidden in production';
    END IF;
END
$$;
SELECT check_revert(:revert_ok);
DROP FUNCTION check_revert(int);