Want to deploy your JVM, Node.js and Go apps effortlessly to AWS? Try our service Boxfuse

Migrations

Overview

With Flyway all changes to the database are called migrations. Migrations can be either versioned or repeatable. Versioned migrations come in 2 forms: regular and undo.

Versioned migrations have a version, a description and a checksum. The version must be unique. The description is purely informative for you to be able to remember what each migration does. The checksum is there to detect accidental changes. Versioned migrations are the most common type of migration. They are applied in order exactly once.

Optionally their effect can be undone by supplying an undo migration with the same version.

Repeatable migrations have a description and a checksum, but no version. Instead of being run just once, they are (re-)applied every time their checksum changes.

Within a single migration run, repeatable migrations are always applied last, after all pending versioned migrations have been executed. Repeatable migrations are applied in the order of their description.

By default both versioned and repeatable migrations can be written either in SQL or in Java and can consist of multiple statements.

Flyway automatically discovers migrations on the filesystem and on the Java classpath.

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

Versioned Migrations

The most common type of migration is a versioned migration. Each versioned migration has a version, a description and a checksum. The version must be unique. The description is purely informative for you to be able to remember what each migration does. The checksum is there to detect accidental changes. Versioned migrations are the most common type of migration. They are applied in order exactly once.

Versioned migrations are typically used for:

  • Creating/altering/dropping tables/indexes/foreign keys/enums/UDTs/…
  • Reference data updates
  • User data corrections

Here is a small example:

CREATE TABLE car (
    id INT NOT NULL PRIMARY KEY,
    license_plate VARCHAR NOT NULL,
    color VARCHAR NOT NULL
);

ALTER TABLE owner ADD driver_license_id VARCHAR;

INSERT INTO brand (name) VALUES ('DeLorean');

Each versioned migration must be assigned a unique version. Any version is valid as long as it conforms to the usual dotted notation. For most cases a simple increasing integer should be all you need. However Flyway is quite flexible and all these versions are valid versioned migration versions:

  • 1
  • 001
  • 5.2
  • 1.2.3.4.5.6.7.8.9
  • 205.68
  • 20130115113556
  • 2013.1.15.11.35.56
  • 2013.01.15.11.35.56

Versioned migrations are applied in the order of their versions. Versions are sorted numerically as you would normally expect.

Undo Migrations

Flyway Pro

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 optional and not required to run regular versioned migrations.

For the example above, this is how the undo migration would look like:

DELETE FROM brand WHERE name='DeLorean';

ALTER TABLE owner DROP driver_license_id;

DROP TABLE car;

Important Notes

While the idea of undo migrations is nice, unfortunately it sometimes breaks down in practice. As soon as you have destructive changes (drop, delete, truncate, …), you start getting into trouble. And even if you don’t, you end up creating home-made alternatives for restoring backups, which need to be properly tested as well.

Undo migrations assume the whole migration succeeded and should now be undone. This does not help with failed versioned migrations on databases without DDL transactions. Why? A migration can fail at any point. If you have 10 statements, it is possible for the 1st, the 5th, the 7th or the 10th to fail. There is simply no way to know in advance. In contrast, undo migrations are written to undo an entire versioned migration and will not help under such conditions.

An alternative approach which we find preferable is to maintain backwards compatibility between the DB and all versions of the code currently deployed in production. This way a failed migration is not a disaster. The old version of the application is still compatible with the DB, so you can simply roll back the application code, investigate, and take corrective measures.

This should be complemented with a proper, well tested, backup and restore strategy. It is independent of the database structure, and once it is tested and proven to work, no migration script can break it. For optimal performance, and if your infrastructure supports this, we recommend using the snapshot technology of your underlying storage solution. Especially for larger data volumes, this can be several orders of magnitude faster than traditional backups and restores.

Repeatable Migrations

Repeatable migrations have a description and a checksum, but no version. Instead of being run just once, they are (re-)applied every time their checksum changes.

This is very useful for managing database objects whose definition can then simply be maintained in a single file in version control. They are typically used for

  • (Re-)creating views/procedures/functions/packages/…
  • Bulk reference data reinserts

Within a single migration run, repeatable migrations are always applied last, after all pending versioned migrations have been executed. Repeatable migrations are applied in the order of their description.

It is your responsibility to ensure the same repeatable migration can be applied multiple times. This usually involves making use of CREATE OR REPLACE clauses in your DDL statements.

