Helper Functions for Testing a PostgreSQL Database

When writing tests for a Node.js server, it’s common to test the database as well. (At least, one hopes.) Here are a few helper functions for testing a PostgreSQL database using Knex.

Setup and Teardown

These two functions are common. Note that getConnection is a call to a function in another file. It gets the connection to Knex and creates it with some configuration if it doesn’t already exist.

async function setup(): Promise {
  const knex = getConnection();
  await knex.migrate.latest();
  await knex.seed.run();
}

async function teardown(): Promise {
  const knex = getConnection();
  await knex.migrate.rollback();
}

Unlock

Why would you want a function to “unlock” the Knex migrations lock? Because, occasionally, while writing and/or breaking tests, there’s a failure in the middle of changing data or some migration. This can cause the database to remain locked, making subsequent runs of the tests fail because the database is still locked. It may not be a common problem, but it happened enough for me to write this function out of pure annoyance.

This should be used in a global-before and global-after function, along with the setup and teardown functions.

async function unlock(): Promise {
  await getConnection().schema.hasTable("knex_migrations_lock").then(async (exists: boolean) => {
    if (exists) {
      await getConnection()("knex_migrations_lock")
        .update("is_locked", '0');
    }
  });
}

Preparing the Database

While running tests on specific tables, you usually want to add and remove data. In some cases, you may not want a test to depend on other data in your tables. The best way to handle this situation is to truncate all of the tables you want. I say truncate, not delete, because Truncate doesn’t scan the tables and is therefore faster.

Table is an enum of the tables and their names as strings. This makes it easy to truncate all of our tables in a single call, targeting only the ones we want to clear. prepareDB I call in the beforeEach for test files that manipulate the database.

async function prepareDB(): Promise {
  const knex = getConnection();
  const tables = _.difference(Object.values(Table), []).join(",");
  await knex.raw(`TRUNCATE TABLE ${tables} CASCADE`);
}

Bringing this all together we can set up a jackage.json script for the server. This will first set some mocha options, then handle a global setup and teardown of our database for tests. Note that you should have a local database setup that will be used when NODE_ENV is set to test

"test:server": "NODE_ENV=test mocha --exit --opts ./mocha.opts server/test-helpers/setup-database-for-testing.ts \"./server/**/*.test.ts*\""

The mocha.opts is now deprecated as of mocha 6.0.0 and instead you would use a config file https://mochajs.org/#configuring-mocha-nodejs

-c
--timeout 15000
--require ts-node/register
--require tsconfig-paths/register
--recursive

These are teh before and after functions for the setup-database-for-testing.ts

before(async function() {
  this.timeout(process.env.CI ? 30000 : 20000);
  await DBHelper.unlock();
  await DBHelper.setup();
});

after(async () => {
  await DBHelper.unlock();
  await DBHelper.teardown();
});

What are your favorite tips for testing with databases? Share your thoughts in the comments.