从NodeJS AWS Lambda函数查询MySQL数据库 [英] Querying a MySQL database from a NodeJS AWS Lambda Function
问题描述
在我的AWS Lambda函数中查询MySQL数据库(从AWS远程托管)时遇到问题.
这是我的代码，除了剩下的Lambda函数所需的部分(被称为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();
当我在命令提示符下使用node运行它时，它工作得很好:
node index.js
我正在使用通过npm安装在带有index.js的目录中的"mysql"模块，并将其压缩并上传到我的Lambda函数中.
同样，它可以在我的开发机器上运行，但是在测试Lambda函数时没有给出任何指示，说明为什么它根本不影响数据库.
我的问题已扩展到Alexa和Lambda中，就像它对MySQL Node.JS模块的正确使用一样.
这是我当前用于Lambda的代码，这里的问题当然仍然是我的测试值->名为"TESTNAME"的用户名没有添加到我的MySQL数据库中.
我将查询放入第一个注释所建议的connect回调中，并且我将新代码放入而不是在上面更新旧代码，只是为了记录我认为代码应如何过渡到我的代码中Alexa的Lambda函数:
更新的代码:
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
};
};
我的控制台输出:
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
问题是我需要将context.succeed放入回调中.非常感谢sqlbot，因为他对回调的讨论使我研究了实际结束执行的地方.
因此，显然在使用AWS Lambda时，如果上下文"在调用回调之前结束，则您不会获得回调.因此，即使我已经像这样放置了所有回调:connect-> query-> end，但从不调用connect链中的第一个回调，因为之后立即调用了"context.succeed"，从而结束了执行.
这是我到目前为止的代码(现在正在进行正确的查询):
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
};
};
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
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
};
};
这篇关于从NodeJS AWS Lambda函数查询MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
