How to return the results of mySql query using express.js?-CodePudding

I am trying to get the results of my simple SELECT command to the index.js file, where I would like to have all records separated in a array. If I print the results in the database.js the JSON.parse just work fine. But if I want to return them and get them into the index.js where I need them, I always get undefined when I print it.

index.js CODE

const express = require('express');
const app = express();
const database = require('./database');

app.use(express.json());

app.use(express.urlencoded());

app.use(express.static('public'));

app.get('/form', (req,res) =>{
    res.sendFile(__dirname   '/public/index.html' );
    console.log(req.url);
    console.log(req.path);
})


app.listen(4000, () =>{
    console.log("Server listening on port 4000");
    database.connection;
    database.connected();
    //console.log(database.select());
    let results = [];
    //results.push(database.select('username, password'));
    let allPlayer = database.select('username');
    console.log(allPlayer);
});

database.js CODE

let mysql = require('mysql');

const connection = mysql.createConnection({
    host: 'localhost',
    database: 'minigames',
    user: 'root',
    password: 'root'
});
    
function connected(){
    connection.connect((err) => {
        if(err) throw err;
        console.log("Connected...");
    })
}

function select(attribute){
    let allPlayer = [];
    let sql = `SELECT ${attribute} FROM player`;
    let query = connection.query(sql, (err, result, field) => {
    
    if(err) throw err;
        return Object.values(JSON.parse(JSON.stringify(result)));
    })
}
module.exports = {connection, connected, select};

CodePudding user response：

Understand that one of the main things that make JavaScript different from other languages is that it's asynchronous, in simple terms meaning code doesn't "wait" for the code before it to finish executing. Because of this, when you're trying to query a database, which takes some time, the code after it gets impatient and executes regardless of how to the query is doing. To solve this problem, the mysql package utilizes callbacks, which allows you to pass a function to it to execute once the query is finished with the queries result.
Because the library operates on callbacks, it doesn't return anything; that seems quite problematic for using it somewhere else, doesn't it?

To solve this problem, we can make our own callback. Or better yet, use the newer JavaScript feature called promises, where you can basically "return" anything from a function, even when you're in a callback.

Let's implement it with the query:

function select(attribute) {
  return new Promise((resolve, reject) => {
    let sql = `SELECT ${attribute} FROM player`;
    let query = connection.query(sql, (err, result, field) => {
      if(err) return reject(err);
      resolve(Object.values(JSON.parse(JSON.stringify(result))));
    });
  });
}

To "return" from a promise, we pass a value to the resolve function. To throw an error, we call the reject function with the error as the argument.

Our new function is rather easy to use.

select("abcd").then(result => {
  console.log("Result received:", result);
}).catch(err => {
  console.error("Oops...", err);
});

You might look at this code and go, "Wait a minute, we're still using callbacks. This doesn't solve my problem!"
Introducing async/await, a feature to let you work just with that. We can call the function instead like this:

// all 'await's must be wrapped in an 'async' function
async function query() {
  const result = await select("abcd"); // woah, this new await keyword makes life so much easier!
  console.log("Result received:", result);
}
query(); // yay!!

To implement error catching, you can wrap you stuff inside a try {...} catch {...} block.