Creating database, tables and populating for unit testing (mySQL)

[Kevin B]

I'm having a heck of a time trying to figure out how to write unit tests for my projects that rely on mysql. I want to be able to roll back my test database so I can run each test in a fresh environment but every time I try to write a simple string of commands to delete, create, populate the database so it's in a known state, I run into issues. I can't seem to get the database to create the same table after it has recently been deleted. Even if I use Promises to mitigate syncranousness of JS the table just refuses to be recreated.Any ideas on how to best setup for unit testing db code?

[Dave Sag]

here's an example of how I do it.

assume a `dbInit()` function somewhere that initialises the database for your app as per usual and returns a promise. I use sequelize but the same ideas apply to any ORM.

add a test_helper.js file

import chai       from 'chai'

import sinon      from 'sinon' // eslint-disable-line no-unused-vars

import sinonChai  from 'sinon-chai'

import chaiString from 'chai-string'

import faker      from 'faker'

import prepare    from 'mocha-prepare'

import dbInit     from '../../src/utils/db/dbInit'

import models     from '../../src/models'

chai.use(sinonChai)

chai.use(chaiString)

prepare((done) => {

  dbInit().then(() => {

    models.sequelize.sync({ force: true }).then(() => {

      done()

    })

  }, done)

})

invoke your test from `npm run test:db`.

"test:db": "find ./test/db -name '*_spec.js' | NODE_ENV=test xargs mocha --compilers js:babel-core/register --require ./test/db/test_helper.js --bail",

then my `user_spec.js`

import { expect } from 'chai'

import models    from '../../../src/models'

import dummyData from '../../utils/dummyData'

import Factories from '../../utils/factories'

const { User } = models

const { makeUser } = Factories(models)

describe('User', () => {

  afterEach((done) => {

    User.destroy({ where: {} }).then(() => done())

  })

  describe('create', () => {

    describe('given basic valid data', () => {

      const userData = dummyData.userData()

      it('creates a user', (done) => {

        User.create(userData, { include: [{ all: true }] }).then((user) => {

          const userJS = user.get({ plain: true })

          expect(userJS).to.have.property('email')

          expect(userJS.email).to.equal(userData.email.toLowerCase())

          expect(userJS).to.have.property('passwordDigest')

          expect(userJS).to.have.property('keys')

          expect(userJS.keys).to.be.empty

          expect(user.authenticate('password')).to.equal(user)

          done()

        }, done)

      })

    })

    describe('given bad email address', () => {

      const userData = dummyData.userData({ email: 'not an email address' })

      it('fails with a validation error', (done) => {

        User.create(userData).then((ignore) => {

          done(ignore) // will error if ignore is not null

        }, (err) => {

          expect(err).to.exist

          expect(err.message).to.equal('Validation error: Validation isEmail failed')

          done()

        })

      })

    })

  })

  describe('#setMagicToken', () => {

    let user

    beforeEach((done) => {

      makeUser().then((u) => {

        user = u

        done()

      }, done)

    })

    it('can set a new magic token', (done) => {

      expect(user.magicToken).to.be.null

      user.setMagicToken().then((token) => {

        expect(token).to.not.be.null

        expect(token).to.not.be.undefined

        expect(user.magicToken).to.equal(token)

        User.findOne({ where: { magicToken: token } }).then((foundUser) => {

          expect(foundUser).to.not.be.null

          expect(foundUser).to.not.be.undefined

          expect(foundUser).to.have.property('email', user.email)

          done()

        }, done)

      }, done)

    })

  })

})

the `dummyData.js` is just

import faker from 'faker'

const credentials = fields => ({

  email: faker.internet.exampleEmail(),

  password: faker.internet.password(),

  ...fields

})

const userData = fields => ({

  email: faker.internet.exampleEmail(),

  password: 'password',

  keys: [],

  ...fields

})

module.exports = {

  credentials,

  userData

}

and factories.js is just

import { userData } from './dummyData'

const Factories = (models) => {

  const { User } = models

  const makeUser = fields => User.create(userData(fields))

  return {

    makeUser

  }

}

module.exports = Factories

Your database config ought to allow multiple environments, ala

{

  "development": {

    "username": "docker",

    "password": "docker",

    "database": "my_project-development",

    "host": "localhost",

    "dialect": "postgres"

  },

  "test": {

    "username": "docker",

    "password": "docker",

    "database": "my_project-test",

    "host": "localhost",

    "dialect": "postgres"

  },

  "production": {

    "username": "docker",

    "password": "docker",

    "database": "my_project-production",

    "host": "localhost",

    "dialect": "postgres"

  }

}

So when your tests are running you'll not touch your dev db.  When you push to production the production environment settings are normally baked in as ENV settings so they get ignored from here.

During development and test (or if I am firing it up in production mode on my laptop) I run my database within docker rather than on the bare metal.

docker-compose.yml

version: '2.1'

# see https://github.com/sameersbn/docker-postgresql

volumes:

  # We'll define a volume that will store the data from the postgres databases:

  postgres-data:

    driver: local

services:

  # Our PostgreSQL service:

  postgres:

    image: postgres:9.6.2

    ports:

      - 5432:5432

    volumes:

      - postgres-data:/var/lib/postgresql

    healthcheck:

      test: "exit 0"

    environment:

      POSTGRES_USER: docker

      POSTGRES_PASSWORD: docker

put all that together and you have a nice modular system for connecting to the database, then running discreet tests.

It would be nice if there was an equiv of DatabaseCleaner (for Ruby's ActiveRecord and RSpec) for Node and Mocha.

Cheers

Dave