Phil Factor

12 January 2023

• Flyway
• CLI basics
• Database Versioning

Getting Started with Flyway Migrations on Oracle

The only objective is to build a working version of our demo database (called Pubs) on Oracle. Our only refinement is that we will avoid mixing the database configuration information that Flyway needs with the database connection information, which is also essential, but best stored separately. We’ll demonstrate this on a Windows machine.

Setting up a practice Oracle server for Flyway

The simplest way of setting up a practice Oracle database is to create an Autonomous Database on an Oracle Autonomous Transaction Processing (ATP) instance, a cloud-based service provided through Oracle Cloud Infrastructure (OCI). If this seems daunting, it is possible to try out the possibilities with Oracle Database XE, working on the same workstation.

Setting up the Oracle instance

I use the ‘Always Free’ version of OCI for development work, which allows you two databases. From the OCI console, you need to find your way to the ‘Autonomous Databases’ (ADB) page. Select ATP from the three kinds of database services. There, you select your region, and a compartment, and click on the ‘Create Autonomous Database’ button.

You need to create an Oracle ATP instance. After you’ve done that, you need to reset the administrator password. This can be done from the ‘Manage the Administrator Account on Autonomous Database.’ dialog. Then you use the ADMIN account to create one or more ‘normal’, unprivileged users.

Connecting from Flyway

To connect an Oracle Autonomous Database instance to Flyway, you should use the JDBC ‘Thin Driver’: Flyway is a Java application and the JDBC thin Driver is a pure Java driver. Many applications, including Oracle SQL Developer, support the JDBC Thin Driver. Oracle Autonomous Database uses certificate authentication and Secure TCP (known as TCPS). We get access to an Autonomous Database via a Transport Layer Security (TLSv1.2) connection.

For security reasons, Oracle prefers us to use Mutual TLS (mTLS) connections, where both the client and the database verify each other’s certificates. Handling this certificate-based authentication requires an ‘Oracle wallet’, to store an encrypted key on both the client and the database server.

You download the wallet and unzip it into a local subdirectory under the user profile directory. TLS authentication without a wallet is only available if you are using either Oracle Instant Client/Oracle Database Client 19.13 (but only on Linux x64) or Oracle Instant Client/Oracle Database Client 19.14 (or later) and 21.5 (or later).

Although Flyway supports JDBC connections to an Oracle ATP instance in the OCI, only Flyway Teams allows the use of mTLS. For Flyway Community, you need to change the default use of mTLS to allow TLS as well, as described here.

If you are happy with CIDR blocks and the esoterica of Virtual Cloud Network, then you should fine with using TLS rather than mTLS– but good luck! It is simplest to configure the Virtual Cloud Network for TLS because you can then use it for all your databases and instances. You are not allowed to provide access from any IP address, because it is a cloud system that is accessible from anywhere. TLS must be explicitly configured to specify the range of IP addresses from which you wish to allow access.

The use of mTLS avoids all this config work.

Setting up your Flyway Database project

The very simplest Flyway project would involve putting all your connection information, usernames and passwords in the same directory as your code, but we won’t cover that because it isn’t really a good solution. Not only does it risk exposing credentials, but you also are likely to want to access the same connection information for several Flyway projects.

If these projects use the same server and database, you can merely put your connection information in a flyway.conf file in your user profile directory. If you need to work in one project with more than one database connection, which includes all of us who test their work, or who use branches or variants, you will want to use a technique that stores all the connection information in one place that only you can access on your workstation.

We’ll use a simple system, also described in more detail here, that allows us to specify extra config files that we save within the user profile directory. We will store the information in the standard Flyway configuration format, in files with the <RDBMS>_<Project>_<Branch>.conf filename format. This technique is borrowed from my Flyway Teamwork PowerShell framework, but works perfectly well by itself and means that I can, with a clean conscience, describe a technique that can be used responsibly without risking your security.

When you install Flyway, keep the flyway.conf in your flyway installation directory merely for your Flyway license key string (flyway.licenseKey) and any other workstation-based information that is relevant to the general working of Flyway. The flyway.conf file in the user profile directory is intended for user information, but it can only be used to supply the username, password and connection URL for one database.

In our case, wherever you put the information, this connection information is likely to be:

• # JDBC URL to use to connect to the database.
  • flyway.url=jdbc:oracle:thin:@//<host>:<port>/<service> …or…
  • flyway.url=jdbc:oracle:thin:@<tns_entry>
