How to Mock postgresql (pg) in node.js using jest

Question

I am new in node.js. I am writing code in node.js for postgresql using pg and pg-native for serverless app. I need to write unit test for it. I am unable to mock pg client using jest or sinon.

My actual code is like this

const { Client } = require('pg');
export const getAlerts = async (event, context) => {

  const client = new Client({
    user: process.env.DB_USER,
    host: process.env.DB_HOST,
    database: process.env.DB_DATABASE,
    password: process.env.DB_PASSWORD,
    port: process.env.PORT
  });

  await client.connect();

  try {
    const result = await client.query(`SELECT * FROM public.alerts;`);
    console.log(result.rows);
    client.end();
    return success({ message: `${result.rowCount} item(s) returned`, data: result.rows, status: true });

  } catch (e) {
    console.error(e.stack);
    client.end();
    return failure({ message: e, status: false });
  }

};

How to mock pg client here?

I use sinon. What part of `Client` do you want to mock? Generally it's something like `sinon.replace(pg.Client.prototype, 'query', sinon.fake.resolves({rows:[]});` — 404, Dec 31 '19 at 09:23
I want to mock ```client.connect()``` and ```client.query``` — Bilal Shah, Jan 01 '20 at 10:03

score 24 · Accepted Answer · answered Dec 31 '19 at 15:17

Here is the unit test solution using jestjs:

index.js:

const { Client } = require('pg');
const { success, failure } = require('./handler');

export const getAlerts = async (event, context) => {
  const client = new Client({
    user: process.env.DB_USER,
    host: process.env.DB_HOST,
    database: process.env.DB_DATABASE,
    password: process.env.DB_PASSWORD,
    port: process.env.PORT,
  });

  await client.connect();

  try {
    const result = await client.query(`SELECT * FROM public.alerts;`);
    console.log(result.rows);
    client.end();
    return success({ message: `${result.rowCount} item(s) returned`, data: result.rows, status: true });
  } catch (e) {
    console.error(e.stack);
    client.end();
    return failure({ message: e, status: false });
  }
};

hander.js:

export function success(data) {}
export function failure(data) {}

index.spec.js:

import { getAlerts } from './';
import { Client } from 'pg';
import { success, failure } from './handler';

jest.mock('pg', () => {
  const mClient = {
    connect: jest.fn(),
    query: jest.fn(),
    end: jest.fn(),
  };
  return { Client: jest.fn(() => mClient) };
});

jest.mock('./handler.js', () => {
  return {
    success: jest.fn(),
    failure: jest.fn(),
  };
});

describe('59540432', () => {
  let client;
  beforeEach(() => {
    client = new Client();
  });
  afterEach(() => {
    jest.clearAllMocks();
  });
  it('should success', async () => {
    client.query.mockResolvedValueOnce({ rows: [], rowCount: 0 });
    await getAlerts();
    expect(client.connect).toBeCalledTimes(1);
    expect(client.query).toBeCalledWith('SELECT * FROM public.alerts;');
    expect(client.end).toBeCalledTimes(1);
    expect(success).toBeCalledWith({ message: '0 item(s) returned', data: [], status: true });
  });

  it('should failure', async () => {
    const mError = new Error('dead lock');
    client.query.mockRejectedValueOnce(mError);
    await getAlerts();
    expect(client.connect).toBeCalledTimes(1);
    expect(client.query).toBeCalledWith('SELECT * FROM public.alerts;');
    expect(client.end).toBeCalledTimes(1);
    expect(failure).toBeCalledWith({ message: mError, status: false });
  });
});

Unit test result with 100% coverage:

 PASS  src/stackoverflow/59540432/index.spec.js (11.792s)
  59540432
    ✓ should success (16ms)
    ✓ should failure (5ms)

  console.log src/stackoverflow/59540432/index.js:3131
    []

  console.error src/stackoverflow/59540432/index.js:3155
    Error: dead lock
        at /Users/ldu020/workspace/github.com/mrdulin/jest-codelab/src/stackoverflow/59540432/index.spec.js:39:20
        at step (/Users/ldu020/workspace/github.com/mrdulin/jest-codelab/src/stackoverflow/59540432/index.spec.js:33:23)
        at Object.next (/Users/ldu020/workspace/github.com/mrdulin/jest-codelab/src/stackoverflow/59540432/index.spec.js:14:53)
        at /Users/ldu020/workspace/github.com/mrdulin/jest-codelab/src/stackoverflow/59540432/index.spec.js:8:71
        at new Promise (<anonymous>)
        at Object.<anonymous>.__awaiter (/Users/ldu020/workspace/github.com/mrdulin/jest-codelab/src/stackoverflow/59540432/index.spec.js:4:12)
        at Object.<anonymous> (/Users/ldu020/workspace/github.com/mrdulin/jest-codelab/src/stackoverflow/59540432/index.spec.js:38:24)
        at Object.asyncJestTest (/Users/ldu020/workspace/github.com/mrdulin/jest-codelab/node_modules/jest-jasmine2/build/jasmineAsyncInstall.js:102:37)
        at resolve (/Users/ldu020/workspace/github.com/mrdulin/jest-codelab/node_modules/jest-jasmine2/build/queueRunner.js:43:12)
        at new Promise (<anonymous>)
        at mapper (/Users/ldu020/workspace/github.com/mrdulin/jest-codelab/node_modules/jest-jasmine2/build/queueRunner.js:26:19)
        at promise.then (/Users/ldu020/workspace/github.com/mrdulin/jest-codelab/node_modules/jest-jasmine2/build/queueRunner.js:73:41)
        at process._tickCallback (internal/process/next_tick.js:68:7)

