With any application, being able to maintain and deploy an environment is of paramount importance.  With any dependency your application has, you need to make sure your application version corresponds with the right version of that dependency. Not to mention, complexity increases when supporting multiple environments (Dev, QA, UAT, etc.) each with a different version of the app.  It’s a bit of a headache!

One of the tools we use to simplify these concerns for databases is FlywayDB.  At a high level, the tool supports: the Integration of your Database Schema DDL and DML with your Source Code; the ability to audit what has been applied to any database in an automated fashion; and the ability to apply any necessary changes to the database since the last migration.

However, despite the benefits of using this type of tool, many organizations are still structured so that the Database Administrators (DBAs) cannot allow a tool like FlywayDB to access controlled environments, such as Production.  These controlled environments still require manual scripts to be run when migrating the database to newer versions.

This leads us to the question: how do we provide ‘upgrade scripts’ for these controlled environments, while still getting the benefits of using FlywayDB?  

Our resolution – we extended the existing functionality of FlywayDB – what we refer to as the ‘Flyway Script Generator’, and it does the following:

  • Utilizes existing Flyway migration scripts to make the content of the generated DBA-friendly manual script
    • At runtime, accepts as parameter the range of flyway versions that should be included – i.e. all the scripts needed since the last release
    • Uses the same audit table as Flyway (schema_version)
      • Therefore, have the same audit trail as normal Flyway – showing the migrations previously applied, when they were applied, script checksum etc.

The source code, with documentation and examples, can be found on Intelliware’s Github site at this link – currently supported are PostgreSQL and Oracle databases.

Here’s an example of the usage:

Usage: FlywayScriptGeneratorRunner [revision location] [start version] [end version] [filename] [database_type]
Revision Location = filesystem:[relative or absolute path]
Start Version = starting revision number
End Version = ending revision number
File name = output file name
Database Type = 'PostGresSQL' or 'Oracle' - if omitted, default is PostGres
Example:
java -jar flywaydb-script-generator filesystem:src/main/resources/dbmigrations 10 25 migrationScriptForDBAs.sql

To generate your own SQL script – take the example point it at your Flyway migration folder, specify the version range to include, run and voila!  You’ve just generated a reviewable, manual DB script that operations can run in any environment, while you can still take advantage of Flyway everywhere else.

We hope you find this code as useful as our clients found it!