• Flyway
• Team-based Development
• Tracking development changes

Recording What’s Changed when Running Oracle Migrations with Flyway

Comparing database versions

How do you keep tabs on all the metadata changes that you make to a database? It isn’t as easy as it sounds.

If you save a database build script for each new version, , then you can track changes to it over time, the added and altered tables views and functions, through the version control system. However, it’s not ideal. If two build scripts create the database objects in a different order, or use different code formatting, then the version control system will flag a lot of spurious ‘differences’. If you provide more comments, that’s a change, in those terms, even if the resulting database is identical.

You can compare generated object-level scripts more usefully because this representation avoids the problem of changes in the order of build, which is why these are popular artefacts for version control. However, without a parser, it is impossible to tell reliably what attributes, such as columns, or constraints have changed. These object-level scripts are even more useful if the actual scripts are generated from the database, since this avoids false positives caused by changes in whitespace or comments.

Of course, tools like Oracle Schema Compare, or SQL Compare for SQL Server, have the parsers that make all this easy. They can generate build scripts and object-level scripts from the database, and then compare source and target object-level scripts to show detailed reports on which objects changed and how.

But what, for example, if we need to know at which version in a long chain of migrations a particular table or procedure was modified? Or at what version that constraint was added that we now know caused a bug? For this sort of development task, we just need a simple, high-level summary of which objects changed at each version, not the full details.

Alongside the schema comparison tools, as well as for RDBMSs where they are not available, it’s useful for developers to have a generic, cross-RDBMS way to generate and save a simple textual ‘model’ for each database version, while running migrations. These models need just enough information so that, by comparing them, we can understand which objects changed between versions. Armed with this big picture, we then use a schema comparison tool to drill into the details.

Creating and using JSON database models

I’ll demonstrate how to create and save a simple JSON model for every version of an Oracle database created by Flyway and then compare each one to the previous version to get a high-level change narrative for every database version.

Without a parser we can’t get the same level of detail as the schema comparison engines, but in this case we’re only interested in changes to database objects such as tables, views and routines, and their constraints and dependencies. It is these objects, their procedural code, relationships and dependencies that determine a version, not the method of creating them (for example, there are several different ways of creating the same foreign key).

In DevOps pipelines, if we have these object models written out and saved as a structured document (XML/JSON/YAML) then they can be used for the other tasks. For example, once we have the JSON model, we use it to generate a ‘table manifest’, a list of tables in the correct dependency order. My framework also creates this manifest for you, from the model. In turn, we can write tasks that use this manifest to, for example, to create or tear down a group of tables, or build a database directly from object-level scripts or load and delete datasets.

So, what is a database model?

The model represents the layout of the database. In my Flyway Teamwork PowerShell framework, I provide a script block task called $SaveDatabaseModelIfNecessary that creates a basic JSON ‘model’ for every Flyway migration.

It connects to the Oracle database and uses SqlCl to query the metadata views and functions for information. It constructs it into a standard model or ‘schema tree’ format and saves it as a JSON file. Essentially, it’s an Oracle equivalent of the ‘Gloop’ method that I’ve described previously in Reporting on Changes Made by Flyway Migration Scripts.

How you construct the model from the metadata is a matter of taste, but I like to take the conventional approach of having the primary objects as database schemas, with tables, routines, types and other ‘parent’ objects supported by the RDBMS as the secondary objects. All these objects have attributes such as columns, parameters and return types. Tables will have triggers, keys and indexes.

It is much easier to explain with an example. Here is the start of a model for the classic ‘Pubs’ (publications) database, redone for Oracle. It is produced by the Flyway Teamwork framework, running on a windows workstation.

I use JSON to store the model on file because it is more portable. In the framework, each new version that Flyway produces has its own model, and I copy the latest model to a ‘current’ subdirectory on every migration, to help with source control.

If you compare the current model with the previous one, you can get a description of what has changed. I refer to this as a ‘narrative of changes’ because it gives you the high-level summary of what has changed.

And what is a ‘narrative of changes?

The framework includes a second script block task called $CreateVersionNarrativeIfNecessary that compares models to generate the narrative of what changed between each version of the database.

By way of illustration, here is a section of a ‘narrative’ of changes between two adjacent Flyway database versions. It only checks the main objects and doesn’t cover Oracle-specific features but it does tell you broadly what the latest migration file has done. The report is generated as a markdown file:

