Support database transactions across multiple services and repositories

[Hajbo]

Clear and concise description of the problem

Currently each repository class holds the database and uses this.db... to access it. This becomes an issue when we want to make an action that spans multiple services or repositories transactional. A good example is inserting a new article:

1. insert article
2. upsert tags
3. insert article - tags

These three should be atomic, and everything should roll back if one of them goes wrong, we don't want partial changes.

Suggested solution

The first step is to remove the database from the repository itself, it should be moved up one level and held by the services. Then each function in the repository will receive the session or a transaction.

Then each function in the transactions that might require to run in a transaction started by a different service should have an optional "transaction" argument. If it's provided then that transaction is passed to the repository, otherwise it passes the session itself.

If we introduce one more layer to manage the services, or prohibit services calling each other and instead a service can use multiple repositories, then it could be implemented easily without any hacky solutions.

This is a minimal example that assumes we don't add a new layer or store multiple repositories in one service, a bit hacky:

export type DatabaseTransaction = Parameters<Parameters<Database["transaction"]>[0]>[0];
class RepositoryOne {

  // This runs in either an implicit transaction using Database
  // or an explicit transaction using DatabaseTransaction that will be
  // committed or rolled back by the caller
  async create(data: any, database: Database | DatabaseTransaction) {
    return await database.insert(articles)....
  }

}

class RepositoryTwo {
  async create(data: any, database: Database | DatabaseTransaction) { 
      return await database.insert(tags)....
  }
}

class ServiceOne {
  constructor(
    private readonly db: Database, 
    private readonly repositoryOne: RepositoryOne,
  ) {}

  async create({data, transaction}: {data: any, transaction?: DatabaseTransaction}) {
    return await this.repositoryOne.create(data, transaction ?? this.db);
  }
}

class ServiceTwo {
  constructor(
    private readonly db: Database, 
    private readonly repositoryTwo: RepositoryTwo,
    private readonly serviceOne: ServiceOne,
  ) {}

  async create(data: any) {
    // Create a transaction to ensure that both create operations are done
    await this.db.transaction(async (tx) => {
        await this.repositoryTwo.create(data, tx);
        return await this.serviceOne.create({data, transaction: tx});
    })
  }
}

Alternative

No response

Additional context

No response

Validations

• Read the Contributing Guide.
• Read the README.md.
• Check that there isn't already an issue that requests the same feature.