Cant get past CORS error for AWS Lambda response on simple HTTP Api Gateway request

I currently have a very simple API request that returns data from MySQL. The current setup is Website calls API Gateway > invokes Lambda > returns data from MySQL Table > displays on website. With Postman, I can get the data returned, but when doing it through my JavaScript code I cannot get past some CORS errors. I think its less CORS and something with my lambda causing an error. These are the errors from console:My web side code to call the gateway is:

        const Http = new XMLHttpRequest();
        const url = 'https://nnebmzru3e.execute-api.us-east-2.amazonaws.com/proxy+';
    
        Http.open("OPTIONS", url);
        Http.setRequestHeader('Access-Control-Allow-Origin', '*')
    
    
        Http.send();
        console.log(Http.responseText)
        Http.onreadystatechange = (e) => {
            console.log(Http.responseText)
        }

My Lambda code is as follows:

const mysql = require("mysql");

// console.log(connection);
exports.handler = (event, context, callback) => {
  console.log(event);
  const connection = mysql.createConnection({
    host: process.env.RDS_HOSTNAME,
    user: process.env.RDS_USERNAME,
    password: process.env.RDS_PASSWORD,
    port: process.env.RDS_PORT,
  });

  connection.query(
    "SELECT * FROM pickemss.pickems where Week = 1",
    function (error, results, fields) {
      if (error) {
        console.log("ERROR DESTROYING CONNECTION");
        connection.end();
        throw error;
      } else {
        // connected!
        console.log("CONNECTED");
        connection.end(function (err) {
          callback(err, formatResponse(results));
        });
        // return formatResponse(results);
      }
    }
  );
};

var formatResponse = function (body) {
  console.log("Formatting response");
  var response = {
    statusCode: 200,
    headers: {
      "Access-Control-Allow-Origin": "*",
      "Access-Control-Allow-Headers":
        "Content-Type,X-Amz-Date,Authorization,X-Api-Key,X-Amz-Security-Token",
      "Access-Control-Allow-Methods": "*",
    },
  };
  response.body = JSON.stringify(body);
  console.log(response.body);

  return response;
};

Ive tried using a GET request on a different route, but have no clue at this point why its failing as in my cloudwatch logs there are no errors and i can see the data from the mysql query aswell.

It seems like your function is not working as intended and it doesn't return the CORS headers properly. To be honest, it was kind of hard to read the code too and although AWS lambda provides the 3rd parameter for function handlers (callback), it is generally not recommended to use it anymore as callback pattern is susceptible to callback hell, more prone to bugs and harder to read. To improve your situation, you can use the following code:

const mysql = require("mysql");

function getData(conn, query) {
  return new Promise((resolve, reject) => {
    conn.query(query, function (error, results) {
      if (error) {
        console.log("ERROR DESTROYING CONNECTION");
        conn.end();
        reject(error);
        return;
      }

      console.log("CONNECTED");
      conn.end();
      resolve(results);
    });
  });
}

function formatResponse(body) {
  const response = {
    statusCode: 200,
    headers: {
      "Access-Control-Allow-Origin": "*",
      "Access-Control-Allow-Headers":
        "Content-Type,X-Amz-Date,Authorization,X-Api-Key,X-Amz-Security-Token",
      "Access-Control-Allow-Methods": "*",
      body: JSON.stringify(body),
    },
  };

  return response;
}

exports.handler = async (event) => {
  console.log(event);
  const connection = mysql.createConnection({
    host: process.env.RDS_HOSTNAME,
    user: process.env.RDS_USERNAME,
    password: process.env.RDS_PASSWORD,
    port: process.env.RDS_PORT,
  });
  const query = "SELECT * FROM pickemss.pickems where Week = 1";
  const results = await getData(connection, query);

  return formatResponse({ results });
};