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.

Answer 2

Alternative solution:

function updateFoo(req, res){ 
    let {name, email, password} = req.body; 
    // Ex: req.body = { name: 'foo', password: 'bar' }
    let data = { name, email, password }; // {name: 'foo', email:undefined, password:'bar}
    //Remove ONLY undefined keys from data
    Object.keys(data).forEach( key => { if(data[key] === undefined) delete data[key] });

    let query = 'UPDATE foo SET';

    let i = 1;
    Object.keys(data).forEach( key => { query += ` ${key}=$${index},`; i++; })
    query = query.slice(0, -1) // Remove exceeding comma
    query += ` WHERE id=$${i}`;

    let values = Object.values(data); // ['foo', 'bar']
    values.push(req.params.id);

    // .....
    // query = 'UPDATE foo SET  name=$1, password=$2 WHERE id=$3'
    // values = ['foo', 'bar', req.params.id]

Answer 3

Thank you @vitaly-t! faster and cleaner, always a good result :)

for reference, i've also included the insert statement using the helpers as described above as well.

    //firstly create a function that skips the nulls for strings
function str(column) {
    return {
        name: column,
        skip: c => c.value === null || c.value === undefined || !c.exists
    };
}

//now a function that skips nulls for integers, while parsing type
function int(column) {
    return {
        name: column,
        skip: c => c.value === null || c.value === undefined || !c.exists,
        init: c => +c.value
    };
}

//creating a column set for all updates
var usefulColumSet = 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'), int('currency1'), int('currency2')
], {table: 'generic1'});

//*********************CREATE a single record*************************
function createRecord(req, res, next) {
    var insert = pgp.helpers.insert(req.body, usefulColumSet);

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


//************************UPDATE a single record*************
function updateRecord(req, res, next) {
    var update = pgp.helpers.update(req.body, usefulColumSet) + ' WHERE id = ' + parseInt(req.params.id);

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