Integrating Flyway into an existing database

Question

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.

Answer 1

To clarify the logic, First, create your scripts with version number 1.0 and set the baseline version of the old database as 2.0, and then name new and future scripts with version 2.x

Steps;

On the test database, in which you already have tables created and data inserted,

1. Create your init script or scripts with version number 1.0, for example, V1_000__blabla.sql, V1_001__blabla.sql, etc.

2. Run the baseline command with version 2.0

   flyway baseline -configFiles=flyway.conf

flyway.conf file content;

flyway.driver=<DB_DRIVER>
flyway.url=<DB_URL>
flyway.user=<DB_USER>
flyway.password=<DB_PASS>
flyway.connectRetries=10
flyway.schemas=<SCHEMA>
flyway.skipDefaultCallbacks=false
flyway.baselineVersion=2.0
flyway.baselineDescription=Base Migration
flyway.locations=<scripts_folder>

That's all, and let's test;

3. Run your project on the test database and see if it started with success.

4. Run it on an empty database and see if it started with success and your scripts(v1.x, v2.x, etc.) applied

5. Do not forget to 'run baseline' command on the prod db as step 1.

Answer 2

This is my how-to instruction on integration flyway with prod DB

1. Use MySQLWorkbench to connect to prod DB.

2. Click menu Sever > Data Export

3. Select the DB and tables, choose Dump Structure Only

4. Selection option Export to selft-contained file and change the name of exported file (eg: schema-backup.sql)

5. Repeat step 1 to step 4 but at step 3 choose Dump Data Only and change the name of exported file (eg: data-backup.sql)

6. Use MySQLWorkbench to create a new local DB.

7. Run schema-backup.sql and data-backup.sql to the newly created local DB to make it as same state as prod DB.

8. Install flyway if it’s not installed.

9. Clone this repo and follow the README.md file https://github.com/haintwork/flyway-db-migration-starter-kit, but stop before step 7 in README.md file.

10. Comment the migrate command in migrate-db-local.sh script, uncomment the baseline command.

11. Copy content of schema-backup.sql and replace to V1__Init_DB.sql file.

12. Run step 7 of README.md file and check the local DB has new table flyway_schema_history and column success value 1.

13. Create new sql file V1_0_1__Init_test.sql in sql folder of the code with content

    CREATE TABLE test (
        id serial PRIMARY KEY,
        text VARCHAR ( 50 ) UNIQUE NOT NULL
    );
    

14. Open script file migrate-db-local.sh and comment baseline command and uncomment migrate command

15. Run /bin/bash/migrate-db-local.sh and check if new test table is created in local DB, if yes then it’s fine now.

16. Clone file migrate-db-local.sh to migrate-db-prod.sh and update prod db info in the script.

17. Uncomment the baseline and comment migrate

18. Run /bin/bash/migrate-db-prod.sh and check if table flyway_schema_history is created in prod db.

19. Comment baseline and uncomment migrate and run /bin/bash/migrate-db-prod.sh again and check if test table is created in prod DB. If yes then we are done.

20. Now using the starter kit to write migration db script and write CI/CD to auto integrating the DB.

Answer 3

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.