Feature request: Repeatable migrations for managing functions and views

[rohanliston]

Feature description

Flyway has the concept of repeatable migrations that are (re-)applied to a database on migrate every time their checksum changes.

This differs from the 'current' migration concept in Graphile Migrate, which as far as I understand is purely for development purposes.

Repeatable migrations are very useful for managing database objects whose definition can then simply be maintained in a single file in version control. They are typically used for

* (Re-)creating views/procedures/functions/packages/...
*  Bulk reference data reinserts

Implementing #209 would probably make this possible with hooks, but I have found it such a useful concept that I think it warrants consideration as a feature.

Motivating example

I've used this Flyway feature on many projects to manage stored procedures and functions and am really missing it now that I am using Graphile Migrate.

I've tried replicating the functionality with the afterCurrent and afterAllMigrations hooks, but:

• In watch mode, I have to go and make a dummy edit to current.sql in order to pick up the changes to my "repeatable" migrations.
• In migrate mode, the "repeatable" migrations don't run at all because the migrations have all been applied already.

Supporting development

I [tick all that apply]:

(I'd love to implement this myself but realistically don't think I would have time, but am happy to assist otherwise).

• am interested in building this feature myself
• am interested in collaborating on building this feature
• am willing to help testing this feature before it's released
• am willing to write a test-driven test suite for this feature (before it exists)
• am a Graphile sponsor ❤️
• have an active support or consultancy contract with Graphile

[benjie]

We have the beginnings of this already in the form of fixtures - you would manage your views/procedures/RLS policies/GRANTs/etc in fixtures, and then --!include them from current.sql. What we lack currently is monitoring to know that these files have changed - currently it relies on you knowing that when you change a fixture you must also add it to the current migration via --!include.

https://github.com/graphile/migrate#including-external-files-in-the-current-migration