config file to manage current migration order

mgoldfield commented 3 years ago

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

[x] am interested in building this feature myself
[x] am interested in collaborating on building this feature
[x] am willing to help testing this feature before it's released
[x] 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 commented 3 years ago

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 commented 3 years ago

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).

graphile / migrate