Here is an example of what a repeatable migration looks like:

CREATE OR REPLACE VIEW blue_cars AS 
    SELECT id, license_plate FROM cars WHERE color='blue';

SQL-based migrations

Migrations are most commonly written in SQL. This makes it easy to get started and leverage any existing scripts, tools and skills. It gives you access to the full set of capabilities of your database and eliminates the need to understand any intermediate translation layer.

SQL-based migrations are typically used for

  • DDL changes (CREATE/ALTER/DROP statements for TABLES,VIEWS,TRIGGERS,SEQUENCES,…)
  • Simple reference data changes (CRUD in reference data tables)
  • Simple bulk data changes (CRUD in regular data tables)

Naming

In order to be picked up by Flyway, SQL migrations must comply with the following naming pattern:

Versioned Migrations

Prefix  Separator       Suffix
                      
   V2__Add_new_table.sql
              
 Version    Description

Undo Migrations

Prefix  Separator       Suffix
                      
   U2__Add_new_table.sql
              
 Version    Description

Repeatable Migrations

Prefix Separator       Suffix
                     
    R__Add_new_table.sql
               
           Description

The file name consists of the following parts:

  • Prefix: V for versioned (configurable), U for undo (configurable) and R for repeatable migrations (configurable)
  • Version: Version with dots or underscores separate as many parts as you like (Not for repeatable migrations)
  • Separator: __ (two underscores) (configurable)
  • Description: Underscores or spaces separate the words
  • Suffix: .sql (configurable)

Discovery

Flyway discovers SQL-based migrations both on the filesystem and on the Java classpath. Migrations reside in one or more directories referenced by the locations property.

Locations with the filesystem: prefix target the file system.
Unprefixed locations or locations with the classpath: prefix target the Java classpath.

 my-project
   src
     main
       resources
         db
   migration                 classpath:db/migration 
             R__My_view.sql
             U1.1__Fix_indexes.sql
             U2__Add a new table.sql
             V1__Initial_version.sql
             V1.1__Fix_indexes.sql
             V2__Add a new table.sql
   my-other-folder                   filesystem:/my-project/my-other-folder
     U1.2__Add_constraints.sql
     V1.2__Add_constraints.sql

New SQL-based migrations are discovered automatically through filesystem and Java classpath scanning at runtime. Once you have configured the locations you want to use, Flyway will automatically pick up any new SQL migrations as long as they conform to the configured naming convention.

This scanning is recursive. All migrations in directories below the specified ones are also picked up.

Syntax

Flyway supports all regular SQL syntax elements including:

  • Single- or multi-line statements
  • Single- (–) or Multi-line (/* */) comments spanning complete lines
  • Database-specific SQL syntax extensions (PL/SQL, T-SQL, …) typically used to define stored procedures, packages, …

Additionally in the case of Oracle, Flyway also supports SQL*Plus commands.

Placeholder Replacement

In addition to regular SQL syntax, Flyway also supports placeholder replacement with configurable pre- and suffixes. By default it looks for Ant-style placeholders like ${myplaceholder}.

This can be very useful to abstract differences between environments.

Example

Here is a small example of the supported syntax:

/* Single line comment */
CREATE TABLE test_user (
  name VARCHAR(25) NOT NULL,
  PRIMARY KEY(name)
);

/*
Multi-line
comment
*/

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

Java-based migrations

Java-based migrations are a great fit for all changes that can not easily be expressed using SQL.

These would typically be things like

  • BLOB & CLOB changes
  • Advanced bulk data changes (Recalculations, advanced format changes, …)

Naming

In order to be picked up by Flyway, Java-based Migrations must implement the JdbcMigration interface.

By default Flyway will automatically extract the version and the description from the class name. To be able to do so, the class name must comply with the following naming pattern:

Versioned Migrations

Prefix  Separator
      
   V2__Add_new_table
              
 Version    Description

Undo Migrations

Prefix  Separator
      
   U2__Add_new_table
              
 Version    Description

Repeatable Migrations

Prefix Separator
     
    R__Add_new_table
               
           Description

The file name consists of the following parts:

  • Prefix: V for versioned migrations, U for undo migrations, R for repeatable migrations
  • Version: Underscores (automatically replaced by dots at runtime) separate as many parts as you like (Not for repeatable migrations)
  • Separator: __ (two underscores)
  • Description: Underscores (automatically replaced by spaces at runtime) separate the words

