Node mysql async await issue while using transaction

Question

I am writing mysql query with transaction but its not waiting to be completed and returns the result even before the function has executed.

Can someone please help me to know how to make function to wait for response?

The calling function is something like this:

deductUserPoint = await helper.deductUserPoint2(data)
console.log('===============================================')
  console.log(deductUserPoint)
  if (deductUserPoint.isError === true) {
    let error = {}
    error.isError = true
    error.message = 'Unable to deduct amount'
    error.error = deductUserPoint.error
    res.status(200).json(error)
  } else {
    res.status(200).json({ 'isError': false, 'message': 'success' })
  }

I always get deductUserPoint as undefined because it does not wait for deductUserPoint2 to return response

The deductUserPoint2 is as follows:

async deductUserPoint2(params) {
    try {
      this.db.connection.getConnection(function(err, conn) {
        console.log('1111111111')
        conn.beginTransaction(function(err) {
        if (err) {
          throw err 
        }
        console.log(params)

        console.log('2222222222')
        conn.query('SELECT id, `expert_id`, user_id, status FROM `call` WHERE `id` = ?', [params.callId], function (error, callInfo, fields) {
          if (error) {
            return conn.rollback(function() {
              throw error
            })
          }

          console.log('33333333')
          let callLength = null
          if (params.callMinute === 'entire_day') {
            callLength = 'entire_day'
          } else {
            const callMinutes = Math.round(params.callMinute)
            if (callMinutes <= 30) {
              callLength = '30_min'
            } else if (callMinutes >= 31 && callMinutes <= 60) {
              callLength = '60_min'
            } else if (callMinutes >= 61 && callMinutes <= 90) {
              callLength = '90_min'
            } else if (callMinutes >= 91) {
              callLength = '120_min'
            }
          }
          console.log('4444444444')
          conn.query('SELECT `amount` FROM `expert_charges` WHERE `status` = "active" AND `call_length` = ? AND `expert_id` = ?', [callLength, callInfo[0].expert_id], function (error, points, fields) {
            if (error) {
              return conn.rollback(function() {
                throw error
              })
            }
            console.log('555555555555')
            let data = {
              fromUserId: callInfo[0].user_id,
              fromUserType: 'user',
              to_user_id: 0,
              to_user_type: null,
              category: 'call',
              type: 'debited',
              callId: params.callId,
              amount: points[0].amount,
              note: params.note,
              point: points[0].amount,
              current_balance_point: 0
            }

            let input = Object.values(data)

            conn.query('INSERT INTO wallet (`from_user_id`, `from_user_type`, `to_user_id`, `to_user_type`, `category`, `type`, `call_id`, `amount`, `note`, `point`, `current_balance_point`) VALUES ?', [[input]], function (error, wallet, fields) {
              if (error) {
                return conn.rollback(function() {
                  throw error
                })
              }
              console.log('666666666666')
              conn.query('UPDATE user SET total_points = total_points - ? WHERE id = ?', [points[0].amount, callInfo[0].user_id], function (error, updateUserPoint, fields) {
                if (error) {
                  return conn.rollback(function() {
                    throw error
                  })
                }
                console.log('7777777777')
                conn.commit(function(err) {
                  if (err) {
                    return conn.rollback(function() {
                      throw err
                    })
                  }
                  console.log('888888888')
                  return {
                    "isError": false,
                    "status": "success"
                  }
                })

                })
              })
          })
        })     
      })
    })

    } catch (error) {
      console.log(error)
      return {
        "isError": true,
        "error": error.toString()
      }
    }

Error it prints is :

undefined
(node:4197) UnhandledPromiseRejectionWarning: TypeError: Cannot read property 'isError' of undefined
    at router.post (/Users/msmexmac/node/msmex-backend/msmex-api/api/wallet.js:120:23)
    at process._tickCallback (internal/process/next_tick.js:68:7)
(node:4197) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
(node:4197) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
1111111111
{ callId: 5, note: 'Deduction for call', callMinute: 25 }
2222222222
33333333
4444444444
555555555555
666666666666
7777777777
888888888

Try not to mix promises with callbacks. Convert all callback-accepting functions to promise-returning (you can use `utils.promisify` for that) and use await consistently. — georg, Sep 10 '19 at 07:46
@georg will it be possible for you to show me how it can be done? — D555, Sep 10 '19 at 07:49
We already have a thread on this: https://stackoverflow.com/questions/44004418/node-js-async-await-using-with-mysql — georg, Sep 10 '19 at 07:56
I've downvoted the question because I think it’s best if you familiarize yourself with the fundamentals of promises and async/await by checking similar questions on SO such as the one georg suggested. — AndreasPizsa, Sep 10 '19 at 08:11
You’re welcome @D555. I hope to be helping other people by keeping the quality of SO questions high. The tooltip of the downvote button says "This question does not show any research effort, it is unclear, or not helpful". Discussion about whether to downvote can be found here: https://meta.stackoverflow.com/questions/252677/when-is-it-justifiable-to-downvote-a-question — AndreasPizsa, Sep 10 '19 at 08:45

Marcos Luis Delgado · Accepted Answer · 2019-09-10T08:12:11.177

You can't use async functions like that, you have to return a promise to be able to wait for your callbacks. Your function would have to end up like this:

function deductUserPoint2(params) {
    return new Promise(function (resolve, reject) {
        try {
            this.db.connection.getConnection(function (err, conn) {
                console.log('1111111111')
                conn.beginTransaction(function (err) {
                    if (err) {
                        return reject(err)
                    }
                    console.log(params)

                    console.log('2222222222')
                    conn.query('SELECT id, `expert_id`, user_id, status FROM `call` WHERE `id` = ?', [params.callId], function (error, callInfo, fields) {
                        if (error) {
                            return conn.rollback(function () {
                                return reject(error)
                            })
                        }

                        console.log('33333333')
                        let callLength = null
                        if (params.callMinute === 'entire_day') {
                            callLength = 'entire_day'
                        } else {
                            const callMinutes = Math.round(params.callMinute)
                            if (callMinutes <= 30) {
                                callLength = '30_min'
                            } else if (callMinutes >= 31 && callMinutes <= 60) {
                                callLength = '60_min'
                            } else if (callMinutes >= 61 && callMinutes <= 90) {
                                callLength = '90_min'
                            } else if (callMinutes >= 91) {
                                callLength = '120_min'
                            }
                        }
                        console.log('4444444444')
                        conn.query('SELECT `amount` FROM `expert_charges` WHERE `status` = "active" AND `call_length` = ? AND `expert_id` = ?', [callLength, callInfo[0].expert_id], function (error, points, fields) {
                            if (error) {
                                return conn.rollback(function () {
                                    return reject(error)
                                })
                            }
                            console.log('555555555555')
                            let data = {
                                fromUserId: callInfo[0].user_id,
                                fromUserType: 'user',
                                to_user_id: 0,
                                to_user_type: null,
                                category: 'call',
                                type: 'debited',
                                callId: params.callId,
                                amount: points[0].amount,
                                note: params.note,
                                point: points[0].amount,
                                current_balance_point: 0
                            }

                            let input = Object.values(data)

                            conn.query('INSERT INTO wallet (`from_user_id`, `from_user_type`, `to_user_id`, `to_user_type`, `category`, `type`, `call_id`, `amount`, `note`, `point`, `current_balance_point`) VALUES ?', [[input]], function (error, wallet, fields) {
                                if (error) {
                                    return conn.rollback(function () {
                                        return reject(error)
                                    })
                                }
                                console.log('666666666666')
                                conn.query('UPDATE user SET total_points = total_points - ? WHERE id = ?', [points[0].amount, callInfo[0].user_id], function (error, updateUserPoint, fields) {
                                    if (error) {
                                        return conn.rollback(function () {
                                            return reject(error)
                                        })
                                    }
                                    console.log('7777777777')
                                    conn.commit(function (err) {
                                        if (err) {
                                            return conn.rollback(function () {
                                                return reject(err)
                                            })
                                        }
                                        console.log('888888888')
                                        return resolve({
                                            "isError": false,
                                            "status": "success"
                                        })
                                    })

                                })
                            })
                        })
                    })
                })
            })

        } catch (error) {
            console.log(error)
            return resolve({
                "isError": true,
                "error": error.toString()
            })
        }
    })
}

Then you use reject(err) instead of throw err and resolve(value) instead of return value.

Another approach is using utils.promisify as @georg suggested.

There is no error, all database transactions gets executed successfully but as the calling function does not wait for the response it always the result is undefined — D555, Sep 10 '19 at 08:14
It should throw, as you are trying to read `isError` property of the response, right? — Marcos Luis Delgado, Sep 10 '19 at 08:16

Node mysql async await issue while using transaction

1 Answers1