CODEWITHSUNDEEP
javascriptnode.jsknex.js
Midoxx
Midoxx

Reputation: 67

Knexjs how to handle multiple updates

I´m quite unsure on how to handle multiple updates / inserts in knex and return whatever it was successfull on the end or not.

I´m passing an array through req.body loop through it and trigger actions based on informations inside the array.

Example:

const data = [...req.body]
for(let i = 0; i < data.length; i++) {
        data[i].totals.length
        for(let y = 0; y < data[i].totals.length; y++) {
            if(data[i].totals[y].info === "Holiday") {
                calcHoliday(data[i].totals[y].total, data[i].id)
            } else if(data[i].totals[y].info === "ZA") {
                calcZA(data[i].totals[y].total, data[i].id)
            }
        }
        calcOvertime(data[i].totalSum, data[i].id)

        if(i === data.length -1) {
            res.json("Success")
        }
    }

The Array I´m passing in looks like this:

[
    {
    "id": 1,
    "totals": [
        {
            "info": "Holiday",
            "total": 4
        }
    ]
    },
    {
    "id": 1,
    "totals": [
        {
            "info": "Holiday",
            "total": 4
        }
    ]
    }
]

Function Example which gets called in for loop:

const calcHoliday = (hours, userid) => {
        knex.transaction(trx => {
            trx.insert({
                created_at: convertedTime,
                info: "Booking Holiday - Hours: " + hours,
                statuscode: 200
            }).into("logs")
                .then(() => {
                    return trx("hours")
                        .decrement("holiday_hours", hours)
                }).then(trx.commit)
                .catch(trx.rollback)
        }).then(() => console.log("WORKED"))
            .catch(err => console.log(err))
}

This is working perfectly fine but I can´t figure out how to gather the results from each table update in order to respond if everything worked or an error appeared. If I call e.g. after one calcHoliday call .then(resp => res.json(resp) I receive only the response from the first operation.

In short I need a way on how to res.json if everything succeeded or an error appeared somewhere.

Thanks in advance!

Upvotes: 0

Views: 1608

Answers (1)

Allxie
Allxie

Reputation: 563

TLDR;

Turning your insert calls into an array of promises and then using await and a Promise.all() / Promise.allSettled() structure might solve this problem, but there are some UX decisions to make on what to rollback and how to return errors.

Error Handling Choices:

  1. Any error --> all insertions in all loop iterations should be rolled back
  • Do you want partial success? The way the code is written now, rollback only applies to items in one function call. If one of the hour-decrement calls fails, it will roll back one log insert, but not any that succeeded for previous data in the loop. If you want the whole dataset to rollback, you'd need to pass the txn through each function call or do a bulk insert of all of your rows in one function call, which might be nice for performance reasons anyway depending on the use case.
  1. Partial success --> commits successes, rolls back single loop iterations that fail, sends detailed list of errors and successes
  • You'd want to use Promise.allSettled(), which aggregates the successes and errors as an array from all promises in the loop.
  1. Partial success --> commits the successes, rolls back single loop iterations that fail, sends just one error
  • Opinion: This can be a misleading UX unless the error is "some of the insertions were unsuccessful" and the endpoint is idempotent
  • This looks closest to what you're describing you want. If this is the case, you'd want to use Promise.all(), which throws an error as soon as one promise in the array errors.

Example Implementation:

Since the original code is incomplete, this is a loose, incomplete example of what option 2/3 might look like. This could easily be transformed into option 1.

First, it might help to modify all of your functions with asynchronous calls to be fulfillable as promises. Async/await helps avoid .then() trees that are hard to reason about.

const calcHoliday = async (hours, userid) => {
  try {
    const result = await knex.transaction(async(trx) => {
      await trx.insert({
        created_at: convertedTime,
        info: "Booking Holiday - Hours: " + hours,
        statuscode: 200
      }).into("logs")

      return trx("hours").decrement("holiday_hours", hours)
    }

    return result
  } catch(err) {
    console.log("It didn't work.")
    throw new Error(`Error: Failure to insert for user ${userid}:`, err)
  }
}

Here are some utilities to get the data transformed, and to get the appropriate unfulfilled promise to supply to the map in Promise.all/allSettled.

/*
 Here's an example of how you might transform the original data with maps in order to avoid nested for-loops:
  [
    { id: 1, info: 'Holiday', total: 4 },
    { id: 1, info: 'Holiday', total: 4 }
  ]
*/
const flatData = data.map(item => {
  return item.totals.map(total => ({
    id: item.id,
    ...total
  }))
}).flat()


// Returns the appropriate promise based on data
const getTotalsPromises = (row) => {
  const {info, id, total} = row
  if(info === "Holiday") {
      return calcHoliday(total, id)
  } else if(info === "ZA") {
      return calcZA(total, id)
  }
}

const getcalcOvertimePromises = (rowInData) = {
  // work left to reader
  return calcOvertime(rowInData.correctData, rowInData.otherData)
}

If you want option 2:

// Replaces the loop

// Fulfills *all* the promises, creating an array of errors and successes
const responses = await Promise.allSettled([
  ...flatData.map(getTotalsPromises),
  ...data.map(getCalcOvertimePromises)
])

// insert loop here to do something with errors if you want

res.send(responses)

OR Option 3 Create an array of all of the promises you want to run, run them, and process up to one error.

// Replaces the loop

// Runs the promises and waits for them all to finish or the first error.

try {
  const responses = await Promise.all([
    ...flatData.map(getTotalsPromises),
    ...data.map(getCalcOvertimePromises)
  ])
  res.send(responses)
} catch(err){
  // Reached if one of the rows errors
  res.send(err)
}

Docs: Promise.allSettled Promise.all

Upvotes: 1

Related Questions