How can I mock a fake database for when unit testing against Knex?

Question

I've been using Knex successfully to connect to a backend database. But I want to be able to unit test my code. Is there a way to mock the database connection?

I've tried using proxyquire but I can't seem to get it to work.

The problem seems to be with the way Knex is initialized.

var knex = require('knex')({
  client: 'mysql',
  connection: {}
});

I setup knex to be mocked in my unit test.

myService = proxyquire('../app/myService', {
        'knex': knexProxy
});

My service includes knex.

var knex = require('knex').knex,

When my service runs a query, it fails.

var sql = knex("table_name");
sql.insert(rowToInsert, "auto_increment_id");
sql.then(function (insertId) {
    resolve();
}, function (err) {
    reject(err);
});

For some reason I just can't seem to capture the request before it attempts the connection.

I've also, tried to create a custom Knex Client, but that hasn't worked yet either.

Answer 1

I've written this tiny lib called knex-mock-client which does exactly this, it allows you to setup your db "connection" with a mockClient which will track your calls & help you with responses.

For example:

// my-cool-controller.ts

import { db } from '../common/db-setup';

export async function addUser(user: User): Promise<{ id }> {
  const [insertId] = await db.insert(user).into('users');

  return { id: insertId };
}

// my-cool-controller.spec.ts
import { expect } from '@jest/globals';
import knex, { Knex } from 'knex';
import { createTracker, MockClient } from 'knex-mock-client';
import faker from 'faker';
import { db } from '../common/db-setup';


jest.mock('../common/db-setup', () => {
  return {db: knex({client: MockClient})};
});

describe('my-cool-controller tests', () => {
  let tracker: Tracker;

  beforeAll(() => {
    tracker = createTracker(db);
  });

  afterEach(() => {
    tracker.reset();
  });

  it('should add new user', async () => {
    const insertId = faker.datatype.number();
    tracker.on.insert('users').response([insertId]);
    const newUser = { name: 'foo bar', email: 'test@test.com' };
    const data = await addUser(newUser);

    expect(data.id).toEqual(insertId);

    const insertHistory = tracker.history.insert;

    expect(insertHistory).toHaveLength(1);
    expect(insertHistory[0].method).toEqual('insert');
    expect(insertHistory[0].bindings).toEqual([newUser.name, newUser.email]);
  });
});

Answer 2

This works for me, hope it helps someone:

//db.ts
import knex from 'knex';

const db = knex({
  client: 'pg',
  connection: {},
  pool: { min: 0, max: 1 }
});

export default db('someTableName');

---

//myFunction.ts
//somewhere inside a function
const data = await db
    // 👇  Knex query builders are mutable so when re-using them .clone() is necessary.
    .clone()
    .where({ pk: 'someId', sk: 'someId2' })
    .select('data')
    .orderBy('inserted_at', 'desc')
    .first()

---

//myFunction.test.ts
describe("myFunction", () => {
  beforeEach(() => {
    jest.spyOn(db, "clone").mockImplementation(() => db);
    jest.spyOn(db, "select");
    jest.spyOn(db, "where");
    jest.spyOn(db, "orderBy");
  });

  afterEach(() => {
    jest.clearAllMocks();
  });

  it("should work as expected", async () => {
    jest.spyOn(db, "first").mockResolvedValueOnce("desiredReturnValue");

    await myFunction();

    expect(db.where).toHaveBeenCalledWith({
      pk: "someId",
      sk: "someId2",
    });
    expect(db.select).toHaveBeenCalledWith("data");
    expect(db.orderBy).toHaveBeenCalledWith("inserted_at", "desc");
    expect(db.first).toHaveBeenCalledTimes(1);
  });
});

Answer 3

I'm using jest and you can do something like this:

   jest.mock('knex', () => {
        const fn = () => {
            return {
                select: jest.fn().mockReturnThis(),
                from: jest.fn().mockReturnThis(),
                where: jest.fn().mockReturnThis(),
                first: jest.fn().mockReturnThis(),
                insert: jest.fn().mockReturnThis(),
                raw: jest.fn().mockReturnThis(),
                then: jest.fn(function (done) {
                  done(null)
                })
                
            }
        }
        return fn
    })

Answer 4

Using jest:

Create the file /__mocks__/knex.js in your app root:

module.exports = () => ({
  select: jest.fn().mockReturnThis(),
  from: jest.fn().mockReturnThis(),
  where: jest.fn().mockReturnThis(),
  first: jest.fn().mockReturnThis(),
  then: jest.fn(function (done) {
    done(null)
  })
})

Pass the desired return value to done

Answer 5

I used jest to mock knex but I had to define an object that contains the method that I used. not the most elegant solution but is working

let knexMock = () => {
    const fn = () => {
        return {
            returning: function() {
                return {
                    insert: jest.fn().mockImplementation(() => [123123])
                }
            },
            insert: jest.fn()
        }
    }
    fn.raw = jest.fn()
    return fn
}

knex.mockImplementation(knexMock)

Answer 6

I have been using in-memory Sqlite3 databases for automated testing with great success. It's not true unit testing however it does run much faster than MySQL or PostgreSQL. I have posted more details about this solution on a different question.