Phil Factor

4 January 2024

• Flyway
• Database Build tasks
• Team-based Development

Using ODBC in PowerShell Scripts and Callbacks

When querying an RDBMS from a scripting language, it is usually easiest to access the database using the same method as that used by database applications, meaning via JDBC or ODBC. JDBC is used by Java-based applications and ODBC is used for other types of application. I’ll be demonstrating a way of using ODBC neatly with Flyway, in a callback script. Of course, because Flyway is a Java application, it makes sense to use JDBC where possible, and this is certainly true in Java scripts. However, we cannot use JDBC directly from PowerShell, so cannot query the metadata we need for tasks such as performing a “diff” of two versions of a database. In such cases we need to use ODBC instead.

Drivers aren’t the only way you can do such queries. If you are scripting in DOS command shell, then you need to stick to the CLI tools provided. In the Flyway Teamwork Framework, I standardized on using the various RDBMSs’ CLI tools, which turned out to be sufficient. SQL Server’s SQLCMD CLI tool was slightly clunky for JSON output but usable, and Oracle’s SQLCI utility proved to be dead slow for single queries, but fine for a run of SQL Statements. I needed the CLI tools anyway, because I needed to get build scripts, backups and other useful deliverables, which neither ODBC nor JDBC provide.

Creating a DSN (Data Source Name)

The obvious way of using ODBC in a PowerShell script, or Flyway callback, is to install the latest ODBC driver on the workstation or server that will be running it, and then use the ODBC Data sources application that comes with Windows to create a DSN.

To do this, you fiddle with the various settings you need, and then use the built-in tester to make sure everything is correct. The details are held in just one place, in the registry, and so you can use the DSN widely for various purposes yet make global changes easily in just one place. This makes DSNs more attractive than the alternative of creating a connection string from scratch for every use. If you prefer the arms-length approach to connection strings, then a DSN is the way to go.

Security issues and credentials

Although it is possible to store a password and user ID in a DSN, it can only be used to test it within the ODBC Data sources app. When you use a DSN, even a User DSN, all its settings except for the User ID (UID) and Password (PWD) are used. This means that the responsibility for managing ‘secrets’ is passed back to the user. So, in a Flyway script or callback, are you then doomed to have to manage your own credential security? Yep, but when Flyway executes a script, it obligingly provides for it the values of the credentials needed to set up any connection, and so it isn’t complicated.

A DOS or PowerShell script that is used in a Flyway callback or migration is presented with several environment variables that are there just for the lifetime of the script. These include the Flyway variables, default placeholders, and custom placeholders. The variables provide connection information such as FLYWAY_PASSWORD, FLYWAY_URL and FLYWAY_USER and you also get default placeholders for the target database (FP__flyway_database) and the Flyway user (FP__flyway_user). If you need the name of the server, you extract it from the URL String. See Passing Parameters and Settings to Flyway Scripts for more information.

Obviously, the ODBC database connection used by the scripts must provide connection attributes that match those of the JDBC connection used by Flyway as closely as possible. You have three alternatives:

1. Supply a fully-stocked connection string
2. Use a DSN and add the credentials to it where necessary
3. Fetch the JDBC URL and convert the parameters within it to ODBC format to create a connection string.

If you use the first option, the full connection string, it is best to provide the entire string, without its credentials, as a custom placeholder in the same config location as the JDBC URL, so it can be accessed within the script or callback. For any of these alternatives to work, you must insert, where necessary, the UID and PWD parameters into the connection string from the FLYWAY_PASSWORD and FLYWAY_USER environment variables. Easier demonstrated than described.

Enough with the theory: using DSNs in Flyway

In this example, we will use the second option, creating a DSN that specifies a default database and then supplying the credentials at runtime using Flyway’s configuration variables.

We configure the DSN using the ODBC Data sources app and, having tested it out successfully, we can use it. Here is an afterInfo callback for a SQL Server development. It contains a custom placeholder called DSN. We can specify a default value for it in any of the Flyway.conf files. For example, we can add the following line to our project-level flyway config file:

We construct our connection string from this value plus the credentials provided via Flyway. We can then execute as many SQL queries as we need and save their results to disk.

The script does a couple of backup-related checks, reporting the last time the database was backed up and checking the amount of log space available. These simple checks are just for demo purposes, but there is no limit to what you can report on with this callback. It is designed so that you just create a list of SQL reports you want, and the script writes the result of each query to a JSON File in the current directory.

Limiting the number of DSNs

This technique works fine if you’ve only a few connections to make, each one represented by a DSN. You probably wouldn’t require anything more complicated. If you have a lot of different databases in your database development workflow, you might end up with a lot of DSNs to configure on each workstation. In this case, you might prefer to specify the database in your connection string rather than provide it in the DSN. This would be used instead of the one in the DSN.

If you prefer to have just a small number of DSNs, you can extract the database and other useful bits of information from the Flyway URL that is obligingly provided, and then leave the more complicated connection configuration details to the DSN, because these are likely to be the same for all connections.

Here is an example of how you might go about getting the values of the server and database from the value of the URL passed to you by Flyway:

You’ll notice that you can also get the value of the JDBC RDBMS name (oracle, sqlite, sqlserver etc.) but you won’t need it in this example.

The above code will substitute the values you’ve obtained into the connection string and their values would overwrite the same values in the DSN.

In the case of a non-standard port, this is specified using a comma after the server name:

The connection will continue to use all the more-esoteric settings from the DSN.

Conclusions

If you are using PowerShell with Flyway, you will probably, at some point, need to connect to your database and run queries, to add useful reports and other functionality to Flyway data build and migration process. ODBC is likely to be the way to go. Although JDBC is Flyway’s way of connecting, it also provides enough information to allow you to use ODBC without any hindrance.

This sort of database access is a complication, of course, though easy to manage, as you’ll have noticed if you’ve read this introduction. However, it really isn’t often necessary because Flyway provides SQL callbacks where you provide the SQL and Flyway executes it, using its JDBC database connection. It even manages the rollback and other consequences if things go wrong. It is always better to leave all DDL code to Flyway to execute in its managed way. However, data loading, reporting and monitoring can easily be delegated to PowerShell scripts, and these scripts have the advantage of making it far easier to process the results of queries.