2

I've seen posts about FlyWay handling multiple databases but it all seems like it is for databases that are independent. As I understand it, FlyWay can only handle one database per instance so creating/cleaning database schemas are completely independent.

Multiple datasources migrations using Flyway in a Spring Boot application

Our problem probably stems from bad db design but it is what it is and it won't be changed quickly. As for background, this is a C# old school application with MSSql DB and we're currently just using the FlyWay command line for evaluation purposes.

The DB(s) is set up like this.

  • AppDB
  • AppLogDB
  • AppAuditDB
  • AppArchiveDB
  • ...

I have examples where AppDB has views/procedures/etc that reference the other tables. There are also cases where other DBs access AppDB (IE ArchiveDB stored procedures will pull from AppDB).

With this dependency between the different databases, is there anyway FlyWay can handle the migration/clean in the order required? For example

If I have this order of scripts

  • AppDB
    1. V1__Create_table
    2. V2__Create_proc_pointing_to_ArchiveDB
  • ArchiveDB
    1. V1__Create_table
    2. V2__Create_proc_pointing_to_AppDb

How could this be re-done to handle it more like this

  • AppDB, ArchiveDB
    1. V1__AppDB_Create_table
    2. V2__ArchiveDB_Create_table
    3. V3__AppDB_Create_proc_pointing_to_ArchiveDB
    4. V4__ArchiveDB_Create_proc_pointing_to_AppDb
Community
  • 1
  • 1
JasonAUnrein
  • 146
  • 6

1 Answers1

0

This can be a tricky problem, especially with your ArchiveDB which has circular dependencies.

We have a similar setup on a project I'm working on. They are actually different schemas within an Oracle database however we have chosen to keep a separate schema_version table for each schema (to try and keep info displays manageable). The approach we use is to number our directories in the predominant order of dependencies and ensure we apply in that order. Yours could be:

10_AppDB
20_AppLogDB
30_AppAuditDB
40_AppArchiveDB

Circular dependencies are a bit more work to ensure that they can be applied to a clean environment correctly. Luckily we haven't encountered this however this may change in the future. An approach I would suggest is staggering the deployments (using the target option/property on migrate) such that the objects are created in the necessary order. Then when creating from scratch in a new environment you would need to apply across all databases to the first consolidation point then apply across all databases to the next one and so on. Building on your example:

# run flyway -target 2 for all DBs
10_AppDB___/V1__AppDB_Create_table
20_AppLogDB/V2__ArchiveDB_Create_table
# run flyway -target 4 for all DBs
10_AppDB___/V3__AppDB_Create_proc_pointing_to_ArchiveDB
20_AppLogDB/V4__ArchiveDB_Create_proc_pointing_to_AppDb
Hamish Carpenter
  • 831
  • 4
  • 9