server.js :

const express = require('express');
const mysql = require('mysql2');
const path = require('path');
const app = express();

app.use(express.json());

const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'db_name',
});

// Connect to MySQL
db.connect((err) => {
  if (err) {
    console.error('Error connecting to the database:', err);
    return;
  }
  console.log('Connected to MySQL database');
});

// Serve static files (HTML, CSS, JS) from the 'public' folder
app.use(express.static('public'));

// Serve index.html at root
app.get('/', (req, res) => {
  res.sendFile(path.join(__dirname, 'public/index.html'));
});

// Serve home.html at /home
app.get('/home', (req, res) => {
  res.sendFile(path.join(__dirname, 'public/home.html'));
});

// Serve about.html at /about
app.get('/about', (req, res) => {
  res.sendFile(path.join(__dirname, 'public/about.html'));
});


app.get('/get-data', (req, res) => {
  db.query('SELECT * FROM Weather_Stations', (err, results) => {
    if (err) {
      res.status(500).send('Error fetching data from Weather_Stations');
      return;
    }
    res.json(results);
  });
});
// API endpoint to get station_id values from Weather_Stations for Weather_Data table
app.get('/get-station-ids', (req, res) => {
  db.query('SELECT station_id FROM Weather_Stations', (err, results) => {
    if (err) {
      console.error('Error fetching station IDs:', err);
      res.status(500).send('Error fetching station IDs');
      return;
    }
    res.json(results);
  });
});

// API endpoint to get data from Weather_Data
app.get('/get-weather-data', (req, res) => {
  db.query('SELECT * FROM Weather_Data', (err, results) => {
    if (err) {
      console.error('Error fetching Weather_Data:', err);
      res.status(500).send('Error fetching Weather_Data');
      return;
    }
    res.json(results);
  });
});
// --------------------------------- Genarate Weather ID --------------------------------------------
app.get('/generate-weatherData-id', async (req, res) => {
  try {
    const [result] = await db.query("CALL region_id_gen(?)", ['WD']);
    const generatedId = result[0]?.NextNumber || null; // Extract the ID from the result
    if (generatedId) {
      res.status(200).json({ weatherData_id: `WD-${generatedId}` });
    } else {
      res.status(500).json({ error: 'Failed to generate weatherData_id' });
    }
  } catch (error) {
    console.error("Error generating weatherData_id:", error);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// --------------------------------- Start the Server --------------------------------------------
app.use((req, res, next) => {
  console.log(`Request URL: ${req.url}`);
  next();
});

const PORT = 3000;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});

error:

Server is running on port 3000
Connected to MySQL database

You have tried to call .then(), .catch(), or invoked await on the result of query that is not a promise, which is a programming error. Try calling con.promise().query(), or require(‘mysql2/promise’) instead of ‘mysql2’ for a promise-compatible version of the query interface. To learn how to use async/await or Promises check out documentation at https://sidorares.github.io/node-mysql2/docs#using-promise-wrapper, or the mysql2 documentation at https://sidorares.github.io/node-mysql2/docs/documentation/promise-wrapper

Error generating weatherData_id: Error: You have tried to call .then(), .catch(), or invoked await on the result of query that is not a promise, which is a programming error. Try calling con.promise().query(), or require(‘mysql2/promise’) instead of ‘mysql2’ for a promise-compatible version of the query interface. To learn how to use async/await or Promises check out documentation at https://sidorares.github.io/node-mysql2/docs#using-promise-wrapper, or the mysql2 documentation at https://sidorares.github.io/node-mysql2/docs/documentation/promise-wrapper

at Query.then (C:UsersPramudith RanganaDocumentsJavaScript ProjectsmySqlDataShownode_modulesmysql2libcommandsquery.js:43:11)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)