Loopback-datasource-juggler: Using v3.12 higher level transactions and "before save" triggers

[jmls]

I'm trying to use the new higher-level transaction api in 3.12.0 and I am confused about operation hooks

the example given in the documentation is this

 await app.dataSources.db.transaction(async models => {
  const {MyModel} = models;
  console.log(await MyModel.count()); // 0
  await MyModel.create({foo: 'bar'});
  console.log(await MyModel.count()); // 1
});
console.log(await app.models.MyModel.count()); // 1

the docs also say that

the models argument to it containing references to all models in this data-source. These model references are different from the ones in app.models in that they are automatically bound to the transaction that was just created.

However, I am not sure about what happens if an "after save" trigger in MyModel references another model (to create an audit log, for example) ? Is the transaction propagated to the auditlog model ?

MyModel.observe('after save' ... 
[snip]
MyModel.app.models.Auditlog.create({ ... <<< how do I reference the `transaction-enabled` Auditlog model ?
}

If not, can't this create a situation where the transaction is undone right at the last moment, after the auditlog record has been created so that MyModel changes are rolled back, but the auditlog change remains ?

Additional information

linux x64 8.1.4
[email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]

[jmls]

bump - anyone ? this issue raises some serious questions and I'm somewhat surprised no-one else seems to have come across it

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been closed due to continued inactivity. Thank you for your understanding. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository.

[JustCodin]

@raymondfeng @bajtos What's the correct way to work with transactions with 'before' and 'after' triggers given the issue mentioned here. A major use case is when working with mysql and the necessity to delete children models (from foreign key constraints) before the parent model, and then audits or finalization in the 'after' trigger.

[bajtos]

I am not familiar with the transaction implementation. From what I vaguely remember, transactions are propagated via options.transaction property. To execute another SQL query from before/after hook and keep it in the same transaction, I would expect that you need to take ctx.options.transactions and pass it in the options object of the data-access call you are making. Since there may be other data passed through options (e.g. currentUser), it's best to forward the entire ctx.options object.

A mock-up to illustrate what I mean:

MyModel.observe('before delete', ctx => {
  return ctx.Model.deleteAll({/*your cascading delete query*/}, ctx.options);
});

[JustCodin]

@bajtos Sincere thanks! I'm using the higher level transaction api, so I'll just pass the bound models instead of the transaction itself.

[itrambovetskyi]

@JustCodin Could you please provide me with an example of how you have done it?

[JustCodin]

@itrambovetskyi Here it is.

Solution

Here's two different ways:

1.) Low level transaction api (Passing transaction object)

const tx = await MyModel.beginTransaction({ isolationLevel: MyModel.Transaction.READ_COMMITTED });

    try{
      //this is from a remote method so I pass the request (req) to have access to accessToken, and these options are passed to all model methods as 'options' params to ensure transaction
      const options = {...req, transaction: tx}; 
      const {MyOtherModel} = MyModel.app.models;

      const my_model_instance = await MyModel.findOne({/*your query*/}, options).then(val => val);

      if(my_model_instance){
        await MyOtherModel.destroyById(someId, options).then(val => val);
      }

      await tx.commit();
    }catch(err){
      await tx.rollback();
      throw err;
    }

and then use the transaction in the hook by passing as option param as we did previously:

MyOtherModel.observe('before delete', ctx => {
   //this has ctx.options.transaction
  return ctx.Model.deleteAll({/*your cascading delete query*/}, ctx.options);
});

2.) High level transaction api (Transaction is bound to models)

    try {
      await MyModel.app.dataSources.mysql.transaction(async models => {
        //you need to reference the main model again because these models are bound to transaction
        const {MyModel, MyOtherModel} = models;

        //this is from a remote method so I pass the request (req) to have access to accessToken, and these options are passed to all model methods as 'options' params to ensure transaction
        const options = {...req, models};

        const my_model = await MyModel.findOne({/*your query*/}).then(val => val);

        if(my_model){
          await MyOtherModel.destroyById(someId, options).then(val => val);
        }

        return my_model;
      });
    } catch (err) {
      next(err);
    }

and then use the transaction bound models in the hook:

MyOtherModel.observe('before delete', ctx => {
   //this has ctx.options.models
  const {[MyOtherModel.definition.name]: MyOtherModelBound} = ctx.options.models;
  return MyOtherModelBound.deleteAll({/*your cascading delete query*/}, ctx.options);
});

Summary:

In the low level transaction api, you need to pass the transaction itself to anything that wants to be part of the transaction. And in the high level transaction api, you need to pass the transaction bound models and use (the methods of) those models on any operations you want included in the transaction.

[dionisisZyg]

@JustCodin In your high level transaction api example your return my_model in your transaction. I tried it and indeed the transaction was successfull but i could not get back the returned value in my custom remote method.

const transactionResult = await app.datasources.mySql.transaction(async models => { ... return result}, {timeout: 30000})

Instead i get undefined. In the link below the doc specifies that the transactions returns a Promise:

http://apidocs.loopback.io/loopback-datasource-juggler/v/3.15.0/#datasource-prototype-transaction

Am i missing something?

Thanks.

[JustCodin]

@dionisisZyg Ya, that seems strange. Would need to debug to figure out what's going on, but here's a quick workaround to unblock you. Adding a 'result' variable outside the transaction scope, and assigning a value to it before the return in the promise gives the desired result.

    let result = null;
    try {
      await MyModel.app.dataSources.mysql.transaction(async models => {
        //you need to reference the main model again because these models are bound to transaction
        const {MyModel, MyOtherModel} = models;

        //this is from a remote method so I pass the request (req) to have access to accessToken, and these options are passed to all model methods as 'options' params to ensure transaction
        const options = {...req, models};

        const my_model = await MyModel.findOne({/*your query*/}).then(val => val);

        if(my_model){
          await MyOtherModel.destroyById(someId, options).then(val => val);
        }

        result = my_model;
        return my_model;
      });
    } catch (err) {
      next(err);
    }

[ewrayjohnson]

@dionisisZyg Ya, that seems strange. Would need to debug to figure out what's going on, but here's a quick workaround to unblock you. Adding a 'result' variable outside the transaction scope, and assigning a value to it before the return in the promise gives the desired result.

    let result = null;
    try {
      await MyModel.app.dataSources.mysql.transaction(async models => {
        //you need to reference the main model again because these models are bound to transaction
        const {MyModel, MyOtherModel} = models;

        //this is from a remote method so I pass the request (req) to have access to accessToken, and these options are passed to all model methods as 'options' params to ensure transaction
        const options = {...req, models};

        const my_model = await MyModel.findOne({/*your query*/}).then(val => val);

        if(my_model){
          await MyOtherModel.destroyById(someId, options).then(val => val);
        }

        result = my_model;
        return my_model;
      });
    } catch (err) {
      next(err);
    }

The problem with this is that the transaction is already committed, so the "result" model is no longer transactional.