Flyway

Rebaselining

A side effect of Continuous Delivery and capturing every small change in it's own migration script is users end up with a lot of migration scripts in their project.  For this reason, users may want to rebaseline if they are experiencing the following issues:

  1. Releases get slower
    Over time, these scripts may cause your build and releases to get slower because Flyway will validate that the checksum for all the Versioned Migration Scripts haven't changed.  This protects the integrity of your database to ensure that the exact script in version control was applied to the target database.

  2. Too many scripts in your migrations folder 
    Grouping migrations into folders allows you to keep your project easier to navigate. The typical granularity for these folders is either per major release or per year.
    Note: Migrations can be moved into sub-folders within the migrations folder, but this has to be done manually in the project folder and moving the migration script into a subfolder after it's saved in Flyway Desktop.  If you would like this feature to be part of the Flyway Desktop GUI, please vote for this feature.

How to Rebaseline

This approach involves creating a new Baseline Migration Script that will bring a blank database up to the latest state in a single migration script that will be generated (starting again from 001.sql) containing all of the logic needed to deploy the database from scratch.

Before proceeding to rebaseline your project, it is essential to confirm that all of your target databases (including test, staging, production, and external customer environments) have been upgraded to the latest version. If any databases have pending migrations, it is recommended that these are deployed to bring your environments in-sync before proceeding.


Currently, the simplest way to rebaseline is to archive your flyway_schema_history table across all environments and archive your migrations folder.  Then you can create a fresh baseline script off a database that represents production.


  1. In all your target environments, rename the flyway_schema_history table and its primary key.

    DECLARE @date VARCHAR(10) = FORMAT(GETDATE(), '__yyyyMMdd')
    PRINT 'Date of archive is: ' + @date
    
    -- Rename the table
    DECLARE @oldName NVARCHAR(25) = 'flyway_schema_history'
    DECLARE @newName NVARCHAR(35) = @oldName + @date
    PRINT 'The ' + @oldName + ' will be renamed to: ' + @newName
    EXEC sp_rename @oldName, @newName
    
    -- Rename the primary key on the table
    SET @oldName = @oldName + '_pk' 
    SET @newName = @oldName + @date
    PRINT 'The primary key will be renamed to: ' + @newName
    EXEC sp_rename @oldName, @newName
    
    
  2. Rename the migrations folder in your Flyway project from migrations to a name that keeps the scripts in an archive area, e.g., migrations_yyyymmdd.
    Note: This can not be a subfolder in the migrations folder since subfolders are considered when running flyway migrate.
     
  3. Visit the Generate Migrations tab in Flyway Desktop.  You'll be prompted to create a new baseline migration script since there aren't any scripts in the migrations folder.  Flyway Desktop creates the migrations folder for you in the project if it doesn't exist.
    1. Flyway Desktop automatically sets baselineOnMigrate = true and the baselineVersion to the number in the generated baseline script in its settings file.  Your pipelines should pick this up to rebaseline the downstream environments and only deploy versioned migration scripts that are a higher number than the baseline version.

What if I'm using Repeatable Migrations?

Please get in touch with the Database DevOps Development Team if you are using Repeatable Migrations and need to rebaseline.  Instead of renaming the flyway_schema_history table, you may want to create a copy of it and then clear out any non R scripts, so the R scripts are not reapplied.  This should only be a performance improvement because re-running a Repeatable Migration should give the same result.



Didn't find what you were looking for?