19

Let's say I have migrations script from V1_1 to V1_300 - it is quite a huge number and takes very long period of time. But from time to time there is a release - can I, from the point of flyway, somehow merge all those migrations so:

  • All migrations from V1_1 to V1_300 will be in one file (for instance: V2_1)
  • Amount of time taken by these migrations will drop

Checking for overlaps manually is really time-consuming. Thank you in advance for your answers.

Michal Kordas
  • 10,475
  • 7
  • 58
  • 103
Mateusz Chrzaszcz
  • 1,240
  • 14
  • 32
  • Possible duplicate of [Any way to "compress" Flyway migrations?](https://stackoverflow.com/questions/33509131/any-way-to-compress-flyway-migrations) – Cephalopod Jan 22 '19 at 10:49

2 Answers2

14

We had the same problem on my project and decided to do a roll up of versions already deployed to production. To roll up incremental changes into 1 file, I ran the migration from scratch in a database then dump (export) back the whole database in 1 SQL file. I named the file using the last version of the migration. In your case V1_300__rollup.sql. Then you can continue adding new versions: V2_1, V2_2, etc. and repeat when you want to roll up.

Sylvain Guillopé
  • 1,358
  • 9
  • 21
  • 22
    @Mateusz would either of you be kind enough to elaborate on this a bit more? Did you replace `V1_300_patch` with the `V1_300_rollup` script, or was it added as a new script? Did you remove scripts V1_1 through V1_299 then? Also, did you have to do a `flyway:repair` to have flyway work properly after the rollup was added? I'm curious how this would work in practice on a production database. – Ryan Clemson Nov 14 '14 at 17:51
  • A bit late to this, but might be useful. Delete all your scripts V1->V1_299, and rename your V300 with the modified contents with your schemas dump. It's a good idea to always do a flyway repair at the end of migrations anyway, it's a no-op if no repair is needed. – yelsayed Oct 04 '21 at 05:47
0

We had the similar problems: too many DB migrations script resulted slow tests execution. On top of this we have pretty complex environment to run the application: all tests are running against empty docker DB, we have several deployments with different DB versions (dev - latest, stage - some, prod - oldest).

There are two cases: when you have all versions in dev/stage/prod aligned and when dev/stage is ahead of prod. Below I'll describe resolution for the case when you have the same DB version on all environments.

The second case is also possible to resolve but it requires more efforts to implement. General approach - merge DB schema up to "oldest" DB version, rename newest migration scripts to new versions after major one and make sure newest DB changes will no apply twice (check if changes already applied or not).

"All envs DB version aligned" steps:

  • Create "merged" DB script. You can run your project against empty DB and extract schema from there using your DB type specific tools.
  • Delete (or move) all DB migration script files from the configured flyway location and replace with generated file in the previous step. I suggest to create new major version, so if you have latest V1_300, merged file will have V2_0
  • Create beforeValidate.sql (see flyway docs) in the same as V2_0 script location with the content (example for MSSQL/AzureSQL):
    -- run baseline manually
if OBJECT_ID(SCHEMA_NAME() + '.flyway_schema_history', 'U') IS NOT NULL
    if exists(select 1
              from flyway_schema_history
              where version = 'YOU_FIRST_MIGRATION_VERSION_AFTER_BASELINE')
        begin
            begin
                declare @max_inst_rank int;
                select @max_inst_rank = MAX(installed_rank) from flyway_schema_history;
                delete from flyway_schema_history;
                INSERT INTO flyway_schema_history (installed_rank, version, description, type, script, checksum,
                                                   installed_by,
                                                   installed_on, execution_time, success)
                VALUES (@max_inst_rank + 1, 'YOUR_MERGED_VERSION', '<< Flyway Baseline >>', 'BASELINE', '<< Flyway Baseline >>', NULL,
                        'WHO_CREATED', GETDATE(), 0, 1);
            end;
        end;
GO

Where:

YOU_FIRST_MIGRATION_VERSION_AFTER_BASELINE - number of first migration after baseline, for example 1.0.1

YOUR_MERGED_VERSION - number of new "merged" version, for example 2.0.0

WHO_CREATED - person who created the record

This script will run BEFORE flyway validates migration DB records against migration files, so it will do:

  1. when flyway_schema_history is not exists (we are running on empty DB)- dong nothing
  2. when flyway_schema_history exists
  • and first migration (1.0.1 for example) exists - delete all records
    in flyway_schema_history and create new baseline out of "merged" and set version to 2.0.0. No actual DB changes will be done since it is already there.
  • and first migration 1.0.1 do not exists - do nothing since we already did all necessary actions.

At the end of all this actions and deployment to all environments you'll have V2.0.0 file and one BASELINE record in flyway_schema_history table. You can proceed to create new DB mitigation scripts starting with version V2.0.1

You can also move flyway_schema_history table records to another table instead of delete for audit purposes.

beforeValidate.sql script will run at each server start and, in general, do nothing since all changes already applied. You can remove the script from flyway folder when al environments upgraded.

Validated on flyway 8.5.x, SpringBoot 2.7.x

Andrey Yakovenko
  • 1