Phil Factor

1 July 2024

• Redgate Test Data Manager
• Development and Testing with Clones

Running Database Tasks and Tests on Clones in Redgate Test Data Manager

This article is part of a series on using data containers (clones) in database development and testing work:

In Redgate Test Data Manager, a ‘clone’ is a database instance running in a container. Each database within is an exact copy of the source databases contained in a ‘virtualized’ data image. An obvious bonus to working with data containers, or clones, is the amount of time and effort saved in database setup and teardown for test-driven development. However, they also provide an easy way to set up servers and databases for development work.

I’ve described previously the basics of defining, creating and removing data images and containers, using the database provisioning, or cloning, CLI (rgclone) in Redgate Test Data Manager. I’ve also shown how to automate these processes when provisioning clones for Flyway migration projects.

This article delvers deeper into the fundamentals of connecting to and working with data containers. I’ll describe how to avoid a few ‘gotchas’, such as around the need to handle short-lived and dynamic connection details, that might otherwise trip you up when connecting your various database CLI tools to containers. I’ll then demonstrate how to adapt existing development tasks (such running tests, generating a build script etc.), to get the required database information from a container, rather than conventional server.

For the demonstration, I provide a PowerShell routine that connects to various data containers, each hosting a different RDBMS (SQL Server, PostgreSQL, Oracle and MySQL) and then, on each one, runs a few standard development tasks and reports the results.

Incorporating data containers into database development and testing

It requires a certain fearlessness to propose the introduction of clones into an established development or testing regime, especially since I don’t know how yours works. As an exercise, I adapted my own test regimes to accommodate data containers (clones). Although the process is reasonably straightforward, there are a few interactions with the server which might prove awkward, especially if you need to adapt tasks that, for example, use the server’s file system to load or dump data, or that rely on credentials having a long lifetime. One other issue that cost me time, when shifting databases previously hosted on Windows over to Linux-hosted data containers, was the discovery that the default way of comparing strings in MySQL and Oracle is different for Linux and Windows hosts!

Having overcome these obstacles, I was relieved to find that, aside from an irritating habit of my rgclone CLI to make random demands for re-authentication, the system worked fine, and the rewards were worth the effort.

Database tests check the performance, security, quality and integrity of a database need to be fast, rigorous and repeatable. Although these tests are best done as part of development, they have separate requirements that aren’t always compatible with the work of designing and creating a database. For a start, it requires the database to start from a set state, hopefully designated by a version number. As well as that, it must be possible to tear down each test easily. I’d add the requirement that it should be as well-automated as possible so that the developer can slip outside for a cup of coffee, blinking in the unfamiliar sunlight. All these requirements become much more easily achievable, once you’ve switched to using data containers.

Connecting database tools to a data container

Most database test systems were written for ‘past times’ when databases were served by static servers to which we could connect using stored and ‘long life’ credentials.

When incorporating clones, we must rewire these test systems to handle dynamic credentials where the locations, port addresses, port, userid or password will change frequently. In these circumstances, it’s important to reference databases (data containers) by a unique and meaningful name

For example, if you use a four-part name for containers containing the project, database, branch and version (e.g., Widget-WidgetDB-dev-V3.2.1.0) then these details, along with the connection details and credentials extracted from the data container object using…

…become a reliable ‘source of truth’, providing all the information required to ensure everyone in the team is certain which version of which database, in which branch of which project needs to be tested, and how to access it.

Getting clear about port addresses

As we’ve seen, we can use the CLI commands, rgclone get data-container, to get the connection details for all our data containers. This will generally provide all we need to reconfigure our test processes to handle containers. The most obvious difference that might catch you out is that the containerized databases use non-standard ports. In fact, a connection to a data container always requires the specification of a non-standard port. However, different RDBMSs often have their own standard ways of specifying the server’s port address if it is not the default port.

The most obvious problem is with SQL Server. Conventional SQL Servers are on port 1433. Containers aren’t, so to carry on as normal, add the port to the server parameter, and make sure that you include the port in JDBC and ODBC connection strings. JDBC uses server:port and ODBC generally, but not always, uses a separate assignment of a value for the server and port.

Also, the various command-line utilities provided with each RDBMS use different conventions for their command line connection parameters. For example:

• sqlcmd -S servername,portnumber -U username -P password
• mysql -h hostname -P port_number -u username -p password
• psql -h hostname -p port_number -U username -d mydatabase
• sqlplus username/password@hostname:port_number/service_name

Some tools, notably psql, require their passwords to be passed in either session-based environment variables or files (the pgpass file). Oracle tools often require use of Oracle wallet (rgclone doesn’t use this method of authentication.)

Third-party database tools follow all sorts of different conventions. Redgate tools generally follow the SQL Server conventions. To connect SQL Compare to a data container, for example, the command might look like this:

Connecting scripts and applications via connection strings

