flyway

Integrating Flyway into an existing database

We have not used Flyway from the beginning of our project. We are at an advanced state of development. An expert review has suggested to use Flyway in our project.

The problem is that we have moved part of our services (microservices) into another testing environment as well.

What is the best way to properly implement Flyway? The requirements are:

  1. In Development environment, no need to alter the schema which is already existing. But all new scripts should be done using Flyway.

  2. In Testing environment, no need to alter the schema which is already existing. But what is not available in testing environment should be created automatically using Flyway when we do migrate project from Dev to test.

  3. When we do migration to a totally new envrionment (UAT, Production etc) the entire schema should be created automatically using Flyway.

From the documentation, what I understood is:

  1. Take a backup of the development schema (both DDL and DML) as SQL script files, give a file name like V1_0_1__initial.sql.
  2. Clean the development database using "flyway clean".
  3. Baseline the Development database "flyway baseline -baselineversion=1.0.0"
  4. Now, execute "flyway migrate" which will apply the SQL script file V1_0_1__initial.sql.
  5. Any new scripts should be written with higher version numbers (like V2_0_1__account_table.sql)

Is this the correct way or is there any better way to do this?

The problem is that I have a test database where we have different set of data (Data in Dev and test are different and I would like to keep the data as it is in both the environments). If so, is it good to separate the DDL and DML in different script files when we take it from the Dev environment and apply them separately in each environment? The DML can be added manually as required; but bit confused if I am doing the right thing.

Thanks in advance.

Solution

  • So, there are actually two questions here. Data management and Flyway management.

    In terms of data management, yes, that should be a separate thing. Data grows and grows. Trying to manage data, beyond simple lookup tables, from source control quickly becomes very problematic. Not to mention that you want different data in different environments. This also makes automating deployments much more difficult (branching would be your friend if you insist on going this route, one branch for each data set, then deploy appropriately).

    You can implement Flyway on an existing project, yes. The key is establishing the baseline. You don't have to do all the steps you outlined above. Let's say you have an existing database. You have to get the script that defines that database. That single script should include all appropriate DDL (and, if you want, DML). Name it following the Flyway standards. Something like V1.0__Baseline.sql.

    With that in place, all you must do is run:

    flyway baseline

    That will establish your existing code base as the start point. From there, you just have to create scripts following the naming standard: V1.1xxx V2.0xxx V53000.1xxx. And run

    flyway migrate

    To deploy appropriate changes.

    The only caveat to this is that, as the documentation states, you must ensure that all your databases match this V1.0 that you're creating and marking as the baseline. Any deviation will cause errors as you introduce new changes and migrate them into place. As long as you've got matching baseline points, you should be able to proceed with different data in different environments with no issues.