Return query data to render in ejs node js

Question

I'm retrieving data from mysql db and I need to send data to render in my ejs file.

router.get('/', function(req, res, next) {
  res.render('index',  { speedAccelData: getSpeedAccelData() });
});

module.exports = router;

Following is the function which I used to get data from database. It successfully prints jsonStr with required data. I need to return jsonStr from the function.

var getSpeedAccelData = function () {

  var jsonStr;

  var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "root",
    database: "db_name"
  });

  con.connect(function(err){
    if(err){
      console.log('Error connecting to Db');
      return;
    }
  });

  con.query('SELECT * FROM table_name',function(err, result){
    if(err) throw err;
    jsonStr = JSON.stringify(result)
    console.log(jsonStr);
  });
  con.end();

};

How can I return query data to render?

it's **asynchronous**, you can't return a value like this. One solution is to pass a callback to your `getSpeedAccelData` function. Or you can return a `Promise` in your `getSpeedAccelData` function — Olivier Boissé, Aug 11 '16 at 08:47
Possible duplicate of [How do I return the response from an asynchronous call?](http://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call) — Ben Fortune, Aug 11 '16 at 09:31

Olivier Boissé · Accepted Answer · 2016-08-11T09:08:23.700

You can use Promise to achieve this

var getSpeedAccelData = function () {

  var promise = new Promise(function (resolve, reject) {

    var jsonStr;

    var con = mysql.createConnection({
      host: "localhost",
      user: "root",
      password: "root",
      database: "db_name"
    });

    con.connect(function(err){
      if(err){
        console.log('Error connecting to Db');
        reject();
      }
    });

    con.query('SELECT * FROM table_name',function(err, result){
      if(err) {
        reject(err);
      } else {
        jsonStr = JSON.stringify(result)
        resolve(jsonStr);
      }

    });

    con.end();

  });

  return promise;
};

Then in your controller you can proceed like this :

router.get('/', function(req, res, next) {
  getSpeedAccelData()
  .then(function success(result) {
     res.render('index',  { speedAccelData: result });
  }, function error(error) {
     ...
  });
});

Return query data to render in ejs node js

1 Answers1