Behavior when unknown migration

[ericklaus-wf]

sql-migrate behaves badly when the database has migrations "unknown" to the current run of sql-migrate. This is most likely to happen when a diverged branch applied its migrations to a database while another branch also applied its own migrations to a database.

For example:

1. branch A: 1, 2, 3
2. branch B: 1, 2, 4
3. branch B applies against DB. State is now "1, 2, 4"
4. branch A tries to apply against DB. Migration "4" is unknown.

See existing issue: #37

How should sql-migrate behave when asked to perform a migration against a database that has unknown migrations? I can think of two reasonable behaviors:

1. Complain that unknown migrations exist. Require the user to pass a -f force flag to complete the migrations. I'm not sure how the library should change, but I could work up some suggestions.
2. Do the best we can. When migrating up, ignore the unknown migrations. When migrating down, fail upon encountering an unknown migration.

(For option 2, we could also allow migrating down by adding a migrate_down column to the migrations table and storing the SQL at migrate up time. That doesn't help any existing installations, but would be a way forward.)

Which behavior seems best? I'm in favor of 2, but see the merits of 1.

[meyerzinn]

Definitely option 1. If there is any issue whatsoever, don't try to forge on--maintain data integrity and make it the user's fault if something goes wrong. If a database is at an unknown migration, that means the client is probably outdated.

[rubenv]

I tend to agree here with @20zinnm: you don't want to silently cover up things that go wrong. They'll come back to bite you later on when they're much harder to diagnose.

[ironsmile]

Resurrecting a slightly older issue here but I have the same problem as described by @ericklaus-wf . In particular, when the last applied migration in the database is "unknown" then the tool would not apply any new migrations and happily tell you everything is alright. You would be non the wiser if you do not check with status. This is related to #85 as well since this is the culprit code.

Think I would be able to implement option 1 described here. My proposal is this: whenever an "unknown" migration is found, then PlanMigration would return an error. This way it would work no matter whether you are using the sql-migrate binary or using this repo as a library as I do. What do you think about it?

[ironsmile]

Done, I've created a pull request which implements option 1. I think it worked out great.

[rubenv]

Fix is merged thanks to @ironsmile!

[simonamdev]

Hi all,

First of all, thank you for the tool. Its alleviating the anxiety I typically experience with migrations 💯

I've encountered the issue above and it seems the resolution to option 1 was to return an error however there does not seem to be a force flag available as was suggested?

Here's my situation, maybe someone can offer 2c on an alternative solution:

My use case is that we are splitting up our Go codebase into modular, re-usable services. Eventually they will be deployed separately, however in the mean time we are currently still merging them into one monolithic binary.

Here's a simplified version of our codebase:
|
|--> serviceA --> migrations/ --> 0000_initial_a.sql
|--> serviceB --> migrations/ --> 0000_initial_b.sql, 0001_adjustment.sql, etc

Service B depends on Service A. I wrote an inhouse migration tool which uses packr to embed the migrations of each service and then perform them in sequence. Eventually this will not be required since Service A's deployment will be independent of Service B, but for the time being they are performed on the same DB.

ServiceB's migrations succeed however when it comes to serviceA's migrations I am on the receiving end of the unknown migration in database error.

Thanks for your time, any tips appreciated!

[e6a5]

Hi @simonamdev, my use case is the same as your use case. Have you had any tips yet?

[e6a5]

Hi all,

First of all, thank you for the tool. Its alleviating the anxiety I typically experience with migrations 💯

I've encountered the issue above and it seems the resolution to option 1 was to return an error however there does not seem to be a force flag available as was suggested?

Here's my situation, maybe someone can offer 2c on an alternative solution:

My use case is that we are splitting up our Go codebase into modular, re-usable services. Eventually they will be deployed separately, however in the mean time we are currently still merging them into one monolithic binary.

Here's a simplified version of our codebase: | |--> serviceA --> migrations/ --> 0000_initial_a.sql |--> serviceB --> migrations/ --> 0000_initial_b.sql, 0001_adjustment.sql, etc

Service B depends on Service A. I wrote an inhouse migration tool which uses packr to embed the migrations of each service and then perform them in sequence. Eventually this will not be required since Service A's deployment will be independent of Service B, but for the time being they are performed on the same DB.

ServiceB's migrations succeed however when it comes to serviceA's migrations I am on the receiving end of the unknown migration in database error.

Thanks for your time, any tips appreciated!

After reading the source code, I found that we could use the different migration table name for each service instead of using the default one gorp_migrations. You can use SetTable function to set the name of the table used to store migration info for each service. For example:
migrate.SetTable("service_a_gorp_migrations") // for your ServiceA
and
migrate.SetTable("service_b_gorp_migrations") // for your ServiceA
That is the way I fixed my problem. Thank you.