Unit of work with node mssql and typescript

Recently I had the opportunity to rewrite a microservice responsible for writing records to two different databases in a single atomic transaction. The service is written in Typescript and deployed in a lambda using the serverless framework.

With my old .Net hat back, transactions were easily achieved with a transaction scope or the Entity Framework’s Db Context. Yes, there were still some patterns required.

Coming across the mssql node package, it seemed to do the trick for all the simple SQL statements. This code would live in a repository that exposes a few create methods. So let’s say we have this in some business processing layer where two records need to be inserted but should be accomplished with a single database transaction:

import repository from './repository';

export const addUser = async (name, email, password) => {
	await repository.createProfile(name, email);
	await repository.createLogin(email, password);
};

If the createLogin method fails, then the createProfile method should roll back successfully.

Given a simple unit of work pattern, I wanted to simply surround these two methods so it looks like the following:

const unitOfWork = withUnitOfWork(repository);
await repository.createProfile(name, email);
await repository.createLogin(email, password);
await unitOfWork.commit();

That type of syntax would be ideal because it is testable in my opinion. What if multiple repositories needed to be called like this?

import userRepository from './userRepository';
import authRepository from './authRepository';
import withUnitOfWork from './unitOfWork';

export const addUser = async (name, email, password) => {
  const unitOfWork = withUnitOfWork(userRepository, authRepository);
  await userRepository.createProfile(name, email);
  await authRepository.createLogin(email, password);
  unitOfWork.commit();
}

Yes, that would also be a valuable requirement here. Generally a business method would span multiple repositories.

Firstly, using typescript each repository needs to have a beginTransaction method.

export interface Repository {
  beginTransaction: () => Promise<Transaction>;
}

Then, the unit of work should be as simple as:

import { Repository, UnitOfWork } from './types';
import { Transaction } from 'mssql';
import logger from './logger';

const withUnitOfWork = async (...repositories: Repository[]): Promise<UnitOfWork> => {
  const transactions: Transaction[] = await Promise.all(repositories.map(async r => await r.beginTransaction()));

  return {
    commit: async (): Promise<void> => {
      try {
        logger.info(`Commiting transactions [${transactions.length}]`);
        for (const { commit } of transactions) {
          await commit();
        }
      } catch (err) {
        logger.error(`An error occurred in SQL Transaction`, err);
        for (const { rollback } of transactions) {
          rollback();
        }
      }
    }
  };
};

export default withUnitOfWork;

So what’s happening here is very simple. Each repository has a beginTransaction method which will connect to the required database and start a transaction then return it to the unit of work. On commit, each transaction’s commit is called.

Should there be any errors thrown, all the transactions will be rolled back.

Happy coding with transactions in Node.js and Typescript.