Handling query errors with node-pg

Question

I'm trying to insert two queries into my postgres database as follows:

const insertLineItemDB = (request, response) => {
    const { wo_num, description, due_date, cost } = request.body
    const query1 = 'INSERT INTO workorders (wo_id, due_date, complete) VALUES ($1, $2, 0);';
    const query2 = 'INSERT INTO lineitems (wo_num, description, cost) VALUES ($1, $2, $3);';

    Promise.all([
        pool.query(query1, [wo_num, due_date]),
        pool.query(query2, [wo_num, description, cost])
    ]).then(function([query1Results, query2Results]) {
        response.status(201).send('Successfully inserted');
    });
}

My issue is that I am getting the following error: error: duplicate key value violates unique constraint "workorders_pkey" which crashes my node web app. How do I handle this error so it just gives a message to the user but doesn't stop the whole application?

Answer 1

You should catch error in the Promise

Promise.all([
    pool.query(query1, [wo_num, due_date]),
    pool.query(query2, [wo_num, description, cost])
]).then(function([query1Results, query2Results]) {    
    response.status(201).send('Successfully inserted');
}).catch(function (e) {
    response.status(500).send('Insert failed');
});

You can also wrap your Promise in try catch block

try {
    Promise.all([
        pool.query(query1, [wo_num, due_date]),
        pool.query(query2, [wo_num, description, cost])
    ]).then(function([query1Results, query2Results]) {    
         response.status(201).send('Successfully inserted');
    })
} catch(e) {
    response.status(500).send('Insert failed');
}

Somewhat not what you asked, but you could also ignore duplicate key with

INSERT INTO workorders (wo_id, due_date, complete) VALUES ($1, $2, 0) ON CONFLICT (wo_id) DO NOTHING (https://www.postgresql.org/docs/9.5/sql-insert.html#SQL-ON-CONFLICT)