If you need more control over the class name, you can override the default convention by implementing the MigrationInfoProvider interface.

This will allow you to name your class as you wish. Version, description and migration category are provided by implementing the respective methods.

Discovery

Flyway discovers Java-based migrations on the Java classpath in the packages referenced by the locations property.

 my-project
   src
     main
       java
         db
   migration             classpath:db/migration 
             R__My_view
             U1_1__Fix_indexes
             V1__Initial_version
             V1_1__Fix_indexes
   pom.xml

New java migrations are discovered automatically through classpath scanning at runtime. The scanning is recursive. Java migrations in subpackages of the specified ones are also picked up.

Checksums and Validation

Unlike SQL migrations, Java migrations by default do not have a checksum and therefore do not participate in the change detection of Flyway’s validation. This can be remedied by implementing the MigrationChecksumProvider interface. Your implementation of the getChecksum() method can then provide your own checksum, which will then be stored and validated for changes.

Sample Class

package db.migration;

import org.flywaydb.core.api.migration.jdbc.JdbcMigration;
import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 * Example of a Java-based migration.
 */
public class V1_2__Another_user implements JdbcMigration {
    public void migrate(Connection connection) throws Exception {
        PreparedStatement statement =
            connection.prepareStatement("INSERT INTO test_user (name) VALUES ('Obelix')");

        try {
            statement.execute();
        } finally {
            statement.close();
        }
    }
}

Spring support (Optional)

If you use Spring, you can choose to implement the SpringJdbcMigration interface instead. It works exactly like the JdbcMigration interface, the only difference being that you now receive a convenient Spring JdbcTemplate to work with instead of a plain JDBC connection.

package db.migration;

import org.flywaydb.core.api.migration.spring.SpringJdbcMigration;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * Example of a Spring Jdbc migration.
 */
public class V1_2__Another_user implements SpringJdbcMigration {
    public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
        jdbcTemplate.execute("INSERT INTO test_user (name) VALUES ('Obelix')");
    }
}

Transactions

By default, Flyway always wraps the execution of an entire migration within a single transaction.

Alternatively you can also configure Flyway to wrap the entire execution of all migrations of a single migration run within a single transaction by setting the group property to true.

If Flyway detects that a specific statement cannot be run within a transaction due to technical limitations of your database, it won’t run that migration within a transaction. Instead it will be marked as non-transactional.

By default transactional and non-transactional statements cannot be mixed within a migration run. You can however allow this by setting the mixed property to true.

Important Note

If your database cleanly supports DDL statements within a transaction, failed migrations will always be rolled back (unless they were marked as non-transactional).

If on the other hand your database does NOT cleanly supports DDL statements within a transaction (by for example issuing an implicit commit before and after every DDL statement), Flyway won’t be able to perform a clean rollback in case of failure and will instead mark the migration as failed, indicating that some manual cleanup may be required.

Query Results

Flyway Pro

Migrations are primarily meant to be executed as part of release and deployment automation processes and there is rarely the need to visually inspect the result of SQL queries.

There are however some scenarios where such manual inspection makes sense, and therefore Flyway Pro and Enterprise Edition also display query results in the usual tabular form when a SELECT statement (or any other statement that returns results) is executed.

Schema History Table

To keep track of which migrations have already been applied when and by whom, Flyway adds a special schema history table to your schema. You can think of this table as a complete audit trail of all changes performed against the schema. It also tracks migration checksums and whether or not the migrations were successful.

Read more about this in our getting started guide on how Flyway works.

Migration States

Migrations are either resolved or applied. Resolved migrations have been detected by Flyway’s filesystem and classpath scanner. Initially they are pending. Once they are executed against the database, they become applied.

When the migration succeeds it is marked as success in Flyway’s schema history table.

When the migration fails and the database supports DDL transactions, the migration is rolled back and nothing is recorded in the schema history table.

When the migration fails and the database doesn’t supports DDL transactions, the migration is marked as failed in the schema history table, indicating manual database cleanup may be required.

Versioned migrations whose effects have been undone by an undo migration are marked as undone.

Repeatable migrations whose checksum has changed since they are last applied are marked as outdated until they are executed again.

When Flyway discovers an applied versioned migration with a version that is higher than the highest known version (this happens typically when a newer version of the software has migrated that schema), that migration is marked as future.

Callbacks