How to properly return a result from mysql with Node?

Five years later, I understand asynchronous operations much better. Also with the new syntax of async/await in ES6 I refactored this particular piece of code:

const mysql = require('mysql2') // built-in promise functionality
const DB = process.env.DATABASE
const conn = mysql.createConnection(DB)

async function getInfo(data){
  var sql = "SELECT a from b where info = data"
  const results = await conn.promise().query(sql)
  return results[0]
}

module.exports = {
  getInfo
}

Then, where ever I need this data, I would wrap it in an async function, invoke getInfo(data) and use the results as needed.

You're going to need to get your head around asynchronous calls and callbacks with javascript, this isn't C#, PHP, etc...

Here's an example using your code:

function get_info(data, callback){
      
      var sql = "SELECT a from b where info = data";

      connection.query(sql, function(err, results){
            if (err){ 
              throw err;
            }
            console.log(results[0].objid); // good
            stuff_i_want = results[0].objid;  // Scope is larger than function

            return callback(results[0].objid);
    })
}


//usage

var stuff_i_want = '';

 get_info(parm, function(result){
    stuff_i_want = result;

    //rest of your code goes in here
 });

When you call get_info this, in turn, calls connection.query, which takes a callback (that's what function(err, results) is
The scope is then passed to this callback, and so on.

Welcome to javascript callback hell...

It's easy when you get the hang of it, just takes a bit of getting used to, coming from something like C#

This was a situation where I was inserting new records to a child table and needed the prent record key, based only on a name.

This was a good example of understanding the asynchronous nature of node.

I needed to wrap the all the code affecting the child records inside the call to find the parent record id.

I was approaching this from a sequential (PHP, JAVA) perspective, which was all wrong.

I guess what you really want to do here is returning a Promise object with the results. This way you can deal with the async operation of retrieving data from the DBMS: when you have the results, you make use of the Promise resolve function to somehow "return the value" / "resolve the promise".

Here's an example:

getEmployeeNames = function(){
  return new Promise(function(resolve, reject){
    connection.query(
        "SELECT Name, Surname FROM Employee", 
        function(err, rows){                                                
            if(rows === undefined){
                reject(new Error("Error rows is undefined"));
            }else{
                resolve(rows);
            }
        }
    )}
)}

On the caller side, you use the then function to manage fulfillment, and the catch function to manage rejection.

Here's an example that makes use of the code above:

getEmployeeNames()
.then(function(results){
  render(results)
})
.catch(function(err){
  console.log("Promise rejection error: "+err);
})

At this point you can set up the view for your results (which are indeed returned as an array of objects):

render = function(results){ for (var i in results) console.log(results[i].Name) }

Edit I'm adding a basic example on how to return HTML content with the results, which is a more typical scenario for Node. Just use the then function of the promise to set the HTTP response, and open your browser at http://localhost:3001

require('http').createServer( function(req, res){
if(req.method == 'GET'){
    if(req.url == '/'){
        res.setHeader('Content-type', 'text/html');
        getEmployeeNames()
        .then(function(results){
          html = "<h2>"+results.length+" employees found</h2>"
          html += "<ul>"
          for (var i in results) html += "<li>" + results[i].Name + " " +results[i].Surname + "</li>";
          html += "</ul>"
          res.end(html);
        })
        .catch(function(err){
          console.log("Promise rejection error: "+err);
          res.end("<h1>ERROR</h1>")
        })
    }
}
}).listen(3001)