1

In an application with a custom database migrator which we want to replace with Flyway.

These migrations are split into some categories like "account" for user management and "catalog" for the product catalog. Files are named $category.migration.$version.sql. Here, $category is one of the above categories and $version is an integer version starting from 0.

e.g. account.migration.23.sql

Although one could argue that each category should be a separate database, in fact it isn't and a major refactoring would be required to change that.

Also I could use one schema per category, but again this would require rewriting all SQL queries.

So I did the following:

  • Move $category.migration.$version.sql to /sql/$category/V$version__$category.sql (e.g. account.migration.1.sql becomes /sql/account/V1_account.sql)
  • Use a metadata table per category
  • set the baseline version to zero

In code that would be 

String[] _categories = new String[] { "catalog", "account" };
for (String _category : _categories) {
  Flyway _flyway = new Flyway();
  _flyway.setDataSource(databaseUrl.getUrl(), databaseUrl.getUser(), databaseUrl.getPassword());
  _flyway.setBaselineVersion(MigrationVersion.fromVersion("0"));
  _flyway.setLocations("classpath:/sql/" + applicationName);
  _flyway.setTarget(MigrationVersion.fromVersion(_version + ""));

  _flyway.setTable(category + "_schema_version");
  _flyway.setBaselineOnMigrate(true); // (1)
  _flyway.migrate();
}

So there would be the metadata tables catalog_schema_version and account_schema_version.

Now the issue is as follows: Starting with an empty database I would like to apply all pre-existing migrations per category, as done above. If I remove _flyway.setBaselineOnMigrate(true); (1), then the catalog migration (the first one) succeeds, but it would complain for account that the schema public is not empty.

Likewise setting _flyway.setBaselineOnMigrate(true); causes the following behavior: The migration of "catalog" succeeds but V0_account.sql is ignored and Flyway starts with V1_account.sql, maybe because it somehow still thinks the database was already baselined?

Does anyone have a a suggestion for resolving the problem?

markdsievers
  • 7,151
  • 11
  • 51
  • 83
user3001
  • 3,437
  • 5
  • 28
  • 54

1 Answers1

1

Your easiest solution is to keep the schema_version tables in another schema each. I've answered a very similar question here.

Regarding your observation on baseline, those are expected traits. The migration of account starts at v1 because with the combination of baseline=0, baselineOnMigrate=true and a non empty target schema (because catalog has populated it) Flyway has determined this is a pre-existing database that is equal to the baseline - thus start at v1.

Community
  • 1
  • 1
markdsievers
  • 7,151
  • 11
  • 51
  • 83