Pg-promise: Recommended practice for issuing a DELETE?

[catastrophe-brandon]

In some POC code I've been working on, it seems that if I issue a DELETE statement using pg.one an error is thrown. To work around the issue I've been executing the statement using pg.none, but I'm uncomfortable with the fact that I can't confirm deletion occurred.

Here's my question: What is the recommended practice for issuing delete statements using pg-promise?

Here is the code I've been using. Forgive me if some of what I've done doesn't make sense; I'm rather new to Node.

function removeItem(req, res) {
    "use strict";
    console.log("Attempting to delete item " + req.params.id)
    pg.none(
        "DELETE FROM ITEMS WHERE ID=$1",
        [req.params.id]
    ).then (
        data => {
            console.log("Deletion stuff happened");
            res.sendStatus(204).send({message: "Deleted item with id: " + data.id})
        }
    ).catch(error => {
        "use strict";

        if (error.name == "QueryResultError") {
            console.log("Nothing was deleted");
            res.sendStatus(404);
        }

        console.log('ERR: ', error);
        res.sendStatus(500).send({message: error});
    })

}

[vitaly-t]

By using the right method + RETURNING * you can monitor how many records are being deleted, as you would receive back the rows that just have been deleted:

• db.one('DELETE FROM table_name WHERE id = $1 RETURNING *', 123) - will reject, if it deletes anything other than one record.
• db.many('DELETE FROM table_name WHERE id = $1 RETURNING *', 123) - will reject, if it deletes less than 1 record.

and so on.

Alternatively, use the [result] method, which resolves with verbose Result object, which includes, among other things, the number of rows affected (deleted in your case) - property rowCount:

db.result('DELETE FROM table_name WHERE id = $1', 123, a => a.rowCount) - will resolve with the number of rows deleted.

The last approach is best-performing, as it doesn't request any rows back, only the counter.

[catastrophe-brandon]

Thank you! This helps clarify things a lot!