Node JS+Express: How to define and use MySQL data in HTML(EJS)?

Question

I created a simple rest-api app(nodeJS+ExpressJS+MySQL) as a test project. It's functioning well, so I wanted to add HTML(EJS) to display the data so I have been working on displaying views. However, I wasn't able to pass on MySQL Data to HTML.

** As I'm still learning nodeJS, I'm still confused as some tutorials added routers in app.js/server.js

Since I have created getAll() in the controller, I used Restaurant.getAll() in server.js. Below is the console.log result of Restaurant.getAll(). I tried to used JSON.stringtify(data) already, but there's no difference.

I would appreciate if anyone could let me how I could change or fix my codes. Appreciate all your helps.

restaurant_list:  [
  RowDataPacket {
    id: 1,
    name: 'A',
    genre: 'Japanese',
    rating: 4
  },
  RowDataPacket {
    id: 2,
    name: 'B',
    genre: 'Mexican',
    rating: 4
  },
  RowDataPacket {
    id: 3,
    name: 'C',
    genre: 'Chinese',
    rating: 4
  }
]

server.js

const express = require("express");
const Restaurant = require("./app/models/restaurant.model.js");
const app = express();

app.use(express.json()); //Used to parse JSON bodies
app.use(express.urlencoded()); //Parse URL-encoded bodies

// set the view engine to ejs
app.set('views', './app/views');
app.set('view engine', 'ejs');

// simple route
app.get("/", function(req, res) {
  var data = Restaurant.getAll();
  res.render("index.ejs", { data: data });
});

require("./app/routes/restaurant.routes.js")(app);

// set port, listen for requests
app.listen(3000, () => {
  console.log("Server is running on port 3000.");
});

restaurant.model.js

const sql = require("./db.js");

// constructor
const Restaurant = function(restaurant) {
    this.name = restaurant.name;
    this.genre = restaurant.genre;
    this.rating = restaurant.rating;
};

Restaurant.getAll = result => {
  sql.query("SELECT * FROM restaurant_list", (err, res) => {
    if (err) {
      console.log("error: ", err);
      result(null, err);
      return;
    }

    console.log("restaurant_list: ", res);
    result(null, res);
  });
};

module.exports = Restaurant;

restaurant.routes.js

module.exports = app => {
    const restaurant_list = require("../controllers/restaurant.controller.js");
  
    // Retrieve all restaurants
    app.get("/", restaurant_list.findAll);

  };

Answer 1

Regarding your orginal question, it seems that the response you got is accessible as a normal object (ref: How to access a RowDataPacket object) and that RowDataPacket is just the constructor name of the object response.

Thus you are not using the callback return of the function getAll. You are passing it a callback named result and returning the standard (err, result). But you are treating it as normal synchronous function. Change this:

// simple route
app.get("/", function(req, res) {
  var data = Restaurant.getAll();
  res.render("index.ejs", { data: data });
});

To this:

app.get("/", function(req, res) {
  Restaurant.getAll(function(err, data) {
    if (err) // handle your error case
    else {
      res.render("index.js", { data: data });
    }
  });
});

And even better (es6 syntax):

app.get("/", function(req, res) {
  Restaurant.getAll((err, data) => {
    if (err) // handle your error case
    else res.render("index.js", { data });
  });
});

Using arrow functions and object destructuring.

Be careful because in the err callback of your getAll function you are returning (null, err) and you should return result(err) as the second parameter is not needed since you should always check for err first.

Also, as an advice, i would like to suggest you to use standard javascript Classes instead of doing the same thing on a normal constant object. Also, you could get rid of the callbacks in your controller functions and treat your query as asynchronous calls like this.