Is there a way to flag a migration as "irreversible"?

[jechols]

I want it to be extremely clear that if somebody tries to undo a particular migration, they cannot. I can always put in invalid SQL or something, but I'd prefer a semantic way that would let the user know that a rollback isn't an option after a certain point.

Some context: I will be doing a very destructive operation in a migration in order to refactor the data architecture. If one were to roll back the database, and then use the prior version of the application, the app would see the right DB structure, but the data would be a mess and the app behavior would be undefined. Data loss would be almost a certainty, and it might be totally silent when things went wrong because of how the old version of the application handled this particular data.

[jechols]

As a workaround I've converted from SQL to Go migrations so I can just have it print a message and exit on attempting to downgrade. I had been hoping to stick to the simpler approach of SQL-only migrations, but my gut tells me this kind of scenario is exactly why more complex Go migrations exist.

I'd still love to hear if there's a simpler way. If nothing else, the conversation may help others in the same situation.

[mfridman]

One way I've seen this done before (for postgres) is adding a thin "raise expectation" wrapper, e.g.,

CREATE OR REPLACE FUNCTION raise_exception(text) RETURNS text AS $$
BEGIN
    RAISE EXCEPTION '%',$1;
END;
$$ LANGUAGE plpgsql;

Then in your down migration add this snippet

SELECT raise_error('down migration not allowed for 00011_k.sql');

And when trying to goose down it'll fail with:

goose run: ERROR 00011_k.sql: failed to run SQL migration: failed to execute SQL query "SELECT raise_error('down migration not allowed for 00011_k.sql');": ERROR: down migration not allowed for 00011_k.sql (SQLSTATE P0001)

SQLSTATE P0001 is a pl/pgsql error.