End-to-end API testing using Knex & Migrations

When we are building an API it is usually a good idea to have some tests that cover the general flow, with as less mocking as possible.

Most of the times, your API will use/depend on one or multiple databases.
On one hand, it would be really nice if we could test our API without mocking anything database related, but on the other hand, we should avoid using our actual database for testing purposes, even if we actually clean up our mess afterwards.

Database Migrations to the rescue

It turns that database migrations can be really handy in this situation. It would be ideal if we could just before the tests, create a new database, migrate it, seed it (if needed) and then after the tests rollback and delete the database, like nothing ever happened.

Knex

In a node application, we can achieve something like that using Knex. All we would need to do is create a new testing database using Knex and then just apply the same migration setup that we have already implemented for our existing non-testing database.

How would this look like

Your already existing migration file. This is the migration that you are already using for your database. This is also the migration that we will be using to prepare the temporary test-database for our tests.
Note that you can also have a seeding file, so you can directly seed your tables with data, if needed.

import * as Knex from 'knex';

export async function up(knex: Knex): Promise<any> {
  return () =>
    knex.schema.createTable('table1', (t) => {
      t.increments('id')
        .primary()
        .notNullable()
        .unique('table1');
      // more columns
    });
    // more tables
}

export async function down(knex: Knex): Promise<any> {
  return () => knex.schema.dropTable('table1');
}

Your tests. Notice that in the beginning and in the end of every test suite, we are migrating and rollbacking respectively, so every test suite can have a fresh test-database.

const config = {
  client: 'postgres',
  debug: true,
  connection: {
    host: 'localhost',
    database: 'test_database',
    port: '5432',
    password: '',
    user: ''            
  },
  migrations: {
    directory: './migrations'
  },
  seeds: {
    directory: './seeds'
  }
};
const knex = require('knex')(config);

describe('test suite description', () => {
  beforeAll(() => {
    return knex.migrate.latest();
    // you can here also seed your tables, if you have any seeding files
  });
  afterAll(() => {
    return knex.migrate
      .rollback()
      .then(() => knex.destroy());
  });

  test('test 1', () => {
    // testing stuff
  });
  test('test 2', () => {
    // testing other stuff
  });
});

Last, we will be using the options globalSetup and globalTeardown options of Jest so we can create our database and also delete it before and after every Jest call respectively.

for the globalSetup:

import * as knex from 'knex';

function getDbConnection() {
  return knex({
    client: 'postgres',
    debug: true,
    connection: {
      host: "localhost"
      database: "postgres",
      port: "5432",
      password: "",
      user: "",            
    }
  });
}

async function createDatabase() {
  const dbConnection = getDbConnection();

  try {
    await dbConnection.raw('CREATE DATABASE test_database');
  } catch (err) {
    console.log(err);
  } finally {
    await dbConnection.destroy();
  }
}

module.exports = async () => {
  await createDatabase();
};

for the globalTeardown:

import * as knex from 'knex';

async function deleteDatabase() {
  const dbConnection = knex({
    client: 'postgres',
    debug: true,
    connection: {
      host: "localhost"
      database: "postgres",
      port: "5432",
      password: "",
      user: "",            
    }
  });

  try {
    await dbConnection.raw('DROP DATABASE IF EXISTS test_database');
  } catch (err) {
    console.log(err);
  } finally {
    await dbConnection.destroy();
  }
}

module.exports = async () => {
  await deleteDatabase();
};

Now we can run Jest and know that we will have a temporary test-database with the same schema as our original database.

Summary

We can use Knex in order to have a temporary database with which we can test the flow and the endpoints of our API without having to mock anything related to the database. We can be sure that the database tables' schema will be identical with the original, since we are going to use the same migration files that the normal database was built with.

PS: This is the first time I write anything, so I would really appreciate feedback and constructive criticism as well.