Deleted '.DBO.TABLE.DISCOUNTS.index.SYS_C0049202 
Deleted '.DBO.TABLE.EDITIONS.index.SYS_C0049216 
Deleted '.DBO.TABLE.PRICES.index.SYS_C0049218 
Deleted '.DBO.TABLE.PUBLICATIONS.index.SYS_C0049213 
Changed 'TITLE_ID NVARCHAR2(6 Char) NOT NULL' to 'ROYSCHED_ID NUMBER(5) NOT NULL' at '.DBO.TABLE.ROYSCHED.columns[0]
Changed 'LORANGE NUMBER(10)' to 'TITLE_ID VARCHAR2(6 Byte) NOT NULL' at '.DBO.TABLE.ROYSCHED.columns[1]
Changed 'HIRANGE NUMBER(10)' to 'LORANGE NUMBER(10)' at '.DBO.TABLE.ROYSCHED.columns[2]
Changed 'ROYALTY NUMBER(10)' to 'HIRANGE NUMBER(10)' at '.DBO.TABLE.ROYSCHED.columns[3]
Changed 'ROYSCHED_ID NUMBER(5) NOT NULL' to 'ROYALTY NUMBER(10)' at '.DBO.TABLE.ROYSCHED.columns[4]
Deleted '.DBO.TABLE.ROYSCHED.foreign_key 
Deleted '.DBO.TABLE.ROYSCHED.index 
Deleted '.DBO.TABLE.ROYSCHED.index.SYS_C0049204 
Deleted '.DBO.TABLE.ROYSCHED.index.TITLEIDINDEX_1 
Changed 'ORD_NUM VARCHAR2(80 Byte) NOT NULL' to 'ORD_NUM VARCHAR2(20 Char) NOT NULL' at '.DBO.TABLE.SALES.columns[1]

Lastly, with the model, it is possible to generate a table manifest, which gives you the correct order for creating tables.

Hang on, what is a table manifest?

The $SaveDatabaseModelIfNecessary task also generates the table manifest for each new version straight after it creates the JSON model. The table manifest is just a simple list of tables in the correct order in which you’d create them or load data into them: like this, for example.

DBO.AUTHORS
DBO.DISCOUNTS
DBO.EDITIONS
DBO.EMPLOYEE
DBO.JOBS
DBO.PRICES
DBO.PUBLICATIONS
DBO.PUBLISHERS
DBO.PUB_INFO
DBO.ROYSCHED
DBO.SALES
DBO.STORES
DBO.TAGNAME
DBO.TAGTITLE
DBO.TITLEAUTHOR

This list can then be used to generate a build script if you have the object-level scripts, or to generate a script to load in a dataset.

Performing a scripted set of migrations with an Oracle Database

I’ve shown, in a previous article, Getting Started with Flyway Migrations on Oracle, how to use Flyway to build an Oracle database using a set of migration files. Now, we are going to extend the amount of information we collect, to make teamwork a bit easier. We’ll create a database model in JSON, a narrative of changes and a table manifest for every version, using the Flyway Teamwork framework.

Setting up the project

We will need a directory. We’ll call it FlywayDevelopments but it could be anything you like. In it we need a projects folder. This projects folder, which again you can call anything you like, is important because it contains a directory called Resources in which we must put all the PowerShell scripts you’ll need for the Flyway Teamwork framework:

I maintain the Flyway Teamwork framework on GitHub, where you can find the Resources folder with all these files. Notably the DatabaseBuldAndMigrateTasks.ps1 file contains all the script block tasks that I use in this article (and many more that I cover in other articles).

The projects folder will also contain all our Flyway projects, each in its own directory, and all projects will share common information and resources. In this case, we need to add our PubsOracle project folder. I’ve provided a GitHub project called PubsOracle that you are welcome to use. When you open the PubsOrcle folder you’re in the main branch, but we are going to work in a Develop sub-branch within main:

Setting up Oracle SQLCl

We use Oracle SqlCl, the command-line version of Oracle SQL Developer, to interrogate the database to fetch its metadata and check the Flyway version of the database. It gives us a PL/SQL interface to query the database.

We can supply the path to it either in the PATH environment variable or else by passing the string for the path to the framework. We use a script that reads in the list of paths from a file called ToolLocations.ps1 which is kept in the ‘Resources’ folder illustrated.

You will need to fill this in with the path to where you’ve put Oracle SqlCl’s Sql.exe. Don’t worry about the rest of the paths. If you are just using Oracle, this is the only essential path.

In my case, I installed it in a sub-folder of my Oracle SQL Developer installation.

Providing Oracle credentials for each database

We will need to provide the credentials of the Oracle databases that support each branch of the project, one database per branch.

As per the scheme described in my previous article, we’re going to put the required details for each database in additional Flyway configuration files, stored securely in the User profile directory on a Windows workstation. In this example, we’ll need one for the main branch called oracle_PubsOracle_Main.conf with the connections details to the main database, and one for the Develop branch database called oracle_PubsOracle_Develop.conf. I specify ‘oracle’ in the filename because I’m sometimes using more than one different RDBMSs for the same database.

Each file provides the JDBC URL to connect to the database, username and password details, and the location of the Oracle wallet:

This provides sufficient information for both Flyway and Oracle SqlCl.

The OraclePubs project files

Each branch of the PubsOracle project has a Migrations folder plus a few necessary files that I’ll explain briefly. This is the main (production or release) branch of the project :

