X

Shay Shmeltzer's Oracle Development Tools Tips

Using Flyway to Manage Oracle DB Versions in the Cloud

Shay Shmeltzer
Director of Product Management - Oracle

This is another entry in my series about managing database scripts/schema as part of agile development. In the past I showed how to use simple SQL and Liquibase to manage schema creation/population scripts, and today I'll show you how to use Flyway.

Flyway log

Flyway is a free open source solution for managing "database migrations" - or basically helping you keep multiple database in synch by tracking and applying changes to the schema structure and data.

Flyway uses simple SQL scripts - which means you can use DB specific syntax - and tracks their execution in the database through a table it maintains. It is very easy to get started with and only has 6 commands that you need to be familiar with.

The main command is "migrate" which will check your database status, and then run all the newer scripts that have yet to be run on that instance.

Flyway uses a directory structure that contains a sql folder where you'll host all your SQL scripts. It uses a naming convention (that can be adjusted) where you start the file name with a Version number (V1, V1.1, V2.1) and then two "_" followed by a description - so something like V1__Create_Emp_Table - will show up as "Create Emp Table" when you issue the "info" command to find out what is the status of a database and which scripts have already run. By the way, the info command will also show you which new scripts are pending to be run on a specific database instance.

In the video below I show how to configure and use Flyway, and how to integrate it into an automatic DevOps process leveraging Oracle Developer Cloud Service. (including task tracking, Git version management of the source, and build execution of the scripts).

Flyway can integrate with various build framework (ant, maven, gradle etc), but since many DB folks are not familiar with those, I chose to use simple command lines in my demo to invoke Flyway. On my laptop and local MySQL DB I just used the Flyway command line utility. However Flyway is not installed by default in the DevCS servers, so I did a little trick:

Flyway is a Java program, so into my DevCS Git repository I uploaded the Flyway directory along with needed jars for flyway and the JDBC driver. Then I looked at the script for invoking the command line and found out the Java command they used and copied it into a regular shell command in my build:

java -cp lib/flyway-commandline-4.2.0.jar:lib/flyway-core-4.2.0.jar org.flywaydb.commandline.Main info -user=fw -password=$Password -url=jdbc:oracle:thin:@ipaddress:1521/servicename

The $Password refers to a build parameter which is encrypted.

The directory structure and files in my Git are shown in this image:

directory structure

 

Join the discussion

Comments ( 4 )
  • Christoph Saturday, February 24, 2018
    Thanks for the info Shay. So I'm trying to decide between Liquibase and Flyway (and Sqitch). Which should I choose?
  • St├ęphane Paquette Tuesday, March 20, 2018
    I've started playing around with flyway.
    On some dbms (postgresql, db2udb) it's quite easy as they handle DDl in transactions.
    With Oracle, because of the implicit commit with DDL, the scripts management is more complex.
    Any best practices on using flyway/Oracle
  • krishna Wednesday, May 2, 2018
    Please help me to install Flyway for oracle Database migration
  • shay Wednesday, May 2, 2018
    Krishna
    See this getting started:
    https://flywaydb.org/getstarted/firststeps/commandline
    All you need to do is create a configuration file that maps to your Oracle DB
    Something like this:
    flyway.url=jdbc:oracle:thin:@localhost:1521/ORCLPDB1
    flyway.user=flyway
    flyway.password=flyway
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services