config file to manage current migration order

[mgoldfield]

Feature description

Add the option of adding a config file that goes with sql files that specifies the order of execution of a current migration, as an alternative to numbered sql files.

Motivating example

adding files not at the end of a migration file list or reordering files creates lots of problems for merging into version control, and makes hard to look at the history of a piece of code.

example:

existing current migration:
001-A.sql
002-B.sql
003-C.sql

... add file at position 2

001-A.sql
002-NEW.sql
003-B.sql
004-C.sql

now files B and C will be hard to merge, and may or may not have file histories in version control.

proposed change:

existing current migration:

A.sql
B.sql
C.sql
config-file

... add file at position 2

A.sql
B.sql
C.sql
NEW.sql
updated-config-file

Supporting development

I [tick all that apply]:

• 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]

Out of interest, why not use BASIC-style line numbering?

1000-A.sql
2000-B.sql
3000-C.sql
4000-D.sql

Then you can always add a file between two previous files - e.g. add 2500-A2.sql.

This is already supported and I use it heavily - e.g. run yarn db uncommit (or have a look at the --! split lines in https://github.com/graphile/starter/blob/main/@app/db/migrations/committed/000001.sql) in Graphile Starter and you'll see the files I used:

0001-reset.sql
0010-public-permissions.sql
0020-schemas.sql
0030-common-triggers.sql
0040-pg-sessions-table.sql
1000-sessions.sql
1010-session-functions.sql
1020-users.sql
1030-user_emails.sql
1040-user_authentications.sql
1100-login.sql
1110-logout.sql
1120-forgot_password.sql
1130-reset_password.sql
1140-request_account_deletion.sql
1150-confirm_account_deletion.sql
1160-change_password.sql
1200-user-registration.sql
1210-make_email_primary.sql
1220-resend_email_verification_code.sql
2000-organizations-reset.sql
2010-organizations.sql
2019-organization_memberships.sql
2030-organization_invitations.sql
2040-create_organization.sql
2050-invite_to_organization.sql
2060-organization-permissions.sql
2070-organization_for_invitation.sql
2080-accept_invitation_to_organization.sql
2090-remove_from_organization.sql
2100-organization-computed-columns.sql
2110-dont-allow-user-delete-when-organization.sql
2120-delete_organization.sql
2130-transfer_organization_ownership.sql
2140-transfer_organization_billing_contact.sql

[mgoldfield]

Good point - I think the motivating example I have is more about re-ordering files. We're in a multi-month process of developing an app and have had cause to reorder files a few times, which isn't solved by spacious numbering (though we do do that).