How to insert into mysql, then return JSON back to websocket client, using AWS Lambda node function

Question

I have an AWS WebSocket API using $connect, which calls a Lambda function to insert a connectionId string into a database.

If using DynamoDB, I would use a process like this:

const AWS = require('aws-sdk');
const ddb = new AWS.DynamoDB.DocumentClient({ apiVersion: '2012-08-10', region: process.env.AWS_REGION });

exports.handler = async event => {
  const putParams = {
    TableName: process.env.TABLE_NAME,
    Item: {
      connectionId: event.requestContext.connectionId
    }
  };
  try {
    await ddb.put(putParams).promise();
  } catch (err) {
    return { statusCode: 500, body: 'Failed to connect: ' + JSON.stringify(err) };
  }

  return { statusCode: 200, body: 'Connected.' };
};

This is vertified working. However, I have been tasked to use MySQL instead of Dynamo. I have the NodeJs mysql package deployed within Lambda already (and verified working), and the mysql config variables set in a config.json file.

When I try to return the json back to my websocket client, I received a 502 error with this CloudWatch error message: "Execution failed due to configuration error: Malformed Lambda proxy response" and also "Endpoint response body before transformations: null"

What do I need to change in the code below to:

a) insert a line into the MySQL table, and then b) return the JSON string back to my websocket client

var mysql = require('mysql');
var config = require('./config.json');

var pool  = mysql.createPool({
    host     : config.dbhost,
    user     : config.dbuser,
    password : config.dbpassword,
    database : config.dbname
}); 

exports.handler = async event => {
    pool.getConnection(function(err, connection) {
        if (err) {
            return { statusCode: 500, body: 'Failed to connect: ' + JSON.stringify(err) };
        }
        else {
            connection.query("insert into " + process.env.TABLE_NAME + " (`connectionId`) values (?)", [event.requestContext.connectionId], function(e, r) {
                if (e) { 
                    return { statusCode: 500, body: 'Failed to add connection id: ' + JSON.stringify(e) };
                }
                else {
                    return { statusCode: 200, body: 'Connected.' };
                }
            });
        }
    });
}

Answer 1

After further research, and some trial and error, I found that the below code works:

var mysql = require('mysql');
var config = require('./config.json');

var pool  = mysql.createPool({
    host     : config.dbhost,
    user     : config.dbuser,
    password : config.dbpassword,
    database : config.dbname
});

exports.handler = function(event, context, callback) {
    pool.getConnection(function(err, connection) {
      
    context.callbackWaitsForEmptyEventLoop = false;
      
    if ( err ) {
        callback(null, {
            statusCode: 500,
            body: "Failed to connect: " + JSON.stringify(err)
        });
    }    
       
    let qry = "insert into " + process.env.TABLE_NAME + " (`connectionId`) values (?)";
      
    connection.query(qry, 
      [event.requestContext.connectionId], 
      
      function(err, r) {
            connection.release();
                
            callback(null, {
                statusCode: err ? 500 : 200,
                body: err ? "Failed to connect: " + JSON.stringify(err) : "Connected"
            }); 
        });
    });
};