Flyway

September 2020 - Flyway-7.0.0-beta

Originally posted September 1st 2020 by Ajay Ahir on flywaydb.org

The Flyway Team has been hard at work on Flyway V7. We've spent a lot of time pouring over GitHub issues, working on some of the most wanted feature requests, and thinking hard about how to overcome some long-standing limitations.

Flyway 7 Beta

After much work, we're proud to announce we are releasing an early-access beta version of Flyway V7, which has a slew of big changes and new features.

This blog post serves as the main documentation source for the changes in Flyway V7 beta. As this is a beta version, everything here is liable to change between now and the full release.

The beta version is available through the normal update channels (Maven, Gradle, etc). See the full Maven Central listing.

Command line downloads

We want your feedback! We encourage you to give Flyway V7 Beta a try in your development environments. Let us know of any issues so we can fix them for the final release. Share your feedback on the GitHub repository, or shout about it on Twitter @flywaydb.

Read on to learn about the new features. See that we've added support for Azure Synapse. Inform yourself of the breaking changes to ensure you're ready to upgrade.

New Features

Cherry Pick Migrations

{% include teams.html %}

Specify which migrations to apply on migrate or undo with the new -cherryPick option.

For example:

  • Create migrations V1__create_table1.sql, V2__create_table2.sql, and R__create_view.sql
  • Run flyway migrate -cherryPick="1,create_view"

The schema history table now shows migration V1 and create_view as being successfully applied. However V2 has been skipped and is marked as 'ignored'. V2__create_table2.sql can be cherry picked for deployment at a later time.

This should unblock users in this issue who have encountered this limitation of flyway.

Mark as Applied

{% include teams.html %}

With the new configuration option skipExecutingMigrations, you can skip script execution on migrate or undo, only updating the schema history table. This means you can 'pretend' a migration was executed.

This is useful when executing an script against your database manually. For instance, when you apply an out-of-process change like a hotfix. The hotfix script can be turned into a migration and applied with skipExecutingMigrations=true. The schema history table will be updated, but the hotfix script won't be run gain.

When combined with cherryPick, you can gain a lot more control of how your deployments take place.

Delete Repeatable Migrations

Flyway can now cope with repeatable migrations that have been deleted. Before, missing entries in the schema history table caused a validation error. In Flyway 7, you can update 'missing' entries to be considered safely 'deleted' by running flyway repair.

For example:

  • Create repeatable migration R__create_view.sql
  • Run flyway migrate
  • Create migration V1__drop_view.sql
  • Run flyway migrate
    • To ensure the view is actually deleted in the database
  • Delete file R__create_view.sql
  • Run flyway validate
    • This produces an error as the R__create_view is 'missing'
  • Run flyway repair
  • Run flyway validate
    • No error, as R__create_view is now considered 'deleted' instead of missing

After this sequence of commands, R__createView.sql now appears as deleted in the schema history table.

Arbitrary Script Migrations

{% include teams.html %}

Plain SQL migrations not flexible enough? Now you can execute common shell scripts as migrations too. We support .ps1, .sh, .cmd, .bat, and .py files as migrations. This is a somewhat experimental feature that we think will offer you more flexibility to work around some trickier limitations of applying a migrations based approach to database changes.

One example is provided in this issue. The user wants to invoke a data import utility as it's quicker than writing many INSERT statements. In Flyway V7 you can now write a shell script migration which invokes SQL* Loader (sqlldr) to perform the bulk insert as part of a deployment.

For example:

  • Run migration V1__create_table.sql
  • Create a script V2__bulk_insert.sh
    • This shell script migration has the following contents: sqlldr parfile=dept_loader.par
  • Run migrate
  • V2__bulk_insert.sh is executed in order, much like a SQL migration
    • sqlldr is invoked, which performs the bulk insert

Amazon S3 Bucket & Google Cloud Storage Support

We now support support Amazon S3 buckets & Google Cloud Storage as locations.

Amazon S3

Locations starting with s3: point to a bucket in AWS S3 and are scanned recursively for migrations. They are in the format s3:<bucket>[/optionalfolder/subfolder]. Wildcards are not supported.

To use AWS S3, the AWS environment variables AWS_REGION, AWS_ACCESS_KEY_ID, and AWS_SECRET_ACCESS_KEY need to be set to the appropriate values for your S3 account.

