Question 20220 in Mysql Question posted in
The official MySQL documentation can be found here.

Using the resolve data of a promise-object in Node-JS to render a data populated webpage – Mysql

I am new to Node-JS and trying to get to grips with the asynchronous concept and promises.
I am trying to ensure the availability of the DB-data at the moment the webserver is being rendered.

With the code I seem to have achieved the correct timing – the function buildhtml being called, when the data is available. A first success after a night of research.

Now I am trying to find the correct way of passing the data to the webserver / express.

The data I am looking for is now encapsulated in a promise-object and can’t be processed. I have looked at many other posts, but was not able to derive a solution for y example.

Question: What is the best practice solution to hand over the data in a consumptional way (basically the promise object, but without the outer promise encapsulation)? Is my thinking entirely wrong or is it just a little step I am missing?

Any answers are much appreciated and my thanks in advance!

//DB Load Users
const promise = new Promise((resolve, reject) => {
    db.connect(function(err) {
        if (err) throw err;
        console.log("Connected!");
        var sql = "SELECT username, useremail, userage, useruniqueid FROM users";
        db.query(sql, function(err, result) {
            if (err) throw err;
            const jsonresult = JSON.parse(JSON.stringify(result)); //to clear RowDatePacket
            resolve(jsonresult);
        });
    })
})

promise
    .then(buildhtml)

//Render (express) HTML with promise resolve after DB-query
function buildhtml() {
    app.get("/", function(req, res) {
        res.render("home", {
            data: promise
        })
    })
}

This was the output with console.log(promise) in buildhtml():

Connected!
Promise {
  [
    {
      username: 'admin',     
      useremail: '888883895',
      userage: '33',
      useruniqueid: 1        
    },
    {
      username: 'admin',     
      useremail: '888883895',
      userage: '22',
      useruniqueid: 2
    },
    {
      username: 'admin',
      useremail: '888883895',
      userage: '22',
      useruniqueid: 3
    },
    {
      username: 'admin',
      useremail: '888883895',
      userage: '22',
      useruniqueid: 4
    },
    {
      username: 'pushy boi',
      useremail: '[email protected]',
      userage: '65',
      useruniqueid: 5
    },
    {
      username: 'push attempt',
      useremail: '[email protected]',
      userage: '65',
      useruniqueid: 6
    },
    {
      username: 'instead of push db',
      useremail: '[email protected]',
      userage: '37',
      useruniqueid: 104
    },
    {
      username: '111',
      useremail: '111',
      userage: '11',
      useruniqueid: 111
    },
    {
      username: null,
      useremail: null,
      userage: null,
      useruniqueid: 112
    },
    {
      username: 'Max Mustermann',
      useremail: '[email protected]',
      userage: '44',
      useruniqueid: 333
    }
  ]
}
Tags:

2

Answers


  1. 0

    What you’ve got should work but introduces a potential race condition… what if a request is made to GET / before the promise resolves? Express will simply return a 404 because it does not know about the route.

    Instead of registering your route handler after the promise resolves, resolve the promise inside the route handler

    app.get("/", async (_req, res, next) => {
  try {
    res.render("home", { data: await promise });
  } catch (err) {
    next(err);
  }
});

    Note that your query will only ever execute once when your Express server starts up. This may or may not be what you want. The more typical approach is to perform the query each time a request is made.

    Login or Signup to reply.
  2. 0

    This is roughly how you should rewrite this. First, wrap the code that fetches the MySQL data in a function.

    function getUsers() {
  return new Promise((resolve, reject) => {
    db.connect(function(err) {
        if (err) reject(err);
        console.log("Connected!");
        var sql = "SELECT username, useremail, userage, useruniqueid FROM users";
        db.query(sql, function(err, result) {
            if (err) reject(err);
            resolve(result[0]);
        });
    })
  })
}

    Note I changed your throws into reject and removed the unneeded JSON step.

    Even better would be if you use a promise-based mysql library like mysql2/promises and use a connection pool:

    async function getUsers() {
   const result = await pool.query("SELECT username, useremail, userage, useruniqueid FROM users");
   return result[0];
}

    So clean in comparison!

    Then you define your route and call getUsers when you need it:

    app.get("/", function(req, res) {
  getUsers()
    .then(result => {
       res.render("home", {
         data: result
       })
    })
    .catch(err => {
       res.status(500);
       console.error(err);    
    });
});
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search