[request] Add pre/post migration SQL hooks

[jflambert]

Is your proposal related to a problem?

I would like to run a set of SQL instructions before and after Hasura runs its migrations.

Describe the solution you'd like

maybe specific files in the migrations folder that are dedicated to pre/post migrations

for example hasura/migrations/pre.sql and hasura/migrations/post.sql

Also, presumably post.sql should only run when migrations are successful? Or maybe make that an option.

Describe alternatives you've considered

None so far. Just recently realized I need this.

If the feature is approved, would you be willing to submit a PR?

No

[jflambert]

The names setup.sql and teardown.sql might also be good

As for use cases, how about sending a pg_notify broadcast that the DB is under "maintenance". In my case I want to pause some timescaledb jobs.

[mirzap]

Not sure why you can't achieve this with transactions? For example:

-- setup hook
BEGIN;
  -- do something here, ie.
  PERFORM pg_notify('platform_event', 'Maintenance in progress');
  ...
COMMIT;

-- migration
BEGIN;
alter table ....
COMMIT;

-- teardown
BEGIN;
  -- do something post migration
  PERFORM pg_notify('platform_event', 'Maintenance completed');
COMMIT;

[jflambert]

no I want a single pre/post for all migrations

pre.sql migrations (100 files) post.sql