Concepts-->Migrations

https://flywaydb.org/documentation/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 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.

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:

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.

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 non-hidden 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.

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.