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.

Are you sure you want to use a global `queryOutput` element instead of waiting for the callback from that inner method? — Nico Haase, Mar 09 '20 at 15:19
Does this answer your question? [How to properly return a result from mysql with Node?](https://stackoverflow.com/questions/31875621/how-to-properly-return-a-result-from-mysql-with-node) — Nico Haase, Mar 09 '20 at 15:30
I try this solution but I got clear string. Dunno why. If you want I cant post into answer here. — Matyáš Boreček, Mar 09 '20 at 15:47
Why don't you `return queryOutput` after `console.log` inside the query callback? Callback will run Async that's why `queryOutput` returns old data. — Shivam, Mar 09 '20 at 15:49

num8er · Accepted Answer · 2022-01-27T14:52:09.897

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

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,
}

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));

@MatyášBoreček if You want to implement cached result (You ask for previous result in question) so read about memcache or redis and cache results from mysql result to cache and then if no result in `query` method simply `resolve(cachedResult)` — num8er, Mar 09 '20 at 16:00
@MatyášBoreček added example of getting user by id, hope example will give You idea for future queries. — num8er, Mar 09 '20 at 16:09

Getting result from MySQL

1 Answers1