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:

Open

Open

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 

Onboarding an existing database to use Flyway

Extract the DDL and reference data from production

First start by taking a snapshot of your most important database: production. This will be the starting point for migrations.

Generate a SQL script that includes the entire DDL (including indexes, triggers, procedures …) of the production database.

This script will form your baseline migration. Save it in the same location in the GIT repository where you plan to have your future versioned and repeatable migrations. Give it a relevant version number and description such as V0__baseline_migration.sql.

Give these databases a baseline version

Now comes the time to baseline the databases that contain data (including production) with a baseline version. This will automatically happen when we run the flyway pipeline for the first time.. it will mark it as the baselined version on the flyway history schema that will be created and future migrations with version > 0.0 will be released as versioned migrations.