How to Mock a Fake Database for When Unit Testing Against Knex

How to mock knex count with jest

You can use jest.mock(moduleName, factory, options) to mock the db manually.

E.g.
index.js:

const db = require('./db/client');

const createUser = async () => {
  return db('users')
    .count()
    .then((data) => {
      return data[0].count;
    })
    .catch((error) => {
      console.log(error);
      return null;
    });
};

module.exports = createUser;

./db/client.js:

// knex

index.test.js:

const createUser = require('./');
const db = require('./db/client');

jest.mock('./db/client', () => {
  const mKnex = { count: jest.fn() };
  return jest.fn(() => mKnex);
});

describe('60357935', () => {
  it('should count user', async () => {
    const mData = [{ count: 10 }];
    db().count.mockResolvedValueOnce(mData);
    const actual = await createUser();
    expect(actual).toBe(10);
  });

  it('should handle error', async () => {
    const mError = new Error('network');
    db().count.mockRejectedValueOnce(mError);
    const actual = await createUser();
    expect(actual).toBeNull();
  });
});

Unit test results with 100% coverage:

 PASS  stackoverflow/60357935/index.test.js (5.972s)
  60357935
    ✓ should count user (11ms)
    ✓ should handle error (65ms)

  console.log stackoverflow/60357935/index.js:2895
    Error: network
        at /Users/ldu020/workspace/github.com/mrdulin/react-apollo-graphql-starter-kit/stackoverflow/60357935/index.test.js:18:20
        at step (/Users/ldu020/workspace/github.com/mrdulin/react-apollo-graphql-starter-kit/stackoverflow/60357935/index.test.js:33:23)
        at Object.next (/Users/ldu020/workspace/github.com/mrdulin/react-apollo-graphql-starter-kit/stackoverflow/60357935/index.test.js:14:53)
        at /Users/ldu020/workspace/github.com/mrdulin/react-apollo-graphql-starter-kit/stackoverflow/60357935/index.test.js:8:71
        at new Promise (<anonymous>)
        at Object.<anonymous>.__awaiter (/Users/ldu020/workspace/github.com/mrdulin/react-apollo-graphql-starter-kit/stackoverflow/60357935/index.test.js:4:12)
        at Object.<anonymous> (/Users/ldu020/workspace/github.com/mrdulin/react-apollo-graphql-starter-kit/stackoverflow/60357935/index.test.js:17:29)
        at Object.asyncJestTest (/Users/ldu020/workspace/github.com/mrdulin/react-apollo-graphql-starter-kit/node_modules/jest-jasmine2/build/jasmineAsyncInstall.js:100:37)
        at resolve (/Users/ldu020/workspace/github.com/mrdulin/react-apollo-graphql-starter-kit/node_modules/jest-jasmine2/build/queueRunner.js:43:12)
        at new Promise (<anonymous>)
        at mapper (/Users/ldu020/workspace/github.com/mrdulin/react-apollo-graphql-starter-kit/node_modules/jest-jasmine2/build/queueRunner.js:26:19)
        at promise.then (/Users/ldu020/workspace/github.com/mrdulin/react-apollo-graphql-starter-kit/node_modules/jest-jasmine2/build/queueRunner.js:73:41)
        at process._tickCallback (internal/process/next_tick.js:68:7)

----------|---------|----------|---------|---------|-------------------
File      | % Stmts | % Branch | % Funcs | % Lines | Uncovered Line #s 
----------|---------|----------|---------|---------|-------------------
All files |     100 |      100 |     100 |     100 |                   
 index.js |     100 |      100 |     100 |     100 |                   
----------|---------|----------|---------|---------|-------------------
Test Suites: 1 passed, 1 total
Tests:       2 passed, 2 total
Snapshots:   0 total
Time:        7.484s

Unit testing with Bookshelf.js and knex.js

I have been using in-memory Sqlite3 databases for automated testing with great success. My tests take 10 to 15 minutes to run against MySQL, but only 30 seconds or so with an in-memory sqlite3 database. Use :memory: for your connection string to utilize this technique.

A note about unit tesing - This is not true unit testing, since we're still running a query against a database. This is technically integration testing, however it runs within a reasonable time period and if you have a query-heavy application (like mine) then this technique is going to prove more effective at catching bugs than unit testing anyway.

Gotchas - Knex/Bookshelf initializes the connection at the start of the application, which means that you keep the context between tests. I would recommend writing a schema create/destroy script so that you and build and destroy the tables for each test. Also, Sqlite3 is less sensitive about foreign key constraints than MySQL or PostgreSQL, so make sure you run your app against one of those every now and then to ensure that your constraints will work properly.

mocking database type error - knex-mock-client

finally fixed that, able to mock the database to test the database module

describe('insert log test', () => {
    let tracker: any;
    const mockDataConn = knex(dummyDbConn);
    tracker = getTracker();
  
    it('should add new log record', async () => {
      const insertId = faker.datatype.number();
      tracker.on.insert('DATA_SYNC_LOG').response([insertId]);
      
      const syncQuantity = 1;
      const syncDate = new Date();
      await insertLog(mockDataConn, syncQuantity, syncDate);
  
      const insertHistory = tracker.history.insert;
  
      //number of record wrote
      expect(insertHistory).toHaveLength(1);

      //SQL method insert/create/update/delete
      expect(insertHistory[0].method).toEqual('insert');
      
      //compare record inserted
      expect(insertHistory[0].bindings).toEqual([ syncDate, syncQuantity]);
    });

    tracker.reset();
  });

Mocking Postgres for unit tests with Sinon.js in Node.js

Use pg-pool: https://www.npmjs.com/package/pg-pool

It's about to be added to pg anyway and purportedly makes (mocking) unit-testing easier... from BrianC ( https://github.com/brianc/node-postgres/issues/1056#issuecomment-227325045 ):

Checkout https://github.com/brianc/node-pg-pool - it's going to be the pool implementation in node-postgres very soon and doesn't rely on singletons which makes mocking much easier. Hopefully that helps!

Unit testing a function that depends on database

The issue is ensuring that your code consistently uses the same database connection. Then you can set it once to whatever is appropriate for the current environment.

Rather than passing the database connection around from method to method, it might make more sense to make it a singleton.

def already_exists(story_data):
    # Here `connection` is a singleton which returns the database connection.
    connection.execute("""SELECT COUNT(*) from posts where post_id = ?""", (story_data.post_id,))
    (number_of_rows,) = connection.fetchone()
    if number_of_rows > 0:
        return True
    return False

Or make connection a method on each class and turn already_exists into a method. It should probably be a method regardless.

def already_exists(self):
    # Here the connection is associated with the object.
    self.connection.execute("""SELECT COUNT(*) from posts where post_id = ?""", (self.post_id,))
    (number_of_rows,) = self.connection.fetchone()
    if number_of_rows > 0:
        return True
    return False

But really you shouldn't be rolling this code yourself. Instead you should use an ORM such as SQLAlchemy which takes care of basic queries and connection management like this for you. It has a single connection, the "session".

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from sqlalchemy_declarative import Address, Base, Person

engine = create_engine('sqlite:///sqlalchemy_example.db')
Base.metadata.bind = engine

DBSession = sessionmaker(bind=engine)
session = DBSession()

Then you use that to make queries. For example, it has an exists method.

session.query(Post.id).filter(q.exists()).scalar()

Using an ORM will greatly simplify your code. Here's a short tutorial for the basics, and a longer and more complete tutorial.

---

Related Topics