Flyway output to SQL File

Question

Is it possible to output the db migration to an SQL file instead of directly invoking database changes in flyway?

Answer 1

Most times this will not be needed as with Flyway the DB migrations themselves will already be written in SQL.

Answer 2

I didnt find a way. Switched to mybatis migration. Looks quite nice.

Answer 3

Yes it's possible and as far as I am concerned the feature is an absolute must for DBAs who don't want to allow flyway in prod.

I made do with modifying code from here, it's a dry run command for flyway, you can add a filewriter and write out migrationDetails:

https://github.com/killbill/killbill/commit/996a3d5fd096525689dced825eac7a95a8a7817e

I did it like so... Project structure (just copied it out of killbill's project and renamed package to flywaydr:

.
./main
./main/java
./main/java/com
./main/java/com/flywaydr
./main/java/com/flywaydr/CapturingMetaDataTable.java
./main/java/com/flywaydr/CapturingSqlMigrationExecutor.java
./main/java/com/flywaydr/DbMigrateWithDryRun.java
./main/java/com/flywaydr/MigrationInfoCallback.java
./main/java/com/flywaydr/Migrator.java
./main/java/org
./main/java/org/flywaydb
./main/java/org/flywaydb/core
./main/java/org/flywaydb/core/FlywayWithDryRun.java

In Migrator.java add (implement callback and put it in DbMigrateWithDryRun.java) :

  } else if ("dryRunMigrate".equals(operation)) {
      MigrationInfoCallback mcb = new MigrationInfoCallback();
      flyway.dryRunMigrate();
      MigrationInfoImpl[] migrationDetails = mcb.getPendingMigrationDetails();

      if(migrationDetails.length>0){              
          writeMasterScriptToFile(migrationDetails);
      }
 }

Then to write stuff to file something like:

private static void writeMasterScriptToFile(MigrationInfoImpl[] migrationDetails){

    FileWriter fw = null;
    try{
        String masterScriptLoc="path/to/file";

        fw = new FileWriter(masterScriptLoc);
        LOG.info("Writing output to " + masterScriptLoc);
        for (final MigrationInfoImpl migration : migrationDetails){
             Path file =Paths.get(migration.getResolvedMigration().getPhysicalLocation());
             //if you want to copy actual script files parsed by flyway
             Files.copy(file, Paths.get(new StringBuilder(scriptspathloc).append(File.separator).append(file.getFileName().toString()).toString()), REPLACE_EXISTING);


        }
             //or just get the sql
             for (final SqlStatement sqlStatement : sqlStatements) {  
                  //sqlStatement.getSql();  
             }
        fw.write(stuff.toString());
    } catch(Exception e){
            LOG.error("Could not write to file, io exception was thrown.",e);
    } finally{
            try{fw.close();}catch(Exception e){LOG.error("Could not close file writer.",e);}
    }

}

One last thing to mention, I compile and package this into a jar "with dependencies" (aka fatjar) via maven (google assembly plugin + jar with dependencies) and run it via command like below or you can include it as a dependency and call it via mvn exec:exec goal, which is something I had success with as well.

$ java -jar /path/to/flywaydr-fatjar.jar dryRunMigrate -regular.flyway.configs -etc -etc