How to set up a test database for integration tests

After our publishing app became more and more complex, we realized that unit tests, those who deal with a particular block of code (such as a function) and mock everything else was not enough. We needed a way to make sure that the way we interact with our database is correct, so we summoned the power of integration tests.

In order to make this possible, we first needed to specify which environment jest should use for testing by adding the following line to your package.json:

"jest": {
    "testEnvironment": "<rootDir>/DatabaseTestEnvironment.js"

Then we need to make sure that the default.js file from the config folder contains the correct database information so that the Postgres client can connect properly before setting up the test database:

const path = require('path')

const defaultConfig = require('hindawi-review/config/default')

defaultConfig['pubsweet-server'].db = {
  database: global.__testDbName || 'postgres',
  connectionTimeoutMillis: 5000,
  idleTimeoutMillis: 1000,

defaultConfig.dbManager.migrationsPath = path.resolve(

module.exports = defaultConfig

In our very own custom environment, we’re going to set up a temporary test database and then we’re going to tear it down after all the tests are complete:

const NodeEnvironment = require('jest-environment-node')
const pg = require('pg')
const logger = require('@pubsweet/logger')
const config = require('config')

const dbConfig = config['pubsweet-server'] && config['pubsweet-server'].db

let client

class DatabaseTestEnvironment extends NodeEnvironment {
  async setup() {
    await super.setup()

    client = await new pg.Client(dbConfig)
    await client.connect()
    // pass the test database name into the test environment as a global = `test_${Math.floor(Math.random() * 9999999)}`

    await client.query(`CREATE DATABASE ${}`)

  async teardown() {
    // terminate other connections from test before dropping db`TEST DB: ${}`)
    await client.query(
    await client.query(`
      SELECT pg_terminate_backend(
      FROM pg_stat_activity
      WHERE pg_stat_activity.datname = '${}'`)
    await client.query(`DROP DATABASE ${}`)
    await client.end()
    await super.teardown()

module.exports = DatabaseTestEnvironment

Once that’s done we can go ahead and run our first test using a connection to a real database:

const models = require('@pubsweet/models')
const { db, migrate } = require('@pubsweet/db-manager')
const { uniq } = require('lodash')

const { getManuscriptsUseCase } = require('../src/use-cases')

describe('get manuscripts use case', () => {
  beforeAll(async () => {
    await dbCleaner()
      directory: '../app/seeds',
      directory: '../app/testSeeds',

  afterAll(done => {

  it('returns all manuscripts if the user is admin', async () => {
    const { Team, TeamMember, Manuscript } = models
    const { userId } = await TeamMember.findOneByRole({
      role: Team.Role.admin,
    const { manuscripts } = await getManuscriptsUseCase
      .initialize({ models })
      .execute({ input: {}, userId })

    const submissionIds = => m.submissionId)

    const draftManuscript = manuscripts.find(
      m => m.status === Manuscript.Statuses.draft,

const dbCleaner = async options => {
  await db.raw('DROP SCHEMA public CASCADE;')
  await db.raw('CREATE SCHEMA public;')
  await db.raw('GRANT ALL ON SCHEMA public TO public;')
  await migrate(options)'Dropped all tables and ran all migrations')

  return true

Thanks for writing this up in such detail, Sebastian! The combo of a custom jest environment and @pubsweet/db-manager's migrate is what we use in pubsweet-server's tests too and has been working fine for a long time.

Let me know if you need anything else :slight_smile: