45

We are using Flyway to migrate the database schema and we already have more than 100 migration scripts.

Once we "squashed" multiple migrations into a single first-version migration, this is ok during development, as we drop and recreate the schema. But in production this wouldn't work, as Flyway won't be able to validate the migrations.

I couldn't find any documentation or best practice of what to do in this case. The problem is that the file quantity increases constantly, I don't want to see thousands of migration files everytime, essentially if production is already in the latest version. I mean, the migration scripts that have a version number that is lower than the version in production are irrelevant to us, it would be awesome if we could squash those files into a single migration.

We are using MySQL.

How should we handle this?

Thiago Negri
  • 5,221
  • 2
  • 28
  • 39

4 Answers4

36

Isn't that what re-baselining would do?

I'm still new to flyway, but this is how I think it would work. Please test the following first before taking my word for it.

Delete the schema_version table. Delete your migration scripts.

Run flyway baseline (this recreates the schema_version table and adds a baseline record as version 1)

Now you're good to go. Bear in mind that you will not be able to 'migrate' to any prior version as you've removed all of your migration scripts, but this might not be a problem for you.

Step by step solution:

  1. drop table schema_version;
  2. Export database structure as a script via MySQL Workbench, for example. Name this script V1__Baseline.sql
  3. Delete all migration scripts and add V1__Baseline.sql to your scripts folder, so it is the only script available for Flyway
  4. Run Flyway's "baseline" command
  5. Done
Thiago Negri
  • 5,221
  • 2
  • 28
  • 39
David Atkinson
  • 5,759
  • 2
  • 28
  • 35
  • 4
    Doesn't this mean, that if you are a team of developers, then all the other developers need to also drop the table schema_version? – Ville Miekk-oja Jan 28 '18 at 16:09
  • 1
    Yes. Every database that is "managed" by that set of Flyway migration scripts will need the same treatment. – David Atkinson Jan 28 '18 at 16:32
  • 1
    When running baseline, will flyway check that the existing schema is matching the new V1__Baseline.sql schema? What if I dont want flyway to validate the existing schema against V1__Baseline.sql, could I create a row with the resulting checksum in the schema_version table? – lloiacono Feb 15 '18 at 11:41
  • 1
    @Illoiacono - No flyway doesn't validate against existing schema. – HopeKing Mar 06 '19 at 15:15
10

We do this to allow us to compress scripts for building new DB in dev environments but also run against existing production DB without having to log on and delete the flyway_version_history table, and we can keep the scripts (mainly for reference):

Compress all the scripts to a new script e.g. V1 to V42 into a new scripts V43. Convert V1 to V42 to text files by putting .txt on the end.

Set the baseline to 43. Set flyway to ignore missing migrations.

In script V43 use an 'if' block to protect the create/insert statements so that they don't run for the existing production database. We use postgres so it is something like this:

DO $$
  DECLARE
    flywayVersion INTEGER;
  BEGIN

    SELECT coalesce(max(installed_rank), 0) INTO flywayVersion FROM flyway_schema_history;
    RAISE NOTICE 'flywayVersion = %', flywayVersion;
    IF flywayVersion = 0 THEN
      RAISE NOTICE 'Creating the DB from scratch';
      CREATE TABLE...
      .....
    END IF;
END$$;

The flyway command looks something like this:

Flyway.configure()
      .dataSource(...)
      .baselineVersion("43")
      .ignoreMissingMigrations(true)
      .load()
      .migrate()
TimT
  • 1,576
  • 16
  • 14
1

I think this article answers your question best: https://medium.com/att-israel/flyway-squashing-migrations-2993d75dae96

For postgres a reusable script has been created that you could execute every so many months for instance. You can of course adapt the script to MySQL specific things instead of postgres: https://github.com/the-serious-programmer/flyway-postgres-squash-script

KeukenkastjeXYZ
  • 169
  • 4
0

I haven't tried this, but what if you deleted all the migrations, create a new migration that creates the new starting point as version 1, set it as the baseline version -- and then modify your configuration to use a different table (e.g. flyway_schema_history_2)?

In existing databases, Flyway will see that you have a non-empty schema with no (recognized) flyway table and ignore the baseline migration. In new environments it will run the baseline migration too.

Am I missing anything?

(Of course a separate problem is how to generate the "compressed" migration. If you don't need any seed data you can just do a schema-only backup of your database and use that. If your migrations populate data too you will probably have to work that out manually.)

nafg
  • 2,424
  • 27
  • 25