Building, seeding and destroying PostgreSQL with Sequelize for testing

Question

I am trying to automate my database being built, seeded and destroyed for each test. I am using PostgreSQL, Mocha and Sequelize.

I found a library: sequelize-fixtures that has got me part way there, but ultimately it's very inconsistent and occasionally will throw constraint errors: Unhandled rejection SequelizeUniqueConstraintError: Validation error even though I do not have any validation on the model.

Here's how I am doing the tests

const sequelize = new Sequelize('test_db', 'db', null, {
  logging: false,
  host: 'localhost',
  port: '5432',
  dialect: 'postgres',
  protocol: 'postgres'
})

describe('/auth/whoami', () => {
  beforeEach((done) => {
    Fixtures.loadFile('test/fixtures/data.json', models)
      .then(function(){
         done()
      })
  })

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

  it('should connect to the DB', (done) => {
    sequelize.authenticate()
      .then((err) => {
        expect(err).toBe(undefined)
        done()
      })
  })

  it('should test getting a user', (done) => {
    models.User.findAll({
      attributes: ['username'],
    }).then((users) => {
      users.forEach((user) => {
        console.log(user.password)
      })
      done()
    })
  })
})

My model is defined like so:

var Sequelize = require('sequelize'),
    db = require('./../utils/db')

var User = db.define('User', {
  username: {
    type: Sequelize.STRING(20),
    allowNull: false,
    notEmpty: true
  },
  password: {
    type: Sequelize.STRING(60),
    allowNull: false,
    notEmpty: true
  }
})

module.exports = User

The error logs:

Fixtures: reading file test/fixtures/data.json...
Executing (default): CREATE TABLE IF NOT EXISTS "Users" ("id"   SERIAL , "username" VARCHAR(20) NOT NULL, "password" VARCHAR(60) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT "id", "username", "password", "createdAt", "updatedAt" FROM "Users" AS "User" WHERE "User"."id" = 1 AND "User"."username" = 'Test User 1' AND "User"."password" = 'testpassword';
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'Users' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "Users" ("id","username","password","createdAt","updatedAt") VALUES (1,'Test User 1','testpassword','2016-04-29 23:15:08.828 +00:00','2016-04-29 23:15:08.828 +00:00') RETURNING *;
Unhandled rejection SequelizeUniqueConstraintError: Validation error

This worked once, then never again. Is there a more robust way for me to, before every test, start with a completely clean DB for me to fill with test data to operate on?

This is the closest I have come to finding any kind of discussion/answer.

Additionally, if anyone also knows why I still get console.logs() even though I have logging: false on, that would be appreciated.

How is your model defined? logging false means Sequelize will not print the SQL to console. In your case, you should enable log to get all the information about your problem you can. — Denis C de Azevedo, Apr 29 '16 at 23:00
@denisazevedo I have updated my post with my model definition and the logs. I tried to disable logging but for some reason it still produces the logs so I am still seeing them. — James Coop, Apr 29 '16 at 23:15

Josh Rickert · Accepted Answer · 2016-04-30T00:07:18.170

The error you pasted seems to indicate that the same data is being inserted multiple times, causing a conflict on the id column.

I would expect calling sequelize.sync({force: true}) would clear out all the database tables for you on each run, but that doesn't seem to be the case. You could try moving your call to a beforeEach hook, to make sure that that first test to run has a fresh database as well.

On an application I'm working on, we don't resync the database for every test, instead doing it once at the beginning and truncating tables between tests. We use a cleanup function that looks like this:

function cleanup() {
    return User.destroy({ truncate: true, cascade: true });
}

A create method does the work of loading data from json fixtures and inserting them into the database.

function create() {
    var users = require('./fixtures/user.json');
    return User.bulkCreate(users);
}

You might be able to simplify your dependencies and improve stability by omitting sequelize-fixtures and handling things yourself.

Also, an unrelated suggestion: Sequelize's methods return promises that Mocha can handle natively, so no need to use the done callback in your tests and setup/teardown code:

 it('should connect to the DB', () => {
   return sequelize.authenticate()
 })

The test will fail if the promise is rejected.

Also, Mocha's docs currently recommend against the use of arrow functions:

Passing arrow functions to Mocha is discouraged. Their lexical binding of the this value makes them unable to access the Mocha context, and statements like this.timeout(1000); will not work inside an arrow function.

Building, seeding and destroying PostgreSQL with Sequelize for testing

1 Answers1