Database Migrations with Flyway

Translation of the article prepared in advance of the start of the course Java Developer.


1. Introduction

This article describes key concepts. Flyway and an example of using this framework to continuously change the database schema using the in-memory H2 database as an example using the maven flyway plugin.

Flyway updates database versions using migrations. Migrations can be written in SQL (with syntax specific to a specific DBMS) or in Java.

Migrations can be versioned or recurring. The former have a unique version and are applied exactly once. The second ones do not have a version number, and they apply when their checksum changes.
Repeated migrations within a single run are always applied after versioned migrations are completed. Repeated migrations are applied in the order they are described. In one migration, all operations are performed within a single database transaction.

In this article, we will focus on using the maven plugin for database migrations.

2. Flyway maven plugin

Add flyway maven plugin to pom.xml:


    org.flywaydb
    flyway-maven-plugin
    4.0.3

The current version of the plugin can be viewed in Maven central.
A list of plugin parameters can be found in documentation. Plugin parameters can be configured in four different ways.

2.1. Section plugin
Parameters can be specified directly in the tag in the plugin section in pom.xml:

org.flywaydb
flyway-maven-plugin
4.0.3
databaseUser
databasePassword
schemaName

2.2. Maven properties

You can also configure the plugin by specifying options in <properties>:


    ...
    
        databaseUser
        databasePassword
        schemaName
        ...
    
    ...

2.3. External configuration file

Or describe the configuration in a separate .properties-file:

flyway.user=databaseUser
flyway.password=databasePassword
flyway.schemas=schemaName
...

Default configuration file name flyway.properties. This file must be in the same directory as the file. pom.xml. The encoding is set in the parameter flyway.encoding (default is UTF-8).

If you use a different name for the file (e.g. customConfig.properties), then it must be specified explicitly when calling maven:

$ mvn -Dflyway.configFile=customConfig.properties

2.4. System properties

Finally, parameters can be specified as system properties when calling maven from the command line:

$ mvn -Dflyway.user=databaseUser -Dflyway.password=databasePassword
  -Dflyway.schemas=schemaName

If the configuration is specified in several ways, the priority will be as follows:

  1. System properties
  2. External configuration file
  3. Section <properties>
  4. Section <configuration> plugin

3. Migration example

In this section, we will consider the necessary steps for migrating a database schema using the in-memory H2 database as an example using the maven plugin. To configure Flyway, we will use an external file.

3.1. POM changes

To get started, add a dependency on H2:


    com.h2database
    h2
    1.4.196

Here we can also check the latest available driver version in Maven central. Add a plugin for Flyway, as described previously.

3.2. Configure Flyway in an external file

Create in $PROJECT_ROOT file myFlywayConfig.properties with the following contents:

flyway.user=databaseUser
flyway.password=databasePassword
flyway.schemas=app-db
flyway.url=jdbc:h2:mem:DATABASE
flyway.locations=filesystem:db/migration

The above configuration indicates that the migration scripts are in the directory db/migration, and to connect to the H2 database are used databaseUser and databasePassword.

Database schema for application - app-db.

Of course, in the parameters flyway.user, flyway.password and flyway.url You must provide the name, password, and URL of your database.

3.3. First migration

Under Flyway conventions, the names of the migration scripts must be in the following format:

<Version> __<Description> .sql

Where:

  • <Prefix> - prefix. For versioned migrations, the default is “V”. The prefix is ​​configured through the flyway.sqlMigrationPrefix property.
  • <Version> - migration version number. The major and minor versions can be separated by underlining. Version should always start with 1.
  • <Description> - a textual description of the migration. The description must be separated from the version number by two underscores.

Example: V1_1_0__my_first_migration.sql

So let's create a directory db/migration at $PROJECT_ROOT with migration script V1_0__create_employee_schema.sql and SQL to create the table employee:

