A knex setup

db.js

const knex = require('knex')

function createDb({ allowWipe, connectionString, tables }) {
  const client = knex(connectionString)

  const retval = {
    client,
  }

  /**
     * @description - Performs an upsert
     * @param {Object} params - the thing to upsert
     * @returns {Object} - The thing that was upserted
     */
  function upsert(params) {
    const { constraint, object, table } = params
    const insert = client(table).insert(object)
    const update = client.update(object)
    const raw = `${insert} ON CONFLICT (${constraint}) DO ${update} returning *`

    return client.raw(raw).then(res => res.rows[0])
  }

  retval.upsert = upsert

  /**
     * @describes - Destroys and recreates the db. Testing likes clean
     *   environments.  Production does not.  This only gets enabled if
     *   the `allowWipe` setting is true.
    */
  function wipe() {
    return tables.reduce(
      (chain, t) => chain.then(() => client(t).del()),
      Promise.resolve(true),
    )
  }

  // Some things aren't meant for non-test environments.
  if (allowWipe) {
    retval.wipe = wipe
  }

  return retval
}

module.exports = createDb

env.js

const colors = require('colors/safe')
const dotenv = require('dotenv')

const packageJson = require('../../package.json')

const envFromRealEnvironment = process.env.NODE_ENV || 'development'
const path = `.env.${envFromRealEnvironment}`

dotenv.config({ path, silent: envFromRealEnvironment === 'production' })

function requireFromEnv(key) {
  if (!process.env[key]) {
    // eslint-disable-next-line no-console
    console.error(`${colors.red('[APP ERROR] Missing env variable:')} ${key}`)

    return process.exit(1)
  }

  return process.env[key]
}

module.exports = {
  allowWipe: requireFromEnv('ALLOW_WIPE'),
  readModelConnectionString: requireFromEnv('READ_MODEL_CONNECTION_STRING'),
}

example-of-using-the-db.js

const createDb = require('./db.js')

const readModelDb = createDb({
  allowWipe: env.allowWipe,
  connectionString: env.readModelConnectionString,
  tables: [
    // The tables in my db
  ],
})

knexfile.js

const dotenv = require('dotenv')

const envFromRealEnvironment = process.env.NODE_ENV || 'development'
const path = `.env.${envFromRealEnvironment}`

dotenv.config({ path, silent: envFromRealEnvironment === 'production' })

module.exports = process.env.EVENT_STORE_CONNECTION_STRING

migration.js

exports.up = function up(knex) {
  return knex.schema.createTable('events', table => {
    table.increments()
    table.string('type')
    table.string('stream_type')
    table.string('stream_id')
    table.string('correlation_id')
    table.json('payload')
    table.timestamp('timestamp').defaultTo(knex.fn.now())

    table.index('type')
    table.index('stream_type')
    table.index('stream_id')
  })
}

exports.down = knex => knex.schema.dropTable('events')