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??

Ashliashlie

Answer 1

I am the author of pg-promise ;)

const 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() {
            const 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() {
            const val = this[col];
            return val === null || val === undefined;
        },
        init() {
            return parseInt(this[col]);
        }
    };
}

// Creating a reusable ColumnSet for all updates:
const 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:
async function updateRecord(req, res, next) {

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

    try {
            await db.none(update);
            res.status(200);
    } catch(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.

Proofread

@Ashliashlie see the update about the very latest version ;) – Proofread

@Ashliashlie another update has been added - option emptyUpdate ;) – Proofread

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]

Quadrivium

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

Ashliashlie

Since you are not providing attribute def - default value when the property doesn't exist, you will end up with c.value equal undefined in those cases, so checking for !c.exists isn't needed ;) – Proofread

Answer 4

Typescript sample:

 async updateService(obj: T): Promise<void>{
        update(obj, table, getFields(obj));
   }

Generate the column set fields that you want to update only:

 private getFields(obj: T) {
    const fields: string[] = [];
    if (string1){
        fields.push('string1');
    }
    if (string2){
        fields.push('string2');
    }
    if (string3){
        fields.push('string3');
    }
    return fields;
   }

Define the PG method:

async update (object: T, table: string, fields: string[]) : Promise<void> {
        try {
            const columnSet = new pgp.helpers.ColumnSet(fields,{table: table});
            const where = pgp.as.format('WHERE id = $1', [object.id]);
            const update = `${pgp.helpers.update(object, columnSet)} ${where}`;
            await this.db.none(update);
        } catch (e) {
            console.error('update: An error happened when trying to update: %s. Error: %s', JSON.stringify(object), e);
        }
    }

Find more details here: pg-promise using named parameters in an UPDATE SET statement

Sanborn

Added await that you forgot in front of this.db.none – Proofread

Because otherwise it will create a loose promise. You also misquoted the very example the link to which you gave, which does use await there. – Proofread