Author: Zachary Leighton
Integration testing vs. unit testing
Unit tests are great for testing a function for a specific behavior, if you code right and create your “mocked” dependencies right you can be reasonably assured of your code’s behavior.
But our code doesn’t live in isolation, and we need to make sure all the “parts” are connected and working together in the way we expect. This is where integration tests come in to play.
A good way to explain the difference would be that a unit test would test that a value (let us say an email for simplicity) passes a test for business logic (possibly a regex or something — maybe checks the URL) and the email and rules would be provided as mocks/stubs/hard-coded in the test, while an integration test of this would check the same logic but also retrieve the rules and value from a database — thus checking all the pieces fit together and work.
If you want more examples or want to read up a bit more on this there are great resources on Medium, as well as Stack Overflow, etc. The rest of this article will assume you are familiar with NodeJS and testing it (here we use Mocha — but feel free to use whatever you like).
Pulling the MS-SQL image
To start you’ll want to pull the Docker image, simply run the command docker pull microsoft/mssql-server-linux:2017-latest (Also if you haven’t installed Docker you might want to do that too 😃)
This might take a few minutes depending on what you have installed in your Docker cache.
After this is done, please make sure to right click, go to “Settings…” and enable: “Expose daemon on tcp://localhost:2375”. As we will see in a few sections this needs to be set to process.env.DOCKER_HOST for the Docker modem to run correctly.
Delaying Mocha for setup
Since we need a few moments to spin up the container and deploy the schema we will use the --delay flag for Mocha.
This adds a global function run() that needs to be called when the setup is done.
You should also use the --exit flag which will kill Mocha after the test run, even if a socket is open.
Preparing the run
In this example, we use the --require flag to require a file before the test run. In this file an IIFE (immediately invoked function expression) is used because we need to call some async functions and await them, and then call the done() function from above. This can be done with callbacks but it is not so clean.
The IIFE should end up looking like this:
(async () => {
const container = require('./infra/container');
await container.createAsync();
await container.initializeDbAsync();
run(); // this kicks off Mocha
beforeEach(async () => {
console.log('Clearing db!');
await container.clearDatabaseAsync();
});
after(async () => {
console.log('Deleting container!');
await container.deleteAsync();
});
})();
Spinning up the container from Node
In the above IIFE we have the method container.createAsync(); which is responsible for setting up the container.
const { Docker } = require('node-docker-api');
const docker = new Docker();
...
async function createAsync() {
const container = await docker.container.create({
Image: 'microsoft/mssql-server-linux:2017-latest',
name: 'mssqltest',
ExposedPorts: { '1433/tcp': {} },
HostConfig: {
PortBindings: {
'1433/tcp': [{ HostPort: '<EXPOSED_PORT>' }]
}
},
Env: ['SA_PASSWORD=<S00p3rS3cUr3>', 'ACCEPT_EULA=Y']
});
console.log('Container built.. starting..');
await container.start();
console.log('Container started... waiting for boot...');
sqlContainer = container;
await checkSqlBootedAsync();
console.log('Container booted!');
}
The container is created from the async method docker.container.create , the docker instance needs to have process.env.DOCKER_HOST set, in our case we have a local Docker server running (see: Pulling the MS-SQL image) so we’ll use that.
The options come from the modem dockerode and it uses the Docker API.
After the container spins up we need to check that SQL finished running, our port is <EXPOSED_PORT> and the password is <S00p3rS3cUr3> (these are placeholders so make sure you put something valid).
If you want to read more about what is happening here with the EULA option, etc. check out the guide here from Microsoft.
Since it takes a few seconds for the SQL server to boot up we want to make sure it is running before firing off the test suite. A solution we came up with here was to continually try and connect for 15 seconds every 1/2 second and when it connects, exit.
If it fails to connect within 15 seconds something went wrong and we should investigate further. The masterDb.config options should line up with where you’re hosting Docker and on what port you’re exposing 1433 to the host. Also remember the password you set for sa .
async function checkSqlBootedAsync() {
const timeout = setTimeout(async () => {
console.log('Was not able to connect to SQL container in 15000 ms. Exiting..');
await deleteAndExitAsync();
}, 15000);
let connecting = true;
const mssql = require('mssql');
console.log('Attempting connection... ');
while (connecting) {
try {
mssql.close();
// don't use await! It doesn't play nice with the loop
mssql.connect(masterDb.config).then(() => {
clearTimeout(timeout);
connecting = false;
}).catch();
}
catch (e) {
// sink
}
await sleep(500);
}
mssql.close();
}
Deploying db schema using Sequelize
We can quickly use Sequelize to deploy the schema by using the sync function, then as we will see below it is recommended to set some sort of flag to prevent wiping of a non-test DB.
First though, we want to actually create the db using the master connection. The code will end up looking something like this:
async function initializeDbAsync() {
const sql = 'CREATE DATABASE [MySuperIntegrationTestDB];';
await masterDb.queryAsync(sql, {});
await sequelize.sync();
return setTestingDbAsync();
}
Safety checks
Let’s face it, if you’ve been programming professionally for any reasonable amount of time — you’ve probably dropped a database or file system.
And if you haven’t go run out and buy a lotto ticket because man you’re lucky.
This is the reason to set up infrastructure for backups and other things of the sort, roadblocks if you will, to prevent human error. While this integration test infrastructure you just finished setting up here is great, there is a chance you may have misconfigured the environment variables, etcetera.
I will propose here one possible solution, but feel free to use your own (or suggest more in the comments!).
Here we will use the SystemConfiguration table and have a key value pair on key TestDB that’s value needs to be truthy for the tables to be truncated. Also at multiple steps I recommend checking the NODE_ENV environment variable to be test which can make sure you didn’t accidentally run this code in a non-test environment.
At the end of the last section we saw the call to setTestingDbAsync the content is as follows:
async function setTestingDbAsync() {
const configSql =
"INSERT INTO [SystemConfiguration] ([key], [value]) VALUES (?, '1')";
return sequelize.query(configSql, {replacements: [systemConfigurations.TestDB]});
}
This sets the value in the database, which we will check for in the next snippit. Here is a snippet of code that will check the existence of a value on the key TestDB (provided from a consts file) that we just set.
const result = await SystemConfiguration.findOne({ where: {key: systemConfigurations.TestDB }});
if (!result) {
console.log('Not test environment, missing config key!!!!');
// bail out and clean up here
}
// otherwise continue
Wiping the test before each run
Taking the code above and combining it with something to clear the database we come up with the following function:
const useSql = 'USE [MySuperIntegrationTestDB];';
async function clearDatabaseAsync() {
const result = await SystemConfiguration.findOne({ where: {key: systemConfigurations.TestDB }});
if (!result || !result.value) {
console.log('Not test environment, missing config key!!!!');
await deleteAndExitAsync();
}
const clearSql = `${useSql}
EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'`;
await sequelize.query(clearSql);
return setTestingDbAsync();
}
async function setTestingDbAsync() {
const configSql = "INSERT INTO [SystemConfiguration] ([key], [value]) VALUES (?, '1')";
return sequelize.query(configSql, {replacements: [systemConfigurations.TestDB]});
}
This will check for the existence of the value for key TestDB in the SystemConfiguration table before continuing. If it isn’t there it will exit the process.
Now how does this run within the context of Mocha?
If you remember in the IIFE we had a call to beforeEach , this is where you want to have this hook so that you have a clean database for each test.
beforeEach(async () => {
console.log('Clearing db!');
await container.clearDatabaseAsync();
});
Shutdown / Teardown
You don’t want to leave the Docker in an unknown state, so at the end of the run simply kill the container, you’ll want to use force too.
The after look looks like this:
after(async () => {
console.log('Deleting container!');
await container.deleteAsync();
});
And the code inside container.deleteAsync(); looks like this:
async function deleteAsync() {
return sqlContainer.delete({ force: true });
}
Putting it all together
Since this article was a bit wordy, and jumped around a bit here are the highlights of what to do to get this to work:
- Delay Mocha using
--delay - Require a setup script and use an IIFE to set up the container/DB
- Spin up a Docker container instance, wait for SQL to boot
- Deploy the schema using Sequelize and also put in a safety check so we don’t wipe a non-test DB.
- Hook the wipe logic into the
beforeEachhook - Hook the teardown logic into the
afterhook - Create amazing codez and test them
I hope you enjoyed this article, and suggestions, comments, corrections and more memes are always welcomed.
Tipalti Labs 