search

rubenv / sql-migrate

SQL schema migration tool for Go.
MIT License
3.24k stars 280 forks source link

Add ability to skip unknown migrations in database #159

Closed dixonwille closed 4 years ago

dixonwille commented 4 years ago

I know this seems like a very strange request. But as it isn't a backwards breaking and I may not be the only one, I figure why not open a PR.

Scenario:

In a microservice type situation where each service maintains its own schema (yes only schema, not DB), there is a case where this pops up.

In Postgres, I have a public schema in each DB and a service_name schema that is owned by a particular service. Multiple services can share the same DB. That way a service can read (not write) to other services data. But all the services in the same DB will use the same public schema.

If service A has newer migrations for the public schema and I want to deploy a version of service B that doesn't have the new migrations, if service A is applied first then service B, Service B would complain that there is a migration in the database that is not in the known migration source.

I am consuming sql-migrate as a package and will be writing my own MigrationSource as we have a pretty specific structure to follow (many microservices in a single mono repository). This is the only that has me stuck. I would really appreciate not having to maintain my own migrator (a fork of this), but completely understand if this is too much of an edge case to merge in and maintain.

rubenv commented 4 years ago

The check for missing migrations is meant as a safeguard against deploying the wrong set of migrations. I'm not opposed to making it optional though.

However, from your description, it sounds like you have multiple services deploying distinct sets of migrations into the same database? In such case it might be better to use separate migration tables (see the recently added MigrationSet functionality).

On Fri, Dec 13, 2019, 03:03 Will Dixon notifications@github.com wrote:

I know this seems like a very strange request. But as it isn't a backwards breaking and I may not be the only one, I figure why not open a PR.

Scenario:

In a microservice type situation where each service maintains its own schema (yes only schema, not DB), there is a case where this pops up.

In Postgres, I have a public schema in each DB and a service_name schema that is owned by a particular service. Multiple services can share the same DB. That way a service can read (not write) to other services data. But all the services in the same DB will use the same public schema.

If service A has newer migrations for the public schema and I want to deploy a version of service B that doesn't have the new migrations, if service A is applied first then service B, Service B would complain that there is a migration in the database that is not in the known migration source.

I am consuming sql-migrate as a package and will be writing my own MigrationSource as we have a pretty specific structure to follow (many microservices in a single mono repository). This is the only that has me stuck. I would really appreciate not having to maintain my own migrator (a fork of this), but completely understand if this is too much of an edge case to merge in and maintain.

You can view, comment on, or merge this pull request online at:

https://github.com/rubenv/sql-migrate/pull/159 Commit Summary

  • Add ability to skip unknown migrations in database

File Changes

Patch Links:

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/rubenv/sql-migrate/pull/159?email_source=notifications&email_token=AAAKPGEEM2AIMQDJWPVQCMDQYLUQXA5CNFSM4J2G27OKYY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4IAGVPLA, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAKPGEQS7ZLXLKVAIXTCXDQYLUQXANCNFSM4J2G27OA .

dixonwille commented 4 years ago

You are correct. I will have a migration set for public and one for each service. The issue is the public one is shared between services. Because of possibly being inconsistent across 60+ services it's possible one of the migrations will fail because it doesn't have a migration file that another had.

I'll have a migration set per schema. But I need only the public one to have the ignore flag.

Is there another way you would like me to tackle this?

rubenv commented 4 years ago

Am I understanding correctly that you're duplicating the same set of migrations across 60 services?

dixonwille commented 4 years ago

yes... just the way our packaging and deployment works across multiple environments...

dixonwille commented 4 years ago

To be clear only certain migrations are duplicated (public schema). each service will have it's own migration set that does not have this issue. Each service deploy will have 2 migration sets. One that is shared (public schema) and one that isn't (service_name schema).

dixonwille commented 4 years ago

@rubenv I did just add more to the comment that will show up in the docs to use that flag sparingly.

rubenv commented 4 years ago

That sounds like a major hassle, no? Isn't it easier to put all those public schema migrations in a separate service?

I think this is mostly caused by a strange use of micro-services (which are supposed to be isolated from each-other) that aren't really separate.

dixonwille commented 4 years ago

@rubenv you would think so wouldn't you. Was laughing with my manager about it actually. But we automate everything! So when we build a service, we pull in all migrations it will need to run at the time of building (versioning the migrations to the service). Since different services are built at different times, different migrations can possibly be pulled (in reference to public).

The issue with having a separate deploy for the public schema is maintaining that deployment as a different deployment that follows a different process. Also, what version of the schema is required for the service to work. So I need to know 2 different versions to deploy a service. The way we are implementing it just makes it another service deployment with a single version that has everything it needs packaged up.

rubenv commented 4 years ago

Okay, as long as you're aware of the things you're doing, I'm cool with it :-)

Code looks good to me. Should we have a unit test for this?

dixonwille commented 4 years ago

Sure give me a second to get working on that!

dixonwille commented 4 years ago

@rubenv UT was added

rubenv commented 4 years ago

Cool, merged, thanks!

dixonwille commented 4 years ago

I know old thread PR. Just wanted to thank you again. This has been a huge save for us! Still using the flag (cautiously) and everything is working as expected (so much so that developers using it do not even notice).

If anyone sees this PR, keep in mind, our public schema even after almost a year later, only has three files in it (keeping shared state small). We have functions in there for triggers that are common to our services and that is it! Everything else is in a MigrationSource specific to that service only!