NodeJS: ASync / callback problems

Question

I'm working on the NodeJS framework, where the sql is executed after the main body.

I have used the basic sql connection block and am not sure how to integrate async or callbacks in the block.

My code is below:

 var mysql = require('mysql');
 var pool = mysql.createPool({
     connectionLimit: 100,
     host: "localhost",
     user: "...user...",
     password: "...pw...",
     database: "...db..."
  });

... ... ...

  app.get('/*', function(req, res) {


  var sql = mysql.format("SELECT * FROM test_rest WHERE location=? LIMIT 2", [user_input]);


  pool.getConnection(function(err,connection) {
        if (err) throw err;
        connection.query(sql, function (err, result, fields) {
            connection.release();
            if (err) throw err;


         });
  });


 var jsonResponse = [];
 var obj = {};
 obj["text"] = 'hi this is' + user_nsew_1;

 jsonResponse.push(obj);
 res.send(jsonResponse);
 });

Please understand that `if (err) throw err` in async callbacks is not useful error handling and will come back to bite you. You're in a response handler. You can do a `res.status(500).send(something); return;` rather than punting on actual error handling. — jfriend00, Sep 05 '17 at 07:04
Possible duplicate of [How do I return the response from an asynchronous call?](https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call) — ponury-kostek, Sep 05 '17 at 07:34

score 1 · Answer 1 · answered Sep 05 '17 at 07:07

You have to send the response INSIDE the inner callback as that is the only place that the data is available:

app.get('/*', function(req, res) {

    var sql = mysql.format("SELECT * FROM test_rest WHERE location=? LIMIT 2", [user_input]);

    pool.getConnection(function(err,connection) {
        if (err) {
            res.status(500).send("could not get database connection");
            return;
        }
        connection.query(sql, function (err, result, fields) {
            connection.release();
            if (err) {
                res.status(500).send("database query failed");
                return;
            }
            var jsonResponse = [];
            var obj = {};
            obj["text"] = 'hi this is' + user_nsew_1;

            jsonResponse.push(obj);
            res.send(jsonResponse);
         });
     });
 });

Also, since it looks like you plan on incorporating user input into your sql query, be careful that the input is sanitized appropriately so you are not vulnerable to sql injection attacks.

score 0 · Answer 2 · answered Sep 05 '17 at 07:27

You would have to make sure that the MySQL library you are using either supports Promise . Which are required by await and async .You can use Bluebird's promisifyAll to wrap the library. or you can encapsulate the mysql library with promise.

For example async-db.js:

const mysql = require('mysql')
const pool = mysql.createPool({
   connectionLimit: 100,
   host: "localhost",
   user: "...user...",
   password: "...pw...",
   database: "...db..."
});
let query = function( sql, values ) {
   return new Promise(( resolve, reject ) => {
     pool.getConnection(function(err, connection) {
        if (err) {
          reject( err )
        } else {
          connection.query(sql, values, ( err, rows) => {
              if ( err ) {
                 reject( err );
              } else {
                 resolve(rows);
              }
              connection.release();
          });
       }
     });
   });
}

module.exports = { query }

Usagetest.js:

const { query } = require('./async-db')
async function selectAllData( ) {
  let sql = 'SELECT * FROM my_table'
  let dataList = await query( sql )
  return dataList
}

async function getData() {
  let dataList = await selectAllData()
  console.log( dataList )
}

getData()

NodeJS: ASync / callback problems

2 Answers2