How to mock SQL Server connection pool using Jest?

Question

I am trying to write a jest unit test for a function that utilizes mssql.

import * as sql from "mssql";

let pool: sql.ConnectionPool = null;

export async function handler() {
  if (pool === null) {
    try {
      pool = new sql.ConnectionPool("");
      await pool
        .request()
        .input("SomeInput", sql.NVarChar(255), "input")
        .execute("SomeStoredProcedure");
    } catch (err) {
      console.error(err);
    }
  }
}

What would be the simplest way to mock the sql methods and assert they have been called?

import { handler } from "../src/main";

describe("test handler", () => {
  it("should succeed", async () => {
    const requestFn = jest.fn();
    const executeFn = jest.fn();
    const inputFn = jest.fn();
    
    // Mock mssql connection pool with above functions
    // *????????*
    
    await handler();
    
    // Expect the functions have been called
    expect(requestFn).toHaveBeenCalled();
    expect(executeFn).toHaveBeenCalled();
    expect(inputFn).toHaveBeenCalled();
  });
});

Sandbox

score 0 · Answer 1 · answered Jun 03 '21 at 17:42

You need to mock the return value of each function in the chain. You can do this using jest.fn().mockImplementation(implementation)

Expanding you example to use this give us the following

import { handler } from "../src/main";

let pool;

describe("test handler", () => {
  it("should succeed", async () => {
    const requestFn = jest.fn();
    const executeFn = jest.fn();
    const inputFn = jest.fn();
    
    pool = {
      request: requestFn,
      execute: executeFn,
      inputFn: inputFn,
    };

    requestFn.mockImplementation(() => pool);
    executeFn.mockImplementation(() => pool);
    inputFn.mockImplementation(() => pool);

    await handler();
    
    // Expect the functions have been called
    expect(requestFn).toHaveBeenCalled();
    expect(executeFn).toHaveBeenCalled();
    expect(inputFn).toHaveBeenCalled();
  });
});

Thanks for the reply but I don't think this solves the problem. The ConnectionPool is created in the handler `pool = new sql.ConnectionPool("");` ...this pool object you've created in the test seems obsolete — Harry, Jun 04 '21 at 08:49
Then I think you need to mock `sql.ConnectionPool("")` to return the mock version of `pool` — David Bradshaw, Jun 04 '21 at 09:56

johannchopin · Accepted Answer · 2021-06-04T11:18:08.900

0

You can mock the mssql package by using an jest ES6 Class Mocks. You can achive that by using:

const mockExecute = jest.fn();
const mockInput = jest.fn(() => ({ execute: mockExecute }));
const mockRequest = jest.fn(() => ({ input: mockInput }));

jest.mock('mssql', () => ({
  ConnectionPool: jest.fn(() => ({
    request: mockRequest
  })),
  NVarChar: jest.fn()
}));

Have a look at the Stackblitz project and run jest in the terminal. You should see that the tests are passing.

edited Jun 04 '21 at 11:18

answered Jun 04 '21 at 11:03

johannchopin

13,720
10
55
101

@Harry No problem good luck for your tests implementation ;) – johannchopin Jun 04 '21 at 13:37
Do I have to restore the mssql module after the test to get the original implementation back? If so, how to that? I read that it is a bit of a hassle if you mock a complete module with jest. – TheDude Aug 29 '23 at 09:40
@TheDude `jest.mock` is actually used to mock a module for an entire test suite so what would be the use case to get the original implementation back? I recommend you to read [this answer](https://stackoverflow.com/a/56512217/8583669) that explain well some pattern to mock stuffs in jest. – johannchopin Aug 29 '23 at 13:42

How to mock SQL Server connection pool using Jest?

2 Answers2

Linked