Using mssql and node-sql to UPDATE

Question

I'm using mssql together with node-sql to build SELECT queries but I can't find any example how to use it to build UPDATE queries. I have an object where properties corresponds to table fields and I would like to update all of them.

Assume:

child: sql.define({
    name: 'children',
    columns: ['id', 'name', 'surname', 'group']
  })

and:

var data = {/*new child data*/};

var query = child.update(data).where(child.id.equals(data.id)).toQuery().text;

How can I use this with mssql without knowing values and count of data properties?

Right now I have this:

connection.query(query, [data.id, data.name, data.surname, data.group], function(err, result) {

      res.redirect('/index');
    });

that can be achieved by using lodash's values:

_.values(data);

which returns array of object properties but it does not guarantee correct order which is deal breaker.

How can I tackle that problem?

hassansin · Accepted Answer · 2015-07-01T15:28:33.443

1

This will return an array of values based on the order of table columns:

child.columns.map(function(col){return data[col.name]})

It might be possible to compact the above in shorter form with lodash.

edited Jul 01 '15 at 15:28

answered Jul 01 '15 at 12:05

hassansin

16,918
3
43
49

`child.columns.map(function(col){return data[col.name]})` did the trick – Dread Boy Jul 01 '15 at 13:52

score 0 · Answer 2 · answered Jul 13 '15 at 06:17

Few days later I figured node-sql's query object also has .values property besides .text property so above update can be written as

var data = {/*new child data*/};

var query = child.update(data).where(child.id.equals(data.id)).toQuery();

connection.query(query.text, query.values, function(err, result) {

  res.redirect('/index');
});

Using mssql and node-sql to UPDATE

2 Answers2