Introduction

Welcome to part 3 of "A whirlwind tour of Database Schema Changes Tracking Tools."

Today, we're diving into the world of Flyway community edition, which is used with Oracle Database. If you want to learn more about the test prerequisites, check out my "Introduction" blog post.

And just so you know, as a Liquibase fan, I promise I won't be making any judgments!

Test

1. Installation, Configuration & Connection to the database

Installation

• Go to the Flyway webpage here.

• Pick the community edition and go straight to CLI download (I don't want a desktop app in this test). Choose your OS (I picked macOS), download & unzip files.

  

  

• After unzipping I've changed the folder name "flyway-[version]" to "flyway" and moved it to my user/apps/ location

  

  

• One last step was adding Flyway to the system PATH. Open the .zshrc file and add this line (or different, depending on your folder location or different shell used) (I believe that if you use Windows you should also configure UTF8 - this can be done using this tutorial)

    export PATH="/Users/[your_username]/apps/flyway:$PATH"
  

• Success, "flyway" command is recognized by my PC :)

  

  Configuration & Connection to Database

  It seems pretty easy and similar to how we do it with Liquibase.

  Here are the exact steps I did:

• I've created a new configuration file called flyway.conf in my new repository location (/flyway_demo/

  

    # More information on the parameters can be found here: https://documentation.red-gate.com/flyway/flyway-cli-and-api/configuration/parameters
    flyway.url=jdbc:oracle:thin:@dev_low?TNS_ADMIN=/Users/rg/apps/oracle/wallets/priv/Wallet_DEV
    flyway.user=HR
    flyway.password=Qwerty12345$
  

• The next command I ran was "flyway migrate"

  

• And I've got this error saying "ERROR: Found non-empty schema(s) "HR" but no schema history table. Use baseline() or set baselineOnMigrate to true to initialize the schema history table."

• Indeed, it's true because I have some objects in my HR schema (my test prerequisites are described here), so I need to set a baseline (it will also create flyway_schema_hisotry table

• I want my baseline version to have the number 0, so I've executed:

    flyway baseline -baselineVersion=0
  

  

• Looked good, So I've checked what I have in the auto-created HR.flyway_schema_history table (btw, this lowercase name of this table is annoying)

  

• I have my baseline but couldn't find a way to automatically export all my schema objects into my repository (using Flyway). I could use SQLcl to do this, but I will skip that and see what vanilla Flyway can offer.

2. Tracking database schema changes using Flyway

As I mentioned in my introduction blog post, I will make some changes in my database schema in the DEV environment:

1. Insert a new row into the table COUNTRIES

2. Create a new table PARAMETERS

3. Add a new column to the existing table EMPLOYEES

4. Change existing procedure SECURE_DML

5. Insert new rows into the newly created table PARAMETERS

My main goal is to find a way to track it and, later, easily deploy it to the production environment.

Making & deploying changes at DEV

Using Flyway, you need to create SQL scripts and write your changes there. So, I will prepare my SQL changes. What's very important: naming pattern matters (source)

• I've created a folder /flyway_demo/db/ to store my changes, and I've added it to my flyway.conf

  

• I created my scripts, keeping the naming convention to ensure that the code runs in the proper order (tables first, then procedures, foreign keys, DML, etc.).

  

• The scripts inside are very straightforward and do not require any additional syntax:

  

  

• To deploy changes to DEV, I could simply run "flyway migrate", but first, I want to see what will be executed (I couldn't find the way to preview the exact SQL code that will be executed):

  

• Looks good, so I've executed "flyway migrate"

  

• And I can see signs of executions in the "flyway_schema_history" table:

  

3. Deployment to another environment (PROD)

• To deploy to another environment, I had to create a new Flyway config file. I've called it flyway_prod.conf

  

• To use this file while running Flyway commands, I will have to explicitly specify a path to it:

    flyway -configFiles=flyway_prod.conf [FLYWAY_COMMAND]
  

• Let's check what will be executed in my PROD environment:

    flyway -configFiles=flyway_prod.conf info
  

  

• Looks good. Seems that Flyway will create a flyway_schema_history table and execute those changes.

• I executed "Flyway migrate"

    flyway -configFiles=flyway_prod.conf migrate
  

• Ups, seems that I will have to set a baseline first:

  

• I removed my scripts from /db/ just for a moment because I want them to be executed and not marked as a baseline.

  

• Then I executed the command for setting a baseline with version 0 (as I did previously in DEV)

    flyway -configFiles=flyway_prod.conf baseline -baselineVersion=0
  

  

• Ok, now the baseline is set

• I restored my files to the /db/ folder and executed the "migrate" command again.

    flyway -configFiles=flyway_prod.conf migrate
  

  

• Everything was as expected. Now let's check what will happen if I'll try to deploy the same changes again:

  

• Perfect! Flyway tracks my scripts and knows that there are no new scripts that should be executed.

  4. Visualise (retrieve) a list of changes made to the database schema.

To check what was deployed, you can simply query the flyway_schema_history table:

select * from "flyway_schema_history"

Or run "info" command:

Summary

I hope this blog post gave you a general idea of what working with Flyway and Oracle Database is like.

Drop me a comment if you have any questions or feedback.

Rafal

Other tested tools