Transactions with node pg and foreach loop and async await

Question

I am trying to insert multiple rows in PostgreSQL using node pg. I am using transactions but my query is executing after a response. I tried async await with my function but it is not working

This is my function

addPersons = async (req, res) => {
try {
    await db.query("BEGIN");
    req.body.forEach((person, index) => {
      if (person.id) {
        try {
          await db.query("ROLLBACK");
        } catch (error) {
          console.error("Error rolling back client", err.stack);
        }
        return res
          .status(Error_code.IdNotFound.code)
          .send(Error_code.IdNotFound);
      }
      const query = `update person set 
        name = ${person.name},
        where id = '${
          person.id
        }'`;
      try {
        await db.query(query);
      } catch (error) {
        try {
          await db.query("ROLLBACK");
        } catch (error) {
          console.error("Error rolling back client", err.stack);
        }
        return res.status(500).send(err);
      }
    })
    await db.query("COMMIT");
    res.status(Error_code.Successfull.code).send(Error_code.Successfull);
  } catch (error) {
    try {
      db.query("ROLLBACK");
    } catch (error) {
      console.error("Error rolling back client", err.stack);
    }
    return res
      .status(Error_code.UnableToBeginTransaction.code)
      .send(Error_code.UnableToBeginTransaction);
  }
}

I also tried calling this function from another function and using foreach on that function but when whenever code detects await or callback in the second function it does not wait and return to the first function. How can I run this code to add my data into PostgreSQL with transactions

Thanks

`res.send` should be after `forEach`. Why don't you split your code into promises ? — darklightcode, Nov 19 '18 at 07:08
Possible duplicate of [Using async/await with a forEach loop](https://stackoverflow.com/questions/37576685/using-async-await-with-a-foreach-loop) — Estus Flask, Nov 19 '18 at 07:10
@darklightcode res.status is after foreach. I didn't used promise in my code till now. I have 2 scenarios where I need to handle this situation that's why I am trying to avoid promises — Unknown, Nov 19 '18 at 08:24

score 1 · Answer 1 · answered Nov 28 '18 at 09:49

Since this is tagged node-postgres, I suggest that you base your code on the A pooled client with async/await example in the node-postgres documentation. I also suggest that you use parameterized queries or a query builder such as mongo-sql. (There are many, but that one's my favourite. )

It could look something like this:

const { Pool } = require("pg");
const pool = new Pool();

const addPersons = async (req, res) => {
  const db = await pool.connect();
  try {
    await db.query("BEGIN");
    const query = `update person set name = $1 where id = $2;`;
    // Promise.all() may improve performance here, but I'm not sure if it's safe
    // or even useful in the case of transactions.
    for (const person of req.body) {
      await db.query(query, [person.name, person.id]);
    }
    await db.query("COMMIT");
  } catch (e) {
    await db.query("ROLLBACK");
    throw e;
  } finally {
    db.release();
  }
};

Looks quite nice. Even though it is discouraged to use await in iterations -but use Promise.all instead-, it suits our needs in transactions, since we, in theory, want them to be executed sequentially instead of concurrently. Thanks for sharing. — John Theodorakopoulos, Nov 26 '19 at 23:15

Transactions with node pg and foreach loop and async await

1 Answers1