Flyway: database migration management
In this article I will talk about one of the tools to ensure versioning of schemes and database migration management - the Flyway library. Sooner or later, developers of any application relying on a DBMS have to deal with the versioned version of the database scheme. Alas, sometimes this problem is taken into consideration too late - for example, if the question of amending the structure of the database arises when the application is already in operation. But even at the development stage, the control of the database scheme causes no less problems than all other aspects of application versioning: in the absence of a clear migration management system, the local, poster and operational bases can quickly “disperse” without providing any information about their current state.
Persistent providers regularly allow only in one form or another to export the actual object model in the form of a database schema. This process can be performed in re-creation mode (with complete deletion of the entire structure), update (with changes) or reconciliation (without changes). For example, in Hibernate, this is done using the hbm2ddl tool, whose operation can be configured with a single configuration parameter in the hibernate.cfg.xml file or persistence.xml. However, the re-creation (create mode) is undesirable if the database already has data, and the update (update mode) does not make all the changes, but only non-destructive (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 database, especially if the current version of the database is unknown. One way or another, but you have to “sink” to SQL scripts - this is where the issue of version control comes up.
On the main page of the project, there is a visual table comparing the library with similar solutions, and here you want to focus on rich functionality, work with migrations in the form of simple SQL files or Java classes (the latter are essentially based on 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 the command line tools, has API for program calling and integration with Spring, works with many DBMS. I will give an example of connecting Flyway to an existing project, the build of which is based on Maven, and the Flyway call is made at the start of the Spring context. MySQL is used as a database in the project.
First, create a db / migration folder in the project’s src / main / resources subdirectory: it will contain migration scripts. Place the previously exported database script with all the tables, views, indexes, etc. Name the file V1__Base_version.sql. The agreements on the naming of migrations are described in detail in the documentation , so far it suffices to say that the file name begins with V, followed by the version number (with an arbitrary number of separator points), a double underscore, and a description of the migration.
Add in the dependencies of the project (dependencies section) the core of the Flyway library:
And to build plugins (build / plugins section) - Flyway plugin:
To run Flyway through the plugin, it is better to create a separate account in the database. You can specify a user and password to connect to the database here, in the configuration of the plugin:
Or in the command line parameters:
But a more convenient way, in the case of assembly on Maven, is to place typical parameters in the Maven configuration file (settings.xml file) and further use them in all similar projects:
If you need to initialize the current database from scratch, you can clean it up. In this case, the entire contents of the database will be deleted:
If the task is successfully completed, the base will be empty, and the following lines will appear in the Maven log:
If the base is up to date (corresponds to the script that was previously uploaded), you need to complete the task that will create the structure necessary for maintaining versioning in it:
Then you can make sure that the schema_version table appeared in the database with a single entry corresponding to the current state of the database:
We will integrate Flyway with the application as a Spring bean, which starts before entityManagerFactory:
After running the application on a clean database, it will be initialized with the V1__Base_version.sql script, in addition, a schema_version table will be created. In this case, you can see the following in the log:
If the application was launched on a base identical to the last migration, then no changes will occur in the schema, which will be reflected in the application log in the following lines:
In any case, if Flyway is correctly integrated, the database should contain the schema_version table above 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 will find the following lines in the log:
And make sure that the test_table table was successfully created, and an entry about the applied migration appeared in the schema_version table:
Flyway, unlike, for example, from the system of migration to Rails, does not support rollback of changes. The authors of the library motivate it by the fact that after making destructive and irreversible changes, it is impossible to roll back the state of the database so that all missing or changed data will be restored to its previous state. Instead, a completely rational approach is proposed using reservation mechanisms. For example, before applying the next migration, you can unload a dump or a snapshot of the database (depending on the backup functionality available in a particular DBMS).
Persistent providers regularly allow only in one form or another to export the actual object model in the form of a database schema. This process can be performed in re-creation mode (with complete deletion of the entire structure), update (with changes) or reconciliation (without changes). For example, in Hibernate, this is done using the hbm2ddl tool, whose operation can be configured with a single configuration parameter in the hibernate.cfg.xml file or persistence.xml. However, the re-creation (create mode) is undesirable if the database already has data, and the update (update mode) does not make all the changes, but only non-destructive (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 database, especially if the current version of the database is unknown. One way or another, but you have to “sink” to SQL scripts - this is where the issue of version control comes up.
Flyway
On the main page of the project, there is a visual table comparing the library with similar solutions, and here you want to focus on rich functionality, work with migrations in the form of simple SQL files or Java classes (the latter are essentially based on 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 the command line tools, has API for program calling and integration with Spring, works with many DBMS. I will give an example of connecting Flyway to an existing project, the build of which is based on Maven, and the Flyway call is made at the start of the Spring context. MySQL is used as a database in the project.
Flyway connection to the project
First, create a db / migration folder in the project’s src / main / resources subdirectory: it will contain migration scripts. Place the previously exported database script with all the tables, views, indexes, etc. Name the file V1__Base_version.sql. The agreements on the naming of migrations are described in detail in the documentation , so far it suffices to say that the file name begins with V, followed by the version number (with an arbitrary number of separator points), a double underscore, and a description of the migration.
Add in the dependencies of the project (dependencies section) the core of the Flyway library:
<dependency> <groupId>com.googlecode.flyway</groupId> <artifactId>flyway-core</artifactId> <version>1.5</version> </dependency> And to build plugins (build / plugins section) - 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 run Flyway through the plugin, it is better to create a separate account in the database. You can specify a user and password to connect to the database here, in the configuration of the plugin:
<configuration> <user>flyway</user> <password>mySecretPassword</password> ... </configuration> Or in the command line parameters:
-Dflyway.user=flyway -Dflyway.password=mySecretPwd But a more convenient way, in the case of assembly on Maven, is to place typical parameters in the Maven configuration file (settings.xml file) and further use them 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 clean it up. In this case, the entire contents of the database will be deleted:
mvn flyway:clean If the task is successfully completed, the base 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 base is up to date (corresponds to the script that was previously uploaded), you need to complete the task that will create the structure necessary for maintaining versioning in it:
mvn flyway:init -Dflyway.initialVersion=1 -Dflyway.initialDescription="Base version" Then you can make sure that the schema_version table appeared in the database with a single entry corresponding to the current state of the database:
We will integrate Flyway with the application as a Spring bean, which starts before entityManagerFactory:
<bean id="flyway" class="com.googlecode.flyway.core.Flyway" init-method="migrate"> <property name="dataSource" ref="..."/> ... </bean> <!-- Flyway, , --> <bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactory" depends-on="flyway"> ... </bean> After running the application on a clean database, it will be initialized with the V1__Base_version.sql script, in addition, a schema_version table will be created. In this case, you can see the following in the log:
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 a base identical to the last migration, then no changes will occur in the schema, 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, if Flyway is correctly integrated, the database should contain the schema_version table above 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:
create table test_table ( id bigint(20) not null, primary key(id) ); After starting the application, we will 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 table was successfully created, and an entry about the applied migration appeared in the schema_version table:
Roll back migration
Flyway, unlike, for example, from the system of migration to Rails, does not support rollback of changes. The authors of the library motivate it by the fact that after making destructive and irreversible changes, it is impossible to roll back the state of the database so that all missing or changed data will be restored to its previous state. Instead, a completely rational approach is proposed using reservation mechanisms. For example, before applying the next migration, you can unload a dump or a snapshot of the database (depending on the backup functionality available in a particular DBMS).
Source: https://habr.com/ru/post/141354/
All Articles