i'm working on a project which has a simple dashboard, on my server i make three mySql connections and get some information like ( users, admins ,events ) which will be shown on the dashboard, i use this code to gather information :

function gather_info(callback) {
  let info = {
    users: [],
    admins: [],
    events: []
  }
  sql.query("SELECT * FROM users", (err, users) => { info.users = users });
  sql.query("SELECT * FROM users WHERE isAdmin = 1", (err, admins) => { info.admins = admins });
  sql.query("SELECT * FROM events", (err, events) => { info.events = events });

  callback(info);
}

then i use above function in my get request :

app.get("/", loginRequired, (req, res, next) => {
//check if user is admin - to render dashboard
  if (req.user.isAdmin) {
    gather_info((info) => {
      return res.render('admin', { events: info.events, users: info.users, admins: users.admins })
    })
  };
  //if user not admin render users page
  sql.query("select * from events where userId = ?", [req.user.ID], (err, events) => {
    res.render("index", { events });
  });
})

• now the problem is that the function doesn't work properly, i tried to use async/await but mysql doesn't work like that, what should i do now ?

• and another question, should i close connection every time i open one while i'm using createPool ?

- thanks for your kindness -

CodePudding user response：

You can use Promise.all to optimise the gather_info method.

function mysqlSyncQuery(q) {
  return new Promise((resolve, reject) => {
    sql.query(q, (err, result) => { 
      if(err) return reject(err); 
      return resolve(result);
    });
  })
}

async function gather_info(callback) {
  let info = {
    users: [],
    admins: [],
    events: []
  }
  let q1 = mysqlSyncQuery("SELECT * FROM users");
  let q2 = mysqlSyncQuery("SELECT * FROM users WHERE isAdmin = 1");
  let q3 = mysqlSyncQuery("SELECT * FROM events");
  let results = await Promise.all([q1, q2, q3]);
  info.users = results[0];
  info.admins = results[1];
  info.events = results[2];
  callback(info);
}

You can further add validations in the catch block of Promise.all and also parse and validate the results array. For reference Promise.all

Promise.all will run these queries in parallel so that you don’t have to wait for one to finish before starting another query. This way, it will be faster since these queries seems independent.

More to this, you can also refactor the app.get with async await to make the code similar throughout. I haven’t touched that since you can figure it out easily.

CodePudding user response：

There are at least 3 ways to do this.

1. nested calls, most likely the worst!!

function gather_info(callback) {
  let info = {
    users: [],
    admins: [],
    events: []
  }
  sql.query("SELECT * FROM users", (err, users) => { 
    info.users = users;
    sql.query("SELECT * FROM users WHERE isAdmin = 1", (err, admins) => {
      info.admins = admins;
      sql.query("SELECT * FROM events", (err, events) => { 
        info.events = events;
        callback(info);
      });
    });
  });
}

2. async/await, better but the queries are called one after another instead of in parallel, meaning you are waiting for each query to finish before you call another

async function gather_info(callback) {
  let info = {
    users: [],
    admins: [],
    events: []
  }
  info.users = await query('SELECT * FROM users');
  info.admins = await query('SELECT * FROM users WHERE isAdmin = 1');
  info.events = await query('SELECT * FROM events');
  callback(info);
}

function query(q) {
  return new Promise(function(resolve, reject){
    sql.query(q, (err, data) => { 
      resolve(data);
    });
  });
};

3. Promise.all, this should run all queries in parallel

async function gather_info(callback) {
  let info = {
    users: [],
    admins: [],
    events: []
  }
  let users = query('SELECT * FROM users');
  let admins = query('SELECT * FROM users WHERE isAdmin = 1');
  let events = query('SELECT * FROM events');

  Promise.all([users, admins, events]).then((values) => {
    info.users = values[0];
    info.admins = values[1];
    info.events = values[2];
    callback(info);
  });
  
}

function query(q) {
  return new Promise(function(resolve, reject){
    sql.query(q, (err, data) => { 
      resolve(data);
    });
  });
};