How to execute different error messages depending on where a query failed in a transaction in pg-promise?

Question

how can I execute varying error messages depending on where a query failed, triggering a rollback, in my transaction?

I'll be using the sample code from the documentation:

db.tx(t => {
    // creating a sequence of transaction queries:
    const q1 = t.none(query);
    const q2 = t.one(query);
    const q3 = t.one(query);

    // returning a promise that determines a successful transaction:
    return t.batch([q1, q2, q3]); // all of the queries are to be resolved;
})
    .then(data => {
        // success, COMMIT was executed
    })
    .catch(error => {
        // failure, ROLLBACK was executed
    });

Preferred output is the following:

• if the transaction failed in q1:

res.json({error: true, message:"q1 failed"})

• if the transaction failed in q2:

res.json({error: true, message:"q2 failed"})

• if the transaction failed in q3:

res.json({error: true, message:"q2 failed"}), etc.

What I'm thinking is using a Switch statement to determine what error message to execute, although I don't have an idea on how to know what query failed in the transaction.

Thank you for your help!

P.S. I recently migrated from node-pg to pg-promise (which is why I'm a bit new with the API) due to having a hard time with transactions as recommended in my previous posts, and yeah, pg-promise made a lot of things easier the 1 day worth of refactoring code is worth it.

Answer 1

Since you are using method batch, you get BatchError thrown when the method fails, which has useful property data, among others:

.catch(err => {
    // find index of the first failed query:
    const errIdx = err.data.findIndex(e => !e.success);

    // do what you want here, based on the index;
});

Note that inside such error handler, err.data[errIdx].result is the same as err.first, representing the first error that occurred.