adam.sellers adam.sellers - 13 days ago 5
Node.js Question

skip update columns with pg-promise

I've got an API up on node using pg-promise for Postgres, this works well but i'm thinking about how to modify the PUT statement to handle NULLS in the input a little better.

The following is the code for the PUT statement:

//UPDATE a single record
function updateRecord(req, res, next) {
db.none('update generic1 SET string1=$1,' +
'string2=$2,' +
'string3=$3,' +
'string4=$4,' +
'string5=$5,' +
'string6=$6,' +
'integer1=$7,' +
'integer2=$8,' +
'integer3=$9,' +
'date1=$10,' +
'date2=$11,' +
'date3=$12,' +
'currency1=$13,' +
'currency2=$14' +
'WHERE id = $15',
[req.body.string1,
req.body.string2,
req.body.string3,
req.body.string4,
req.body.string5,
req.body.string6,
parseInt(req.body.integer1),
parseInt(req.body.integer2),
parseInt(req.body.integer3),
req.body.date1,
req.body.date2,
req.body.date3,
parseInt(req.body.currency1),
parseInt(req.body.currency2),
parseInt(req.params.id)])
.then(function(){
res.status(200)
.json({
'status': 'success',
'message': 'updated one record'
});
})
.catch(function(err){
return next(err);
});
}


Now this statement works, but it also removes existing values if I pass in NULLS to the next update. For example, if I wanted to just update string1 and date2 for example, i'd have to send the entire json object or all the other values are being set to NULL.

Is there a better way to handle this? Should I be using the PATCH verb instead??

Answer

I am the author of pg-promise ;)

var pgp = require('pg-promise')({
    capSQL: true // capitalize all generated SQL
});

// generic way to skip NULL/undefined values for strings:
function str(col) {
    return {
        name: col,
        skip: function () {
            var val = this[col];
            return val === null || val === undefined;
        }
    };
}

// generic way to skip NULL/undefined values for integers,
// while parsing the type correctly:
function int(col) {
    return {
        name: col,
        skip: function () {
            var val = this[col];
            return val === null || val === undefined;
        },
        init: function () {
            return parseInt(this[col]);
        }
    };
}

// Creating a reusable ColumnSet for all updates:
var csGeneric = new pgp.helpers.ColumnSet([
    str('string1'), str('string2'), str('string3'), str('string4'), str('string5'),
    str('string6'), int('integer1'), int('integer2'), int('integer3'),
    str('date1'), str('date2'), str('date3')
], {table: 'generic1'});

// Your new request handler:
function updateRecord(req, res, next) {

    var update = pgp.helpers.update(req.body, csGeneric) + ' WHERE id = ' +
        parseInt(req.params.id);

    db.none(update)
        .then(function () {
            res.status(200)
                .json({
                    'status': 'success',
                    'message': 'updated one record'
                });
        })
        .catch(function (err) {
            return next(err);
        });
}

See the helpers namespace ;)


Alternatively, you can do your own verification for each column, and then generate an UPDATE query accordingly, though it won't be as elegant ;)

Comments