# All your (data)base are belong to us

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:

• May be administered by your IT department who (rightly!) won’t give you free reign. During early development in particular, the database design may need to iterate rapidly.
• Will need to handle multiple users running tests against it at the same time without contention.

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:

1. We’ve worked out the Docker commands to launch the right containers in the right state for you. Currently we ship implementations for Microsoft SQL Server® 2017 and 2019, and PostgreSQL®. We also have the same functionality for SQLite, although this doesn’t actually need Docker.
2. We’ve provided the code as a shared test fixture with corresponding test classes that your own tests can inherit from. Being a shared fixture minimises the number of times that the database gets setup & torn down (provided you’ve sorted your test suite), maximising efficiency – but the onus is on you not to write leaky tests!
3. Additional functionality includes checkpointing (restoring the database to a set state) and loading a backup file. You might use this if you want to debug an issue from production for example.
4. The port used by each database server instance is changed from the default to another free port. This allows multiple test suites to run independently at the same time. For example, you may want to run your tests in parallel, or your CI system may execute multiple jobs at the same time.

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);

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")

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