Connect to #MSSQL from #AWS #LAMBDA using #NODE
In order to connect to Microsoft SQL server from Node in AWS Lambda you need to include a reference to require(‘mssql’); – but how do you install NPM packages on AWS Lambda.
That’s where the AWS CLI comes in. Download it, and the first thing you do is configure it with an IAM user as follows
aws configure
which prompts;
AWS Access Key ID [****************]:
AWS Secret Access Key [****************]:
Default region name [None]: eu-west-1
Default output format [None]:
Now, get a list of lambda functions as follows;
aws lambda list-functions
You can get the function name from the json returned, then if you want to get a zip of your function call;
aws lambda get-function –function-name <Function Name>
The JSON will return a property “Location” which you use to download the zip file. Unzip the zip file, and navigate into the folder, then type
npm install mssql
Once complete, zip up the folder, and upload it back to AWS as follows
aws lambda update-function-code –function-name <Function Name> –zip-file “fileb://<ZIP Name>”
Now, you can create a file, database.js in your Lambda project as follows
const sql = require(‘mssql’);
(function() {
// ——- Database support ——- //
module.exports.execute = function(sqlQuery, callback)
{
const config = {
user: ‘xxxxxx’,
password: ‘xxxxx’,
server: ‘xxxx.xx.xx.xxx’,
database: ‘xxxxxx’,
};
sql.connect(config, (err) => {
if (err) {
console.log(err);
callback(err);
} else {
const req = new sql.Request();
req.query(sqlQuery, (error, result) => {
if (error) {
console.log(error);
callback(error);
} else {
console.log(result);
sql.close();
callback(null, result.recordset);
}
});
}
});sql.on(‘error’, (err) => {
console.log(err);
callback(err);
});};
}());
this is the only full example of MSSQL in AWS Lambda with Nodejs I have found. Unfortunately, I have been unable to get ANYTHING to work. Any ideas why this does not work?
module.exports.container = function(event, context, done) {
// change how the function waits to respond.
context.callbackWaitsForEmptyEventLoop = false;
let req = JSON.parse(event.body),
options = {
subscriptionId: _.get(req, ‘customerId’, _.get(req, ‘user.subscriptionId’)),
customerCd: _.get(req, ‘customer’),
userId: _.get(req, ‘user.userId’),
fromDate: _.get(req, ‘fromDate’),
toDate: _.get(req, ‘toDate’),
command: cmd.container
};
sql.connect(dbConfig, (err) => {
if (err) {
console.log(err);
done(err);
} else {
let request = new sql.Request();
request.input(‘subscriptionId’, sql.Int, options.subscriptionId);
request.input(‘customerCd’, sql.VarChar(10), options.customerCd);
request.input(‘fromDate’, sql.DateTime2, options.fromDate);
request.input(‘toDate’, sql.DateTime2, options.toDate);
request.execute(options.command, (error, result) => {
if (error) {
console.log(error);
done({
statusCode: 500,
body: JSON.stringify(error)
});
} else {
console.log(result);
sql.close();
done(null, {
statusCode: 200,
body: JSON.stringify(result.recordset)
});
}
});
}
});
sql.on(‘error’, (err) => {
console.log(err);
done({
statusCode: 500,
body: JSON.stringify(err)
});
});
};