Configure Flyway Command Line Tool


Flyway – The agile database migration framework for Java. Don't think that this can be used only for java apps.  Flyway can be used  for  versioning  Oracle, SQL Server, DB2, MySQL, PostgreSQL, H2 and for Hsql databases.

This might be the first documentation other than the Flyway wiki for installing and configuring Flyway CLT (I have searched the google and not found any good and simple doc- 😉 ). Based, on the Flyway wiki, I have tried to run Flyway, but failed. It looks like it failed due to a bug in Flyway CLT. Whenever, I tried to migrate database, it was showing “Unable to find path for sql migrations”. But, it was working when tried using maven. However, maven is outside the scope of this blog.

So, lets move on and findout how I have configured Flyway CLT.

The documentation is based on flyway version 1.4.1 and for a new database..

Requirements:

  • Linux OS
  • Java
  • Mysql JDBC Driver
  • Flyway v1.4.1

Assuming that you have a linux os and Java is configured, lets move on to flyway.

Dowload flyway command line tool from http://code.google.com/p/flyway/downloads/list

Extract it to /opt and rename to flyway. Lets update the configuration file. You can find the properties file in conf folder inside flyway. In our case, it will be in /opt/flyway/conf/flyway.properties

Following are the configurations which I have used.

 

# Fully qualified classname of the jdbc driver

flyway.driver=com.mysql.jdbc.Driver

# Jdbc url to use to connect to the database , here we are using flyway_sample as database name

flyway.url=jdbc:mysql://localhost/flyway_sample

# User to use to connect to the database

flyway.user=root

# Password to use to connect to the database

# flyway.password=root

# Directory on the classpath to scan for Sql migrations

flyway.baseDir=database

# File name prefix for Sql migrations (default: V )

flyway.sqlMigrationPrefix= DB_

# File name suffix for Sql migrations (default: .sql)

flyway.sqlMigrationSuffix=.sql

All other settings, I have left in default state.

Create a folder named database in /opt/flyway and store all the db scripts in it. The files should start with the name DB_ and should end with .sql extension. If you want to change these, update the corresponding fields in properties file.

Download mysql jdbc connector ( mysql-connector-java) from  http://dev.mysql.com/downloads/connector/j/

Extract the jdbc driver (mysql-connector-java-5.1.16-bin.jar)  to /opt/flyway/jars

Now, lets initialize the database.

root@nixhat:/opt/flyway# sh flyway.sh init

This will create a table named schema_version in flyway_sample table. This table stores the version informations. Make sure the database is created before running the command.

Its time to try the db migration.

root@nixhat:/opt/flyway# sh flyway.sh migrate

Running this command might give you the error – “Unable to find path for sql migrations” . If not, then you are lucky and get away from here.

Following is the procedure which I have followed to overcome this situation.

  • Create a folder named flycode – mkdir /opt/flyway/flycode
  • Extract the Flyway jar file – cd /opt/flyway/flycode && jar -xf /opt/flyway/bin/flyway-commandline-1.4.1.jar
  • Comment out the line in flyway.sh –

                   $JAVA_CMD -jar -cp /opt/flyway bin/flyway-commandline-1.4.1.jar $@

                   and add the line –

                   $JAVA_CMD -cp .:/opt/flyway/bin/*:/opt/flyway/flycode com/googlecode/flyway/commandline/Main $@

Here, we have commented the script from loading the jar file and updated it to load the source file and other jar files location in its classpath.

That's it! Now, you can run the migrate command – sh flyway.sh migrate