postgresql - Flyway multiple metadata tables in one schema

Question

I'm trying to use Flyway to version the database of a modular application. Each module has its own separate set of tables, and migration scripts that will control the versioning of that set of tables.

Flyway allows me to specify a different metadata table for each module - this way I can version each module independently. When I try to upgrade the application, I run a migration process for each module, each with its own table and set of scripts. Note that these tables are all in the same schema.

However, when I try to migrate my application, the first migration is the only one that works. Subsequent migrations fail with the following exception: org.flywaydb.core.api.FlywayException: Found non-empty schema(s) "public" without metadata table! Use baseline() or set baselineOnMigrate to true to initialize the metadata table.

If I create the metadata table for each module manually, migrations for each module work correctly. Creating the table myself rather than having Flyway create it for me seems like a hack to work around a problem, rather than a solution in itself.

Is this a valid way of managing multiple sets of tables independently, or is there a better way of doing this? Is it a valid approach to create the metadata table myself?

Answer 1

An ideal solution for you would be to split your modules into schemas. This gives you an effective unit of isolation per module and is also a natural fit for modular applications (modules completely isolated and self managing), rather than dumping everything into a single schema (especially public). eg

application_database
    ├── public
    ├── module_1
    │?? ├── schema_version
    │?? ├── m1_t1
    │   └── m1_t2
    ├── module_2
    │?? ├── schema_version
    │?? ├── m2_t1
    │   └── m2_t2
    ...

Your second option is to remain using the public schema to host all tables, but use an individual schema for each schema_version. This is less refactoring effort but certainly a less elegant design than that mentioned above. eg

application_database
    ├── public
    │?? ├── m1_t1
    │   ├── m1_t2
    │?? ├── m2_t1
    │   └── m2_t2
    ├── module_1
    │?? └── schema_version
    │
    ├── module_2
    │?? └── schema_version
    ...