1 Comment

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.


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

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