Querying a MySQL database from a NodeJS AWS Lambda Function

Question

I'm having a problem querying my MySQL database (hosted remotely from AWS) inside of my AWS Lambda function.

This is my code except for the parts I need for the rest of Lambda function (which is being called for an Alexa Skill):

  var mysql = require('mysql');
  var connection = mysql.createConnection({
        host     : '<myserver>',
        user     : '<myusername>',
        password : '<mypw>',
        database : '<mydatabase>'
  });
  connection.connect(function(err){
        if(!err) {
              console.log("Database is connected ... nn");
        }
        else {
              console.log("Error connecting database ... nn");
        }
  });

  connection.query("INSERT INTO Users (user_id) VALUES ('TESTNAME')");
  connection.end();

This works just fine when I run it with node from my command prompt:

node index.js

I'm using the "mysql" module installed via npm in the directory with index.js and zip it and upload it to my Lambda function.

Again, this works on my development machine, but gives no indicator when testing my Lambda function as to why it doesn't effect my database at all.

My question extends into Alexa and Lambda as much as it does the proper usage of the mysql Node.JS module.

Here's my current code for my Lambda, and the problem here, of course, is still that my test value -> a username called "TESTNAME" doesn't get added to my MySQL database.

I put the query into the connect callback as the first comment suggests, and I'm putting my new code instead of updating my old code above just to keep a record of what how I think the code should transition to being in my Alexa's Lambda function:

Updated code:

var mysql = require('mysql');
var connection = mysql.createConnection({
      host     : '<myserver>',
      user     : '<myusername>',
      password : '<mypw>',
      database : '<mydatabase>'
});
exports.handler = (event, context) => {
    try {

        if (event.session.new) {
            // New Session
            console.log("NEW SESSION");
        }


        switch (event.request.type) {

            case "LaunchRequest":
                // Launch Request
                console.log(`LAUNCH REQUEST`);
                context.succeed(
                    generateResponse({},
                        buildSpeechletResponse("Welcome to an Alexa Skill, this is running on a deployed lamda function", true)
                    )
                );
                break;

            case "IntentRequest":
                // Intent Request
                console.log(`Intent Request`);
                console.log('Then run MySQL code:');
                connection.connect(function(err) {
                    console.log('Inside connection.connect() callback');
                    if (!err) {
                        console.log("Database is connected ... ");
                        connection.query("INSERT INTO Users (user_id) VALUES ('TESTNAME')",
                            function(err, result) {
                                console.log("Inside connection.query() callback")
                                if (!err) {
                                    console.log("Query Successful! Ending Connectection.");
                                    connection.end();
                                } else {
                                    console.log("Query error!");
                                }
                            });
                    } else {
                        console.log("Error connecting database ..." + err.message);
                    }
                });
                context.succeed(
                    generateResponse({},
                        buildSpeechletResponse("Welcome to the incredible intelligent MySQLable Alexa!", true)
                    )
                );

                break;

            case "SessionEndedRequest":
                // Session Ended Request
                console.log(`SESSION ENDED REQUEST`);
                break;

            default:
                context.fail(`INVALID REQUEST TYPE: ${event.request.type}`);

        }

    } catch (error) {
        context.fail(`Exceptiodn: ${error}`)
    }

};

//Helpers
buildSpeechletResponse = (outputText, shouldEndSession) => {

    return {
        outputSpeech: {
            type: "PlainText",
            text: outputText
        },
        shouldEndSession: shouldEndSession
    };
};

generateResponse = (sessionAttributes, speechletResponse) => {
    return {
        version: "1.0",
        sessionAttributes: sessionAttributes,
        response: speechletResponse
    };
};

And my console output:

START RequestId: 5d4d17a7-0272-11e7-951c-b3d6944457e1 Version: $LATEST
2017-03-06T13:39:47.561Z    5d4d17a7-0272-11e7-951c-b3d6944457e1    Intent Request
2017-03-06T13:39:47.562Z    5d4d17a7-0272-11e7-951c-b3d6944457e1    Then run MySQL code:
END RequestId: 5d4d17a7-0272-11e7-951c-b3d6944457e1
REPORT RequestId: 5d4d17a7-0272-11e7-951c-b3d6944457e1  Duration: 82.48 ms  Billed Duration: 100 ms     Memory Size: 128 MB Max Memory Used: 14 MB  

Answer 1

The problem was that I needed to put my context.succeed inside of my callbacks. Many thanks to sqlbot, as his talk of callbacks led me to study where things were actually ending their execution.

So apparently when using AWS Lambda, if the "context" ends before your callbacks get called, you don't get your callbacks. So even though I had placed all of my callbacks like so: connect -> query -> end, the first callback of the chain from connect never gets called because "context.succeed" was getting called right afterwards, which ended execution.

Here's my code as of now (getting a proper query happening now):

var mysql = require('mysql');
var connection = mysql.createConnection({
    ...
});

exports.handler = (event, context) => {
    try {

        if (event.session.new) {
            // New Session
            console.log("NEW SESSION");
        }


        switch (event.request.type) {

            case "LaunchRequest":
                // Launch Request
                console.log(`LAUNCH REQUEST`);
                context.succeed(
                    generateResponse({},
                        buildSpeechletResponse("Welcome to an Alexa Skill, this is running on a deployed lamda function", true)
                    )
                );
                break;

            case "IntentRequest":
                // Intent Request
                console.log(`Intent Request`);
                console.log('Then run MySQL code:');
                connection.connect(function(err) {
                    console.log('Inside connection.connect() callback');
                    if (!err) {
                        console.log("Database is connected ... ");
                        connection.query("INSERT INTO Users (user_id) VALUES ('TESTNAME')",
                            function(err, result) {
                                console.log("Inside connection.query() callback")
                                if (!err) {
                                    console.log("Query Successful! Ending Connection.");
                                    connection.end();
                                } else {
                                    console.log("Query error!");
                                }
                            });
                    } else {
                        console.log("Error connecting database ..." + err.message);
                    }
                    context.succeed(
                        generateResponse({},
                            buildSpeechletResponse("Welcome to the incredible intelligent MySQLable Alexa!", true)
                        )
                    );
                });

                break;

            case "SessionEndedRequest":
                // Session Ended Request
                console.log(`SESSION ENDED REQUEST`);
                break;

            default:
                context.fail(`INVALID REQUEST TYPE: ${event.request.type}`);

        }

    } catch (error) {
        context.fail(`Exceptiodn: ${error}`)
    }

};

//Helpers
buildSpeechletResponse = (outputText, shouldEndSession) => {

    return {
        outputSpeech: {
            type: "PlainText",
            text: outputText
        },
        shouldEndSession: shouldEndSession
    };
};

generateResponse = (sessionAttributes, speechletResponse) => {
    return {
        version: "1.0",
        sessionAttributes: sessionAttributes,
        response: speechletResponse
    };
};