Milkman Matty
02/03/2022

Testing in Electron with TypeScript isn't always as easy as I'd hoped. I'm not ashamed to say that I struggled a bit when trying to set up an sqlite in-memory database to mock a file sqlite database in TypeScript-Electron. But I did manage to get it working.

Getting sqlite itself working in my TypeScript-Electron setup was a whole kettle of fish on its own. For this example however I'm assuming a basic module setup of sqlite3 for the db API and sqlite for await/async. Two issues I ran into were having to add sqlite3 as an external and having to remove the libraryTarget from the webpack.configuration file.

const configuration: webpack.Configuration = {

  // Add ", { sqlite3: 'sqlite3' }" like so:
  externals: [...Object.keys(externals || {}), { sqlite3: 'sqlite3' }],

  //...

  output: {
    path: webpackPaths.srcPath,
    library: {
      type: 'commonjs2',
    },
    // libraryTarget: 'commonjs2', <-- This will break everything
	// remove or comment it out if present
  },

Assuming everything is actually up and running correctly switching out the production db for an in-memory one when testing was the next big hurdle. I had 3 files that did database related things: DBConnection, DBSetup, DBAction. DBAction is contrieved and could be any number of files, but for the sake of simplicity this is only one file now.

DBConnection handles creating a connection to an existing file db, and if it doesn't exist it creates one and the nconnects to it:

import sqlite3 from 'sqlite3';
import { open } from 'sqlite';

const DBConnection = async () => {
  const db = await open({
    filename: `./db/database.db`,
    driver: sqlite3.Database,
  });
  return db;
};

export default DBConnection;

DBSetup handles basic setup and is only run once by the program at startup:

import DBConnection from './DBConnection';

const DatabaseSetup = async () => {
  const db = await DatabaseConnection();
  
  // Check initial tables exist, create them otherwise
  await db.exec(`CREATE TABLE IF NOT EXISTS Test (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    TestVal1 TEXT,
    TestVal2 TEXT
  )`);
  await db.exec(`CREATE TABLE IF NOT EXISTS Test_Foreign (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    ExampleCol TEXT,
    TestID INT,
    FOREIGN KEY(TestID) REFERENCES Test(ID)
  )`);
  // Tables exist with certainty
};

export default DatabaseSetup;

DBAction is any file that does anything to the database, business logic etc:

import DBConnection from './DBConnection';
import TestDTO from './types/TestDTO';

const DBAction = async (idToReturn: number) => {
  const db = await DatabaseConnection();
  await db.run(
    `INSERT INTO Test (TestVal1, TestVal2) VALUES (:t1, :t2)`,
    {
      ':t1': 'Looking for some ' + idToReturn,
      ':t2': 'Happy testing ' + idToReturn,
    }
  );
  
  const existing = await db.get(
    `SELECT * FROM Test WHERE ID = ?`,
    idToReturn
  );
  
  return existing;
}

export default DBAction;

Now that the files have been introduced lets get into how to test these things. Create a new jest test file:

import '@testing-library/jest-dom';

describe('Database', () => {
  it('should connect to in-memory instance when testing', () => {});
  it('should persist data when using in-memory instance', () => {});
});

Those tests are looking mighty empty, so before the mocking is setup I'm going to fill them in with how they should work if they were using the real calls. Note that these will all FAIL:

import '@testing-library/jest-dom';
import DBConnection from '../DBConnection';
import DBSetup from '../DBSetup';
import DBAction from '../DBAction';

describe('Database', () => {
  it('should connect to in-memory instance when testing', async () => {
    // DBSetup calls DBConnection already, so no need to manually call it.
    await DBSetup();
  
    // Double check it got called
    expect(DBConnection).toHaveBeenCalledTimes(1);
  
    // Check if tables got created in DBSetup
    const tables = await (
      await DBConnection()
    ).all(`select name from sqlite_master where type='table'`);
    expect(tables.length).toBeGreaterThanOrEqual(1);
  });
  
  it('should persist data when using in-memory instance', () => {
    await DBSetup();
    const first = await DBAction(1);
    const second = await DBAction(2);
    expect(first.ID).toBe(1);
    expect(second.TestVal1).toBe('Happy testing 2');
  });
});

We have our tests, an now to mock out the database to be in-memory using jest's mock function. Notice that because the entire module is being mocked the second parameter of jest.mock must return an object with a property of __esModule: true and then a property for the default export:

import '@testing-library/jest-dom';
import sqlite3 from 'sqlite3';
import { Database, open } from 'sqlite';
import DBConnection from '../DBConnection';
import DBSetup from '../DBSetup';
import DBAction from '../DBAction';

jest.mock('../DBConnection', () => ({
  __esModule: true,
  default: () => {
    return open({
      filename: ':memory:',
      driver: sqlite3.Database,
    })
  }
}));

describe('Database', () => {
  ...
});

The above mocks out the entire DBConnection file and replaces it with the one that's specified in default:. This means that for every DBConnection() call that goes on the stack that originated from within this file is intercepted by jest and instead the mock is used. This means that the even though DBSetup() calls DBConnection() in its own file it still is going to use the mock that was just created. Pretty neat. Unfortunatley there's still some bugs and those tests still won't pass.

First problem is that our mock function cannot be spied on in its current form. This means that every call to expect(DBConnection).toHaveBeenCalledTimes(*) will fail. To fix this we implement jest.fn:


jest.mock('../DBConnection', () =>
  jest.fn().mockImplementation(() => {
    return open({
      filename: ':memory:',
      driver: sqlite3.Database,
    });
  })
);

By using jest.fn jest can spy on our mock function and those expect calls will pass. Notice that the __esModule and default properties are no longer needed now that jest.fn is being used. With that handled only one last bug remains.

The tests are getting a new instance of the in-memory database for every CALL. This means that there is no persistent data being stored for the life of each test. To fix this a reference to a db object should be kept in the test file for each test to use, this reference can be wiped clean between tests to ensure no test-contamination occurs.

import '@testing-library/jest-dom';
import sqlite3, { Statement } from 'sqlite3'; // Add Statement import
import { Database, open } from 'sqlite';
import DBConnection from '../DBConnection';
import DBSetup from '../DBSetup';
import DBAction from '../DBAction';

// Type saftey is needed here for IDE auto-complete.
let db: Promise<Database<sqlite3.Database, Statement>>;

jest.mock('../DBConnection', () =>
  jest.fn().mockImplementation(() => {
  
    // If a db already exists return that one.
    if (db) {
      return db;
    }
  
    // Otherwise create one and save it.
    db = open({
      filename: ':memory:',
      driver: sqlite3.Database,
    });
  return db;
  })
);

describe('Database', () => {

  // After each test, wipe the variable clean with a fresh new instance.
  afterEach(() => {
    // create a new in-mem db
    db = open({
      filename: ':memory:',
      driver: sqlite3.Database,
    });
  });

  it('should connect to in-memory instance when testing', async () => {
    // DBSetup calls DBConnection already, so no need to manually call it.
    await DBSetup();
  
    // Double check it got called
    expect(DBConnection).toHaveBeenCalledTimes(1);
  
    // Check if tables got created in DBSetup
    const tables = await (
  
      // ALL calls to DBConnection() (with the parentheses) within 
      // this test file must now be changed to "db"
      await db
    ).all(`select name from sqlite_master where type='table'`);
    expect(tables.length).toBeGreaterThanOrEqual(1);
  });
  
  it('should persist data when using in-memory instance', () => {
    await DBSetup();
    const first = await DBAction(1);
    const second = await DBAction(2);
    expect(first.ID).toBe(1);
    expect(second.TestVal1).toBe('Happy testing 2');
  });
});

After creating a variable to hold the instance and returning that on subsequent calls the same instance of an in-memory database can be given to all calls within each test. A fair bit of work involved but very easy to replicate and change to your needs.