Mysql + Nodejs, SELECT Return Undefined

Question

I’m trying to return the SELECT Mysql to make a listing, however, when I give a return for the function to return the result of the SELECT, the variable in which I saved this data gets the value undefined, following example below:

JS:

sqlQuery(dbConnection)

function sqlQuery (dbConnection) {
    dbConnection.query('SELECT * FROM printers', function (error, results, fields) {
      if (error) return console.log(error)
      dbConnection.end()
      console.log(results)
    })
  }

// Neste exemplo, o console.log lista os valores normalmente:
/*
[
  RowDataPacket {
    ID: 1,
    nome: 'Ender 3',
    producer: 'Creality'
  },
  RowDataPacket {
    ID: 2,
    nome: 'Tarantula',
    producer: 'TEVO'
  }
]
*/

When I try to return the values of this function and store them in a variable, the following happens:

JS:

const results = sqlQuery(dbConnection)

console.log(results) // undefined

function sqlQuery (dbConnection) {
    dbConnection.query('SELECT * FROM printers', function (error, results, fields) {
      if (error) return console.log(error)
      dbConnection.end()
      return results
    })
  }

How can I fix this?

Answer 1

What happens is that the Node trabaha asynchronously, that is, when you execute the function "sqlQuery" it does not wait for the return, it already executes the next line. Why the return is "Undefined".

To wait for the return, you need to use Async/Await. Your code would look like this:

const results = await sqlQuery(dbConnection);
console.log(results);

async function sqlQuery (dbConnection) {
    try {
      const resultado = await dbConnection.query('SELECT * FROM printers');
      dbConnection.end()
      return resultado
    } catch (err) {  
        return err;
    }
}

Here is more information:

https://codeburst.io/node-js-mysql-and-async-await-6fb25b01b628

Answer 2

That’s because the function sqlQuery has no return, it takes as parameter a callback that will be executed asynchronously, that is, when the query has the result ready, executes the function passed, but still does not return anything

You can create the variable outside and set its value inside the callback:

sqlQuery(dbConnection)

const queryResults

function sqlQuery (dbConnection) {
  dbConnection.query('SELECT * FROM printers', function (error, results, fields) {
      if (error) console.log(error)
      dbConnection.end()
      queryResults = results
  })
}

console.log(queryResults) // undefined

Note that even so queryResults will continue with the same problem, because the log runs before the callback, which takes a few milliseconds to run (because it is asynchronous)

You can settle this with a promise:

const results = sqlQuery(dbConnection)

function sqlQuery (dbConnection) {
  return new Promise((resolve, reject) => {
      dbConnection.query('SELECT * FROM printers', function (error, results, fields) {
          if (error) {
              console.log(error)
              //Rejeita a promessa
              reject(error)
          }
          dbConnection.end()
          //Conclui a promessa
          resolve(results)
      })
  })
}

console.log(results) // Promise
results.then(console.log) // Os valores

Another way is to use async and await but beneath the surface is also a promise