pg-promise - unable to receive db response back when use batch

Question

I'm trying to get a response back from the database when I use tx.batch.

Thanks.

• 1) query db for pre-existing records that need to be updated
• 2) using results from 1, make another batch call to db to either create or update records
• 3) respond back with status 200 to my api with records created and records updated

Batch calls does work, verified records are inserted or/and updated into db.

 db.tx(t1 => {
    let queryCheck = [];

    reqData.forEach(obj => {
      for (let key in obj) {
        obj[key].resovledURLs.forEach(data => {
          queryCheck.push(
            t1.any(
              `SELECT * FROM testDB WHERE url='${key}' AND testurl='${data}';`
            )
          );
        });
      }
    });

    return t1
      .batch(queryCheck)
      .then(data => {

        return t1.tx(t2 => {
          let t2QueryBatch = [];

         // got rid of queryBatchOfEverything()
         // uses data from t1.batch(queryCheck) to make joinedArray
         let joinedArray = updateArray.concat(createArray);

          joinedArray.forEach(obj => {
            if (obj.queryType === "Update") {
              t2QueryBatch.push(
                t2.none(
                  `UPDATE testDB SET count = count + 1 WHERE url='${
                    obj.url
                  }' AND errorurl='${obj.testurl}';`
                )
              );
            } else {
              t2QueryBatch.push(
                t2.none(
                  `INSERT INTO testDB (url, testurl) VALUES ('${
                    obj.url
                  }', '${obj.testurl}');`
                )
              );
            }
          });

          return t2.batch(t2QueryBatch);
        });
      })
      .then(data => {
        console.log(data);
      });
  });

Answer 1

Your code exhibits so many problems, it is impossible to advise on the exact issue. You would need to revise the code thoroughly before any further diagnostics is even possible.

Immediately noticeable problems in your code...

problem 1

You forget to chain queryBatchOfEverything to the transaction that contains it, i.e. it should be return queryBatchOfEverything..., if that's your transaction logic

problem 2

You open a nested transaction on the db object, which is invalid, i.e. you cannot create an independent top-level transaction while inside another transaction.

You can only create a sub-transaction there, aka savepoint, from the context of the parent transaction, i.e. t1.tx(t2 => {}).

problem 3

You forget to chain the result of the nested transaction into the parent transaction, so another loose promise on your hands. It needs to be return t1.tx(t2 => )

problem 4

You use method one inside the nested transaction, which means you expect exactly one row of data back, while none of your queries return anything, i.e. you are supposed to use method none there instead. This is the very problem that you asked about. But you need to fix the rest in order for the whole to work right.

problem 5

You do not chain t2.batch to the transaction, creating another loose promise. It should be return t2.batch.

problem 6

This is not a problem, just some useless code:

let updateRecords = await t1.batch(queryCheck).then(data => {
    return data;
});

It is exactly the same as this:

let updateRecords = await t1.batch(queryCheck);