best way to mock pg (using sinon?) for unit-tests

[ReinsBrain]

I'm using client pooling which (i think) further complicates the matter of trying to mock pg for unit tests. Really, I'd like test that the connection is being called (once) with the correct arguments... then, once the client is made available from the callback, test the query and params being passed.

Anybody have success with this and can offer guidance please? (Thanks in advance)
Rein

[brianc]

Hey Rein -

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!

On Mon, Jun 20, 2016 at 9:31 PM, Rein Petersen notifications@github.com
wrote:

I'm using client pooling which (i think) further complicates the matter of
trying to mock pg for unit tests. Really, I'd like test that the connection
is being called (once) with the correct arguments... then, once the client
is made available from the callback, test the query and params being passed.

Anybody have success with this and can offer guidance please? (Thanks in
advance)
Rein

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#1056, or mute the thread
https://github.com/notifications/unsubscribe/AADDoeU9amkPAS3olB1Smpt17RjCWLucks5qN00egaJpZM4I6TrI
.

[lukas-gitl]

I'd also be very interested in mocking capabilities.

We are currently using nock for everything else:
https://github.com/node-nock/nock

[erikkrietsch]

@brianc I'm having a hard time connecting the dots. Can you elaborate how pg-pool can help make mocking easier?

[ReinsBrain]

@erikkrietsch : prior to node-pg-pool added to node-pg, you couldn't get a reference to the client object so that you could mock the query function call. Now, you can instance the pool and using that reference, put a mock to catch calls to pool.query.

[daniyel]

Hi.

Is there some "pseudo" code or simple example, how would pg-pool mock look like? I have updated pg to the latest version 6.1.0 so I could use the benefit of the pool and make my unittesting easier, but I still did not find any example that could give me some insights on how to properly mock pg.

Best regards.

[benoittgt]

Hello

I'm stuck since 3 days on the same issue :

I'm building a AWS lambda that connect to a postgres database. I wanted to mock this database for testing.
In my code_base I have something similar to :

var pg = require('pg');
var client = new pg.Client();

function query_aggregate(callback) {
  client.connect(function (err) {
    if (err) throw err;
    client.query('SELECT $1::text as name', ['brianc'], function (err, result) {
      if (err) callback(err);
      callback(null, result.rows)
      client.end(function (err) {
        if (err) callback(err);
      });
    });
  });
}

I have this simple mocha test :

  describe('query_aggregate', function() {
    it('fail to connect to postgres', function(done){
      proxyquire('../index', {
        Cient: function(host) {
            console.log(host); // print pg://host:3456
            this.connect = function(callback) {
              console.log('Here!'); // never printed
              callback(new Error('Failed to connect to postgres'))
            }
          }
        }
      });
      testingAggregate.query_aggregate(function(err, data){
        expect(err).to.equal('Failed to connect to postgres');
        done();
      });
    });
  });

  1) Testing aggregate function query_aggregate fail to connect to postgres:
     Uncaught AssertionError: expected [Error: connect EHOSTDOWN 168.410.131.63:5439 - Local (0.0.0.0:0)] to equal 'Failed to connect to postgres'

Do you have any idea why I can't mock connect?

Thanks in advance

[lukas-gitl]

I would be very, very interested in a good solution for the exact same use case as well!

[benoittgt]

Hello

Here you go :

// index.js
const pg = require('pg');
const async = require('async');
AWS.config.region = 'eu-west-1';
const table_name = 'myapp';
const host = 'mypath.rds.com:5529';
const conn = 'pg://***:****@' + host + '/' + table_name;
const client = new pg.Client(conn);

// This function will be used in a waterfall with async.js
function query_aggregate(callback) {
  client.connect(function (err) {
    if(err) return callback(err);

    let QUERY = 'SELECT shop_id, COUNT(DISTINCT(user_id)) from connections GROUP BY shop_id';
    client.query(QUERY, function (err, result) {
      if (err) return callback(err);

      callback(null, result.rows);

      client.end(function (err) {
        if (err) return callback('Error in query:', err);
      });
    });
  });
}

if (typeof exports !== 'undefined') {
  exports.query_aggregate = query_aggregate;
}

Test :

const proxyquire = require('proxyquire').noCallThru();
const pgStub = {};
const expect = require('chai').expect;

describe('Testing aggregate function', function () {
  describe('query_aggregate', function() {
    it('fail to connect to postgres', function(done){
      var pgStub = {
        Client: function(host) {
          this.connect = function(callback) {
            return callback('Failed to connect to postgres');
          };
        }
      };
      var testingAggregate = proxyquire('../index', { 'pg': pgStub });

      testingAggregate.query_aggregate(function(err, data){
        expect(err).to.deep.equal('Error: Failed to connect to postgres');
        done();
      });
    });


    it('fail on query and return an error', function(done){
      var pgStub = {
        Client: function(host) {
          this.connect = function(callback) { return callback(null); };
          this.query = function(query, callback) {
            return callback('Failed to query postgres');
          };
        }
      };
      var testingAggregate = proxyquire('../index', { 'pg': pgStub });

      testingAggregate.query_aggregate(function(err, data){
        expect(err).to.deep.equal('Error: Failed to connect to postgres');
        done();
      });
    });

    it('succeed on query and return rows', function(done){
      let resultRows = [{ a:1 },{ b:2 }];
      let pgData = { rows: resultRows };
      var pgStub = {
        Client: function(host) {
          this.connect = function(callback) { callback(); };
          this.query = function(query, callback) {
            expect(query).to.eq('SELECT shop_id, COUNT(DISTINCT(user_id)) from connections GROUP BY shop_id');
            return callback(null, pgData);
          };
        }
      };
      var testingAggregate = proxyquire('../index', { 'pg': pgStub });

      testingAggregate.query_aggregate(function(err, data){
        expect(data).to.deep.equal(resultRows);
        done();
      });
    });
  });
});

I'm not a javascript dev so this code have probably some caveats but it works.

  Testing aggregate function
    query_aggregate
      ✓ fail to connect to postgres
      ✓ fail on query and return an error
      ✓ succeed on query and return rows

  3 passing (27ms)

[lukas-gitl]

@benoittgt Thank you! I was kind of hoping for a general way similar to how nock does it for http requests... Maybe time to write our own library... urg

[cressie176]

How about writing an abstraction above your DB layer with a real implementation and fake implementation. I tend to write a memory-store and a postgres-store and assert that they work correctly using a shared test.

This approach is less brittle than mocking a third party library and you get the benefit of faster tests too.

[charmander]

Yep, do that!

[jedwards1211]

@cressie176 that's a lot less viable when you're using an ORM like Sequelize on top of your database connector layer. There's a lot of functionality to mock in both but I think ultimately mocking the database later would be easier.

[cressie176]

Attempting to mock the db layer when using an ORM doesn't sound like a great idea. The whole point of using an ORM is to abstract away SQL so you don't have to think about it. Instead you would have to discover the SQL generated by the ORM and setup appropriate expectations.

When I've used ORMs in the past, the approach we took was to substitute an in memory driver. It looks like this is a recommended approach with Sequelize too. As one commenter advises you can't then "break out" of the ORM and do something native .