I am struggling to get async working with Hapi and MySQL in Node.js

Question

I am updating an older node.js application which was pre-async/await and promise. I am struggling to get Hapi handler() working correctly with async/await. The code does not throw an error but I am not getting results returned to the browser window. I suspect the return values are not getting sent back.

method: "GET",
      path: "/getCountyByState/{state}",
      handler: async function (request, h) {
        var state = request.params.state;
        var sql = 'select county_name from static_web_data.state_counties where state_name = "' + state + '"';

        var pool = require("./database-promise");
        const myresult = await pool.query(sql, function(err, results, fields) {
          if (err) {
            console.log("Query threw exception: " + err);
            throw new Error(err);
          }

          // Do something with result.
          if (typeof results !== "undefined") {
            console.log("Query results:" + JSON.stringify(results));

            // This is a hack to work around returning a full JSON object which contains field names.
            var temp = [];
            results.forEach(function (item) {
              temp.push(item.county_name);
            });

            return temp;
          } else {
            return h.response("404 Error! Page Not Found!").code(404);
          }
        });
        return myresult;
      },

The database-promise.js is as follows:

const util = require('util');
const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: "localhost",
  user: "root",
  password: "secret",
  database: "static_web_data",
  connectTimeout: 0
});

// Ping database to check for common exception errors.
pool.getConnection((err, connection) => {
  if (err) {
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
      console.error('Database connection was closed.');
    }
    if (err.code === 'ER_CON_COUNT_ERROR') {
      console.error('Database has too many connections.');
    }
    if (err.code === 'ECONNREFUSED') {
      console.error('Database connection was refused.');
    }
  }

  if (connection) connection.release()

  return
});

// Promisify for Node.js async/await.
pool.query = util.promisify(pool.query);

module.exports = pool;

Answer 1

1. Although not the main issue, I think you'll need to bind the promisify, so pool.query = util.promisify(pool.query).bind(pool);

2. The main problem is that you're mixing await and callbacks. You'd want this, but please see point 4:

const myresult = await pool.query(sql);
// now you can handle the value of myresult. 

3. Move the import outside of the handler to the top level, so that var pool = require("./database-promise"); is not being called on every request. That way your connections can actually be pooled.

4. You're passing unsantised inputs directly to your SQL statement. You'll want to use prepared statements instead of string concatenation. So you'd want:

const myresult = await pool.query(
`select county_name from  static_web_data.state_counties where state_name = ?`, [state]);