Truncate data during tests with knex

test.js

'use strict';

var Promise   = require('bluebird');
var knex      = require('../../src/db').knex;

var tables = [
  'organizations',
  'campaigns',
  'donors',
  'pledges',
  'payments'
];

function truncate () {
  return Promise.each(tables, function (table) {
    return knex.raw('truncate table ' + table + ' cascade');
  });
};

function seed () {
  return Promise.each(tables, function (table) {
    return knex(table).insert(require('./seeds/' + table));
  });
};

describe('Integration Tests', function () {

  beforeEach(function () {
    return truncate().then(seed);
  });

  afterEach(function () {
    return truncate();
  });

  require('require-all')(__dirname + '/specs');

});

Thanks for sharing your integration test code!

It is also recommended to update the test database to the latest schema before running any test:

const config = require(`${process.cwd()}/knexfile`)[process.env.NODE_ENV]
const knex = require('knex')(config)

const {MyPOJO} = require('../src/MyPOJO')
const {Model} = require('objection')

beforeAll(async done => {
  await knex.migrate.latest()
  await knex(MyPOJO.tableName).truncate()
  Model.knex(knex)
  done()
})

My solution, truncates all tables in schemas

// ./truncateSchemas.js

export async function truncateSchemas(
  knexClient,
  schemas
) {
  if (schemas.length < 1) {
    throw new Error(`Expected schemas be non-emtpy array, but got ${schemas}`)
  }

  await knexClient.raw(
    `
      DO
      $func$
      BEGIN
        EXECUTE
        (
          SELECT 'TRUNCATE TABLE ' || string_agg(format('%I.%I', table_schema, table_name), ', ') || ' RESTART IDENTITY CASCADE'
          FROM information_schema.tables
          WHERE table_schema IN (${schemas.map(x => `'${x}'`).join(', ')})
          AND table_type = 'BASE TABLE'
        );
      END
      $func$;
    `
  )
}

// jest.config.js
module.exports = {
  transform: {
    '^.+\\.jsx?$': 'babel-jest',
  },
  setupTestFrameworkScriptFile: '<rootDir>/jest/index.js',
  testPathIgnorePatterns:       ['/node_modules/'],
}

// jest/index.js

import knex from 'knex'
import { truncateSchemas } from './truncateSchemas'

const knexClient = knex({
  client: 'pg',
  connection: {
    user:     process.env.POSTGRES_USER,
    password: process.env.POSTGRES_PASSWORD,
    host:     process.env.POSTGRES_HOST,
    port:     process.env.POSTGRES_PORT,
    database: process.env.POSTGRES_DB,
  }
})

beforeEach(async () => {
  await truncateSchemas(knexClient, ['app_public', 'app_private', 'app_hidden'])
})