Knex + Mocha tests with seeding & clearing db

sql.ts

import * as shelljs from 'shelljs'

import db from 'path-to-knex-client'
import config from 'src/config'

/** 
  Mocha hooks
 */

before(async () => {
  await setupTestDatabase() // setup DB
})

beforeEach(async () => {
  await clearData()
})

/** 
  SQL helpers
 */

/** Recreate test database */
export const setupTestDatabase = async () => {
  if (config.NODE_ENV !== 'test') {
    throw new Error('Running setupTestDatabase but NODE_ENV is not `test`')
  }
  const { user, host, port, dbname } = parseConnectionString(config.DATABASE_URL)
  const sqlcreate = `path_to_sql_create.sql`
  const cmd = `psql --host ${host} --port ${port} -U ${user}`

  shelljs.exec(`echo "DROP DATABASE ${dbname};" | ${cmd}`)
  shelljs.exec(`echo "CREATE DATABASE ${dbname};" | ${cmd}`)
  shelljs.exec(`${cmd} -d ${dbname} < ${sqlcreate}`, { silent: true })
}

/** Load fixtures */
export const loadFixtures = async (fixtures: string[]) => {
  await Promise.all(
    fixtures
      .map(fixt => fixt.trim())
      .map(async fixt => {
        const data = require(`../integration/fixtures/${fixt}.json`)
        const rows = data.map(...)
        return await db(table).insert(rows)
      }),
  )
}


/** Truncate all tables */
export const clearData = async () => {
  const schemas = ['public']
  await db.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$;
    `,
  )
}