Photo by Testalize on Unsplash

An Alternative to Integration Testing With Jest and Sequelize Transactions

Richard Miles
Dec 3, 2020 · 4 min read

An integration test is one that is written to cover multiple modules working as a group. For the most part this would mean testing that our code works as expected when making real queries to a database (e.g not mocking database connections and function calls — in the case of unit tests).

The problem

The usual approach I have taken in the past for integration testing is to create a test database that I migrate and seed during the lifecycle of each running test. This is probably fine for the first couple of tests, but becomes exponetially slower to run as the number of database migrations and seeds needed grows.

A shortcut that I have taken in the past is to have an initial seed that works for all tests. Although this does speed up the time it takes to run the tests, it fails to isolate each test result from the rest of the test data, which means more often then not, you will constantly be updating tests and seed data to get new tests to run as expected. The worst part about all of this is that is almost impossible to actually know what the state of your database is going to be when a particular test is run, especially for tests that are run later in the test lifecycle.

Before we talk about the solution, we should probably talk a bit about transactions in sequelize.

Database transactions

If you didn’t know, a transaction is basically a way in which we can defer writing to our database permintantly, but still visualise the effects any changes to the database would have, without actually commiting them. There are a couple of good reasons why we would want to use transactions for pretty much everything database query related, but the main one being that we can group individual queries together, so that if one of the queries fail - all of them are rolled back. This is super useful when the effects of different queries rely on each other e.g:

const someFunction = async () => {
const transaction = await sequelize.transaction();
try {
await sequelize.query("INSERT INTO Persons (FirstName,LastName) VALUES ('John','Smith')", { transaction });
const result = await sequelize.query("SELECT * FROM Persons", { transaction })
await transaction.commit();
return result
} catch (error) {
await transaction.rollback();
throw new Error(error)

In the above example we are adding a new Person to the Persons table and then querying all of the records in Persons. The statement in this case relies on the success of the and visa versa, and so if anything fails, then everything is rolled back, and nothing is actually added to the database.

The proposal

In light of the fact that we can still get a snapshot of our database even if the changes haven’t been commited perminently, we see there is an oppertunity to test the results of our queries without actually commiting them perminantly to the database and then rolling them back after every test.

What we would need for this is a seed test database that includes everything we need to run our tests, as well as a way to make sure we always rollback our commits.

Mocking the commit function

One way we can do this is by mocking our transaction’s commit function as follows:

// someModule.test.js
const { sequelize } = require('./db')
jest.spyOn(sequelize, 'transaction');beforeEach(() => {
describe('db connection', () => {
test('test db connection', async () => {
const transaction = await sequelize.transaction();
await sequelize.query("INSERT INTO Persons (FirstName,LastName) VALUES ('Robert','Hope')", { transaction });
transaction.commit = transaction.connection.rollback
sequelize.transaction.mockImplementation(() => Promise.resolve(transaction));
console.log(await someFunction())

Let’s break this down a bit:

  • Firstly we are importing our sequelize connection instance
  • Next we want to spy on the transaction method of the sequelize instance (this is a way to help partially mock specific methods)
  • Then we want to make sure our mocks are reset after every test is run, mainly because we want a new mock transaction for each test
  • Within the test itself we want to create an actual transaction (line 12)
  • We can use this transaction to make any additional database calls within our test (line 13) (which will also be rolled back), before the function being tested is run. This will all conviently be added to the same single transction
  • We then want to reassign the commit method on this transaction to be the connection.rollback method (this is just the usual rollback method)
  • Finally we want to create a mock of our transaction method in our tested function which instead uses the transaction we have just created (with the reassigned commit method).
  • Now everytime we call the commit method within our tested function it will rollback the results, while we can still visualise the queries effects with in the database.


Using this process means that we can test the actual integrated effects of our queries on the database, without changing it in anyway for every single test.

This not only speeds up the time it takes to run our integration tests, but I believe could be a viable solution to replace traditional database integration tests all together.

The Startup

Get smarter at building your thing. Join The Startup’s +724K followers.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store