How do I create charts on my javascript website using data from mySQL database. Using EJS front-end

I am making a website where I can enter data for golf course greens, and then analyze it. I am using express in my backend to route to my ejs pages. I am also using a mySQL database to store the data. When creating the charts I used chart.js from w3schools, which got me to create a script within the html code to insert the javascript code. But then realized that I would have to create a database connection in the ejs file (which seems inefficient and unsafe (creating multiple connections to DB)). I then spent ages, creating functions inside my js file with the database and express connections where I do all my other queries. I ended just trying to keep the script from website in the ejs file, and then try and pass the data from my js file into this ejs file to display. This is the reason that I'm asking for help. I can't figure out how to pass this list of data from my js file to my ejs file to use in a script? While, thinking about it. There must be a more efficient way of doing this?

EJS file where I am trying to display the graph:

<body>
    <div class="container">
        <div class="chart">
            <canvas id="myChart" style="width:100%;max-width:700px"></canvas>
        </div>
    </div>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.4/Chart.js"></script>
    <script>
    document.addEventListener('DOMContentLoaded', function () {
            const scatterGraphData = JSON.parse(document.getElementById('myChart').dataset.scatterData);
            
            new Chart("myChart", {
                type: "scatter",
                data: {
                    datasets: [{
                        pointRadius: 4,
                        pointBackgroundColor: "rgb(0,0,255)",
                        data: scatterGraphData
                    }]
                },
                options: {
                    legend: { display: false },
                    scales: {
                        xAxes: [{ ticks: { min: 40, max: 160 } }],
                        yAxes: [{ ticks: { min: 6, max: 16 } }],
                    }
                }
            });
        });
        </script>
</body>

Function which calculates the data:

async function getChartData() {
    const now = new Date();
    const formattedDate = now.toISOString().split('T')[0];

    connection.query("SELECT * FROM sample WHERE Date = ?", [formattedDate], (error, results) => {
        if (error) {
            console.log("Database Error inside getChartData");
            return;
        }
        if (results.length === 0) {
            // No data found today
            console.log("Entered in the no data found");
            // random test data to see if graph even works
            const defaultData = [
                { x: 50, y: 7 },
                { x: 60, y: 8 },
                { x: 70, y: 8 },
                { x: 80, y: 9 },
                { x: 90, y: 9 },
                { x: 100, y: 9 },
                { x: 110, y: 10 },
                { x: 120, y: 11 },
                { x: 130, y: 14 },
                { x: 140, y: 14 },
                { x: 150, y: 15 }
            ];
            return defaultData;
        }
        // real Data exists to map
        let AveragesList = [];
        let counter = 0;
        const sampleHoles = [2, 12, 16];
        for (const holeid of sampleHoles) {
            const holeRecord = connection.query("SELECT * FROM holes WHERE Id = ?", [results[0][holeid]]);
            const upSlopeData = connection.query("SELECT * FROM upslopespeed WHERE UpSlopeID = ?", [holeRecord[0].UpSlopeSpeedID]);
            const downSlopeData = connection.query("SELECT * FROM downslopespeed WHERE DownSlopeID = ?", [holeRecord[0].DownSlopeSpeedID]);

            const backLeftSpeed = (upSlopeData[0].BackLeft * 2) / (upSlopeData[0].BackLeft + downSlopeData[0].BackLeft);
            const backCentreSpeed = (upSlopeData[0].BackCentre * 2) / (upSlopeData[0].BackCentre + downSlopeData[0].BackCentre);
            const backRightSpeed = (upSlopeData[0].BackRight * 2) / (upSlopeData[0].BackRight + downSlopeData[0].BackRight);

            const AverageSpeed = (backLeftSpeed + backCentreSpeed + backRightSpeed) / 3;
            AveragesList[counter] = AverageSpeed;
            counter++;
        }

        const xyValues = [
            {x:2, y:AveragesList[0]},
            {x:12, y:AveragesList[1]},
            {x:16, y:AveragesList[2]}
          ];

        return xyValues;
    });
}

This is function that is called when the page is to be loaded:

function DashboardDetermine(req, res) {
    if (req.session.user.isAdmin === 1) {
        // ADMIN
        // get all employees to list on admin dashboard
        connection.query("SELECT * FROM user WHERE admin = 0", async (error, results) => {
            if (error) {
                console.error('Error executing query:', error);
                req.flash('error_msg', 'An error occurred while loading users.');
                res.redirect('/dashboard');
                return;
            }
            const scatterGraphData = await JSON.stringify(getChartData());
            console.log(scatterGraphData);
            // Pass the results to the template
            res.render('dashboardAdmin', { user: results, scatterGraphData });
        });
    } else {
        // EMPLOYEE
        res.render("dashboard");
    }
}

ChatGPT suggested to format it as a JSON and then pass that when rendering the file. Not sure if this changed anything. Before I just passed the getChartData() directly. Like this res.render('dashboardAdmin', { user: results, scatterData: getChartData()}); Which didn't work.

On a day which is empty the getChartData() function should just return the fixed data which I took from the w3schools website in their example just to eliminate sources of error, it seems to get data from the getChartData() call, but the JSON file from the console.log returns {}. Which causes the chart to not appear.

This is why I am wondering whether the move the scatter graph script into the same into this JS file so I can have the functions enter the data there?

Hope this not to confusing to understand, many thanks for any help!

Solution

A few observations, comments and also a working code based on your code enclosed.

a. The second argument to res.render basically is an object. It will then be available in the template as an object itself. Please see in the code below as it has been passed as an object, more specifically in this case as - an object of array of objects.

  res.render('index', { graphdata });

b. Continuing the above point, there is no mandate that it must be in JSON format. Formatting the same entirely depends on the context in which it will be used in the template. Please also note that the Chart constructor function directly accepts javascript object, it does note need to be JSON formatted. This you may be able to test and confirm by assigning the array of objects directly to its property data.datasets.data.

c. However the below code has used JSON format. This has come as a technical requirement. The object passed into the template will be available as an object itself which has already mentioned in point a, however accessing the same in JavaScript in the 'script' tag would require a little more work. The following statement does the job. In this particular case, it first JSON formatted, and thus made available in the JavaScript scripting context, finally reverted the JSON format by parsing. By these steps, it has become the same object type passed from the server. This is one of the technical ways to get an object passed from the server made accessible in the scripting context as well. Please also note the hyphon in the ejs tag '<%-', it is also required to get the unescaped values.

const graphdata = JSON.parse('<%- (JSON.stringify(graphdata)) %>');

A working code:

server.js

const express = require('express');
const app = express();

app.use(express.static('./'));

app.engine('ejs', require('ejs').__express);
app.set('views', './');
app.set('view engine', 'ejs');

const graphdata = [
  { x: 50, y: 7 },
  { x: 60, y: 8 },
  { x: 70, y: 8 },
  { x: 80, y: 9 },
  { x: 90, y: 9 },
  { x: 100, y: 9 },
  { x: 110, y: 10 },
  { x: 120, y: 11 },
  { x: 130, y: 14 },
  { x: 140, y: 14 },
  { x: 150, y: 15 },
];
console.log(graphdata);
app.get('/', (req, res) => {
  res.render('index', { graphdata });
});

app.listen(3000, () => console.log('L@3000'));

index.ejs

<!DOCTYPE html>
<html>
  <head>
    Chart.js sample - Scatter chart
  </head>
  <body>
    <div class="container">
      <div class="chart">
        <canvas id="myChart" style="width: 100%; max-width: 700px"></canvas>
      </div>
    </div>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.4/Chart.js"></script>
    <script>
      document.addEventListener('DOMContentLoaded', function () {
        const graphdata = JSON.parse('<%- (JSON.stringify(graphdata)) %>');

        new Chart('myChart', {
          type: 'scatter',
          data: {
            datasets: [
              {
                pointRadius: 4,
                pointBackgroundColor: 'rgb(0,0,255)',
                data: graphdata,
              },
            ],
          },
          options: {
            legend: { display: false },
            scales: {
              xAxes: [{ ticks: { min: 40, max: 160 } }],
              yAxes: [{ ticks: { min: 6, max: 16 } }],
            },
          },
        });
      });
    </script>
  </body>
</html>

Output:

Citations:

Accessing passed EJS variable in Javascript file

Update : 11th Jul 24