Home > Uncategorized > Connect to #MSSQL from #AWS #LAMBDA using #NODE

Connect to #MSSQL from #AWS #LAMBDA using #NODE

1_fMm7C03LaAEgngRJWtgHwg

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);
});

};
}());

 

 

Advertisements
Categories: Uncategorized
  1. Jay Koeding
    February 28, 2019 at 2:53 am

    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)
    });
    });
    };

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: