NodeJS Lambda Return result from inside a promise

I have a NodeJS lambda function that uses the mysql library for selecting data and retrieve it as a result. It gets triggered via an API Gateway http call.

But I can't seem to find a way to return the result of the query as the response of the call.

This is the sample code:

import { createRequire } from 'module';
const require = createRequire(import.meta.url);

const mysql = require('mysql');

const con = mysql.createConnection({
  host: process.env.RDS_HOSTNAME,
  user: process.env.RDS_USERNAME,
  password: process.env.RDS_PASSWORD,
  port: process.env.RDS_PORT,
  database: process.env.RDS_DATABASE,
});

const tableName = 'fit_guide';

export const handler = async (event, context, callback) => {
  let response = {};
  const routeKey = event.routeKey;

  let sql = `SELECT * FROM ${tableName}`;
  con.query(sql, function (error, results, fields) {
    if (error) throw error;
    // THIS DOES PRINT THE RESULTS FROM THE DATABASE TABLE
    console.log('******** ', results);
    // THIS DOESN'T RETURN
    response = {
      statusCode: 200,
      success: true,
      results,
    };
    callback(null, JSON.stringify(response));
    return response;
  });
  con.end();

  // THIS RETURNS AN EMPTY OBJECT {} (initial value)
  callback(null, JSON.stringify(response));
  return response;
};

How can I return the response inside the query execution rather than the initial value of the object?

TL; DR Your handler has a race condition. The return response; line returns {} before your query and the Lambda callback have a chance to execute. The easist fix is to remove return response;.

The race condition exists because your code mixes the Lambda Non-Async (callback, no return value) and Async (no callback, return Promise<Something>) handler patterns. As currently written, the return value is winning the race against the callback. You should use one pattern or the other, not both.

Here is a demo with a race condition like yours (Don't try this at home, kids!). As written, the message will be Callback wins! because the callback fires with a short delay, while the Promise resolves only after 1 second. Add a 2 second callback delay to get Promise return wins!. Of course, the fix is to remove one or the other, unless you really enjoy a race.

export const raceHandler = async (
  context: Context,
  event: APIGatewayProxyEventV2,
  callback: APIGatewayProxyCallbackV2
): Promise<APIGatewayProxyResultV2> => {
  
  // Race contestant 1:  callback with delay - simulates your MySQL query
  setTimeout(() => {
    callback(null, {
      statusCode: 200,
      body: JSON.stringify({ message: "Callback wins!" }),
    });
  }, 10);

  // Race contestant 2:  Promise return resolution with delay
  return new Promise((resolve) => {
    setTimeout(() => {
      resolve({
        statusCode: 200,
        body: JSON.stringify({ message: "Promise return wins!" }),
      });
    }, 1000);
  });
};