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
