Is this a Flyway use case

Question

I have delivered a Product to the customer. Now I have upgraded the Product, which includes changes to the database.

Customer wants to upgrade the Product. Now will Flyway help in the migration of Customer data from older version to newer version. Please let me know, if this is a valid use case. The flyway documentation talks about its use during development only.

Answer 1

Flyway allows you to change your database by running a set of scripts in a defined order. These scripts are called 'migrations' as they allow you to 'migrate' your database from one version to another.

The idea is you can start with an an empty database and each migration script will successively bring that database up from empty up to the current version. However, it's also possible to start with an existing database by creating a 'baseline' migration.

As SudhirR said, Flyway's primary use case is to define schema changes. However, it's perfectly possible to change data also. Since Flyway is just running plain SQL, in principle almost anything you can do in a SQL script you can also do in a Flyway migration.

In the case you described it should be possible to use Flyway to migrate the customer database. The steps you could take are:

• Generate a sql script that includes the entire DDL (including indexes, triggers, procedures, ...) of the production database. To do this you will need to add insert statements for all the reference data present in the database.
• Save this script in your Flyway project as something like 'V1__base_version.sql'
• Run the flyway baseline command against your production database
  • This will set up your production database for use with Flyway
• Add a new migration script to migrate your customer's data to the new version
  • e.g. create new table, copy data from old table to new table, delete old table
• Run flyway migrate to upgrade production

These steps are adapted from the Flyway documentation page here.

Of course you should read the Flyway docs and manually test on a throwaway DB before you run anything against production. However I think in principle Flyway could be a good fit for your use case.