Example 1: High-level availability checks with NodeJS and the Mocha testing framework

We can use a couple of tools to check for the general availability of a database and do some high-level checks – even a very simple, self-written tool in NodeJS, using the Mocha and Chai libraries.

It might not be the best option for every situation, but it’s definitely something to get you started very quickly.

You can find the complete source code here.

Package.json

For our tool to work we need a couple of dependencies:

• The mocha framework
• The chai libary (for nicer assertions)
• A database adapter library, in this case the oracle-node library

We also want to configure the npm test command to use mocha and run our tests.

{
  "name": "test_impdb-availability",
  "version": "1.0.0",
  "description": "Example of High-level and availability tests of my talk \"Testing in the Imperial Database\"",
  "main": "index.js",
  "scripts": {
    "test": "mocha test/*.test.js"
  },
  "author": "Samuel Nitsche",
  "license": "ISC",
  "dependencies": {
    "chai": "^4.3.4",
    "mocha": "^9.0.0",
    "oracledb": "^5.2.0"
  }
}

Test connecting to the database

Before we actually start connecting, there’s one very important thing we need to think about:

We should never put actual credentials in the code, not even the test code     So we load the actual credentials from a json-file with the structure
 { "user":"", "password":"" }

We want to make sure this json-file is mentioned in .gitignore so it doesn’t get added to the repository.

const credentials = require("../credentials.json");

Next, we want to have a helper-function that returns a connection from the database adapter:

    async function getConnection() {
        return await oracledb.getConnection({
            connectString   : "localhost:1523/XEPDB1",
            user            : credentials.user,
            password        : credentials.password  
        });
    }

We can use this function for our first two tests:

    it("Connection can be created", async () => {
        const connection = await getConnection();

        expect(connection).to.be.an("object");

        await connection.close();
    });

This one only checks if we are actually getting a connection-object back, but doesn’t really prove an established connection.

    it("Connection allows simple SELECT", async () => {
        const connection = await getConnection();

        const resultSet = await connection.execute("select 1 from dual");
        expect(resultSet)
            .to.haveOwnProperty("rows")
            .that.deep.equal([[1]]);

        await connection.close();
    });

But this one does, by issuing the simplest possible SELET statement to the database we can think of: select 1 from dual (this might even be reduced to select 1 in other databases).

High-Level Checks

We an now go on and add whatever high-level checks we can think of to the same tool, for example a query that will return all objects inside the database that are not valid:

    it("Database contains no invalid objects", async () => {
        const connection = await getConnection();

        const resultSet = await connection.execute(
            `select  object_name
                    ,object_type
                from user_objects
                where status != 'VALID'
            `
        );

        const results = resultSet.rows
            .map(entry => entry[0]+" ("+entry[1]+")")
            .join("\n");
        expect(results).to.be.empty;

        await connection.close;
    });

We could even turn the connection.execute-part into its own function again, making additional high-level checks even easier.

Running the tests

Running these tests is absolutely simple now by calling npm test in the folder of your package.json. Mocha will create a nice summary:

The whole mocha-testfile

const expect = require('chai').expect;
const oracledb = require("oracledb");

describe("Database Availability", () => {
    
    /*
     We should never put actual credentials in the code, not even the test code
     So we load the actual credentials from a json-file that should have this structure:
     { "user":"", "password":"" }
    */
    const credentials = require("../credentials.json");
    
    async function getConnection() {
        return await oracledb.getConnection({
            connectString   : "localhost:1523/XEPDB1",
            user            : credentials.user,
            password        : credentials.password  
        });
    } 

    /* Very basic check that our database adapter can create a connection object */
    it("Connection can be created", async () => {
        const connection = await getConnection();

        expect(connection).to.be.an("object");

        await connection.close();
    });

    /* A second check to verify we can actually select from the database */
    it("Connection allows simple SELECT", async () => {
        const connection = await getConnection();

        const resultSet = await connection.execute("select 1 from dual");
        expect(resultSet)
            .to.haveOwnProperty("rows")
            .that.deep.equal([[1]]);

        await connection.close();
    });

    /* High-level check if there are any invalid objects in the database */
    it("Database contains no invalid objects", async () => {
        const connection = await getConnection();

        const resultSet = await connection.execute(
            `select  object_name
                    ,object_type
                from user_objects
                where status != 'VALID'
            `
        );

        const results = resultSet.rows
            .map(entry => entry[0]+" ("+entry[1]+")")
            .join("\n");
        expect(results).to.be.empty;

        await connection.close;
    });
});

Previous: Preparations: Setting up the database

Next: Example 2: Automated tests of database functionality with pure PL/SQL