node.js - How to mock the connect, query, and release methods of pool in postgreSql using sinon?

Question

I am having a function, where I am doing some db operations. Like,

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

const pool = new Pool({
  connectionString: `some connection string...`,
});

var fun = async function (pk_name, pk_value) {
  try {
    const db = await pool.connect();
    const query = `SELECT *
                   FROM creds
                   WHERE ${pk_name} = $1`;
    var res = await db.query(query, [pk_value]);
    db.release();
    return res.rows;
  } catch (ex) {
    return [];
  }
};

module.exports.isValidUser = async function (pk_name, pk_value, password) {
  try {
    var userData = await fun(pk_name, pk_value);
    return userData[0].email === pk_value && userData[0].password === password;
  } catch (ex) {
    return false;
  }
};

and I am trying to mock the above methods like pool.connect() ,db.query() and db.release()

so, I tried following things

var sinon = require("sinon");
var assert = sinon.assert;
const { Pool } = require("pg");
const pool = new Pool()
var databaseControllerTestPositive = function () {
  it("valid user check test", async function () {
    sinon.stub(logUtils, "info");
    sinon.stub(pool, 'connect')
    sinon.stub(pool, 'query').returns({
      email: "[email protected]",
      password: "demo"
    })
     sinon.stub(pool.prototype.connect, "release");
    // isValidUser is another function which calls the above fun() internally.
    var result = await dbUtils.isValidUser(  
      "fake_pk_name",
      "fake_pk_value",
      "pass"
    );
    assert.match(result, { rows: [] });
  });
};

But, the above test is failing and the methods which I was trying to mock, is not really getting mocked.

I have found a similar question, but I didn't find it's answer so helpful.

Could anyone please help me, if I am doing anything wrong.

question from:https://stackoverflow.com/questions/65940805/how-to-mock-the-connect-query-and-release-methods-of-pool-in-postgresql-using

Answer 1

You can use Link Seams, so we need a extra package - proxyquire. Since the fun function is not exported, we can't require it and test it directly. We need to test it together with isValidUser function.

E.g.

main.js:

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

const pool = new Pool({
  connectionString: `some connection string...`,
});

var fun = async function (pk_name, pk_value) {
  try {
    const db = await pool.connect();
    const query = `SELECT *
                   FROM creds
                   WHERE ${pk_name} = $1`;
    var res = await db.query(query, [pk_value]);
    db.release();
    return res.rows;
  } catch (ex) {
    return [];
  }
};

module.exports.isValidUser = async function (pk_name, pk_value, password) {
  try {
    var userData = await fun(pk_name, pk_value);
    return userData[0].email === pk_value && userData[0].password === password;
  } catch (ex) {
    return false;
  }
};

main.test.js:

const proxyquire = require('proxyquire');
const sinon = require('sinon');
var assert = sinon.assert;

describe('65940805', () => {
  it('valid user check test', async function () {
    const res = { rows: [{ email: 'fake_pk_value', password: 'pass' }] };
    const dbStub = {
      query: sinon.stub().resolves(res),
      release: sinon.stub(),
    };
    const poolInstanceStub = {
      connect: sinon.stub().resolves(dbStub),
    };
    const PoolStub = sinon.stub().returns(poolInstanceStub);
    const dbUtils = proxyquire('./main', {
      pg: {
        Pool: PoolStub,
      },
    });

    var result = await dbUtils.isValidUser('fake_pk_name', 'fake_pk_value', 'pass');
    assert.match(result, true);
    sinon.assert.calledWithExactly(PoolStub, {
      connectionString: `some connection string...`,
    });
    sinon.assert.calledOnce(poolInstanceStub.connect);
    sinon.assert.calledWithExactly(dbStub.query, sinon.match.string, ['fake_pk_value']);
    sinon.assert.calledOnce(dbStub.release);
  });
});

unit test result:

  65940805
    √ valid user check test (1013ms)


  1 passing (1s)

----------|---------|----------|---------|---------|-------------------
File      | % Stmts | % Branch | % Funcs | % Lines | Uncovered Line #s
----------|---------|----------|---------|---------|-------------------
All files |   86.67 |      100 |     100 |   86.67 | 
 main.js  |   86.67 |      100 |     100 |   86.67 | 17,26
----------|---------|----------|---------|---------|-------------------