Mock mysql connection with Jest

Question

Trying to test code that looks like this :

const mysql = require('mysql2/promise');

async myFunction () {

    const db = await mysql.createConnection(options);

    const results = await db.execute('SELECT `something` from `table`;');

    await db.end();
    
    // more code ...

}

I need to mock the the mysql connection in a way that will allow me to use whatever it returns to mock a call to the execute function.

I have tried mocking the whole mysql2/promise module but of course that did not work, since the mocked createConnection was not returning anything that could make a call to the execute function. I also tried only mocking these 3 functions that I need instead of mocking the whole module, something like:

jest.mock('mysql2/promise', () => ({
    createConnection: jest.fn(() => ({
        execute: jest.fn(),
        end: jest.fn(),
    })),
}));

But that did not work too. Any suggestions are highly appreciated.

Answer 1

I would approach this differently. When you feel you need to mock entire third-party libraries for testing, something is off in your application.

As a general best practice, you should always wrap third-party libraries. Check out this discussion for starters.

Basically the idea is to define your own interfaces to the desired functionality, then implement these interfaces using the third-party library. In the rest of your code, you would only work against the interfaces, not against the third-party implementation.

This has a couple of advantages

1. You can define the interfaces yourself. It will normally be much smaller than the entire third-party library, as you rarely use all functionality of that third-party library, and you can decide what's the best interface definition for your concrete use cases, rather than having to follow exactly what some library author dictates you.
2. If one day you decide you don't want to use MySQL anymore but move to Mongo, you can just write a Mongo implementation of your DB interface.
3. In your case, most importantly: You can easily create a mock implementation of your DB interface without having to start mocking the entire third-party API.

So how could this work?

First, define an interface as it would be most useful in your code. Perhaps, a DB interface for you could look like this:

interface Database<T> {
  create(object: T): Promise<void>;
  get(id: string): Promise<T>;
  getAll(): Promise<T[]>;
  update(id: string, object: T): Promise<void>;
  delete(id: string): Promise<void>;
}

Now, you can develop your entire code base against this one Database interface. Instead of writing MySQL queries all across your code, when you need to retrieve data from 'table', you can use your Database implementation.

I'll just take an example ResultRetriever here that is pretty primitive, but serves the purpose:

class ResultRetriever {
  
    constructor(private database: Database<Something>) {}

    getResults(): Promise<Something[]> {
        return this.database.getAll();
    }
  
}

As you can see, your code does not need to care about which DB implementation delivers the data. Also, we inverted dependencies here: ResultReteriver is injected its Database instance. It does not know which conrete Database implementation it gets. It doesn't need to. All it cares about is that it is a valid one.

You can now easily implement a MySQL Database class:

class MySqlDatabase<T> implements Database<T> {

  create(object: T): Promise<void> {...}

  get(id: string): Promise<T> {...}

  getAll(): Promise<T[]> {
      const db = await mysql.createConnection(options);
      const results = await db.execute('SELECT `something` from `table`;');
      await db.end();
      return results;
  }

  update(id: string, object: T): Promise<void> {...}

  delete(id: string): Promise<void> {...}

}

Now we've fully abstracted the MySQL-specific implementation from your main code base. When it comes to testing, you can write a simple MockDatabase:

export class MockDatabase<T> implements Database<T> {

  private objects: T[] = [];

  async create(object: T): Promise<void> {
    this.objects.push(object);
  }

  async get(id: string): Promise<T> {
    return this.objects.find(o => o.id === id);
  }

  async getAll(): Promise<T[]> {
    return this.objects;
  }

  update(id: string, object: T): Promise<void> {...}

  delete(id: string): Promise<void> {...}
  
}

When it comes to testing, you can now test your ResultRetriever using your MockDatabase instead of relying on the MySQL library and therefore on mocking it entirely:

describe('ResultRetriever', () => {

    let retriever: ResultRetriever;
    let db: Database;

    beforeEach(() => {
      db = new MockDatabase();
      retriever = new ResultRetriever(db);
    });

    ...

});

---

I am sorry if I went a bit beyond the scope of the question, but I felt just responding how to mock the MySQL library was not going to solve the underlying architectural issue.

If you are not using/don't want to use TypeScript, the same logics can be applied to JavaScript.

---