Looking to achieve DevOps with Flyway? Watch our latest webinar on the Redgate Hub ▶ Watch now
2020-05-08 by Philip Liddell
In Flyway 6.4.0 we introduced a new feature, support for wildcards in the locations. With this feature a new set of solutions to organizing your migration files has now become available. In this post we hope to detail some of them, to aid the users of Flyway in deciding the solution that best suits their environment.
First let’s start by explaining what we mean by wildcards. Wildcards allow for matching patterns of paths instead of just a single one. Flyway will then use this pattern to search for all paths that match it, and returns all the migrations found beneath that folder, just as if the user had specified the path manually. The wildcards that Flyway supports are:
**: Matches any 0 or more directories
*: Matches any 0 or more non-separator characters
?: Matches any 1 non-separator character
A common working pattern we have seen is to structure your migrations into ‘releases’. Each release is a package of migrations, along with tests for those migrations, probably developed on a branch of the project. When the release is ready the folder is brought into the trunk project, and Flyway will then pick up those new migrations and apply them.
my-project release1.0 migrations V1_0__createBaseStructure.sql test V1_0_1__createTestData.sql release1.2 migrations V1_2__createMoreTables.sql test V1_2_1__createMoreTestData.sql
Ideally you could then specify
flyway.locations=filesystem:path/to/my-project and it would pick up all the migrations immediately.
However, doing that means you cannot exclude the test migrations in production. You would either have to specify all the folders explicitly
flyway.locations=filesystem:path/to/my-project/release1.0/migrations,filesystem:path/to/my-project/release1.2/migrations (extremely unwieldy to maintain as release count gets higher), or restructure your repository to put the test migrations somewhere else (maybe a separate
test folder tree that matches the release folder tree). Neither solution is ideal.
With wildcards, there is a better solution. The wildcard path
flyway.locations=filesystem:path/to/my-project/*/migrations will match all releases in the project with a migrations folder directly beneath them, and return only the migrations in that folder. Then on the test server you can add
filesystem:path/to/my-project/*/test to the locations to execute the test migrations.
You can even go a step further and restrict the locations to
path/to/my-project/release-*/migrations to only match folders that start with
Let’s say you are developing a project that needs to work on multiple database types. Some of the scripts are shareable, some are not. You do however want to organize your scripts by test, or by feature, not by database.
my-project table shared V1_0__createTableGeneric.sql oracle V1_0_1__createPLSQLSpecificTable.sql function create shared V2_0__createGenericFunction.sql mysql V2_0_1__createFunctionUsingMySQLProcedure.sql
Without using wildcards executing this would required specifying the subfolder for each database type manually, a real maintenance headache. However, with using wildcards you can provide the location
flyway.locations=filesystem:path/to/my-project/**/shared,filesystem:path/to/my-project/**/oracle to execute all the
oracle folders, no matter where the are in the project folder structure.