node.js - skip update columns with pg-promise

Question

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 1

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 ;)

UPDATE

Please note that the way init and skip are parameterized changed in version 5.4.0 of the library, see the release notes.

Starting from version 5.4.0, you can simplify the code as this:

// generic way to skip NULL/undefined values for strings:
function str(column) {
    return {
        name: column,
        skip: c => c.value === null || c.value === undefined
    };
}

// generic way to skip NULL/undefined values for integers,
// while parsing the type correctly:
function int(column) {
    return {
        name: column,
        skip: c => c.value === null || c.value === undefined,
        init: c => +c.value
    };
}

And if you want to skip the properties that were not passed in at all, and thus do not even exist within the object, then instead of this:

skip: c => c.value === null || c.value === undefined

you can do this:

skip: c => !c.exists

UPDATE

Version 5.6.7 of the library received a further improvement for this - option emptyUpdate, which when specified represents the value to be returned by the method, rather than throwing Cannot generate an UPDATE without any columns. See helpers.update for details.

See also: ColumnConfig.