Redgate Flyway

Migrations

Migrations capture incremental changes to your development database. These are SQL scripts that can capture schema and data changes (eg., CREATE, ALTER, INSERT, UPDATE, etc.).  They should be tracked in version control.  These scripts are used to deploy to your other environments in a consistent way.  The same scripts will be run in the same order on each environment, which gives you a repeatable deployment process.

Migrations can be either versioned or repeatable or baseline. Versioned migrations come in 2 forms: regular and undo.

Migrate

Versioned and repeatable migrations are executed via the Migrate command.

Migrate is the centerpiece of the Flyway workflow. It will scan the filesystem or your classpath for available migrations. It will compare them to the migrations that have been applied to the database. If any difference is found, it will migrate the database to close the gap.

Migrate should preferably be executed on application startup to avoid any incompatibilities between the database and the expectations of the code.

Executing migrate is idempotent and can be done safely regardless of the current version of the schema.

Example 1: We have migrations available up to version 9, and the database is at version 5.

Migrate will apply the migrations 6, 7, 8 and 9 in order.

Example 2: We have migrations available up to version 9, and the database is at version 9.

Migrate does nothing.

Undo

Undo migrations are the opposite of regular versioned migrations. An undo migration is responsible for undoing the effects of the versioned migration with the same version.  Undo migrations are executed via the undo command.

Callbacks

While migrations are sufficient for most needs, there are certain situations that require you to execute the same action over and over again. This could be recompiling procedures, updating materialized views and many other types of housekeeping.

Callbacks can be used to hook into the migration processes at different points in the lifecycle to accomplish this.

File formats

Migrations are most commonly written in SQL, but Flyway also supports Java-based migrations and script migrations.

Schema history

To keep track of which migrations have already been applied when and by whom, Flyway adds a schema history table to your schema. 

Discovery

Flyway discovers migrations from one or more directories, classpath locations referenced by the locations property.

Once you have configured the locations you want to use, Flyway will automatically pick up SQL migrations as long as they conform to the configured naming convention.

This scanning is recursive. All migrations in non-hidden directories/packages below the specified ones are also picked up.

Migration execution

Migrations are executed using the configured JDBC driver.

Within a single migration run, all pending versioned migrations are applied in order first followed by any repeatable migrations that have changed.  Repeatable migrations are applied in the order of their description. It is also possible to configure more advanced rules for conditional execution.

When Flyway executes SQL statements it reports all warnings returned by the database. In case an error is returned Flyway displays it with all necessary details, marks the migration as failed and automatically rolls it back if possible.

Error and logging handling originate from the driver but can be overridden in Flyway.

Migration generation

You can either write your migration scripts manually and add them to the relevant location or use Flyway's database comparison technology to automatically generate migration scripts based upon a schema model or development environment.

See Generating migrations.

Migration validation

The following validation can be run on your migrations at any time, and do not require access to a target environment:

  • Validate migration deployment - note that this requires a build environment in a baseline state
  • Run code analysis against migrations
    • This can be done either by integrating with SQL Fluff or by defining regular expression rules

Deployment reports

Ahead of migrating a database it may be advisable to check that database for drift, in order to ensure that it is still in the expected state for running deployments.

It is also possible to generate some reports for manual review ahead of running deployment:

  • Changes report providing a diff view of all modified objects
  • Dry run script indicating what will be executed against the target database 

These reports can also be kept as a deployment record

Placeholders

In addition to regular syntax, Flyway also supports placeholder replacement in all types of migrations. By default it looks for Ant-style placeholders like ${myplaceholder}. This can be very useful to abstract differences between environments.

See Placeholders.

Transaction handling

By default Flyway wraps the execution of each migration script in a single transaction and applies them in order.

See Migration Transaction Handling.

Schema handling

By default, Flyway will attempt to create the schemas provided by the schemas and defaultSchema configuration options for the given environment. This behavior can be toggled with the createSchemas configuration option. These same schemas will be deleted on clean.

For SQL Server it is recommended to instead allow Flyway control of all schemas, by setting clean mode to all. With SQL Server, schemas are often used almost as folders for organizing a database, rather than part of the database connection configuration, so listing all schemas explicitly in configuration tends to be noisy and hard to maintain. In Flyway Desktop configuration of SQL Server schemas is suppressed with this in mind.

Script configuration

It is possible to configure SQL migrations on a per-script basis. This is achieved by creating a script configuration file in the same folder as the migration. The script configuration file name must match the migration file name, with the .conf suffix added.

These files allow for customising transaction handling and conditional deployment. For more information see the script configuration reference documentation.

When generating migrations through Flyway Desktop, script configuration will be automatically generated if a script contains objects which cannot be wrapped in a transaction.



Didn't find what you were looking for?