Implementing tansactions across Repositories with knex.js

Knex

Knex is a node.js query builder that suppoerts many relational databases. One reason I like this library compared to other ORM is that it is just a simple query builder. It does not do anything you don't need. No too much abstraction, which means it offers a lot of flexibility in terms of the queries you can build.

Repository pattern

Although for small projects, it may be fine to just write queries in your service layer, as your project grows large, there comes a point where you find yourself needing the same query in different services and writing the same query like findSomethingById over and over again in different services. To avoid writing duplicate code, as well as to separate your business logic from database queries, we often use the Repository pattern, which just means you put another abstraction layer between your service layer and the database client library.

Handling transactions - expose knex instance?

One issue I ran into when trying to implement the repository pattern with knex is transactions across different repositories.

With knex, a transaction is represented by a transaction object. So if you want to initiate a transaction in your service layer, the intuitive way to do it would be just get the knex instance and call the transaction method:

// in some service
const knexInstace = await userRepository.getKnex()
knexInstace.transaction(trx => {
  await trx.insert(...)
  await trx.update(...)
// do other transaction stuff
})

However, doing transactions this way misses the whole point of the repository pattern. If you have to write db queries in your service layer, you might as well drop the repository layer altogether.

How to support transactions while hiding knex

Ideally, when using the repository pattern, your service layer should not know which database client you use and should not have any db specific code like the queries above. The best way I thought of to achieve this is to implement your transactions like this:

const result = await this.accountRepository.transaction(async (trx) => {
      const account = await this.accountRepository
        .transacting(trx)
        .findById(id);
      const result1 = await this.accountRepository
        .transacting(trx)
        .update(...);
      const result2 = await this.articleRepository
        .transacting(trx)
        .insert(...);

      return result1 + result2;
    });

This implementation sort of mimics knex's interface for handling transactions. To implement your repositories so they can be used like the example aboce, you need to implement 2 methods for each repository. transaction and transacting.

transaction needs to take a callback as an argument, and just returns the return value of the callback, and the callback is responsible for running the operations you want executed during the transaction.

transacting needs to take a transaction object as an argument, and returns a repository instance which the service layer can use to interact with the database. And every call to this repository instance's methods will be executed within the same transaction transacting took as an argument.

Because the transaction method can be shared beteen repositories, we are going to implement it in an abstract class. We wrap the knex transaction object in BaseTransaction just to prevent the service layer having direct access to the knex transaction. I really wanted to make it a private member of BaseTransaction to completely hide it from the service layer, but as we will have to access the inner knex transaction object when we implement the transacting method, we have to keep it public.

import { KNEX_INSTANCE_TOKEN } from '@/constants';
import { Knex } from 'knex';

export abstract class BaseRepositoryService {
  constructor(protected readonly knex: Knex) {}

  async transaction<T>(callback: (trx: BaseTransaction) => Promise<T>) {
    return await this.knex.transaction(async (knexTrx) => {
      const baseTrx = new BaseTransaction(knexTrx);
      return await callback(baseTrx);
    });
  }

  abstract transacting(trx: BaseTransaction): BaseRepositoryService;
}

export class BaseTransaction {
  constructor(readonly transaction: Knex.Transaction<any, any[]>) {}
}

With the base classes implemented, now we can implement the actual repositories!

import { Const } from '@/constants';
import { Injectable } from '@nestjs/common';
import {
  BaseRepositoryService,
  BaseTransaction,
} from '../base-repository/base-repository.service';

@Injectable()
export class CollectionRepositoryService extends BaseRepositoryService {
  transacting(trx: BaseTransaction): CollectionRepositoryService {
    const collectionRepositoryTransaction = new CollectionRepositoryService(
      trx.transaction,
    );
    return collectionRepositoryTransaction;
  }

  async findByIds(ids: number[]) {
    const collections = await this.knex('collection')
      .select('title_id', 'collection_id')
      .whereIn('collection_id', ids);
    return collections;
  }
}

As shown in the example above, we extend BaseRepositoryService and implement the transacting method the way we defined above.

We are done! Now we can use repositories to start transactions, and perform operations across different repositories within the same transaction! The only imperfection is that the service can still get the knex instance because it is a public member variable of BaseTransaction. In C++, you could use friend classes to make knex completely hidden from the service layer, but unfortunately, TypeScript does not support this feature at this time. So we will have to live with this for now...