Getting result from MySQL

Question

My backend is consist of Api and DB. When I want to get response from DB I have had delayed output by 1 query.
API (I think api is ok. Start read DB first)

app.post('/api/query', (req, res) => {
  console.log(`\n  Query input : ${JSON.stringify(req.body)}`);
  let queryInput = (Object.values(req.body).join(' '));


    if(!dbApi.checkArray(queryInput)){ //If array is not made from clear strings
      res.json(dbApi.queryFromUser(queryInput));
    }
    else{
      res.json(dbApi.queryOutput);
    }
});
app.listen(dbConfig.server.port, () =>
    console.log(`Server running on port ${dbConfig.server.port}`));

DB

queryOutput = [];
    const receivingQuery =(queryInput) => {

        db.query(queryInput, (err, result) =>{
            if(err) throw err+' : '+queryInput;
            queryOutput = result;
            console.log("\nQuery output "+ JSON.stringify(queryOutput)); //Output (result) is ok
        });
        return queryOutput //Here is Output from previous query (sends to API)

    }

module.exports = {
    queryOutput: queryOutput,
    queryFromUser: receivingQuery,
}

I tryied callback method and I rewrite it couple of times. But I dont have enough skill to solve it.

Answer 1

If You want to return result of query so simply do following things:

1. add query method to db module:

function query(sql, args = []) {
  return new Promise(function(resolve, reject) {
    db.query(sql, args, (err, result) => {
      if (err) return reject(err);
      resolve(result);
    });
  });
}

// extra feature, getting user by id
async function getUserById(id) {
  const result = await query('SELECT * FROM users WHER id = ? LIMIT 1', [id]);
  if (Array.isArray(result) && result[0]) return result[0];
  return null;
}

module.exports = {
    query,
    getUserById, // export user by id

    queryOutput,
    queryFromUser: receivingQuery,
}

2. use it (with async and await):

app.post('/api/query', async (req, res) => {
  try {
    console.log('Query input:', req.body);
    const queryInput = Object.values(req.body).join(' ');
  
    const result = await dbApi.query(queryInput);
    res.json(result);
  }
  catch (error) {
    console.error(error);
    res.status(500).json({message: 'Please try again soon'});
  }
});

app.get('/api/users/:id', async (req, res) => {
  try {
    const user = await dbApi.getUserById(req.params.id);
    if (!user) return res.status(404).json({message: 'User not found'});
    res.status(200).json(user);
  }
  catch (error) {
    console.error(error);
    res.status(500).json({message: 'Please try again soon'});
  }
});

app.listen(dbConfig.server.port, () =>
    console.log('Server running on port', dbConfig.server.port));