----------|----------|----------|----------|----------|-------------------|
File      |  % Stmts | % Branch |  % Funcs |  % Lines | Uncovered Line #s |
----------|----------|----------|----------|----------|-------------------|
All files |      100 |      100 |      100 |      100 |                   |
 index.js |      100 |      100 |      100 |      100 |                   |
----------|----------|----------|----------|----------|-------------------|
Test Suites: 1 passed, 1 total
Tests:       2 passed, 2 total
Snapshots:   0 total
Time:        14.109s

Source code: https://github.com/mrdulin/jest-codelab/tree/master/src/stackoverflow/59540432

to be honest I've somehow got a different solution, but this is interesting and cool. I am also mocking out the console until I replace it with a decent logging library — MrMesees, Aug 03 '20 at 12:27
Excellent answer. Especially how to mock behavior of query() in each test. From jest mock documentation I could not understand how to do it. — Michael S, May 07 '22 at 21:12

Olivier · Answer 2 · 2021-01-16T15:52:35.317

10

Its an old question, but here is a new answer:

You can have a look at pg-mem, a library I released recently which emulates an in-memory postgres instance.

It supports most of the usual SQL queries (but will fail on less frequent syntaxes - file an issue if you encounter such a situation).

I wrote an article about it here

For your use case (how to use it with pg), see the this wiki section

edited Jan 16 '21 at 15:52

answered Oct 29 '20 at 18:08

Olivier

5,578
2
31
46

score 5 · Answer 3 · answered Sep 27 '21 at 08:37

This is my set up

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

// setup for to mock pg
jest.mock('pg', () => {
  const mPool = {
    connect: function () {
      return { query: jest.fn() };
    },
    query: jest.fn(),
    end: jest.fn(),
    on: jest.fn(),
  };
  return { Pool: jest.fn(() => mPool) };
});

describe('test for handle relay action', () => {
  let pool;
  // before each test case
  beforeEach(() => {
    pool = new Pool();
  });
  // clean up after each test case done
  afterEach(() => {
    jest.clearAllMocks();
  });

  test('should test', async () => {
    \* implement your test here *\
    
    pool.query.mockResolvedValue({ rows: [] });
    expect(pool.query).toBeCalledTimes(3);
    expect(pool.query).toHaveBeenCalledWith("check some texts");
    expect(pool.query).toHaveBeenCalledWith(expect.anything());
 });

Thanks, man, thank you very much indeed – Olavo André Parno Apr 20 '22 at 15:55 — Olavo André Parno, Apr 20 '22 at 15:55

score 0 · Answer 4 · answered Aug 03 '20 at 12:32

I do not advise anyone uses this, and there may be some errors, but it's getting me 100% unit test coverage for-now...

It covers the Pool API with callbacks instead of the Client API everyone seems to have focused on.

jest.mock('pg')
const { Pool } = require('pg')
const mockPgPoolOn = jest.fn((_event, callback) => { callback() })
const mockPgPoolConnectClientClose = jest.fn(() => {})
const mockPgPoolConnectQueryDoneFn = jest.fn(() => {
  mockPgPoolConnectClientClose()
})
let mockPgPoolConnectErr = null
let mockPgPoolConnectQueryErr = null
let mockPgPoolConnectQueryResult = null
const mockPgPoolConnectQuery = jest.fn((_text, _params, callback) => {
  callback(
    mockPgPoolConnectQueryErr,
    mockPgPoolConnectQueryResult
  )
})
const mockPgPoolConnectClient = jest.fn(() => {
  return {
    query: mockPgPoolConnectQuery,
    on: mockPgPoolOn,
    close: mockPgPoolConnectClientClose
  }
})
const mockPgPoolConnect = jest.fn((callback) => {
  callback(
    mockPgPoolConnectErr,
    mockPgPoolConnectClient(),
    mockPgPoolConnectQueryDoneFn
  )
})
let mockPgPoolQueryErr = null
let mockPgPoolQueryResult = null
const mockPgPoolQuery = jest.fn((_text, _params, callback) => {
  callback(mockPgPoolQueryErr, mockPgPoolQueryResult)
})
Pool.mockImplementation((_config) => {
  return {
    connect: mockPgPoolConnect,
    query: mockPgPoolQuery,
    on: mockPgPoolOn
  }
})

const db = require('./index') // My module that uses require('pg')

I also have handlers to clean-up as this is all before I start defining anything...

afterEach(() => {
  mockPgPoolQuery.mockClear()
  mockPgPoolConnect.mockClear()
  // ...

  mockPgPoolQueryErr = null
  mockPgPoolConnectErr = null
  mockPgPoolConnectQueryErr = null
  mockPgPoolConnectQueryResult = null
  mockPgPoolQueryResult = null
  // ...
})

It seems like a lot of work and like I've said, although it gives me the coverage that 1:1 matches observed usage, it seems like a lot.

I'm using the callback API as it's what was documented by the pg dependency... Not loving it so far.

How to Mock postgresql (pg) in node.js using jest

4 Answers4

Linked