Database Migrations with Flyway
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:
- System properties
- External configuration file
- Section
<
properties>
- 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.sql
containing 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:
- Checks database schema for metadata table (default
SCHEMA_VERSION
) If the metadata table does not exist, it creates it. - Scans the classpath for available migrations.
- 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.
- Marks all remaining migrations as pending. Then it sorts them in ascending version numbers and executes them in the specified order.
- 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