CREATE TABLE IF NOT EXISTS `employee` (
 
    `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` varchar(20),
    `email` varchar(50),
    `date_of_birth` timestamp
 
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

3.4. We carry out migrations

Further in $PROJECT_ROOT run the following maven command to apply database migrations:

$ mvn clean flyway:migrate -Dflyway.configFile=myFlywayConfig.properties

Our first migration must be completed.
Now the database schema is as follows:
employee:

+----+------+-------+---------------+
| id | name | email | date_of_birth |
+----+------+-------+---------------+

We can repeat the previous steps to perform other migrations.

3.5. Second migration

For the second migration, create a file with the name V2_0_create_department_schema.sqlcontaining the following two queries:

CREATE TABLE IF NOT EXISTS `department` (
 
`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(20)
 
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 
ALTER TABLE `employee` ADD `dept_id` int AFTER `email`;

Let's perform the migration, just as we did for the first migration.
Now our database schema has changed: in employee a new column is added and a new table is created department:

employee:

+----+------+-------+---------+---------------+
| id | name | email | dept_id | date_of_birth |
+----+------+-------+---------+---------------+

department:

+----+------+
| id | name |
+----+------+

To verify that both migrations were successful, run the following maven command:

$ mvn flyway:info -Dflyway.configFile=myFlywayConfig.properties

4. Disabling Flyway in Spring Boot

Sometimes you may need to disable Flyway migrations.

This may be needed during tests when a database schema is generated based on entities. In this case, you can disable Flyway for the test profile.

In Spring Boot, this is very simple.

4.1. Spring boot 1.x

All we need to do is set the property flyway.enabled in file application-test.properties:

flyway.enabled=false

4.2. Spring boot 2.x

In later versions of Spring Boot, this property has been changed to

spring.flyway.enabled:
spring.flyway.enabled=false

4.3 Empty FlywayMigrationStrategy

If we want to disable only automatic Flyway migration at startup, but want to start migrations manually, then using the above properties will not work for us.

This is due to the fact that Spring Boot will not automatically configure Flyway bins and, therefore, we will have to configure them ourselves, which is not very convenient.

In this case, we can leave Flyway turned on and implement empty FlywayMigrationStrategy:

@Configuration
public class EmptyMigrationStrategyConfig {
 
    @Bean
    public FlywayMigrationStrategy flywayMigrationStrategy() {
        return flyway -> {
            // do nothing 
        };
    }
}

In fact, this will disable Flyway migrations when the application starts.

But we can still start the migration manually:

@RunWith(SpringRunner.class)
@SpringBootTest
public class ManualFlywayMigrationIntegrationTest {
 
    @Autowired
    private Flyway flyway;
 
    @Test
    public void skipAutomaticAndTriggerManualFlywayMigration() {
        flyway.migrate();
    }
}

5. How Flyway Works

To track when, by whom and what migrations were applied, a special table with metadata is added to the database schema. This table also stores the checksums of migrations and information on whether the migration was successful or not.

The framework works as follows:

  1. Checks database schema for metadata table (default SCHEMA_VERSION) If the metadata table does not exist, it creates it.
  2. Scans the classpath for available migrations.
  3. Compares migrations to a metadata table. If the version number is less than or equal to the version marked as current, then it is ignored.
  4. Marks all remaining migrations as pending. Then it sorts them in ascending version numbers and executes them in the specified order.
  5. As migrations apply, updates the metadata table.

6. Teams

Flyway has the following main migration management teams:

  • Info. Displays the current status / version of the database schema. Information about which migrations are expected, which were applied, the status of completed migrations and the date of their completion.
  • Migrate. Updating the database schema to the current version. Scan a classpath to find available migrations and apply pending migrations.
  • Baseline. Installing the database schema version, ignoring migrations up to and including baselineVersion. Baseline helps you use Flyway on an existing database. New migrations are applied as usual.
  • Validate. Checking the current database schema against available migrations.
  • Repair. Restore metadata table.
  • Clean. Deletion of all objects in the scheme. Of course, you never need to use clean in production databases.

7. Conclusion

  • In this article, we showed how Flyway works and how it can be used to reliably and easily manage database changes.
  • The article code is available at Github.

MANAGE DATABASE VERSIONS VIA FLYWAY


Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *