Optional INSERT statement in transaction chain using NodeJS and Postgres

Question

I'm building a simple webapp using NodeJS/Postgres that needs to make 3 insertions in the database.

To control the chain of statements I'm using pg-transaction.

My problem is that I have to always run the 2 first INSERTS, but I have a condition to run the 3rd one.

Maybe my code could be built in a better manner (suggestions are welcome).

Here's a pseudo code:

function(req, res) {
  var tx = new Transaction(client);
  tx.on('error', die);
  tx.begin();
  
  tx.query('INSERT_1 VALUES(...) RETURNING id', paramValues, function(err, result) {
    if (err) {
      tx.rollback();
      res.send("Something was wrong!");
      return;
    }
    
    var paramValues2 = result.rows[0].id;
    tx.query('INSERT_2 VALUES(...)', paramValues2, function(err2, result2) {
      if (err) {
        tx.rollback();
        res.send("Something was wrong!");
        return;
      }
      
      // HERE'S THE PROBLEM (I don't want to always run this last statement)
      // If I don't run it, I will miss tx.commit()
      if (req.body.value != null) {
        tx.query('INSERT_3 VALUES(...)', paramValues3, function(err3, result3) {
          if (err) {
            tx.rollback();
            res.send("Something was wrong!");
            return;
          }
        
          tx.commit();
          res.send("Everything fine!");
        });
      }
    });
  });
}

It looks so ugly to repeat three times the same if (err) {} after each query.

Trying to check some options I found Sequelize, but couldn't see a way to solve this problem with it.

Any suggestions are welcome!

Thanks!

Are you sure that in the last insert it is 'INSERT_3 VALUES(...)', paramValues2 and not some 'INSERT_3 VALUES(...)', paramValues3? i.e. do you need to use result from the first query to do the last one? — vitaly-t
– vitaly-t, Commented May 5, 2016 at 15:04

Mikayil · Accepted Answer · 2024-12-06 07:41:24Z

Manual transaction management is a treacherous path, try to steer away from that! ;)

Here's how to do it properly, with the help of pg-promise:

    function(req, res) {
        db.tx(t => { // automatic BEGIN
                return t.one('INSERT_1 VALUES(...) RETURNING id', paramValues)
                    .then(data => {
                        var q = t.none('INSERT_2 VALUES(...)', data.id);
                        if (req.body.value != null) {
                            return q.then(()=> t.none('INSERT_3 VALUES(...)', data.id));
                        }
                        return q;
                    });
            })
            .then(data => {
                res.send("Everything's fine!"); // automatic COMMIT was executed
            })
            .catch(error => {
                res.send("Something is wrong!"); // automatic ROLLBACK was executed
            });
    }

Or, if you prefer ES7 syntax:

function (req, res) {
    db.tx(async t => { // automatic BEGIN
            let data = await t.one('INSERT_1 VALUES(...) RETURNING id', paramValues);
            let q = await t.none('INSERT_2 VALUES(...)', data.id);
            if (req.body.value != null) {
                return await t.none('INSERT_3 VALUES(...)', data.id);
            }
            return q;
        })
        .then(data => {
            res.send("Everything's fine!"); // automatic COMMIT was executed
        })
        .catch(error => {
            res.send("Something is wrong!"); // automatic ROLLBACK was executed
        });
}

UPDATE

Replaced ES6 generators with ES7 async/await in the example, because pg-promise stopped supporting ES6 generators from version 9.0.0

Exactly what I needed. Just tested and it works perfectly. Thanks for your complete answer!

Collectives™ on Stack Overflow

Optional INSERT statement in transaction chain using NodeJS and Postgres

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related