Node-postgres: 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 [email protected]
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
https://github.com/brianc/node-postgres/issues/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 .

[jedwards1211]

@cressie176 An in memory driver is essentially a mock db layer. It's a real db I guess, but fulfills the same role a mock db layer would.

But the problem with in memory dbs is when you're using features like JSON fields that your production db (e.g. Postgres) supports but available in-memory databases (like SQLite) don't. Sequelize is sort of a leaky abstraction in that regard. You don't have to think about SQL, but you have to know about what features your database supports.

I think what you're really saying is the purest approach would be if it were possible to use ORM models without a connection to any database. I'm already running integration tests on a real database anyway, it's just more of a hassle than if I could do some sort of mocking.

[cressie176]

An in memory driver is essentially a mock db layer. It's a real db I guess, but fulfills the same role a mock db layer would.

There's a subtle but important difference. With a mock db layer you are testing interactions, e.g. that db.query("SOME SQL", [ a, b, c ]) was invoked with the expected parameters. With an in-memory database you are testing side effects, e.g. After an insert, there is 1 more row than before. Testing interactions tend to be far more brittle, and far less expressive than testing side effects.

But the problem with in memory dbs is when you're using features like JSON fields that your production db (e.g. Postgres) supports but available in-memory databases (like SQLite) don't. Sequelize is sort of a leaky abstraction in that regard. You don't have to think about SQL, but you have to know about what features your database supports.

I agree, you can only get so far with by replacing the driver with an ORM. How far depends on the level of support the ORM provides. GORM had excellent testing support for example.

I think what you're really saying is the purest approach would be if it were possible to use ORM models without a connection to any database. I'm already running integration tests on a real database anyway, it's just more of a hassle than if I could do some sort of mocking.

Not really. My prefered approach is to avoid ORMs, and to encapsulate the persistence code in one or more stores which execute hand written SQL. I create my own in-memory/array backed versions of the stores and ensure parity by running a shared test suite against them. It's a fair amount of work though.

[jedwards1211]

@cressie176 I see. Well what I meant by mocking the db layer when using an ORM was really more along the lines of testing side effects than testing interactions.

I create my own in-memory/array backed versions of the stores and ensure parity by running a shared test suite against them. It's a fair amount of work though.

How do you test the execution of your hand written SQL then?

[cressie176]

How do you test the execution of your hand written SQL then?

Something along the following lines...

  describe('Delete Namespace', () => {

    it('should delete namespace', async () => {
      const namespace = makeNamespace();
      const saved = await store.saveNamespace(namespace);
      await store.deleteNamespace(saved.id);

      const retrieved = await store.getNamespace(saved.id);
      expect(retrieved).toBe(undefined);
    });
  });

[MikaKorpela]

I tried the example on above, but for some reason the stub was never injected to the real code, and the call was going always to the real database. What I might be missing?

[MikaKorpela]

The example from @benoittgt.