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.

Solution 1

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

Solution 2

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.

Solution 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
    })

Comments

[jomaora]

did you find any solution to this? I'm working with Knex and i'm having the same issue. Thanks

[WhyAyala]

After fumbling with sinon and proxyquire, this worked for me. Thanks.

[Richard]

I'm having trouble following. Could you add an example for using this mock?

[Lajos Arpad]

Can you explain your solution in the answer?