How to mock pg Pool with Sinon

Question

In a previous project I mocked the mysql library with Sinon. I did this like so:

X.js:

const con = mysql.createPool(config.mysql);
...

Some other place in the project:

const rows = await con.query(query, inserts);
...

X.test.js:

const sinon = require('sinon');
const mockMysql = sinon.mock(require('mysql'));
...

mockMysql.expects('createPool').returns({
        query: () => {
            // Handles the query...
        },
...

It worked perfectly.

In another project I am trying to mock pg, again with Sinon.

pool.js:

const { Pool } = require('pg');
const config = require('@blabla/config');
const pool = new Pool(config.get('database'));

module.exports = pool;

Some other place in the project:

const con = await pool.connect();
const result = await con.query(...

Y.test.js:

???

I can't understand how to mock connect().query(). None of the following approaches work:

1:

const { Pool } = require('pg');
const config = require('@blabla/config');

const mockPool = sinon.mock(new Pool(config.get('database')));
...
mockPool.expects('connect').returns({
  query: () => {
    console.log('query here');
  },
});

1 results in no error but the real db connection is used.

2:

const { Pool } = sinon.mock(require('pg'));
const config = require('@blabla/config');

const pool = new Pool(config.get('database'));

pool.expects('connect').returns({
  query: () => {
    console.log('query here');
  },
}); 

2 => TypeError: Pool is not a constructor

3:

const { Pool } = sinon.mock(require('pg'));
const config = require('@blabla/config');

const pool = sinon.createStubInstance(Pool);
pool.connect.returns({
  query: () => {
    console.log('query here');
  },
});

3 => TypeError: The constructor should be a function.

Can anybody point me in the right direction with how to mock my PostgreSQL connection?

Answer 1

Example: I have postgres.js like this.

const { Pool } = require('pg');

const handler = {
  count: async (pgQuery) => {
    try {
      const pool = new Pool();
      const res = await pool.query(pgQuery);
      return { count: parseInt(res.rows[0].counter, 10) };
    } catch (error) {
      // Log/Throw error here.
    }
    return false;
  }
}

module.exports = handler;

The spec test I created on postgres.spec.js is like this.

const { expect } = require('chai');
const sinon = require('sinon');
const pgPool = require('pg-pool');
const handler = require('postgres.js');

describe('Postgres', function () {
  it('should have method count that bla bla', async function () {
    // Create stub pgPool query.
    const postgreeStubQuery = sinon.stub(pgPool.prototype, 'query');
    postgreeStubQuery.onFirstCall().throws('XXX');
    postgreeStubQuery.onSecondCall().resolves({
      rows: [{ counter: 11 }],
    });

    // Catch case.
    const catcher = await handler.count('SELECT COUNT()..');
    expect(catcher).to.equal(false);
    expect(postgreeStubQuery.calledOnce).to.equal(true);

    // Correct case.
    const correct = await handler.count('SELECT COUNT()..');
    expect(correct).to.deep.equal({ count: 11 });
    expect(postgreeStubQuery.calledTwice).to.equal(true);

    // Restore stub.
    postgreeStubQuery.restore();
  });
});

To stub pool.query(), you need to stub pg-pool prototype and method query.

Hope this helps.

Answer 2

Since you're needing to mock the returned results of a query, I think the easiest solution would be to abstract your database from the the code needing the query results. Example being, your query results are returning information about a person. Create a person.js module with specific methods for interacting with the database.

Your other code needing the person information from the database won't know or care what type of database you use or how you connect to it, all they care to know is what methods are exposed from person.js when they require it.

//person.js
const { Pool } = require('pg')
// do other database connection things here
const getPersonById = function (id) {
  // use your query here and return the results
}
module.exports = { getPersonById }

Now in your tests, you mock the person module, not the pg module. Imagine if you had 20 some odd tests that all had the mock MySQL pool set up then you changed to pg, you'd have to change all of those, nightmare. But by abstracting your database connection type/setup, it makes testing much easier, because now you just need to stub/mock your person.js module.

const person = require('../person.js') //or whatever relative file path it's in
const sinon = require('sinon')

describe('person.js', function () {
  it('is stubbed right now', function () {
    const personStub = sinon.stub(person)
    personStub.getPersonById.returns('yup')
  
    expect(personStub.getPersonById()).to.eq('yup')
  })
})

Answer 3

Below is a simpler approach that means the system-under-test doesn't need any special tricks.

It is comprised of two parts, though the first is "nice to have":

1. Use a DI framework to inject the pg.Pool. This is a better approach IMO anyway, and fits really well with testing.

2. In the beforeEach() of the tests, configure the DI framework to use a mock class with sinon.stub instances.

   If you aren't using a DI framework, pass the mock as a Pool parameter... but DI is better ;)

The code below is TypeScript using tsyringe, but similar approaches will work fine with plain JavaScript etc.

Somewhere you'll have code that uses pg.Pool. A contrived example:

import { Pool } from 'pg'
...

function getPets(pool: Pool): Promise<Pet[]> {
  return pool.connect()
    .then(db => db.query(SQL_HERE)
      .then(result => {
        db.release()
        return result.rows // or result.rows.map(something) etc
      })
      .catch(error => {
        db.release()
        throw error
      })
   )
}

That works, and it's fine if you want to pass the Pool instance in. I'd prefer not to, so I use tsyringe like this:

import { container } from 'tsyringe'

...

function getPets(): Promise<Pet[]> {
  return container.resolve<Pool>().connect()
    .then(...)
}

Exactly the same outcome, but getPets() is cleaner to call - it can be a pain to lug around a Pool instance.

The main of the program would set up an instance in one of a few ways. Here's mine:

...
container.register(Pool, {
  useFactory: instanceCachingFactory(() => {
    return new Pool(/* any config here */)
  })
})

The beauty of this comes out in tests.

The code above (the "system under test") needs a Pool instance, and that instance needs a connect() method that resolves to a class with query() and release() methods.

This is what I used:

class MockPool {
  client = {
    query: sinon.stub(),
    release: sinon.stub()
  }

  connect () {
    return Promise.resolve(this.client)
  }
}

Here's the setup of a test using MockPool:

describe('proof', () => {
  let mockPool: MockPool

  beforeEach(() => {
    // Important! See:
    // https://github.com/microsoft/tsyringe#clearing-instances
    container.clearInstances()
    mockPool = new MockPool()
    container.registerInstance(Pool, mockPool as unknown as Pool)
  })
})

The cast through unknown to Pool is needed because I'm not implementing the whole Pool API, just what I need.

Here's what a test looks like:

  it('mocks postgres', async () => {
    mockPool.client.query.resolves({
      rows: [
        {name: 'Woof', kind: 'Dog'},
        {name: 'Meow', kind: 'Cat'}
      ]
    })
    const r = await getPets()
    expect(r).to.deep.equal([
        {name: 'Woof', kind: 'Dog'},
        {name: 'Meow', kind: Cat'}
    ])      
  })

You can easily control what data the mock Postgres Pool returns, or throw errors, etc.