Currently, I am diving into the realm of learning express and pg promise. However, during this journey, I have encountered a puzzling issue that I suspect stems from my somewhat shaky understanding of promises.
Essentially, I have crafted some functions for querying the database. Additionally, I have a function in place to handle any rejections. When used independently, these functions work flawlessly, whether they succeed or encounter an exception.
Let's take a look at a quick sample:
const addToColumn = (tableName, columnName, entryId, amountToAdd) => {
return db.one('UPDATE ${table:name} SET ${column:name} = ${column:name} + ${amount:csv} WHERE id = ${id:csv} RETURNING *', {
table: tableName,
column: columnName,
amount: amountToAdd,
id: entryId
}).catch(err => handleQueryErr(err));
};
Moreover, these functions perform admirably when manually passed into a batch transaction like so:
const transferEnvelopeBudgetByIds = async (req, res, next) => {
try{
req.updatedEnvelopes = await db.tx(t => {
return t.batch([
addToColumn('envelopes', 'budget', req.envelopeFromId, -req.transferBudget),
addToColumn('envelopes', 'budget', req.envelopeToId, req.transferBudget)
]);
}).catch(err => handleTransactionErr(err));
next();
}catch(err){
next(err);
}
};
However, when attempting to call this function within the middleware instead, if there is any sort of database error (e.g., the database not running), the server crashes with an uncaught exception:
const batchQuery = (queryArray) => {
return db.tx(t => {
return t.batch(queryArray);
}).catch(err => handleTransactionErr(err));
};
const transferEnvelopeBudgetByIds = async (req, res, next) => {
try{
req.updatedEnvelopes = await batchQuery([
addToColumn('envelopes', 'budget', req.envelopeToId, -req.transferBudget),
addToColumn('envelopes', 'budget', req.envelopeToId, req.transferBudget)
])
next();
}catch(err){
next(err);
}
};
It appears that the rejection is never caught anywhere and seems to be related to passing an array of promises into the function. The batchQuery function works only when the query succeeds but fails to handle a database error. Any insights on this? If necessary, I can manually write out the entire batchQuery each time I require it, yet I'd like to comprehend why it isn't functioning as expected.
Thank you in advance!
Edit: Here is the updated version with callbacks, which now correctly catches all errors and operates smoothly upon success. Although it identifies errors if any of the queries fail, it unfortunately does not rollback when utilizing the batchQuery function. Originally, I believed that batch would accept an array of promises to resolve, but the current method doesn't align with my expectations.
const transferEnvelopeBudgetByIds = async (req, res, next) => {
try{
req.updatedEnvelopes = await batchQuery(() =>
[
addToColumn('envelopes', 'budget', req.envelopeFromId, -req.transferBudget),
addToColumn('envelopes', 'budget', req.envelopeToId, req.transferBudget)
]);
next();
}catch(err){
next(err);
}
};
Edit 2: I managed to make it work using functions, though not entirely with the batchQuery function. Now, I include 't' as an optional argument in addToColumn, and it functions superbly.
const addToColumn = (tableName, columnName, entryId, amountToAdd, t) => {
let context = t;
if(context === undefined){
context = db;
}
return context.one('UPDATE ${table:name} SET ${column:name} = ${column:name} + ${amount:csv} WHERE id = ${id:csv} RETURNING *', {
table: tableName,
column: columnName,
amount: amountToAdd,
id: entryId
}).catch(err => handleQueryErr(err));
};
const transferEnvelopeBudgetByIds = async (req, res, next) => {
try{
req.updatedEnvelopes = await db.tx(t => {
return t.batch([
addToColumn('envelopes', 'budget', req.envelopeFromId, -req.transferBudget, t),
addToColumn('envelopes', 'budget', req.envelopeToId, req.transferBudget, t)
])
}).catch(err => handleTransactionErr(err));
next();
}catch(err){
next(err);
}
};