How to loop over different Sequelize queries using transactions

Question

Currently, I am trying to do an iteration over an array of objects which need to be inserted depending on the number of items in the array (from request.body).

Expected behavior:

I thought that the for loop would result in different sequelize SQL queries which will be inserted into the database one after the other.

--

Actual behavior:

The actual behavior is that only the last item in the array is inserted into the database and the first one is overwritten and not being inserted into the database.

--

My question:

How to change this existing logic to be able to insert multiple records into the database while using transactions / sequelize SQL queries?

The data I send to the API request is:

[{
  "venue_id": 5,
  "event_id": 13,
  "table_id": 4,
  "date_in": "2017-11-30",
  "date_out": "2017-12-31",
  "check_in": "2017-12-31T17:04:42.333Z",
  "check_out": "2017-12-31T17:05:42.333Z"
},
{
  "venue_id": 6,
  "event_id": 18,
  "table_id": 6,
  "date_in": "2017-11-30",
  "date_out": "2017-12-31",
  "check_in": "2017-12-31T17:04:42.333Z",
  "check_out": "2017-12-31T17:05:42.333Z"
}]

The API call logic can be found below. This API request basically does the following things:

1. Starts a SQL transaction in order to either commit or rollback when something goes wrong.
2. Searches for a venueId and CustomerId and TableId. (in case someone tries to insert some non-existing ids)
3. Calculates the price of the tables together
4. Creates a reservation
5. Commits the transaction and returns response.

router.post(
  "/api/v1/reservations",
  [passport.authenticate("jwt", { session: false }), isCustomer],
  (request, response) => {

    return models.sequelize.transaction().then(t => {


      // I was trying to do this by using a for loop but it doesn't seem to work.

      for (var i = 0; i < request.body.length; i++) {

        return models.Venue.findById(request.body[i].venue_id)
          .then(venue => {
            return models.Customer.findById(request.customer.id);
          })
          .then(customer => {
            return models.Table.findAllById(request.body[i].table_id);
          })
          .then(tables => {
            var price = 0;

            for (var i = 0; i < tables.length; i++) {
              price = price + tables[i].price;
            }

            return models.Reservation.createReservation(
              request.body[i],
              price,
              request.customer.id
            ).then(reservation => {
              return reservation.addTables(tables).then(() => {
                if (request.body.length - 1 === i) {
                  t.commit().then(() => {
                    return response.status(200).send(reservation);
                  });
                }
              });
            });
          })
          .catch(error => {
            console.log(error);
            t.rollback().then(() => {
              return response.status(error.status_code).send(error.message);
            });
          });
      }
    });
  }

Answer 1

Instead of using a for loop, I have used a foreach loop. After some small adjustments it seems to work.

Answer 2

You have made your code look so complex and you are reaching to callback hell situation , I would suggest to use aysnc await :

Here I have tried to achieve almost error less code from yours , but still your code looks to complicated , please try to solve if have any errors. But this is the way you can achieve you are expecting :

router.post(
"/api/v1/reservations", [passport.authenticate("jwt", {
    session: false
}), isCustomer],
(request, response) => {

    return models.sequelize.transaction().then(async (t) => { // <--- ASYNC

        // I was trying to do this by using a for loop but it doesn't seem to work.

        for (var i = 0; i < request.body.length; i++) {

            try{
                let venue = await models.Venue.findById(request.body[i].venue_id) // <--- AWAIT
                let customer = await models.Customer.findById(request.customer.id); // <--- AWAIT
                let tables = await models.Table.findAllById(request.body[i].table_id); // <--- AWAIT
                let price = 0;

                for (var i = 0; i < tables.length; i++) {
                    price = price + tables[i].price;
                }

                let reservation = await models.Reservation.createReservation(
                    request.body[i],
                    price,
                    request.customer.id
                ); // <--- AWAIT   
                await reservation.addTables(tables); // <--- AWAIT
                if (request.body.length - 1 === i) {
                    await t.commit();
                    // return response.status(200).send(reservation); 
                }
            } catch (err) {
                await t.rollback();
                return response.status(error.status_code).send(error.message);
            }
        }

    });
}

Hope this will help you to get what you want :)

---

The actual behavior is that only the last item in the array is inserted into the database and the first one is overwritten and not being inserted into the database.

Reason : Is async behaviour , look in your first for loop , it will get executed no matter your inner code executed or not , so it will loop through all the request.body first and then your inner code will start execute , this is called event loop