The Migrations folder for main contains just two migration files, which create and then populate the ‘classic’ version of the Pubs database.

The preliminary.ps1 script all the necessary checks for the required files, directories, and tools Each time you execute preliminary file it in turn executes ToolLocations.ps1 and all other code in the Resources folder. It creates a hashtable called DBDetails that provides all PowerShell scripts, including callbacks, with all the data they could possibly require.

The RunMe.bat file adds an environment variable that tells Flyway where you’ve put your login, user and authentication information within your user profile (in our case, it’s in the oracle_PubsOracle_Main.conf). You will need this batch file even if you’re running the code within my framework, because preliminary.ps1 fetches it for you, by pulling the environment setting out of RunMe.bat. You’ll need the batch file anyway if you just want to use Flyway ‘raw’ in the command line.

The DirectoryNames.json file defines the names and paths to the project directories for migrations scripts, reports and so on. If you don’t supply this file, the framework creates it and add the default names to it. The easiest approach is to let Flyway do this, and then change the names as you prefer.

You also need a project-level flyway.conf file, on per branch, with at least the following entries:

The Develop branch of the project looks spookily like the main branch:

The difference will just be in the contents of the Migrations branch and the contents of the RunMe.bat since this accessing a different database

Models, change narratives, manifests for Flyway migrations.

So, we are ready to start. For simplicity here, we’ll simply use the four migration files specific to this branch and copy across the first two files from main that bring the database up to the current release version (to avoid this sort of file duplication, you can use Flyway’s Locations parameter).

We’ve already created a database for the Develop branch and put all the connection information in the oracle_PubsOracle_Develop.conf file in the user area.

We can now test out the system and see if we can produce all the extra information that the PowerShell framework can provide. Normally, of course, you’d just do a migration, and afterwards you could either do the rest with a callback in Flyway Teams, as I demonstrated in Reporting on Changes Made by Flyway Migration Scripts, or by just running the tasks you need after each migration if you only have Flyway Community edition.

To start, we’ll just get a model, manifest and version narrative for the first version only (V1.1.1).

We get, without the Flyway clutter, some indications of success.

Creating schema "DBO" ...
Creating schema "PEOPLE" ...
Creating schema "ACCOUNTING" ...
Creating Schema History table "DBO"."flyway_schema_history" ...
Current version of schema "DBO": null
Migrating schema "DBO" to version "1.1.1 - Initial Build"
WARNING: DB: Warning: execution completed with warning (SQL State: 99999 - Error Code: 17110)
no rows updated
Successfully applied 1 migration to schema "DBO", now at version v1.1.1 (execution time 00:03.165s)
Executed GetCurrentVersion
Executed SaveDatabaseModelIfNecessary
Executed CreateVersionNarrativeIfNecessary
For the SaveDatabaseModelIfNecessary, we saved the report in <path-to>\FlywayDevelopments\projects\PubsOracle\Branches\Develop\Versions\1.1.1\Reports\DatabaseModel.JSON
in GetCurrentVersion, current version is 1.1.1, previous 0.0.0.in SaveDatabaseModelIfNecessary, written object-level model to <path-to>\FlywayDevelopments\projects\PubsOracle\Branches\Develop\Versions\1.1.1\model written table manifest  to:\work\FlywayDevelopments\projects\PubsOracle\Branches\Develop\Versions\1.1.1\Reports\TableManifest.txt

Having done this, we’ll run each migration in turn to make sure that we run each task in turn for each migration. We can do this with a test-harness that runs each migration in turn rather than all-at-once and performs a few extra tasks:

With a certain apprehension, we then see what has been written out by the framework.

You’ll notice that the framework has created a Versions directory, and within it has created a subdirectory for each version it created. This makes it easier for the framework to compare between versions.

In the Reports folder for each version, we find the database models, the change report that includes both changed and unchanged objects and attributes from the previous version, and the change narrative markdown report that uses it to report which objects have changed in this version. Also, there is the table manifest:

Conclusions

Flyway’s command line makes automation of many database tasks very easy, because they can be done at the right time without any intervention. Flyway is also a multi-RDBMS system, so it makes sense to make the framework multi-RDBMS. The Teamwork framework can now work with SQL Server, Oracle, PostgreSQL, MySQL, MariaDB and SQLite.

I’d be lying if I said that extending the system to include Oracle was easy, because it wasn’t. The worst awkwardness is that there is no practical CLI-based way of doing SQL calls. The SqlCl utility is large and takes a while to run to the point that using it for single queries, as one can with all the other databases, is painful.

However, Flyway works well with Oracle and the only down-side to developing databases this way is the fact that one cannot roll back a failed migration as is possible with SQL Server and PostgreSQL.

Tools in this post

Flyway

DevOps for the Database

Find out more

Flyway Teams

Ideal for organizations looking to improve collaboration and fine tune their processes during development and the deployment of database changes.

Find out more