Support non-linear migrations

[ywk253100]

We maintains 2 versions of an application:
v1.0 released with 10_schema.up.sql
v2.0 released with 10_schema.up.sql and 20_schema.up.sql

We found a bug in v1.0 and fixed it by changing database schema, so we released v1.1 with 10_schema.up.sql and 11_schema.up.sql.

But when users trying to upgrade v1.1 to v2.0, we got the "file does not exist" error as there is no 11_schema.up.sql in release v2.0. How can we handle this? Does checking the existence of 11_schema.up.sql make sense?

[dhui]

Back-filling context from a related issue:

#79 (comment):

Assuming you can detect when an upgrade occurs, you can either:

1. Force the migration. e.g. set the last successful migration to 10_schema.up.sql before running the v2.0 migrations
2. Apply an upgrade migration. e.g. a set of migrations that contains the last v1.1 migration, missing migration(s) and the first v2.0 migration.

Neither solution is great since (1) is dangerous if a failure occurs during the migration and (2) makes it hard to maintain migrations. Unfortunately, migrate isn't as sophisticated as git where you can merge branches of migrations. Currently, migrations are expected to be linear.

#79 (comment):

Our solution for now is that for v1.x only releases with 10_schema.up.sql, any other changes in sub version are handled by our own logic

[lkebin]

Same problem.

Developer A create migration file on branch issue-a, version is:  20190813143726
Developer B create migration file on branch issue-b, version is: 20190828180746

Currently, if issue-b release before issue-a, the migration 20190813143726 will be ignored.

[dhui]

Same problem.

Developer A create migration file on branch issue-a, version is: 20190813143726
Developer B create migration file on branch issue-b, version is: 20190828180746

Currently, if issue-b release before issue-a, the migration 20190813143726 will be ignored.

This is not the same problem as this case can be caught and made linear before deploy/shipping. We don't have a fix for this in migrate yet but have potential solutions.

[lkebin]

@dhui Sorry, I just open many related issue pages. Should comment on 179.