How to pass table names and parameters with pg-promise

Question

I'm new to node and subsequently pg-promise. What I am trying to achieve in my node express app is a route that will work as an api to do a single field update in a table in the DB. This has to be just a single route that will be called with table name, column name, column value and column id value to identify which row to update in the table.

This is the code I have tried so far but I cannot seem to get it right:

router.post('/update_db_field', function (req, res, next) {
    db.one('update $table~ set $dbcol~=$dbcolval~ where $dbcolid~=$dbcolidval~ returning $colid~', [
        req.body.table, 
        req.body.dbcol, 
        req.body.dbcolid, 
        req.body.dbcolval, 
        req.body.dbcolidval
    ]).then(function (data) {
        console.log(data)
        res.send(data)
    }).catch(function (error) {
        console.log("ERROR:", error.message || error); // print error;
    });
})

Unfortunately it is throwing the following error:

ERROR: syntax error at or near "$"

Is there also a way to print the query string from the catch() function so debugging can be a little easier?

Answer 1

You confuse variable formatting with Named Parameters, your query should be:

router.post('/update_db_field', function (req, res, next) {
    db.one('UPDATE ${table~} SET ${dbcol~} = ${dbcolval} WHERE ${dbcolid~} = {dbcolidval} RETURNING ${colid~}', req.body, a => a[req.body.dbcolid])
    .then(function (dbcolid) {
        console.log(dbcold)
        res.send(dbcolid)
    })
    .catch(function (error) {
        console.log("ERROR:", error.message || error); // print error;
    });
})

Though you still likely will have problems, because passing in a dynamic table + column means you do not know how to cast them to their correct type, so for example, you may end up with your dbcolval being passed in as a string, while it needs to be an integer, and then the query will reject because of that.

Note that we added optional a => a[req.body.dbcolid], so instead of resolving with an object with one property, it resolves with the property value directly.

How can I see the query text printed in console?

var query = pgp.as.format(query, values);
console.log(query);

API: as.format.