When you are connecting your database utilities and tools to databases via JDBC or ODBC, the change to accessing data containers will be easy, because rgclone get data-container will provide values for the parameters that are required. However, sometimes you need to connect a utility that doesn’t take a connection string but instead requires all the connection information via parameters. Sometimes you can still gather all the information you need from rgclone, but not always. Fortunately, this information can often be parsed and extracted instead from the information that is provided by rgclone in either the JDBC or ODBC connection string, and then used to make the connection. As an example of this, I had previously connected to Oracle Cloud via a wallet, so providing access to an Oracle data container required adjustment. I had a bit of a struggle with Oracle tools until I realized that the string used to connect to the Oracle data container rather than the Cloud service was just a subset of the JDBC string provided by rgclone.

Server versions

Another potential source of errors comes from the version of the database server used in the container. If the tools you use, installed locally on the machine that is running the scripts, are of a version that is incompatible with the database server version, you’ll start getting errors. You can specify the version of the server used to create the data images. That is a good idea. It is quite a good strategy to keep everything up-to-date but ideally you should move the server version and utility version in unison, because you can get errors from a too-ancient version of the database server or, more rarely, a too recent version.

Server host and collation

The final ‘gotcha’ of using containers, at least for anyone mainly using Windows, is that some RDBMSs adopt Linux’s default of case sensitivity for both identifiers and string literals. When creating databases, it is worth specifying your collation to get around this problem. Each RDBMS has its own approach. The SQL Standard specifies that identifiers (the names of all the database objects) should be case-insensitive whereas string literals can be case-sensitive. By specifying a sensible collation then everything can work smoothly, but most of the problems I’ve hit in moving to Linux hosts for relational databases come from confusion in doing comparisons of string identifiers and literals.

A demonstration

When I started preparing this demonstration, I laboriously tried to show how to adapt all the common database CLI tools to work with data containers, such as: sqlcmd, codeguard, sqlcompare, sqldatacompare, pgdump, psql, sqlite, oraclecmd, mysqldump, mysql, and sqlpackage.

However, I soon realized that I’d already done all the hard work in my Flyway Teamwork framework, to which I’ve added a range of supporting utilities to run routine database development and testing tasks. It includes PowerShell script block tasks to execute scripts with timings, execute scripts from a file one expression at a time, test the results, do a backup, generate build scripts and manifests, produce a report, and so on.

When adding these tasks to a Flyway development, you do this through a callback that retrieves the Flyway version of the database and then runs the required tasks. This allows you to create a script that accesses the database easily, and at the right point in the process, to test, record or report a version of the database. Your run of tests can be done on the version of the database that Flyway just created, or at whatever point in the process you choose.

However, you probably want to be able to run your existing database tasks and tests directly on your data containers, without requiring use of Flyway. That means adapting those tasks so that they can assemble the connection and other project and database details they need, such as the database version) directly from rgclone, and the data containers (as well as through Flyway).

The main difference is that you’ll be directly accessing the database and running the tasks (obviously we can’t use Flyway callbacks) and you’ll need to expend effort explicitly maintaining the current version of the database so that reports and database models can be saved in an appropriately-named file.

Below is the routine to help you tackle all this. It is designed to run a list of tasks that you specify on one or more of your data containers. You can set up your own data container, but this example assumes you use a similar naming convention to that described in Provisioning Clones for Flyway Projects, with the project and branch specified as the second and fourth part of the name of the data container. It also assumes that you’ve set up the rgclone CLI, provided the API endpoint for your Clone server, and authenticated to the server.

The tasks you want to run might include database tests, executing other SQL scripts, producing a report, or creating a build script. I’ve included a few random tasks from the Framework, just for the purposes of this demo. You will probably want to run tasks related to testing such as timing scripts or checking results. The links I provide at the end of the article provide a lot more information about how to run different types of tests using the framework.

A few notes on running the code (see also the comments in the code):

• It uses the $WhatIsWanted filter to run the tasks on all data containers, or just the ones you want (in this example only myql containers)
• It extracts the necessary details (host, port, user, password, etc.) from each data container and saves them in a hashtable, $DBDetails
• It creates the directories for the output of running each task, named “Project-Database-Branch” with subfolders per database version.
• When we run each of Process-FlywayTasks, each task uses the information in $DBDetails to connect to each data container and perform the required operations.

For more details of testing using the Flyway Teamwork framework, See:

1. Testing Databases: What’s Required?
2. Planning a Database Testing Strategy for Flyway
3. Test-Driven Development for Flyway Using Transactions
4. Basic Functional Testing for Databases using Flyway
5. Running Unit and Integration Tests during Flyway Migrations
6. Performance Testing Databases with Flyway and PowerShell
7. Running Structured Database Tests in Flyway
8. Running Database Assertion Tests with Flyway

Conclusions

Most of the corporate databases I’ve developed were done at a time when the cost and distraction of even the essential test databases was an important factor on deciding a test strategy. Then, the idea of branching and merging, using copies of the development databases, was science fiction. Now, by contrast, I can easily provide myself with a large bank of database servers on cheap hardware and automate away the pain of provisioning and maintenance.

Redgate Test Data Manager’s cloning CLI provides a novel way of taming containerization technology to make it quicker and easier to provision and test databases, to ensure that testing will be done throughout the development cycle. It’s novel, but not disruptive, because it is likely to fit in well with your existing system of automating database chores.