This week at work I've started writing some tests for a small application I've been developing over the last few months (late to write tests but better late than ever), and for the last couple days I've been struggling to mock mssql.

The main reason behind my struggle was because I needed the query function to be different per every test, and I was overthinking how simple it actually is, so here is a quick guide on mocking mssql.

Firstly, within your test you want to mock the mssql module. Here's what that may look like:

 + person.service.test.js

const sql = require("mssql");

// NOTE: You must use the name of the mssql wrapped in a string, as you would in require()
jest.mock("mssql");

After that, feel free to give it a "default implementation". Mine looks like the following:

 * person.service.test.js

sql.connect = jest.fn().mockResolvedValue({
  query: jest.fn().mockResolvedValue({
    recordset: [0],
    recordsets: [0, 0],
  }),
  close: jest.fn()
});

Obviously you will have to add in here any methods that your application makes use of.

Now in your tests you can create a connection as you usually would (it doesn't matter if it's valid or not as at this point it's mocked):

 * person.service.test.js

it ("getPersonDetails returns an object", async () => {
  const conn = await sql.connect("");
});

Now that you have a conn object, feel free to edit the query function like so:

 * person.service.test.js
it ("getPersonDetails retunrs an object", async () => {
  const conn = await sql.connect("");
  conn.query - jest.fn().mockResolvedValue({
    // Whatever you want your query to return
  });
});

And that's it! Now you can keep on testing your application that uses mssql.

It's very important that you make sure that query returns what your database would otherwise return, as otherwise you're tests are guaranteed to fail.