Flyway: Database Migration Management
In this article, I will talk about one of the means of ensuring versioned schemas and database migration management - the Flyway library . Sooner or later, developers of any application based on a DBMS have to deal with the problematic versioning of a database schema. Alas, sometimes this problem is taken into consideration too late - for example, if the question of making changes to the database structure arises when the application is already in operation. But even at the development stage, control of the database schema causes no less problems than all other aspects of the versioning of the application: in the absence of a clear migration management system, the local, stand and operational bases can quickly “leave” without providing any information about their current state.
Performance providers routinely allow only in one form or another to export the current object model as a database schema. This process can be performed in the mode of re-creation (with the complete removal of the entire structure), updating (with changes) or reconciliation (without making changes). For example, in Hibernate, this is done using the hbm2ddl tool, the operation of which can be configured with a single configuration parameter in the hibernate.cfg.xml or persistence.xml file. However, re-creation (create mode) is undesirable if the database already has data, and updating (update mode) does not make all the changes, but only non-destructive ones (for example, columns and tables are not deleted) and does not take into account the required data restructuring. Often, if the data model has undergone many changes, it can be difficult to apply them to the operational base, especially if the current version of the database is unknown. One way or another, but you have to "stoop" to SQL scripts - this is where the question of version control comes up.
The main page of the project provides a clear table comparing the library with similar solutions, and here I want to focus on rich functionality, working with migrations in the form of simple SQL files or Java classes (the latter are essentially based on the Spring JDBC Template) and support for native SQL popular DBMSs (Oracle PL / SQL, SQL Server T / SQL, MySQL and PostgreSQL stored procedures).
Flyway integrates well with Ant, Maven and command-line tools, has an API for programmatically invoking and integrating with Spring, and works with many DBMSs. I will give an example of connecting Flyway to an existing project, the assembly of which is based on Maven, and Flyway is called when the Spring context starts. MySQL is used as a database in the project.
First, create the db / migration folder in the src / main / resources subdirectory of the project: migration scripts will be stored in it. Let's put there a pre-exported database script - with all the tables, views, indexes, etc. Name the file V1__Base_version.sql. The naming conventions for migrations are described in detail in the documentation , for now it’s enough to say that the file name starts with V, followed by the version number (with an arbitrary number of separator points), a double underscore and a description of the migration.
Add the core of the Flyway library in the project dependencies (dependencies section):
And to the assembly plugins (section build / plugins) - the Flyway plugin:
To launch Flyway through the plugin, it is better to create a separate account in the database. You can specify the user and password for connecting to the database here, in the plugin configuration:
Or in the command line options:
But a more convenient way, in case of assembly on Maven, is to place standard parameters in the Maven settings file (settings.xml file) and their further use in all similar projects:
If you need to initialize the current database from scratch, you can perform its cleaning. In this case, the entire contents of the database will be deleted:
Upon successful completion of the task, the database will be empty, and the following lines will appear in the Maven log:
If the database is up-to-date (corresponds to the script unloaded earlier), it is necessary to complete the task, which will create the necessary structure for maintaining versioning in it:
Next, you can make sure that the schema_version table appears in the database with a single record that corresponds to the current state of the database: We
integrate Flyway with the application as a Spring bean that starts before entityManagerFactory:
After starting the application on a clean base, it will be initialized with the V1__Base_version.sql script, in addition, a schema_version table will be created. In the log, you can observe the following:
If the application was launched on the basis identical to the last migration, then no changes will occur in the scheme, which will be reflected in the application log in the following lines:
In any case, with the correct integration of Flyway, the database should contain the above schema_version table with a single entry.
Create a file in the db / migration folder with the name V2__Test_change.sql and with the following contents:
After starting the application, we find the following lines in the log:
And make sure that the test_table was successfully created, and an entry about the applied migration appeared in the schema_version table:
Flyway, unlike, for example, the migration system in Rails, does not support rollback of changes. The authors of the library motivate this by the fact that after making destructive and irreversible changes, it is possible to roll back the state of the database so that all missing or changed data is restored to its previous state, in the general case, it is impossible. Instead, a reasonable approach to using redundancy mechanisms is proposed. For example, before applying the next migration, you can do a dump dump or a snapshot of the database (depending on the backup functionality available in a particular DBMS).
Performance providers routinely allow only in one form or another to export the current object model as a database schema. This process can be performed in the mode of re-creation (with the complete removal of the entire structure), updating (with changes) or reconciliation (without making changes). For example, in Hibernate, this is done using the hbm2ddl tool, the operation of which can be configured with a single configuration parameter in the hibernate.cfg.xml or persistence.xml file. However, re-creation (create mode) is undesirable if the database already has data, and updating (update mode) does not make all the changes, but only non-destructive ones (for example, columns and tables are not deleted) and does not take into account the required data restructuring. Often, if the data model has undergone many changes, it can be difficult to apply them to the operational base, especially if the current version of the database is unknown. One way or another, but you have to "stoop" to SQL scripts - this is where the question of version control comes up.
Flyway
The main page of the project provides a clear table comparing the library with similar solutions, and here I want to focus on rich functionality, working with migrations in the form of simple SQL files or Java classes (the latter are essentially based on the Spring JDBC Template) and support for native SQL popular DBMSs (Oracle PL / SQL, SQL Server T / SQL, MySQL and PostgreSQL stored procedures).
Flyway integrates well with Ant, Maven and command-line tools, has an API for programmatically invoking and integrating with Spring, and works with many DBMSs. I will give an example of connecting Flyway to an existing project, the assembly of which is based on Maven, and Flyway is called when the Spring context starts. MySQL is used as a database in the project.
Connect Flyway to the project
First, create the db / migration folder in the src / main / resources subdirectory of the project: migration scripts will be stored in it. Let's put there a pre-exported database script - with all the tables, views, indexes, etc. Name the file V1__Base_version.sql. The naming conventions for migrations are described in detail in the documentation , for now it’s enough to say that the file name starts with V, followed by the version number (with an arbitrary number of separator points), a double underscore and a description of the migration.
Add the core of the Flyway library in the project dependencies (dependencies section):
<dependency><groupId>com.googlecode.flyway</groupId><artifactId>flyway-core</artifactId><version>1.5</version></dependency>And to the assembly plugins (section build / plugins) - the Flyway plugin:
<plugin><groupId>com.googlecode.flyway</groupId><artifactId>flyway-maven-plugin</artifactId><version>1.5</version><configuration><driver>com.mysql.jdbc.Driver</driver><url>jdbc:mysql://localhost:3306/flywaytest?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&connectionCollation=utf8_general_ci&characterSetResults=UTF-8</url><baseDir>db/migration</baseDir></configuration></plugin>To launch Flyway through the plugin, it is better to create a separate account in the database. You can specify the user and password for connecting to the database here, in the plugin configuration:
<configuration><user>flyway</user><password>mySecretPassword</password>
...
</configuration>Or in the command line options:
-Dflyway.user=flyway -Dflyway.password=mySecretPwd
But a more convenient way, in case of assembly on Maven, is to place standard parameters in the Maven settings file (settings.xml file) and their further use in all similar projects:
<servers><server><id>flyway-db</id><username>flyway</username><password>mySecretPassword</password></server></servers>If you need to initialize the current database from scratch, you can perform its cleaning. In this case, the entire contents of the database will be deleted:
mvn flyway:clean
Upon successful completion of the task, the database will be empty, and the following lines will appear in the Maven log:
[INFO] --- flyway-maven-plugin:1.5:clean (default-cli) @ flyway-test-project ---
[INFO] Cleaned database schema 'flywaytest' (execution time 00:03.911s)
If the database is up-to-date (corresponds to the script unloaded earlier), it is necessary to complete the task, which will create the necessary structure for maintaining versioning in it:
mvn flyway:init -Dflyway.initialVersion=1 -Dflyway.initialDescription="Base version"Next, you can make sure that the schema_version table appears in the database with a single record that corresponds to the current state of the database: We
integrate Flyway with the application as a Spring bean that starts before entityManagerFactory:
<beanid="flyway"class="com.googlecode.flyway.core.Flyway"init-method="migrate"><propertyname="dataSource"ref="..."/>
...
</bean><!-- Ставим фабрику менеджеров сущностей в зависимость от Flyway, чтобы убедиться, что она будет выполнена после внесения изменений в базу --><beanclass="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"id="entityManagerFactory"depends-on="flyway">
...
</bean>After starting the application on a clean base, it will be initialized with the V1__Base_version.sql script, in addition, a schema_version table will be created. In the log, you can observe the following:
2012-04-04 06:42:09,279 INFO [com.googlecode.flyway.core.metadatatable.MetaDataTable] -- <Metadata table created: schema_version (Schema: flywaytest)>
2012-04-04 06:42:09,318 INFO [com.googlecode.flyway.core.migration.DbMigrator] -- <Current schema version: null>
2012-04-04 06:42:09,320 INFO [com.googlecode.flyway.core.migration.DbMigrator] -- <Migrating to version 1>
2012-04-04 06:42:24,897 INFO [com.googlecode.flyway.core.migration.DbMigrator] -- <Successfully applied 1 migration (execution time 00:15.615s).>
If the application was launched on the basis identical to the last migration, then no changes will occur in the scheme, which will be reflected in the application log in the following lines:
2012-04-04 06:36:14,081 INFO [com.googlecode.flyway.core.migration.DbMigrator] -- <Current schema version: 1>
2012-04-04 06:36:14,085 INFO [com.googlecode.flyway.core.migration.DbMigrator] -- <Schema is up to date. No migration necessary.>
In any case, with the correct integration of Flyway, the database should contain the above schema_version table with a single entry.
Create migration
Create a file in the db / migration folder with the name V2__Test_change.sql and with the following contents:
createtable test_table (
idbigint(20) notnull,
primary key(id)
);
After starting the application, we find the following lines in the log:
2012-04-04 06:51:02,708 INFO [com.googlecode.flyway.core.migration.DbMigrator] -- <Current schema version: 1>
2012-04-04 06:51:02,710 INFO [com.googlecode.flyway.core.migration.DbMigrator] -- <Migrating to version 2>
2012-04-04 06:51:03,137 INFO [com.googlecode.flyway.core.migration.DbMigrator] -- <Successfully applied 1 migration (execution time 00:00.480s).>
And make sure that the test_table was successfully created, and an entry about the applied migration appeared in the schema_version table:
Rollback migration
Flyway, unlike, for example, the migration system in Rails, does not support rollback of changes. The authors of the library motivate this by the fact that after making destructive and irreversible changes, it is possible to roll back the state of the database so that all missing or changed data is restored to its previous state, in the general case, it is impossible. Instead, a reasonable approach to using redundancy mechanisms is proposed. For example, before applying the next migration, you can do a dump dump or a snapshot of the database (depending on the backup functionality available in a particular DBMS).