Flyway Concepts

Overview

With Flyway all changes to the database are called migrations. Migrations can be either versioned or repeatable.

Flyway automatically discovers migrations on the filesystem

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

How Flyway Works?

For each migration It will try to locate its schema history table. If it does no find it, it will create one instead. This table will be used to track the state of the database. Immediately afterwards Flyway will begin scanning the filesystem for migrations. The versioned migrations are then sorted based on their version number and applied in order, followed by the repeatable migrations. As each migration gets applied, the schema history table is updated accordingly. flyway_schema_history(sample)

For every subsequent migration flyway will scan the filesystem of the application for migrations. The migrations are checked against the schema history table. If their version number is lower or equal to the one of the version marked as current, they are ignored. The remaining migrations are the pending migrations: available, but not applied. They are then sorted by version number and executed in order, followed by the repeatable migrations that have a checksum difference. The schema history table is updated accordingly:

flyway_schema_history

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 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

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.

Naming

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

The file name consists of the following parts:

• Prefix: V for versioned and R for repeatable migrations

• Version: Version with dots or underscores separate as many parts as you like (Not for repeatable migrations)

• Separator: __ (two underscores)

• Description: Underscores or spaces separate the words

• Suffix: .sql