« Back to Blogs

DATABASE MIGRATION USING FLYWAY

Flyway is an open source database migration tool. It has a command line client, Maven plugin, JAVA API, Gradle plugin, Ant plugin, SBT plugin. Migration can be written in database specific syntax and one of its biggest assets is the ability to run both SQL and JAVA migration scripts. It supports many flavors of Databases such as Oracle, SQL Server, SQL Azure, DB2, DB2 z/OS, MySQL (including Amazon RDS), MariaDB, Google Cloud SQL, PostgreSQL (including Amazon RDS and Heroku), Redshift, Vertica, EnterpriseDB, Greenplum, H2, Hsql, Derby, SQLite, SAP HANA, solidDB, Sybase ASE and Phoenix.

FEATURES  :

  • It supports plain old SQL script

  • It supports Java based migrations for advanced data transformations and handling with LOBs

  • Only Java 6+ and JDBC are all you need to go

  • Filesystem and classpath scanning to automatically discover SQL and Java migrations

  • Safe for cluster environment

  • Cloud support

  • Auto-migrations on startup using API

  • Inconsistent database or failed migration prevents the app from starting

  • Schema clean approach

WORKING :

Once you point flyway to the empty database, it will try to find it’s own metadata table. If it’s own metadata table is not available, it will create new metadata table (SCHEMA_VERSION). Then onwards it will try to continuously track the database on the basis of SCHEMA_VERSION table. It will look into the db/migration folder inside the target folder for the migration files. The migration file name would be like V1_1__Create_Table.sql, V1_2__Add_New_Table.java, V1_3__Alter_DB.sql and so on.

Let’s go through one short example of how flyway migration works :

MIGRATIONS :

Flyway updates the database using migrations. Migrations are of two types: Versioned migration and Repeatable migration. Both migrations can be written in either SQL or Java. It can consist of multiple statements and all statements will run within a single transaction.

VERSIONED MIGRATION :

Each versioned migration consists of a unique version. It will be applied only once. It is mainly used for creating tables, dropping tables, altering tables, indexes, foreign keys and other such stuff.

REPEATABLE MIGRATION :

Repeatable migration does not have versions. It will be applied each time as soon as there are checksum changes. It will be always applied last after all the versions migration is executed. It is mainly used for creating views, creating procedures, creating functions.

CALLBACKS :

Flyway comes with the concept of callbacks in order to execute some action repeatedly. Flyway supports hooks to add into its lifecycle in order to use callbacks. Callbacks supported by flyway are as under:

beforeMigrate, beforeEachMigrate, afterEachMigrate, afterMigrate, beforeClean, afterClean, beforeInfo, afterInfo, beforeValidate, afterValidate, beforeBaseline, afterBaseline, beforeRepair, afterRepair

Comments
No comments yet. Be the first.
contact-us Request a callback