• # User to use to connect to the database. Flyway will prompt you to enter it if not specified, and if the JDBC connection is not using a password-less method of authentication.
  • flyway.user=<username>
• # Password to use to connect to the database. Flyway will prompt you to enter it if not specified, but only if the JDBC connection is not using a password-less method of authentication:
  • flyway.password
• # The location on disk of your Oracle wallet when using mTLS. If you use TLS, you don’t need this:
  • flyway.oracle.walletLocation

Here is an example of a typical entry, ‘masked’:

We will put this in a file called oracle_pubsOracle_main.conf in the user profile folder.

We now change to the Flyway project directory:

The project-level flyway.conf file is very simple. It merely states what schemas there are and the location(s) of the directories that hold migrations

When we start a Flyway session, we need to tell Flyway about the extra config file to which we’ve added all the connection information. This can be done simply in Windows by running the RunMe.bat batch file, which sets the FLYWAY_CONFIG_FILES environment variable to point to the extra config file in the user directory:

Fine. We are ready to go!

Running Flyway migrations on Oracle

I’ve provided a sample Flyway project on GitHub called PubsOracle. It contains a couple of migrations that build the original version of a database called Pubs and populate it with data. The six migrations to an expanded version is in the development branch. It is based on the old Pubs database, published by Sybase as their demo database and designed to be a mock database for book distributers.

If the database is empty, as it will be if you have newly created the Oracle Autonomous Transaction Processing (ATP) instance, you can then try out building the database. In the command-line console, we type:

(ourPathTo)\PubsOracle>flyway migrate

After a pause we get the following (I’ve pruned some of the verbosity…)

Flyway Teams Edition 8.5.10 by Redgate
Creating schema "DBO" ...
Creating Schema History table "DBO"."flyway_schema_history" ...
Current version of schema "DBO": null
Migrating schema "DBO" to version "1.1.1 – FirstRelease"
Migrating schema "DBO" to version "1.1.2 – FirstReleaseData"
Successfully applied 2 migrations to schema "DBO", now at version v1.1.2 (execution time 00:08.115s)

We can now check that all is in place

(ourPathTo)\PubsOracle>flyway info
Flyway Teams Edition 8.5.10 by Redgate
Schema version: 1.1.2
+-----------+---------+------------------------------+--------+---------+----------+
| Category  | Version | Description                  | Type   | State   | Undoable |
+-----------+---------+------------------------------+--------+---------+----------+
|           |         | << Flyway Schema Creation >> | SCHEMA | Success |          |
| Versioned | 1.1.1   | FirstRelease                 | SQL    | Success | No       |
| Versioned | 1.1.2   | FirstReleaseData             | SQL    | Success | No       |
+-----------+---------+------------------------------+--------+---------+----------+

Now we check in Oracle SQL Developer. Yeah. It’s all there:

I’ve connected Oracle SQL Developer to my Oracle Cloud database via the same wallet that I used with Flyway. The only difference is that Flyway requires the wallet to be unzipped, whereas Oracle SQL Developer uses the zipped Wallet for connection and authentication. Oracle SQL Developer conveniently allows you to access SQL Server, MySQL and other JDBC databases as well as Oracle. Oracle SQL Developer is particularly useful to the developer for creating and maintaining Oracle databases.

Oracle SQL Developer has a CLI version called Sqlcl, which I use in the Flyway Teamwork PowerShell framework.

Rolling back failed migrations

Oracle isn’t my own first choice for a Flyway RDBMS, purely because it doesn’t have the means to cleanly roll back a failed migration, but at this early stage of a project, it is easy to just use the Flyway Clean command to rebuild up to the version of any migration that failed.

With an RDBMS that doesn’t roll back DDL code, I find it easiest to write an UNDO migration at the same time as a forward migration so that, as I work on a migration that goes wrong, which is inevitable, I can simply run the code that will mop up and bring the database back to a correct version.

With the Flyway Teamwork framework, you can arrange matters so that a build script is generated for every new database version that Flyway creates, so you can quickly restore the database to that version before a migration failed, without requiring Flyway to execute every migration file.

Conclusions

We now have a sample Oracle database that we can use to try things out. We can also build on this base, adapt it, and use it to demonstrate all the possibilities of Flyway. As well as the two migrations that provide the original PUBS database, I’ve provided all six migrations in the PubsOracle development branch so that users of the framework can have the same database running in SQL Server, PostgreSQL, Oracle, Sqlite, MySQL and MariaDB, and try out all sorts of strange and interesting experiments in cross-RDBMS work.