The community had great interest in this and we are thankful for the GitHub issue and the code contributed in this pull request.

Google Cloud Storage

{% include teams.html %}

Locations starting with gcs: point to a bucket in Google Cloud Storage, may only contain SQL migrations, and are scanned recursively. They are in the format gcs:bucket(/optionalfolder/subfolder). To use GCS, the GCS library must be included, and the GCS environment variable GOOGLE_APPLICATION_CREDENTIALS must be set to the credentials file for the service account that has access to the bucket.

Supply JDBC properties in Clients

Flyway Teams feature

You can now specify any JDBC property from our Maven, Gradle, Command Line clients, and environment variables. Before this was only possible by directly using the Flyway API and manually altering the DataSource object.

This is achieved with the flyway.jdbcProperties.<myproperty>=<value> configuration option.

One use case for this is to supply an Azure access token as explained in this documentation as the accessToken property cannot be specified in the JDBC URL.

SQL*Plus Login File

Flyway Teams feature

We now support login.sql files for SQL*Plus users. Documentation can be found here.

login.sql is a file used to execute statements before every script run, and is typically used to configure the session in a consistent manner by calling SQL*Plus commands such as SET FEEDBACK and SET DEFINE.

Flyway will look for this file in all the valid migration locations, and load it if present and oracle.sqlplus is enabled.

Postgres SCRAM Authentication

We have upgraded the PostgreSQL JDBC driver so that SCRAM authentication is now possible.

Thanks to Fabian Koehler for raising this issue.

New Callbacks Between Versioned and Repeatable Migrations

There are two new callbacks, afterVersioned and beforeRepeatables.

This GitHub issue requested a callback to execute between versioned and repeatable migrations.

One use case for these is to execute SQL once after all versioned migrations have executed, such as that which creates a view, to optimize the repeatable migrations.

Support for New Databases

Azure Synapse

We now support Azure Synapse (Formerly Data Warehouse).

  • URL format: jdbc:sqlserver://host:port;databaseName=database
  • SSL support: Add ;encrypt=true to the JDBC URL
  • Ships with Flyway Command-line
  • Maven Central coordinates: com.microsoft.sqlserver:mssql-jdbc:7.2.0.jre8
  • Supported versions: 4.0
  • Default Java class: com.microsoft.sqlserver.jdbc.SQLServerDriver
  • Syntax: See SQL Server
  • Compatibility: See SQL Server
  • Authentication: See SQL Server
  • Limitations:

Example:

/* Single line comment */
CREATE TABLE test_user (
  id INT NOT NULL,
  name VARCHAR(25) NOT NULL,  -- this is a valid ' comment
  PRIMARY KEY NONCLUSTERED (name) NOT ENFORCED
);
GO

-- Placeholder
INSERT INTO ${tableName} (name) VALUES ('Mr. T');

Breaking Changes

Java 7 Compatibility is no Longer Guaranteed

We no longer guarantee support Java 7 as we have upgraded to Java 8.

This guarantee only applied to Enterprise customers. If you are concerned this change will affect your ability to use the latest versions of Flyway going forward please contact customer support.

Database Support Drop Out

Due to their age, we will no longer be supporting the following database versions in Community and Pro editions:

  • Derby 10.11
  • Postgres 9.4
  • Sybase ASE 16
  • HSQLDB 2.3

Breaking Changes

Try it out!

This is a beta release which, although very stable, we do not recommend to deploy in production just yet.

CLI

You can download the commandline from maven central:

Maven

Community

<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>7.0.0-beta1</version>
</plugin>

Enterprise

<plugin>
    <groupId>org.flywaydb.enterprise</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>7.0.0-beta1</version>
</plugin>

Gradle

Community

plugins {
    id "org.flywaydb.flyway" version "7.0.0-beta1"
}

Enterprise

plugins {
    id "org.flywaydb.flyway.enterprise" version "7.0.0-beta1"
}

API

Community

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>7.0.0-beta1</version>
</dependency>

Enterprise

<dependency>
    <groupId>org.flywaydb.enterprise</groupId>
    <artifactId>flyway-core</artifactId>
    <version>7.0.0-beta1</version>
</dependency>

You can find a detailed list of issues fixed in the release notes.

Enjoy!


Didn't find what you were looking for?