Implementing Transactional Tests with MySQL

Mitya — Tue, 22 Jul 2025 20:41:40 +0000

I want to share about my library for writing tests within transactions when working with MySQL.

I enjoy writing tests for my code, but I dislike creating mocks and all the necessary boilerplate around them. This is especially true for databases - while mocking calls to external services and message queues isn't too difficult, databases present a much greater challenge. Interaction with databases is typically quite 'rich', which leads to having to write numerous fragile and tedious mocks/stubs. Moreover, the actual database queries themselves remain untested (and this is where errors often lurk, related to incorrect queries or schema migration mistakes).

The solution here is to use a real database with test data that we populate before running all tests or a specific test. But this raises the question: how do we restore the database state after each specific test runs?

There are several approaches to solving this problem:

Restart the database container before each test to reset the state
Use TRUNCATE
Write custom code to load and clean/restore data after each test

The first approach is too cumbersome for running tests in parallel without conflicts, as it would require launching multiple containers simultaneously. The second approach is faster than the first, but TRUNCATE clears all data in the table, creating problems when running parallel tests that work with the same tables - which can lead to conflicts. Additionally, if FOREIGN KEY constraints are used, they would need to be enabled/disabled before calling TRUNCATE. And we mustn't forget about manually restoring test data before running tests. The last approach requires writing a lot of boilerplate code, and if a test fails, we might end up with 'dirty' data in the database.

But there's another approach: wrapping tests in transactions and rolling back the transaction after each test completes. This approach isn't exactly new - it's used in other languages and platforms like Ruby on Rails, Laravel, and Spring - but it's rarely implemented in Node.js projects. In projects I work on, MySQL with various ORMs/query builders is commonly used, but I didn't want to write transactional test implementations for each library, and in some cases it's impossible due to their API specifics. The solution was found by recognizing that most ORMs and query builders use one of two packages, mysql or mysql2, as clients. Therefore, it would be sufficient to patch these drivers to add support for transactional tests for all libraries using them.

Realization

Installing the library and the necessary driver for ORM/Query builder:

npm i mysql-transactional-tests -S;

# installing one of the two drivers
npm i mysql -S;
npm i mysql2 -S;

Then we'll write a simple test that verifies adding a new employee to the employee table.

/** 
 Must be imported earlier, 
 the module that creates a database connection (mysql driver)
**/
import { startTransaction, unPatch } from 'mysql-transactional-tests/mysql';
/** 
 Must be imported earlier, 
 the module that creates a database connection (mysql2 driver)
**/
// import { startTransaction, unPatch } from 'mysql-transactional-tests/mysql2';

import MySQLClient from '../client/mysql_client';
const mysqlConfig = require('../mysql.config.json');

const dbName = mysqlConfig.database;
const mysqlClient = new MySQLClient(mysqlConfig);

describe('[mysql]: transactional test', () => {
  let rollback: () => Promise<void>;

  beforeEach(async () => {
    // Creating a separate transaction for each test
    ({ rollback } = await startTransaction());
  });

  afterEach(async () => {
    // We rollback the transaction after passing each test
    await rollback();
  });

  afterAll(async () => {
    // We close the connection and return the driver to its original state
    mysqlClient.close();
    unPatch();
  });

  it('create employee', async () => {
    await mysqlClient.query(
      `INSERT INTO ${dbName}.employee SET first_name='John', last_name='Brown', age=35, sex='man', income=23405`,
    );
    const result = await mysqlClient.query(`SELECT * FROM ${dbName}.employee`);
    expect(result).toHaveLength(4);
  });
});

Depending on the type of driver used, you need to import the corresponding module — either mysql-transactional-tests/mysql or mysql-transactional-tests/mysql2 — and you must do this before importing the module that creates the database connection. This is necessary so that the library has the opportunity to patch the driver before the connection is established.

The use of transactions in the code under test is supported:

it('create employee with transaction', async () => {
  const trx = await mysqlClient.beginTransaction();
  await trx.query(
    `INSERT INTO ${dbName}.employee SET first_name='John', last_name='Brown', age=35, sex='man', income=23405`,
  );
  await trx.commit();
  const result = await mysqlClient.query(`SELECT * FROM ${dbName}.employee`);
  expect(result).toHaveLength(4);
});

