Docs: How to create a transaction and pass it around through various repo CRUD operations

[dhmlau]

Description / Steps to reproduce / Feature proposal

Cross posting my comments:

Talked to @bajtos @raymondfeng, what we should do regarding transaction support:

• shorter term: explain to users how to open a transaction and pass the transaction token/handles around in options.
• longer term: have a spike on a more elegant solution.

There are different workarounds that our users have posted:

• Support transaction for mysql orm #1599
• mongodb transaction support? loopback-connector-mongodb#489

Loopback 3 provides transaction support at model level.

In LoopBack 4 this should be at the Repository level.

There will be sugar API in a repository to delegate the work to the datasource level.

//obtain a transaction object with  repo.beginTransaction()
let transaction: Transaction =
await customerRepository.beginTransaction({isolationLevel: Post.Transaction.READ_COMMITTED});

// isolationLevel values:
// Transaction.READ_UNCOMMITTED
// Transaction.READ_COMMITTED (default)
// Transaction.REPEATABLE_READ
// Transaction.SERIALIZABLE

//pass the transaction inside the existing options object
await customerRepository.create( customer, { transaction} ) ;

//pass the transaction inside the existing options object
await orderRepository.create( order, { transaction} ); 

// the two repositories above (customer and order) have the same data source, 
// so they can share the transaction.

// finish working with the transaction, either with commit or rollback
await transaction.commit();//  ( OR await transaction.rollback() )

The Transaction type mentioned above should be a wrapper for the typescript type of the transaction object in Juggler.

When using the juggler bridge, we need to make sure that the options we set in the wrapper type are consistent with what juggler is expecting.

Since LoopBack 4 doesn't support a distributed transaction, all repos using the transaction must be from the same data source. It is the job of wrapper Transaction to throw an exception if user attempts to use a transaction against disparate data sources.

DefaultCRUDRepository will implement an additional interface to handle transactions.

Acceptance criteria

• Only covers relational databases (NoSQL databases are out of scope)
• Document relational database transaction support in LoopBack 4
  • Transaction wrapper type
  • How to create a transaction using repo.beginTransaction() and isolation level argument
  • How to pass transaction object into the options object of various repository CRUD methods
  • How to terminate a transaction via commit() or rollback()
• Create mocha test case for 1 SQL relational database (juggler tests have exhaustive transaction tests already)

[gordancso]

loopback4-spring arising from Issue #1599 is a good example of using decorator style to perform transaction.

[HarshalNathe]

Can anybody please give an example of how to use transaction with execute() method. I tried below but not getting expected output.

const transaction = await this.employeeRepository.dataSource.beginTransaction(
  {isolationLevel: IsolationLevel.READ_COMMITTED}
);
try {
  await this.employeeRepository.execute(
    `CREATE USER 'TestUser'@'%' IDENTIFIED BY 'TestP@ssword'`,
    '', {transaction});
  // ... more comands.
  await transaction.commit();
} catch {
  await transaction.rollback();
}

I'm expecting rollback once catch is called. But not able to rollback it.