Phil Factor

2 August 2024

• Redgate Test Data Manager
• Get Started with Clones

Connecting Easily to Data Containers from Database Tools and IDEs

When you start using data containers, or clones, in Redgate Test Data Manager, the initial surprise is the sudden ease with which one can move back and forth between different versions of the same database, or test the same task on different database systems, or the same version of a database but with different datasets.

You can try things out, wildly trampling all the data until, in a moment, restoring everything back to how it was. Even in the eighties I had a reputation for doing wild things to databases in shared database development work, but in those days the process of ‘tear-down’ and ‘tidy up’ could be a long one.

You are rightly encouraged to make data containers short-lived, volatile creations. The snag here, albeit deliberate, is that each time you create or reset a data container, it will have the same host, but both the port and password will always be different. Do you remember passwords? These were the quaint old way that we once did security, long before we used 2FA with security keys, fingerprints and facial recognition. Remarkably, userid and password combinations still exist, and they are too long even for retaining in short-term memory.

When working with data containers of any sort, you are faced with a constantly shifting password and port that then must be supplied, repeatedly, to the IDE or editor you use to develop the database. There are also the problems of connecting the command line tools we typically use for doing backups, creating a build script the database, inserting data into it, generating data, comparing database versions, executing SQL Code, producing ER Diagrams, and so on. That’s before you add in the requirement of your database testing regime.

Supplying connection details for data containers to database tools

Redgate Test Data Manager’s cloning CLI (rgclone) has a simple way of providing the information required to connect to a data container. Once you are authenticated, you can get all the connection details you could possibly want or need in a simple PowerShell command:

JDBC connection string? ODBC Connection String? Hostname? User? Password? Yes, it is all there. Now you can filter out the details you want:

Now we just need to supply this connection information to the database tool, and these tools tend to have different ways of specifying these details. You’ll find some that use environment variables at startup, others require configuration files or command line parameters. You don’t get much choice. Many tools, the ones that cause the most difficulty, just assume that you’ll modify a connection definition, via an IDE, whenever any of the connection details change. This isn’t satisfactory for working with volatile data containers, possibly with many of them.

It is reasonable to store volatile credentials and connection details in a file in the user area, encrypted. You’ll then need a script to decrypt them and ‘splat’ the configuration information into the command line program. However, it is usually better to use session environment variables if the tool that you are using accepts them, because they are ephemeral, only existing while the session is running. You just need to be careful with executing scripts within the same session once you’ve done so.

Connecting to data containers from IDEs for interactive development

With one command, you want your IDE to appear, logged into the database and ready for action. To do this, you need a good way of getting the connection details and credentials for the container, or containers, that you wish to access.

Connecting to data containers from SSMS

If you are using SQL Server, you can invoke SQL Server Management Studio (SSMS) for your container very simply (it used to be even easier until they removed the command line option to specify your password for the connection, but we can get around that!).

I’ll assume you’ve already created a set of data containers, as described in Provisioning Clones for Flyway Projects. To connect to the required data container from SSMS, it provides PowerShell with the path to the SSMS executable. It then retrieves the host, port and user and password details from rgclone and splits out the database name from the name of the data container (my container names contain various bits of information such as the database name, engine, and branch, separated by dashes (-)). It copies the password to the clipboard and uses the rest of the details to fill in the connection parameters for SSMS.

SSMS allows you time to sip your coffee as it boots up. It will protest that it can’t connect and will ask you for the password in the connection window. The password will be on the clipboard, so no typing required. If you want, you only need to paste into the password field the once. If you click on the ‘remember password’ option, then it will use it with subsequent invocations that use the same port number.

Connecting from a multi-RDBMS IDE

When working with other IDEs, you’ll use a similar mechanism to invoke them, using a script that provides the credentials and everything else necessary as arguments or command line parameters. If your processes or tasks need to invoke the IDE, or other utility, often then you’d create a PowerShell alias to do this. In the following example, we invoke HeidiSQL and connect to a MySQL data container, using connection details collected manually from rgclone:

As if by magic, HeidiSQL then appears, logged into the container.

Of course, in practice, we’d extract the volatile details from rgclone, such as credentials and port, and save them into a file in the user area. Then, we’d put the principles of the previous routine into a function that accesses the saved file (although you can, if you prefer, grab the connection details directly from rgclone). This gives us most of the details we need, but HeidiSQL is a bit awkward because it likes to know the nettype (a combination of the RDBMS you’re using and the network protocol type), and the provider.

To illustrate how we might automate this, we use an imaginary project (See Provisioning Clones for Flyway Projects) for which we are developing a database using HeidiSQL, a multi-RDBMS IDE. In this example, we invoke three instances of HeidiSQL, each one connected to a different data container, and each container hosting the same version of the Northwind database on a different RDBMS. HeidiSQL isn’t really designed for having more than one instance of its IDE working on one machine, but this is just to prove that it is possible:

The advantage of the technique is that the start-process cmdlet invokes a new process with its own environment variables (such as the password or token) which are lost when the process ends. Newer versions of the cmdlets allow you to declare the environment variables as a hashtable, making the invocation less complicated.

Connecting from a typical command line tool

Normally, for command line tools, you don’t have to have anything quite as complicated. Here, we connect mysqldump to a MySQL data container to take a database backup.

The PowerShell script simply filters for th data containers we want to backup, establishes where to store the backup scripts, retrieves the host, port and user and password details from rgclone and uses them to populate the hash table array, then runs the mysqldump command on each data container:

Conclusions

For most purposes, data containers will work in much the same way as a regular database. Where containers are most effective, for automated testing, and test-driven database development, they save a lot of time. The fact that credentials are securely kept by the Redgate Clone system is a bonus.

However, the software industry has developed tools that assume that servers are stable systems that survive and maintain their credentials and connection details. Hopefully this will change, but in the meantime, we can escape the tedium of having to deal with ports, userids and passwords with a little help from automation scripts.