Bind problem in SQL query in Node, Express, Mysql2 app

Question

I have been following a tutorial on setting up REST APIs in Node, using Express for an app that accesses an existing MariaDB database. My version only needs to read data and I have the DB co-located with the Node application (same host).

My goal for this entry-level example is to just access the data, using static SQL, so I can see it rendered in the web page by the JSON pritifier.

[Next, I want to present the data in a table (EJS?). Later, when I can get that to work, I'll add form controls (React?) to let a user specify start and end date bounds for the SQL query. Finally I'll aim to render the data as a line graph (D3js).]

The tutorial runs the web server successfully (it returns 'OK' on the base URL), but when I go to URL/solarData it tries an async function to getMultiple rows from the DB, it responds:

Bind parameters must not contain undefined. To pass SQL NULL specify JS null TypeError: Bind parameters must not contain undefined. To pass SQL NULL specify JS null
    at /SunnyData/solarViz/node_modules/mysql2/lib/connection.js:628:17
    at Array.forEach (<anonymous>)
    at Connection.execute (/SunnyData/solarViz/node_modules/mysql2/lib/connection.js:620:22)
    at /SunnyData/solarViz/node_modules/mysql2/promise.js:120:11
    at new Promise (<anonymous>)
    at PromiseConnection.execute (/SunnyData/solarViz/node_modules/mysql2/promise.js:117:12)
    at Object.query (/SunnyData/solarViz/services/db.js:6:40)
    at processTicksAndRejections (internal/process/task_queues.js:95:5)
    at async Object.getMultiple (/SunnyData/solarViz/services/solarData.js:7:16)
    at async /SunnyData/solarViz/routes/solarData.js:8:14
app.js:61

./app.js

const express = require('express');
const app = express();
const port = process.env.PORT || 3800;
const solarDataRouter = require('./routes/solarData');

app.use(express.json());
app.use(
  express.urlencoded({
    extended: true,
  })
);

app.get('/', (req, res) => {
  res.json({'message': 'ok'});
})

app.use('/solarData', solarDataRouter);

/* Error handler middleware */
app.use((err, req, res, next) => {
  const statusCode = err.statusCode || 500;
  console.error(err.message, err.stack);
  res.status(statusCode).json({'message': err.message});


  return;
});

app.listen(port, () => {
  console.log(`Example app listening at http://localhost:${port}`)
});

./routes/solarData.js

const express = require('express');
const router = express.Router();
const solarData = require('../services/solarData');

/* GET solar data. */
router.get('/', async function(req, res, next) {
  try {
    res.json(await solarData.getMultiple(req.query.page));
  } catch (err) {
    console.error(`Error while getting solar data `, err.message);
    next(err);
  }
});
module.exports = router;

./config.js

const env = process.env;

const config = {
  db: { 
    host: env.SUNNY_HOST,
    user: env.SUNNY_USER,
    password: env.SUNNY_PW,
    database: env.SUNNY_DB,
  },
  listPerPage: env.LIST_PER_PAGE,
};

module.exports = config;

./services/solarData.js

const db = require('./db');
const helper = require('../helper');
const config = require('../config');

async function getMultiple(page = 1){
  const offset = helper.getOffset(page, config.listPerPage);
  const rows = await db.query(
    `SELECT * FROM DTP LIMIT ?,?`, [offset, config.listPerPage]
  );
  const data = helper.emptyOrRows(rows);
  const meta = {page};

  return {
    data,
    meta
  }
}
module.exports.getMultiple = getMultiple;

./services/db.js

const mysql = require('mysql2/promise');
const config = require('../config');

async function query(sql, params) {
  const connection = await mysql.createConnection(config.db);
  const [results, ] = await connection.execute(sql, params);

  return results;
}

module.exports = {
  query
}

I've left out the ./helper.js

Everything runs fine until I direct the webpage to /solarData. At that point I get the Debug Console (vscode) mentioned up-front

Searching seems to point at a mysql2 shortcoming/bug but not at a practical solution If you respond, please describe the 'bind' mechanism, as I'm not sure what's going on.

Hope I've put enough info in. Please ask if I need to add anything else.

Answer 1

The error says

Bind parameters must not contain undefined.

It means that in the file ./services/solarData.js on the line

const rows = await db.query(
    `SELECT * FROM DTP LIMIT ?,?`, [offset, config.listPerPage]
  );

Some of the 2 variables is undefined, you need to check offset and config.listPerPage to be defined.

Just use

console.log('offset: ' + offset)
console.log('listPerPage: ' + config.listPerPage)

and you will find out what is undefined in your case