jest.mock() will mock a module with an auto-mocked version, factory and options are optional.

You can use mockFn.mockReturnThis() to mock the method chaining calls.

Besides, if you initialize Knex inside the module scope, you need to require the module after mock is set up.

E.g.

index.js:

import Knex from 'knex';

const knex = Knex({ client: 'mysql' });

export function main() {
  const query = knex('table_name')
    .where({
      title: 'xxx-yyy',
      lang: 'eng',
    })
    .select()
    .orderBy('date', 'desc')
    .toSQL()
    .toNative();
}

index.test.js:

import Knex from 'knex';

jest.mock('knex');

describe('68717941', () => {
  test('should pass', () => {
    const querybuilder = {
      where: jest.fn().mockReturnThis(),
      select: jest.fn().mockReturnThis(),
      orderBy: jest.fn().mockReturnThis(),
      toSQL: jest.fn().mockReturnThis(),
      toNative: jest.fn(),
    };
    const mKnex = jest.fn().mockReturnValue(querybuilder);
    Knex.mockReturnValue(mKnex);
    const { main } = require('./');
    main();
    expect(Knex).toBeCalledWith({ client: 'mysql' });
    expect(mKnex).toBeCalledWith('table_name');
    expect(querybuilder.where).toBeCalledWith({ title: 'xxx-yyy', lang: 'eng' });
    expect(querybuilder.select).toBeCalledTimes(1);
    expect(querybuilder.orderBy).toBeCalledWith('date', 'desc');
    expect(querybuilder.toSQL).toBeCalledTimes(1);
    expect(querybuilder.toNative).toBeCalledTimes(1);
  });
});

test result:

 PASS  examples/68717941/index.test.js (8.844 s)
  68717941
    ✓ should pass (7464 ms)

----------|---------|----------|---------|---------|-------------------
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:       1 passed, 1 total
Snapshots:   0 total
Time:        9.439 s

Checkout a small lib that I've written knex-mock-client, which allows you to write a unit tests that uses DB without real DB :].

The benefit of this method, is that you don't need to know all Knex's internal (which may change overtime) and it allows you control what data your "DB" should return for a specific query.

An example of usage:

// 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 { getTracker, MockClient } from 'knex-mock-client';
import faker from 'faker';

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

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

  beforeAll(() => {
    tracker = getTracker();
  });

  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]);
  });
});