How to upgrade from flyway 3 directly to flyway 5

Question

Working on a product that is deployed by many clients in many production environments. It includes at least one Spring Boot app.

We've used flyway for db schema migrations. Upgrading from Spring Boot 1.5.x to 2.0.x has bumped our flyway version from 3.x to 5.x.

The Spring Boot migration guide simply says to upgrade to flyway 4 before the boot upgrade. However, this would require all of our customers to do an intermediate upgrade before being able to upgrade to the latest.

So, the question is: How would you upgrade from flyway 3 directly to flyway 5?

Answer 1

The code above is not compatible with version 5. It uses deprecated classes. Here is an updated version.

import lombok.extern.slf4j.Slf4j;
import org.flywaydb.core.Flyway;
import org.flywaydb.core.api.callback.Callback;
import org.flywaydb.core.api.callback.Context;
import org.flywaydb.core.api.callback.Event;
import org.flywaydb.core.api.configuration.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.core.annotation.Order;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.MetaDataAccessException;
import org.springframework.stereotype.Component;

import static org.springframework.core.Ordered.HIGHEST_PRECEDENCE;

@Component
@Order(HIGHEST_PRECEDENCE)
@Slf4j
public class FlywayUpdate3To4Callback implements Callback {
    private final Flyway flyway;

    public FlywayUpdate3To4Callback(@Lazy Flyway flyway) {
        this.flyway = flyway;
    }

    private boolean checkColumnExists(Configuration flywayConfiguration) throws MetaDataAccessException {
        return (boolean) JdbcUtils.extractDatabaseMetaData(flywayConfiguration.getDataSource(),
                callback -> callback
                        .getColumns(null, null, flywayConfiguration.getTable(), "version_rank")
                        .next());
    }

    @Override
    public boolean supports(Event event, Context context) {
        return event == Event.BEFORE_VALIDATE;
    }

    @Override
    public boolean canHandleInTransaction(Event event, Context context) {
        return false;
    }

    @Override
    public void handle(Event event, Context context) {
        boolean versionRankColumnExists = false;
        try {
            versionRankColumnExists = checkColumnExists(context.getConfiguration());
        } catch (MetaDataAccessException e) {
            log.error("Cannot obtain flyway metadata");
            return;
        }
        if (versionRankColumnExists) {
            log.info("Upgrading metadata table the Flyway 4.0 format ...");
            Resource resource = new ClassPathResource("db/migration/flyway_upgradeMetaDataTable_V3_to_V4.sql",
                    Thread.currentThread().getContextClassLoader());
            ScriptUtils.executeSqlScript(context.getConnection(), resource);
            log.info("Flyway metadata table updated successfully.");
            // recalculate checksums
            flyway.repair();
        }
    }
}

Answer 2

Step 0.

Upgrade to spring boot v2.1 (and therby implicitly to flyway 5).

Step 1.

Since schema_version was used in flyway 3.x let new flyway versions know that they should keep using this table.:

# application.yml
spring.flyway.table: schema_version # prior flyway version used this table and we keep it

Step 2.

Create file src/main/ressources/db/migration/flyway_upgradeMetaDataTable_V3_to_V4.sql for upgrading the meta table based on the dialect you use.

See https://github.com/flyway/flyway/commit/cea8526d7d0a9b0ec35bffa5cb43ae08ea5849e4#diff-b9cb194749ffef15acc9969b90488d98 for the update scripts of several dialects.

Here is the one for postgres and assuming the flyway table name is schema_version:

-- src/main/ressources/db/migration/flyway_upgradeMetaDataTable_V3_to_V4.sql
DROP INDEX "schema_version_vr_idx";
DROP INDEX "schema_version_ir_idx";
ALTER TABLE "schema_version" DROP COLUMN "version_rank";
ALTER TABLE "schema_version" DROP CONSTRAINT "schema_version_pk";
ALTER TABLE "schema_version" ALTER COLUMN "version" DROP NOT NULL;
ALTER TABLE "schema_version" ADD CONSTRAINT "schema_version_pk" PRIMARY KEY ("installed_rank");
UPDATE "schema_version" SET "type"='BASELINE' WHERE "type"='INIT';

Step 3.

Create Java file your.package/FlywayUpdate3To4Callback.java

Please note that this does the following:

• Run the sql script from Step 2
• call Flyway.repair()

// FlywayUpdate3To4Callback.java
package your.package;

import static org.springframework.core.Ordered.HIGHEST_PRECEDENCE;

import org.flywaydb.core.Flyway;
import org.flywaydb.core.api.callback.Callback;
import org.flywaydb.core.api.callback.Context;
import org.flywaydb.core.api.callback.Event;
import org.flywaydb.core.api.configuration.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.core.annotation.Order;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.MetaDataAccessException;
import org.springframework.stereotype.Component;

import lombok.extern.slf4j.Slf4j;

@Component
@Order(HIGHEST_PRECEDENCE)
@Slf4j
public class FlywayUpdate3To4Callback implements Callback {
    private final Flyway flyway;

    public FlywayUpdate3To4Callback(@Lazy Flyway flyway) {
        this.flyway = flyway;
    }

    private boolean checkColumnExists(Configuration flywayConfiguration) throws MetaDataAccessException {
        return (boolean) JdbcUtils.extractDatabaseMetaData(flywayConfiguration.getDataSource(),
                callback -> callback
                        .getColumns(null, null, flywayConfiguration.getTable(), "version_rank")
                        .next());
    }

    @Override
    public boolean supports(Event event, Context context) {
        return event == Event.BEFORE_VALIDATE;
    }

    @Override
    public boolean canHandleInTransaction(Event event, Context context) {
        return false;
    }

    @Override
    public void handle(Event event, Context context) {
        boolean versionRankColumnExists = false;
        try {
            versionRankColumnExists = checkColumnExists(context.getConfiguration());
        } catch (MetaDataAccessException e) {
            log.error("Cannot obtain flyway metadata");
            return;
        }
        if (versionRankColumnExists) {
            log.info("Upgrading metadata table the Flyway 4.0 format ...");
            Resource resource = new ClassPathResource("db/migration/common/flyway_upgradeMetaDataTable_V3_to_V4.sql",
                    Thread.currentThread().getContextClassLoader());
            ScriptUtils.executeSqlScript(context.getConnection(), resource);
            log.info("Flyway metadata table updated successfully.");
            // recalculate checksums
            flyway.repair();
        }
    }
}

Step 4.

Run spring boot.

The log should show info messages similar to these:

...FlywayUpdate3To4Callback      : Upgrading metadata table the Flyway 4.0 format 
...FlywayUpdate3To4Callback      : Flyway metadata table updated successfully.

Credits

This answer is based on Eduardo Rodrigues answer by changing:

• Use Event.BEFORE_VALIDATE to trigger a flyway callback that upgrade flyway 3 to 4.
• more information on application.yml setup
• provide upgrade sql migration script