CODEWITHSUNDEEP
javascriptnode.jsmodel-view-controllernode-modulesrollback
Ali
Ali

Reputation: 1759

How to rollback when error occurred in the second query execution| NodeJS

I used to use rollback in PHP, I want to implement the same concept in NodeJS. I am calling a function that inserts a data to the database, and on success I am calling another function from another Model to insert data into another table.

My Code

StoreReport

'use strict';

var sql = require('./db.js');

// IMPORT ANOTHER MODEL
var CashDetailModel = require('./cashDetailModel.js');

var StoreReport =function(storeReport){
    this.store_id=storeReport.store_id;
    this.cash_expense=storeReport.cash_expense_amount;

}

StoreReport.addNewStoreReport = function (report_details,request,result){

    sql.query('INSERT INTO store_report SET ?',report_details, function(err,res){
        if(err){
            result(err,null);
        }else{
            var new_cash_detail = new CashDetailModel(report_details);
            new_cash_detail.store_report_id=res.insertId;
            CashDetailModel.addCashDetail(new_cash_detail,function(err,CashDetailModel){
                if(err){

                  // I NEED TO ROLLBACK IF ERROR OCCURED IN THIS PHASE
                    res.send(err);
                }else{
                    result(null,res.inserted);
                }
            })
        }
    })
}

module.exports = StoreReport;

Upvotes: 2

Views: 8285

Answers (2)

Gaurav Varshney
Gaurav Varshney

Reputation: 512

It depends on which mysql engine you are using, transnational engines like innodb considers each query as separate transaction if you are explicitly not defining the transaction.

So in order to revert the change you have two options :

  1. Revert changes using update or delete query, which is a wrong approch.
  2. Explicitly define transaction using connection.beginTransaction() and commit or rollback accordingly.

Upvotes: 2

Husein
Husein

Reputation: 540

It is explained pretty well here: https://know-thy-code.com/transactions-with-multiple-queries-nodejs-mysql/.

You call sql.beginTransaction() before the first query and write your queries in the callback for that. Then you can rollback your transaction with sql.rollback() or commit the changes with sql.commit().

Upvotes: 4

Related Questions