Allow migrations for multiple databases

[stevenwjy]

Is your feature request related to a problem? Please describe.

I want to perform database migrations into multiple databases (for context: I'm using MySQL).

Each migration file will have use <database_name>; statement(s) that will decide which database should be used for their subsequent queries.

At the moment, the database name must be specified from the beginning, and the schema_migrations table is assumed to be located on that database. It causes an issue when I have "use" statement(s) since the connection may switch database into one that no longer contains the schema_migrations table.

Note that this won't have any issues with Go's SQL driver being a connection pool if we have multiStatements=true and each SQL file chooses the database first before performing other queries.

Describe the solution you'd like

Allow an additional config to specify the database name that contains the schema_migrations table. If such config is provided, the query to update the schema_migrations table will first have "use <database_name>;".

Describe alternatives you've considered

As a current workaround, I'm doing some sort of "hacks" by:

• Specifying the name of the database that stores the schema_migrations table when opening the connection
  • Note that this is not required by Go's database/sql package, but without it github.com/golang-migrate/migrate/v4/database/mysql package will return an error with message "no database name".
• At the end of each migration file, I need to switch back to the database that contains the "schema_migrations" table, by writing "use <database_name>;"

Additional context

I'm currently using this for MySQL, but I think the specification is pretty generic and may apply to other databases.

[tim-hilt]

Just chiming in to say I'm looking for a similar solution. I'm working with MongoDB and need to migrate some data from one database to another one. The tool we're currently using is not able to maintain multiple connections to multiple databases.