How do I change JSON result before returning route in express.js with express4-tedious

Question

having previously used the very helpful pg-promise library in my express app, I now need to convert to SQL Server.

The most similar library I can find to achieve a similar outcome is the express4-tedious package. I can make this work for simple queries, however, I can't figure out how to manipulate the returned json prior to returning the result.

app.get('/heatmapData', function(req, res) {
  db.manyOrNone(`
    SELECT
    	id
    	, metricname
    	, metricval as value
    	, backgroundcolor as fill
    	, suggestedtextcolor as color
      , heatmapname
    FROM
    	heatmapdata a
    INNER JOIN
    	heatmapcolors b
    ON
    	a.heatmapset = heatmapname and a.heatmapnumber=b."Order"
  `)
    .then(function(data) {
      let bob = {}
      data.map(item => {
        if (bob[item.metricname] === undefined) {
          bob[item.metricname] = {};
        }
        bob[item.metricname][item.id] = {
          fill: item.fill,
          color: item.color,
          value: item.Value
        };
        bob[item.metricname].heatmapname = item.heatmapname;
      })
      res.status(200).json(bob);
    });
});

When converting this to SQL server, I can use FOR JSON PATH, to return a nice javascript object, however, in express4-tedious, I have the following syntax:

req.sql('that previous big sql statement...').done(
  (data)=>{
    console.log('this is meant to manipulate the data');
    return data
    })
    .into(res)

However, this isn't quite returning the result I expect. Any pointers in the right direction would be super helpful!

Answer 1

So after spending another few hours in the 'learning zone', I've used the higher level mssql instead of express4-tedious. This was a suprisingly difficult recommendation to find, but this library is somewhat closer in function to the pg-promise library, although the connection setup seems significantly harder.

const sql = require('mssql');
var sqlconfig = require('./config.js'); //this is where the connection details are
const config = sqlconfig.config;
 
 app.get('/myQueryData', function(req,res){
	
	new sql.ConnectionPool(config).connect().then(pool => {
    return pool.request()
    .query(`	
			SELECT myQuery.* FROM myTables					
		`)
	}).then(function(data){
	let bob={}
        data.recordset.map(item=>{
          if (bob[item.metricname]=== undefined) {
            bob[item.metricname]={};
          }
          bob[item.metricname][item.id]={fill:item.fill, color:item.color, value:item.Value};
          bob[item.metricname].heatmapname = item.heatmapname;
          })
          res.status(200).json(bob); sql.close();
    })
	.catch(err => {
    console.log(err)
	})

sql.on('error', err => {
    console.log(err)
})
});