Support for multiple migration dirs

I think a rather common scenario is to have a set of migrations that apply to all environments, and a set of (often data, not schema) migrations which are environment specific. The only way I see sql-migrate currently supporting this out of the box is if the user somehow arranges all migration files into a single migration dir, for example by duplicating the common ones in all dirs having also environment specific ones. Or a pre-run script that copies files around. Would be great to improve on this.

There are some existing issues here that propose implementing a MigrationSource according to one's liking. That's fine when used as a library, but a chore when using the command line tool. My current use case is not written in Go, so a project that produces an command line tool with the changed behavior would have to be created.

I'm proposing a change that would allow specifying multiple directories as the source. Files from them would be "interleaved" or "flattened" when determining the order, solely based on the files' basename, completely ignoring the dir they reside in. So for example,

migrations
├── 20221029143942-base.sql
├── 20221029144011-schema-change.sql
├── dev
│   └── 20221029143950-dev-data.sql
└── prod
    └── 20221029143954-prod-data.sql

...would with an environment configured to use dirs migrations and migrations/dev produce the sequence

20221029143942-base.sql
20221029143950-dev-data.sql
20221029144011-schema-change.sql

Interleaving is necessary due to the general incremental/dependent nature of migrations. If it wasn't, multiple environments could be created and applied to the same db sequentially. But unfortunately that doesn't work.

I believe this implementation would satisfy all existing cases I was able to find in the tracker here. Another thing I thought of was to add include/exclude filters to apply to filenames, but that gets hairy and doesn't scale as well as multiple dirs.

As far as actual implementation goes, a new key dirs could be added that would take multiple dirs, or dir processing could be changed so that multiple dirs could be specified in it for example using ; as a separator (same one everywhere for config platform independence).

If the idea is accepted, I can look into creating a PR for it.

Start by locating the CLI configuration and migration-discovery entry points; the issue names no files or tests. Confirm how multiple directories should be configured and how files are interleaved by basename. Done means common and environment-specific migrations can be selected together in the requested order.