Mocking Postgres for unit tests with Sinon.js in Node.js

Question

I am having trouble getting my head round how i can use sinon to mock a call to postgres which is required by the module i am testing, or if it is even possible.

I am not trying to test the postgres module itself, just my object to ensure it is working as expected, and that it is calling what it should be calling in this instance.

I guess the issue is the require setup of node, in that my module requires the postgres module to hit the database, but in here I don't want to run an integration test I just want to make sure my code is working in isolation, and not really care what the database is doing, i will leave that to my integration tests.

I have seen some people setting up their functions to have an optional parameter to send the mock/stub/fake to the function, test for its existence and if it is there use it over the required module, but that seems like a smell to me (i am new at node so maybe this isn't).

I would prefer to mock this out, rather then try and hijack the require if that is possible.

some code (please note this is not the real code as i am running with TDD and the function doesn't do anything really, the function names are real)

TEST SETUP

describe('#execute', function () {
it('should return data rows when executing a select', function(){
 //Not sure what to do here
});
});

SAMPLE FUNCTION

PostgresqlProvider.prototype.execute = function (query, cb) {
var self = this;

if (self.connection === "")
    cb(new Error('Connection can not be empty, set Connection using Init function'));

if (query === null)
    cb(new Error('Invalid Query Object - Query Object is Null'))

if (!query.buildCommand)
    cb(new Error("Invalid Query Object"));

//Valid connection and query
};

It might look a bit funny to wrap around the postgres module like this but there are some design as this app will have several "providers" and i want to expose the same API for them all so i can use them interchangeably.

UPDATE

I decided that my test was too complicated, as i was looking to see if the connect call had been made AND then returning data, which smelt to me, so i stripped it back and put it into two tests:

The Mock Test

it('should call pg.connect when a valid Query object is parsed', function(){
        var mockPg = sinon.mock(pg);
        mockPg.expects('connect').once;            

        Provider.init('ConnectionString');
        Provider.execute(stubQueryWithBuildFunc, null, mockPg);

        mockPg.verify();
    });

This works (i think) as without the postgres connector code it fails, with it passes (Boom :))

Issue now is with the second method, which i am going to use a stub (maybe a spy) which is passing 100% when it should fail, so i will pick that up in the morning.

Update 2

I am not 100% happy with the test, mainly because I am not hijacking the client.query method which is the one that hits the database, but simply my execute method and forcing it down a path, but it allows me to see the result and assert against it to test behaviour, but would be open to any suggested improvements.

I am using a spy to catch the method and return null and a faux object with contains rows, like the method would pass back, this test will change as I add more Query behaviour but it gets me over my hurdle.

    it('should return data rows when a valid Query object is parsed', function(){

        var fauxRows = [
            {'id': 1000, 'name':'Some Company A'},
            {'id': 1001, 'name':'Some Company B'}
        ];

       var stubPg = sinon.stub(Provider, 'execute').callsArgWith(1, null, fauxRows);

       Provider.init('ConnectionString');
       Provider.execute(stubQueryWithBuildFunc, function(err, rows){
           rows.should.have.length(2);
       }, stubPg);

       stubPg.called.should.equal.true;
       stubPg.restore();
    });

Answer 1

Use pg-pool: https://www.npmjs.com/package/pg-pool

It's about to be added to pg anyway and purportedly makes (mocking) unit-testing easier... from BrianC ( https://github.com/brianc/node-postgres/issues/1056#issuecomment-227325045 ):

Checkout https://github.com/brianc/node-pg-pool - it's going to be the pool implementation in node-postgres very soon and doesn't rely on singletons which makes mocking much easier. Hopefully that helps!