1

I have a patch endpoint in my REST API where every body parameter is optional. What is the best way of implementing it without checking each parameter manually?

db.none("update tasks set title=$1, description=$2, value=$3 where id=$4",
    [req.body.title, req.body.description, parseInt(req.body.value), req.params.id])
    .then(function () {
        res.status(200)
            .json({
                status: "success",
                message: "Updated task"
            });
    })
    .catch(function (err) {
        return next(err);
    });
cksrc
  • 2,062
  • 3
  • 24
  • 39
  • 1
    Your query contains 4 parameters, while the you pass in only 3 values. This doesn't look right. You need to fix your example first. – vitaly-t Oct 18 '17 at 19:42

1 Answers1

1

Use methods in the helpers namespace:

static declarations

const optional = name => ({name, skip: col => !col.exists});

const cs = new pgp.helpers.ColumnSet([
    optional('title'),
    optional('description'),
    optional('value')
], {table: 'tasks'});

generating the query and executing it:

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

db.none(update)
    .then(function () {
        res.status(200)
            .json({
                status: "success",
                message: "Updated task"
            });
    })
    .catch(function (err) {
        return next(err);
    });

In addition, you might want to use option emptyUpdate of method helpers.update, to check that at least one column is being set, to avoid invalid query generation request and an error thrown.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • can you briefly explain or point me to the right direction so I understand the static declaration part? – cksrc Oct 18 '17 at 20:44
  • @Shakur Static - means you declare it only once. The links to all the types with API and documentation - I gave you from the beginning. If you want more examples - there are plenty on StackOverflow, if you search for `pg-promise ColumnSet`: https://stackoverflow.com/search?tab=votes&q=pg-promise%20ColumnSet – vitaly-t Oct 18 '17 at 22:50