How do I use Sinon to stub interaction with a database using mssql library?

Question

Using the node-mssql library to pull data from SQL. I've been using Sinon for a while now (written about 200 tests with it); having a ton of trouble getting my head around how to stub this library out. The code looks like:

var sql = require('mssql');
var conn = new sql.Connection(sqlConfig); // sqlConfig is connection info, defined elsewhere 

conn.connect(function(err) {
  var req, selectFromTable;
  if (err != null) {
    // handle error
  }
  req = new sql.Request(conn);
  selectFromTable = "select * from DW." + table + " where DWCreatedDate >= '" + start + "' and DWCreatedDate <= '" + end + "' ";
  logger.debug("Selecting with: ", selectFromTable);
  req.input('statement', sql.NVarChar, selectFromTable);
  return req.execute('sp_executesql', function(err, results, returnValue, affected) {
    if (err != null) {
      // etc.
    } else {
      // data processing   
    }
  });
});

Code works fine. Now I'm trying to write a test for it. I knew this library would be difficult to test so I procrastinated. My closest code:

var conn, reqExecute, sqlReqStub; 
sqlReqStub = sinon.stub(); 
sqlReqStub.execute = sinon.stub(); 
sinon.stub(sql, 'Request').returns(sqlReqStub); 
conn = sinon.stub(); 
sinon.stub(sql, 'Connection').returns(conn);

conn.connect = sinon.stub().callsArgWith(0, null);

reqExecute = sqlReqStub.execute.withArgs('sp_executesql').onFirstCall().callsArgWith(1, null, {
  a: 1
});

Your natural inclination might be to say "well, use createStubInstance" but when I use that I get back connection objects (new sql.Connection(config)) that have TediousRequest (what the library defaults to when it builds out the driver object inside of the connection) in them instead of my stub request. I can't find TediousRequest anywhere in the sql object to stub it out.

I'm stuck here; hoping someone has some code around that does this or can explain what I'm doing wrong.

Answer 1

Well, I did manage to solve it but it feels a little hacky for some reason. Possibly because I never figured out how to stub the new sql.Request(conn) call.

Ideally I'd like to get this working without having to include the sql library in my module.exports. I can do that with the request library but after a few hours knocking away at this library I'm unable to get it working in the same way.

First: export the sql library:

sql = require('mssql')
// much code
module.exports.sqlLib = sql

Second: stub things:

  var connection, sqlReqStub, sqlStubLib;
  var server = require('./index.js')    
  sqlStubLib = {};    
  connection = new EventEmitter();    
  connection.connected = true;    
  connection.close = function() {};    
  connection.connect = sinon.stub().callsArgWith(0, null);    
  sqlStubLib.Connect = sinon.stub();    
  sqlStubLib.Connection = function() {
    return connection;
  };    
  sqlReqStub = sinon.stub();    
  sqlReqStub.input = function() {};    
  sqlReqStub.execute = sinon.stub();    
  sqlReqStub.execute.withArgs('sp_executesql').onFirstCall().callsArgWith(1, null, [
    [
    // js object 
    ]
  ], null, null);

  sqlStubLib.Request = function() {
    return sqlReqStub;
  };

  server.sqlLib = sqlStubLib;