[Chartjs]-Script won't run from ejs file in nodejs application

There are a bunch of misconceptions here: The HTML file will be served to your client, which will see <script> tags, request them from web server, and execute the code. You cannot expect a web browser to run a SQL query on your server), so obviously this is not the way you want to execute this JS code.

So much is wrong, it will be a long answer. Here is a your two main misconceptions:

• You try to make client execute server code
• You run asynchronous code and think it will return synchronously

Then your many smaller errors:

• length is a property, not a method
• you have to export a method to make it usable from outside in node
• you use for (i in rows) so i is the item index (and I guess you got that because you named it i), then you use i.count
• in your SQL you put SELECT COUNT(*) FROM then just use .count property, I’m not sure it will work without AS count

At this point I can only guess your SQL and Chart usage are no better, sorry 🙁 I will try to point you in the right direction though.

Identify client and server

So, first of all, you need to execute this JS code from your Node.js server. The usual steps would be:

• initiate Express app
• configure EJS rendering
• in your route:
  • run your sql queries, get a bunch of data (you’re still server side)
  • render your HTML, passing some data
  • now in your template, just inject what you need from server, to client
• profit

Sample data structure for the next steps:

/
+- app.js
+- lib/
   +- map-access-data.js
+- views/
   +- index.ejs
   +- map-access-chart.ejs
+- stylesheets/
   +- styles.css

The server

Required server dependencies: npm install express ejs mysql, the rest is for client (like chart)

// app.js
const express = require('express')
const app = express()

// Serve public static assets, like stylesheets and client-side JS
app.use(app.static('public'))

// Configure EJS template engine
app.set('view engine', 'ejs')

// Your route
app.get('/', (req, res) => {
  // Your route handler
  // req is client's request
  // res is server's response
})

// Start web server on http://localhost:8000
app.listen(8000)

OK, here you’re server-side, you can use MySQL and similar systems. But first we need to address another issue.

Asynchronous code

Asynchronous is a very important part of Node, really, but we can’t address everything here. You will have the keywords, I let you do your research to tame that part. I’ll use async/await so you’re not too disturbed when reading the code, and util.promisify to transform the methods. The things you have to understand:

• connection.query will query a remote server, in Node it will be done asynchronously, which means you won’t get any result immediately, but your code won’t be stopped either (or it would be blocking, which sucks)
• to represent asynchronous operations, you have basically two ways:
  • pass a callback function to your async function, this callback will be called with the result as soon as it’s available; when using a callback you cannot return anything interesting
  • return an object (called a promise) which is just an empty wrapper; then later this object will suddenly contain the result, and be able to call functions you will have passed to its then method; when using promises you must return those objects, which are a representation of your future data and the only way to access it
• when you work with promise there is a specific syntax called async which allows you to wait for promised data, but your async function will still be async which means it returns a wrapper, not your actual result, unless you wait for it too

Here is your errors:

• In getNumMaps, your return is in the callback. This callback is called way after the function has returned its own result, so it will just return undefined
• In getMapAccessData you didn’t even bother to return anything, still undefined
• In getMapAccessNames, finally you return something! but as connection.query is async, you will push data to your array way after funtion has already returned arr, so it always returns []

And I’ll add you execute three times the same request, sounds wasteful 😉 So, you know you want to finally include all this in your Chart instance, let’s not split that in 4 functions which all execute the same query, we’ll instead build a single result with adapted format.

// lib/map-access-data.js
const mysql = require('mysql')
const connection = mysql.createConnection(/* your config here */)

// get promises instead of using callbacks
const util = require('util')
const queryCallback = connection.query.bind(connection) // or we'll have issues with "this"
const queryPromise = util.promisify(queryCallback) // now this returns a promise we can "await"

// our asynchronous method, use "async" keyword so Node knows we can await for promises in there
async function getChartData () {
  const rows = await queryPromise("SELECT name, COUNT(*) AS count FROM map_access GROUP BY name ORDER BY name")

  // Array#map allows to create a new array by transforming each value
  const counts = rows.map(row => row.count)
  const names = rows.map(row => row.name)
  const colors = rows.map(row => 'rgba(255,99,132,1)')

  // Return an object with chart's useful data
  return {
    labels: names,
    data: counts,
    colors: colors,
  }
}

Modules

OK, now you have a function, available server side only, that gives you what you need.

Now you need to be able to call it from app.js, which means you need to:

• export it:

// lib/map-access-data.js
…

// Export your function as default
module.exports = getChartData

• then import and use it in your route handler:

// app.js
const getChartData = require('./lib/map-access-data)

This is called CommonJS modules

Now in your route handler you can simply call your async function, and await for its result:

// app.js
…

app.get('/', async (req, res) => {
  // Your route handler
  const data = await getChartData()
})

Generating the HTML

Now you have your data made available, you’re still server-side, you now have to generate valid HTML for your client, which currently looks like:

<!DOCTYPE html>
<html>
  … a bunch of HTML …

    <p>See how people are using our app <br/></p>
    <canvas id="myChart" width="50%" height="100px"></canvas>

    <!-- NO! it's not a client JS, it's server JS, client CANNOT download it -->
    <script scr="map-access-data.js" type="text/javascript"></script>

    <script id ="map-popularity" type="text/javascript">
        var Chart = require('chart'); // NO! you can't *require* from client
        var ctx = document.getElementById("myChart").getContext("2d");
        var myChart = new Chart(ctx, {
            type: 'bar',
            data: {
                labels: getMapAccessNames(), // NO! You can't call server methods from client
                datasets:[{
…

Obviously we need to fix a few things:

• Remove the reference to map-access-data.js which makes no sense
• Add chart.js the browser’s way, like from a CDN
• Inject data inside your client JS

Here I think instead of injecting the real data directly into HTML you could use an Ajax request, but I don’t know Chart so I will let you do this part. An Express app serving JSON data is absolutely trivial, just res.send(data), then do some Ajax on client side. Let’s see the version where you inject data directly into HTML to break all the walls:

• Server-side you run your SQL, that gives you some data
• You pass this data to your EJS template, which (still server-side) generates HTML
• In this HTML you will inject String representation of your server data (using JSON.stringify)
• Server finally sends generated HTML to client
• Client receives this wellformed HTML, with some JS in <script>, runs it, everyone is happy

<!-- views/map-access-chart.ejs -->
<canvas id="myChart" width="50%" height="100px"></canvas>

<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/1.0.2/Chart.min.js"></script>
<script id ="map-popularity" type="text/javascript">
    var ctx = document.getElementById("myChart").getContext("2d");
    var myChart = new Chart(ctx, {
        type: 'bar',
        data: {
            labels: <%- JSON.stringify(data.labels) %>,
            datasets:[{
                label: "Map Selection Popularity",
                data: <%- JSON.stringify(data.data) %>,
                backgroundColor: <%- JSON.stringify(data.colors) %>,
                borderColor: <%- JSON.stringify(data.colors) %>,
                borderWidth: 1
…

// app.js
…

// Your route handler
const data = await getChartData()
// Render 'index.ejs' with variables 'title' and 'data' available
res.render('index', {
  title: 'Page title',
  data: data,
})

Now when you run node app.js from your terminal, and go to http://localhost:8000 you should see the result. I guess there will be many remaining errors, but that will be a better start 🙂