How do you mock MySQL (without an ORM) in Node.js?

Question

I'm using Node.js with felixge's node-mysql client. I am not using an ORM.

I'm testing with Vows and want to be able to mock my database, possibly using Sinon. Since I don't really have a DAL per se (aside from node-mysql), I'm not really sure how to go about this. My models are mostly simple CRUD with a lot of getters.

Any ideas on how to accomplish this?

Allegorize

Can you give me more details? For example paste some code and show perhaps a pseudocode of what you'd like to achieve? I would like to help. – Goldie

@Goldie I honestly don't really even know where to begin. Really Ideally I'd like to see someone else's model class and their associated mocks/tests. – Allegorize

Anybody using Jest with the same problem? – Epigone

Answer 1

With sinon, you can put a mock or stub around an entire module. For example, suppose the mysql module has a function query:

var mock;

mock = sinon.mock(require('mysql'))
mock.expects('query').with(queryString, queryParams).yields(null, rows);

queryString, queryParams are the input you expect. rows is the output you expect.

When your class under test now require mysql and calls the query method, it will be intercepted and verified by sinon.

In your test expectation section you should have:

mock.verify()

and in your teardown you should restore mysql back to normal functionality:

mock.restore()

Chromophore

.with() appears to have been deprecated in favor of .withArgs(). I'm testing with sinon 1.7.2 – Ballyhoo

Sinon works very well. Thank you. (no more hitting a real database for unit testing!) :) – Mindymine

Answer 2

It may be a good idea to abstract away your database into its own class which uses mysql. Then you can pass that class' instance to your model's constructors instead of them loading it using require().

With this set up you can pass a mock db instance to your models inside your unit test files.

Here's a small example:

// db.js
var Db = function() {
   this.driver = require('mysql');
};
Db.prototype.query = function(sql, callback) {
   this.driver... callback (err, results);
}
module.exports = Db;

// someModel.js
var SomeModel = function (params) {
   this.db = params.db
}
SomeModel.prototype.getSomeTable (params) {
   var sql = ....
   this.db.query (sql, function ( err, res ) {...}
}
module.exports = SomeModel;

// in app.js
var db = new (require('./db.js'))();
var someModel = new SomeModel ({db:db});
var otherModel = new OtherModel ({db:db})

// in app.test.js
var db = {
   query: function (sql, callback) { ... callback ({...}) }
}
var someModel = new SomeModel ({db:db});

Kwei

Answer 3

I'm not entirely familiar with node.js, but in a traditional programming sense, to achieve testing like that, you'd need to abstract away from the data access method. Couldn't you create a DAL class like:

var DataContainer = function () {
}

DataContainer.prototype.getAllBooks = function() {
    // call mysql api select methods and return results...
}

Now in the context of a test, patch your getAllBooks class during initialization like:

DataContainer.prototype.getAllBooks = function() {
    // Here is where you'd return your mock data in whatever format is expected.
    return [];
}

When the test code is called, getAllBooks will be replaced with a version that returns mock data instead of actually calling mysql. Again, this is a rough overview as I'm not entirely familiar with node.js

Radiator

Answer 4

I ended up starting with @kgilpin's answer and ended up with something like this to test Mysql in an AWS Lambda:

const sinon = require('sinon');
const LambdaTester = require('lambda-tester');
const myLambdaHandler = require( '../../lambdas/myLambda' ).handler;
const mockMysql = sinon.mock(require('mysql'));
const chai = require('chai');
const expect = chai.expect;

describe('Database Write Requests', function() {

 beforeEach(() => {
   mockMysql.expects('createConnection').returns({
     connect: () => {
       console.log('Succesfully connected');
     },
     query: (query, vars, callback) => {
       callback(null, succesfulDbInsert);
     },
     end: () => {
       console.log('Connection ended');
     }
   });

 });
 after(() => {
   mockMysql.restore();
 });

 describe( 'A call to write to the Database with correct schema', function() {

   it( 'results in a write success', function() {

     return LambdaTester(myLambdaHandler)
       .event(anObject)
       .expectResult((result) => {
         expect(result).to.equal(succesfulDbInsert);
       });
   });
 });


 describe( 'database errors', function() {

   before(() => {
     mockMysql.expects('createConnection').returns({
       connect: () => {
         console.log('Succesfully connected');
       },
       query: (query, vars, callback) => {
         callback('Database error!', null);
       },
       end: () => {
         console.log('Connection ended');
       }
     });
   });

   after(() => {
     mockMysql.restore();
   });

   it( 'results in a callback error response', function() {


     return LambdaTester(myLambdaHandler)
       .event(anObject)
       .expectError((err) => {
         expect(err.message).to.equal('Something went wrong');
       });
   });
 });
});

I didn't want any actual database connections so I manually mocked all the mysql responses.
By added another function to .returns you can mock any method off of createConnection.

Ambrosine

Good job - however - you could take one more step: you mocked well the static createConnection api, but you return a manually made connection instance, and lost the power of sinon. I would save a reference to the original createConnection, stub it, program it with stub#callsFake, and in the fake I would produce a connection and stub it like shown in kgilpin's example. – Handel

mm. I see it's not clear enough. I just convinced myself to write a full answer... – Handel

Answer 5

You can mock out external dependencies using horaa

And I also believe felixge's node sandboxed-module can also do something similar.

So using kgilpin's same context, in horaa it would look something like:

var mock = horaa('mysql');
mock.hijack('query', function(queryString, queryParam) {
    // do your fake db query (e.g., return fake expected data)
});

//SUT calls and asserts

mock.restore('query');

Belabor

sinon.js appears to be the defacto these days – Belabor

Answer 6

Since using the mysql driver requires you first to create a connection, and use apis of the returned connection controller - you need a two step approach.

There are two ways to do that.

stubbing the createConnection, and have it return a stubbed connection

During setup:

const sinon = require('sinon');
const mysql = require('mysql');
const {createConnection} = mysql;
let mockConnection;
sinon.stub(mysql, 'createConnection').callsFake((...args) => {
    mockConnection = sinon.stub(createConnection.apply(mysql, args))
      .expects('query').withArgs(.... )//program it how you like :)
    return mockConnection;
})

const mockConnectionFactory = 
  sinon.stub(mysql)
  .expects('createConnection')

During Teardown:

mysql.createConnection.restore();

Note that here the query method is mocked on an instance, and has no implication on the underlying mecahnism, so only the createConnection must be restored.

stubbing the .query method on the connection prototype

This technique is a little more tricky, because the mysql driver does not expose officially it's connection for import. (well you could just import just the module implementing the connection, but there's no guarantee that any refactoring won't move it from there). So in order to obtain a reference to the prototype - I usually create a connection and traverse up the constructor-prototype chain:

I usually do it in one line, but I'll break it down to steps and explain it here:

During setup:

const realConnection = mysql.createConnection({})
const mockTarget = realConnection.constructor.prototype;
//Then - brutally
consdt mock = sinon.mock(mockTarget).expect('query'....
//OR - as I prefer the surgical manner
sinon.stub(mockTarget, 'query').expect('query'....

During Teardown

//brutal
mock.restore()
// - OR - surgical:
mockTarget.query.restore()

Note that we don't mock the createConnection method here. All the connection-parameter validations will still happen (which I want them to happen. I aspire to work with maximum authentic parts - hence mock the absolute minimum required to get a fast test). However - the query is mocked on the prototype, and must be restored.

Also note that if you work surgically, the verify will be on the mocked method, not on the mockTarget.

Here's a good resource about it: http://devdocs.io/sinon~6-stubs/

Handel