How to mock SQL Server connection pool in node

Question

I am attempting to mock SQL Server connection pool so that I can test the function of a DAL.

I have a connection pool file

connectionPool.js

const sql = require('mssql');
const log = require('../services/logger');

const config = {
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  server: process.env.SERVER,
  database: process.env.DATABASE
};

const poolPromise = new sql.ConnectionPool(config)
  .connect()
  .then(pool => {
    log.info('Connected to SQL Server');
    return pool;
  })
  .catch(err => {
    log.error(err, 'Database connection failed');
  });

module.exports = poolPromise;

and I use it in the DAL. Very stripped down, but the essentials are there.

const {poolPromise} = require('./connectionPool');

const getData = async () => {
  const pool = await poolPromise;
  const request = pool.request()
  const result = await request('SELECT * FROM table');
}

This way, the connection pool is only created once per application. (See How can I use a single mssql connection pool across several routes in an Express 4 web application?)

I want to mock the mssql module so that the connection pool function still works. I have tried multiple options. How to mock SQL Server connection pool using Jest? gets me close, but its not quite there.

__mocks/mssql.js

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()
}));

const sql = require('mssql');

module.exports = sql;

However I get the error

TypeError: (intermediate value).connect is not a function

17 |
18 | const poolPromise = new sql.ConnectionPool(config)
19 | .connect()
| ^
20 | .then(pool => {
21 | log.info('Connected to SQL Server');
22 | return pool;

Ian MacRae Ian MacRae · Accepted Answer · 2021-09-30T22:51:35

This may be a solution. A bit of refactoring of connectionPool.js

const sql = require('mssql');
const log = require('../services/logger');

const config = {
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  server: process.env.SERVER,
  database: process.env.DATABASE
};
if (process.env.NODE_ENV === 'development') {
  config.options = {
    encrypt: false,
    trustServerCertificate: true
  };
}

const connectionPool = new sql.ConnectionPool(config);
const poolPromise = connectionPool
  .connect()
  .then(pool => {
    log.info('Connected to MSSQL');
    return pool;
  })
  .catch(err => {
    log.error(err, 'Database connection failed');
  });

module.exports = poolPromise;

Then in /__mocks__/mssql.js

'use strict';

const mockExecute = jest.fn();
const mockInput = jest.fn().mockReturnValue({ execute: mockExecute });
const mockQuery = jest.fn().mockReturnValue({recordset: 'Mock data'});
const mockRequest = jest.fn().mockReturnValue({
  input: mockInput,
  query: mockQuery
});

const mockTransaction = jest.fn().mockImplementation(() => {
  return {
    begin: callback => callback(),
    commit: jest.fn(),
    rollback: jest.fn()
  };
});

const mockConnect = jest.fn().mockImplementation(() => {
  return Promise.resolve({ transaction: mockTransaction });
});

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

const mssql = require('mssql');

module.exports = mssql;

It appears to work, but I am not sure if it is correct

How to mock SQL Server connection pool in node

1 Answers