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

[asmgit]

I solved it this way:

Migrate Routines V1

1. folder structure

migrations/
    committed/
    current/
    fixtures/
        functions/
        rls/
        triggers/
        views/
        _index.sql

2. migrations/fixtures/_index.sql

--All routines in right order for re-creation
--FOR MIGRATE: --!include _index.sql

--!include rls/auth_user.sql

--views
--!include views/import_partner.sql
--!include views/price.sql
--!include views/projectForReport.sql
--!include views/public_entity.sql
...

--functions
--!include functions/document/document_dayPosted.sql
--!include functions/addressComputed.sql

...
--rls
...
--triggers
...

3. When I edit the routines code, I add this

--!include _index.sql

to
migrations/current/1-current.sql
4. watch, then commit
5. migrate on PROD

Cons:

If I edit only the routines, I have to remember to add the migration and commit it + problems with collaborative migration development + additional migration + this file contains a full listing of all the routines, it's very large

Migrate Routines V2!!!

1. folder structure

migrations/
    committed/
    current/
routines/
    committed/ (always empty)
    fixtures/
        functions/
        rls/
        triggers/
        views/
        _index.sql
    current.sql
.gmrc_routines
package.json

2. routines/current.sql

--!include _index.sql

3. .gmrc_routines

{ 
  migrationsFolder: "./routines",
}

4. package.json

"scripts": { 
  "gm": "export $(grep -v '^#' .env | xargs) && node_modules/.bin/graphile-migrate", 
  "routines": "yarn gm watch --config .gmrc_routines",
  "watch": "yarn gm watch --once && yarn gm watch --once --config .gmrc_routines",
  "commit": "yarn gm commit",
  "migrate": "yarn gm migrate && yarn gm watch --once --config .gmrc_routines",
...
},

I use folder migrations/ for schema-changing migrations, folder routines/ for migrating routines.

Pros:

• No need to create migration when changing routines; just change the code in the file.
• No extra migration files
• Migrations contain only data schema changes