Today I'd like to introduce first time blog contributor Tim Johns. Tim is a consultant in our UK office who has a wealth of experience utilizing the power of MATLAB for production applications. He is here to highlight a powerful workflow he has developed and is sharing for testing with databases. Take it away Tim!

You need automated testing!

For the past year or so, a couple of my colleagues from the consulting team and I have been working with one of our customers on a large software development project in MATLAB® – 60 000 lines of code, 10+ developers in 3 countries. The resulting application is being used by the customer worldwide. I can easily say that if it wasn’t for automated testing, we would have been completely lost!

The application performs a lot of data analysis. To do this it must connect to an enterprise database. This presents a conundrum:

How do we automatically test against a database?

When writing such data processing applications, at some point you end up needing to test against a database. Mocking can and should be used at the unit level, but as you progress into the integration testing, a representative system is required. The question is then what to test against?

Using the production database is an absolute no-no: much like performing electrical work on your house without turning off the main electrical supply, you’ll get a nasty shock sooner or later.

A better option is to run a test database either on a separate server or locally on each development machine. This creates a clear separation between production and development work, however there are some disadvantages.

Disadvantages of a “fat” test server

A central test server:

Running locally on each development machine is a heavyweight installation, even if IT permits it, and in either case, can you be confident that you haven’t inadvertently changed something on the server that affects subsequent test runs?

Solution: use a transient database

The solution that we came up with was to spin up and populate an instance of the database server in a Docker® container at the moment it was needed. That way you can guarantee you have got a clean database ready for testing, and no matter how wrong the test may go, it all gets cleaned up at the end!

Docker is fast becoming a standard DevOps tool. The Docker install is lightweight compared to a complete local install of a database server.

Introducing our database testing framework

The database testing framework that we developed to solve this is now available on the File Exchange for you to use to. It helps you to do a few things:

Getting started

You will need the Database Toolbox™ to run this code. Install the Database Testing Framework from the File Exchange. This toolbox is currently supported only on Windows so be sure to do this from a Windows machine. Next, make sure you have the relevant driver installed for the database you want to use (Microsoft SQL Server or PostgreSQL). Finally, if you don’t have it already, download and install Docker Desktop for Windows along with the Windows Subsystem for Linux.

A full set of instructions is included with the toolbox, but here are some basic commands to get you started. We’ll use an interactive test session here, but normally you’ll want to use a class-based approach.

Establish a database and connection

First, we’ll run the command to setup the database in Docker and establish a connection to it:

tc = dbtest.WithMsSqlServer2019.forInteractiveUseWithAutoSetup()

EULA: <a href="https://hub.docker.com/_/microsoft-mssql-server">https://hub.docker.com/_/microsoft-mssql-server</a>
[13-Nov-2020 11:49:45] Attempting to create container: Source=lh:55430_13-11_11:49:45.568, Port=55430
[13-Nov-2020 11:49:48] Attempt [01] Waiting for container to be initialized 
[13-Nov-2020 11:49:53] Attempt [02] Waiting for container to be initialized 
[13-Nov-2020 11:49:58] Attempt [03] Waiting for container to be initialized 
[13-Nov-2020 11:50:03] Attempt [04] Connected: took 16.5sec 
Running Setup Fcn ...
Finished setting up database

tc = 

  WithMsSqlServer2019 with properties:

    DatabaseConnection: [1×1 database.odbc.connection]
       CheckpointNames: [1×0 string]
            TableNames: [0×1 string]

Write some data to the database

Next, we’ll create a simple table and write it to the database:

tbl = table("Batman", 35,"Male", 200,"Gotham",'VariableNames',...
    {'LastName', 'Age', 'Gender', 'Height', 'Location'})

tbl =

  1×5 table

    LastName    Age    Gender    Height    Location
    ________    ___    ______    ______    ________

    "Batman"    35     "Male"     200      "Gotham"

tc.DatabaseConnection.sqlwrite("Characters",tbl);

Retrieve the data again

Now we’ll fetch the data back from the database and check it’s the same as what we sent:

tc.DatabaseConnection.sqlread("Characters")

ans =

  1×5 table

     LastName     Age     Gender     Height     Location 
    __________    ___    ________    ______    __________

    {'Batman'}    35     {'Male'}     200      {'Gotham'}

Make use of checkpoints

One of the features built into the framework is the ability to create a checkpoint in the database that you can restore to at a later point in time. This is really useful when you want to set up the database once for a test suite but start each test point with the database in the same state.

Let’s create a checkpoint called “BatmanOnly”:

tc.createCheckpoint("BatmanOnly");

Now we’ll add some more data to the table in the database:

tbl.LastName = "Robin";
tc.DatabaseConnection.sqlwrite("Characters",tbl);
tc.DatabaseConnection.sqlread("Characters")

ans =

  2×5 table

     LastName     Age     Gender     Height     Location 
    __________    ___    ________    ______    __________

    {'Batman'}    35     {'Male'}     200      {'Gotham'}
    {'Robin' }    35     {'Male'}     200      {'Gotham'}

And now we’ll restore the database to the “BatmanOnly” checkpoint:

tc.restoreCheckpoint("BatmanOnly")
tc.DatabaseConnection.sqlread("Characters")

ans =

  1×5 table

     LastName     Age     Gender     Height     Location 
    __________    ___    ________    ______    __________

    {'Batman'}    35     {'Male'}     200      {'Gotham'}

Clean up!

Finally, we’ll clear the connection to the database from the workspace. Since it’s no-longer in use, it will automatically delete the connection, and stop and remove the container in Docker:

clear tc

[13-Nov-2020 11:50:16] Attempting to tear down container: Source=lh:55430_13-11_11:49:45.568, Port=55430

Your turn!

Grab the Database Testing Framework toolbox from File Exchange or GitHub and have a go yourself! Let me know how you get on in the comments below.

Get the MATLAB code

Published with MATLAB® R2020b