Integration Testing with NodeJs, Jest, Knex and SQLite In-Memory Databases

If you're using Jest to write integration tests (that hit the database) for your Node application, you'll quickly notice that it's tricky to isolate your tests if you're using only one test database.

While Jest runs all tests in a single testsuite sequentially, it runs your testsuites in parallel (by default). This makes for faster execution times, but it also means that changes to your database in one testsuite could interfere with another test that's currently running in a different testsuite.

An easy solution to this is to configure a new SQLite in-memory database for each of your testsuites or test-files. This way, a testsuite only makes changes to its own database without affecting the database being used by other running testsuites.

In this tutorial, I'll walk through a simple setup for using in-memory databases in your Jest testsuites and running your database migrations on them before each test using Knex. The code for this tutorial is available here: https://github.com/rukykf/jest-sqlite-tutorial

In your knexfile create a new connection for your test file like so:

// knexfile.js

const path = require("path")

module.exports = {
  ...

  test: {
    client: "sqlite3",
    connection: ":memory:",
    useNullAsDefault: true,
    migrations: {
      directory: path.join(__dirname, "migrations")
    },
    seeds: {
      directory: path.join(__dirname, "seeds")
    }
  },

}

When instantiating Knex, specify that you want to use the test config we just created in our knexfile

For instance, in a file db-config.js we could do this

// db-config.js

const knex = require("knex")
const config = require("./knexfile")

let db = null
if (process.env.NODE_ENV === "test") {
  db = knex(config.test)
} else {
  db = knex(config.development)
}

module.exports = db

By default, Jest will set NODE_ENV to test when you are running your tests.

Then in your integration testsuites, you need to run your migrations on the in-memory database before executing any tests.

// users.test.js and db-config.js are in the same directory
const db = require("./db-config")

beforeAll(async () => {
  // run the migrations and do any other setup here
  await db.migrate.latest()
})

test("select users", async () => {
  let users = await db.from("users").select("name")
  expect(users.length).toEqual(0)
})

That's it. Now each of your testsuites will use a separate SQLite database. You still have to keep in mind that all the tests in a given testsuite will use one database.