SAP HANA Cloud Portal: Migrating your application's database using Flyway

[Former Member]

SAP HANA Cloud Portal is developed on top of SAP HANA.

Working in short release cycles, we needed a tool that will allow us to populate a DB schema from scratch and easily alter the schema between releases.

We opted for Flyway (http://flywaydb.org/), an open source (Apache license) framework for database migration.

It is very easy to use, simple to integrate and with minimal effort was extended to support SAP HANA.

In our application Flyway runs every time the application is loaded, using Spring.

In this post I will provide the necessary steps to get up and running with Flyway.

Note:

In case you need SAP HANA support, please follow the guidelines here.

Adding Flyway as a dependency

The simplest way to add Flyway is dropping "flyway-core-x.x.x.jar" into the WebContent/WEB-INF/lib folder.

Then in your Spring configuration file (i.e. "beans.xml") add a bean named "flyway":

<bean id="flyway" class="com.googlecode.flyway.core.Flyway" init-method="migrate">

   <property name="dataSource" ref="..."/>

</bean>

Make sure you also add "flyway" as a dependency for your EntityManagerFactoryBean:

<bean id="entityManagerFactory"

                    class="org.springframework.orm.jpa.LocalEntityManagerFactoryBean"

                    depends-on="flyway">

                    <property name="jpaPropertyMap">

                              <map>

                                        <entry key="javax.persistence.nonJtaDataSource">

                                                  <ref bean="dataSource" />

                                        </entry>

                              </map>

                    </property>

  ...

</bean>

This makes sure Flyway runs before the rest of the application starts.

Writing a migration

Migrations are simple to write. Flyway supports both Java and SQL migrations. We have only used SQL migrations so far.

Flyway has an attribute called "locations", which defaults to the /WebContent/WEB-INF/db/migration folder.

Create a file called V1__Initial_version.sql with the following content:

create table PERSONS (

          ID INTEGER not null,

          PERSON_NAME VARCHAR (255) not null,

      VERSION INTEGER,

PRIMARY KEY (ID)

);

This will create a "PERSONS" table in the schema.

Now let's go over some of the details:

1. The sql file name is used for maintaining the metadata about this migration.

    It starts with a version for the migration. Migrations are sorted in ascending version order and run sequentially. 

    After the DOUBLE underscore (don't omit an underscore, since it messes up the file name parsing) is a descriptive name for the migration.

    

2. The content can be any valid SQL statement.

Checking schema status

All migrations are recorded in a dedicated table called "SCHEMA_VERSION".

It lists all the migrations that ran, who ran them, etc.

It keeps track of what was the last migration version that ran.

What's next

When you need to alter your DB, simply create a new script with a higher version.

Flyway checks against the metadata table and only runs versions that are higher than the last one listed in the table.