List of supported libraries:

Knex
Kysely
Sequelize
TypeORM
MikroORM
and other clients that use mysql or mysql2 driver

Examples of tests with different ORMs/Query builder.

How it works

For this approach to work, the test is wrapped in a transaction. The resulting SQL code for testing employee addition to the employee table would look like:

BEGIN;
  INSERT INTO employee SET first_name='John', last_name='Brown', age=35, sex='man', income=23405;
  SELECT * FROM employee;
ROLLBACK;

If the code under test contains transactions, they will be converted to savepoints. The BEGIN/START TRANSACTION statements will be replaced with SAVEPOINT, COMMIT with RELEASE SAVEPOINT, and ROLLBACK with ROLLBACK TO SAVEPOINT. This approach emulates nested transactions.

BEGIN;
  SAVEPOINT sp_1;
  INSERT INTO employee SET first_name='John', last_name='Brown', age=35, sex='man', income=23405;
  RELEASE SAVEPOINT sp_1;
  SELECT * FROM employee;
ROLLBACK;

Limitations

A relatively rare case, but it's worth knowing about. You may encounter an error when testing code that runs multiple transactions, the completion order of which is non-deterministic:

it('insert: two parallel transcations: one commit, one rollback', async () => {
  const [ trx1, trx2 ] = await Promise.all([
    mysqlClient.beginTransaction(),
    mysqlClient.beginTransaction(),
  ]);

  await trx1.query(
    `INSERT INTO ${dbName}.employee SET first_name='John', last_name='Brown', age=35, sex='man', income=23405`,
  );
  await trx2.query(
    `INSERT INTO ${dbName}.employee SET first_name='Matthew', last_name='Black', age=45, sex='woman', income=11000`,
  );

  // ❌ An error may happens if commit occurs before rollback.
  await Promise.all([
    trx1.commit,
    trx2.rollback,
  ]);
});

This happens because transactions are converted into savepoints, and all SQL queries execute within a single global transaction.

BEGIN;

  SAVEPOINT sp_1;
  INSERT INTO employee SET first_name='John', last_name='Brown', age=35, sex='man', income=23405;
  SAVEPOINT sp_2;
  INSERT INTO employee SET first_name='Matthew', last_name='Black', age=45, sex='woman', income=11000;

  -- ✅
  RELEASE SAVEPOINT sp_2;
  RELEASE SAVEPOINT sp_1;

  -- ❌
  RELEASE SAVEPOINT sp_1;
  -- Not found savepoint sp_2
  RELEASE SAVEPOINT sp_2;

ROLLBACK;

Based on the above, to avoid issues, transactions within the code under test should be initiated and finalized according to the LIFO principle (last in, first out):

it('insert: two parallel transcations, one commit, one rollback', async () => {
  // start two parallel transaction
  const trx1 = await mysqlClient.beginTransaction();
  const trx2 = await mysqlClient.beginTransaction();

  await trx1.query(
    `INSERT INTO ${dbName}.employee SET first_name='John', last_name='Brown', age=35, sex='man', income=23405`,
  );
  await trx2.query(
    `INSERT INTO ${dbName}.employee SET first_name='Matthew', last_name='Black', age=45, sex='woman', income=11000`,
  );

  // ✅
  await trx2.rollback();
  await trx1.commit();

  // ❌
  await trx1.commit();
  await trx2.rollback();

  const result = await mysqlClient.query(`SELECT * FROM ${dbName}.employee`);
  expect(result).toHaveLength(4);

  const notFound = await mysqlClient.query(`SELECT * FROM ${dbName}.employee WHERE first_name='Matthew' LIMIT 1`);
  expect(notFound).toHaveLength(0);
});

Another obvious point is that this module won't work with Prisma or other ORM/query builders that don't utilize either the mysql or mysql2 package as their MySQL driver.

Summary

Transactional tests provide the following capabilities:

use a real database (MySQL) in tests without writing mocks for database interaction code
no need to manually restore database state after tests, since transaction rollback at test completion automatically reverts data
tests interacting with the same database tables can run in parallel

If you find this solution useful, I'd appreciate a GitHub star! 😊

DEV Community: Mitya

Implementing Transactional Tests with MySQL

Realization

How it works

Limitations

Summary