`Cannot set headers after they are sent to client` error when using mysql2 async/await

Question

I am changing my backend (switching over from Mongo to MySql). I know the error is getting thrown because a return statement is being executed before my SQL query has finished and then when my sql query finishes, it tires to send another res.send, hence why I am trying to use mysql2 with Promise wrappers to use await on queries in my async function.

I have a separate file that creates the DB connection so I can access the connection throughout my Nodejs backend:

const mysql = require('mysql2');

async function pool(){
    const pool = await mysql.createPool({
        host: "ip",
        user: "username",
        password: "password",
        database: "db"
    });
    return pool
}

exports.getConnection = async function(callback) {
    const currentPool = await pool();
    currentPool.getConnection(function(err, conn) {
        if(err) return callback(err);
        callback(err,conn)
    });
};

Then, to create a query that follows async/await:

sql.getConnection(async function(err, client){
        client.query(`select email from users where email = "${email}"`, function (error, result){
            if(error) return res.status(500).send('an internal db error occurred');
            // carry on with code ...
    });
});

I've tried using await on the query too:

await sql.getConnection(async function(err, client){
        client.query(`select email from users where email = "${email}"`, function (error, result){
            if(error) return res.status(500).send('an internal db error occurred');
            // carry on with code ...
    });
});

What am I missing? I haven't tired to use the normal mysql NPM library and make my own promise wrapper yet...

NEW CODE:

I've updated my function:

const mysql = require('mysql2');

const pool = mysql.createPool({
    host: "ip",
    user: "user",
    password: "pass",
    database: "db"
});

exports.db = (sql) => {
    new Promise((resolve, reject) => {
        pool.getConnection((err, conn) => {
            if(err) return reject(err);

            conn.query(sql, (err, results, fields) => {
                conn.release()
                if(err) return reject(err)
                console.log(results)
                resolve(results);
            });
        });
    });
}

Then I call it via:

try{
    const emailExisit = await sql.db(`SELECT email FROM users WHERE email = "${email}"`);
    console.log(emailExisit);
    if(emailExisit.length > 0) return res.status(422).send({"data": "", "code": "105", "message": "An account with given email already exists"});
}catch (err) {
    console.log(err)
    return res.status(500).send({"data": "", "code": "108",  "message": `There seems to be an error contacting the database. Try again later <br> ${err}`});
}

However, my code still continues, leaving my emailExists variable undefined (yes, it is inside an async function)

Answer 1

This is my configuration to use MySQL with Node.js. I hope it works with you.

/config/mysql.js

const mysql = require('mysql2');

const pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  port: process.env.MYSQL_PORT,
  database: process.env.MYSQL_DB_NAME,
});

const query = (query, args = []) =>
  new Promise((resolve, reject) => {
    pool.getConnection((err, connection) => {
      if (err) {
        return reject(err);
      }
      connection.query(query, args, (err, results) => {
        connection.release();
        if (err) {
          return reject(err);
        }
        resolve(results);
      });
    });
  });

module.exports = { query };

---

/index.js

const { query } = require('./mysql');
const express = require('express');
const bodyParser = require('body-parser');

const app = express();

app.use(bodyParser.json());
app.use(bodyParser.urlencoded());

app.get('/api/v1/sum', (req, res) => {
  query('SELECT 1 + 1 as sum')
    .then(results => {
      res.json({ sum: results[0].sum });
    })
    .catch(err => {
      console.error(err);
      res.status(500).json({ error: 'error msg' });
    });
});

// anther example with async
app.get('/api/v1/sum', async (req, res) => {
  try {
    const results = await query('SELECT 1 + 1 as sum');
    res.json({ sum: results[0].sum });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'error msg' });
  }
});

app.listen(3000);