Aws Lambda And Sql Server Integration

My application uses MS Sql Server. Now after knowing about AWS Lambda, I want to shift my application to AWS Lambda to have server-less architecture. But as I mentioned, applicatio

Solution 1:

Here is some example boilerplate with comments to connect to an MS SQL Server database from Lambda (Assuming using NodeJS as your language).

const sql = require('mssql');

exports.handler = async (event, context, callback) => {

    let lookupValue = event.lookupValue;

    // Take DB Config from environment variables set in Lambda configconst config = {
        user: process.env.DB_USERNAME,
        password: process.env.DB_PASSWORD,
        server: process.env.DB_SERVER,
        database: process.env.DB_DATABASE,
        options: {
            encrypt: true// Use this if you're on Windows Azure
        }
    }

    try {
        // Open DB Connectionlet pool = await sql.connect(config)

        // Query Databaselet result = await pool.request()
            .input('lookupValue', sql.Int, lookupValue)
            .query('select * from exampleTable where id = @lookupValue');

        // Close DB Connection
        pool.close();

        // The results of our queryconsole.log("Results:", result.recordset);

        // Use callback if you need to return values from your lambda function.// Callback takes (error, response?) as params.callback(null, result.recordset);
    } catch (err) {
        // Error running our SQL Queryconsole.error("ERROR: Exception thrown running SQL", err);
    }

    sql.on('error', err =>console.error(err, "ERROR: Error raised in MSSQL utility"));
}

Note: You will need to upload to your function the node_modules required to run mssql. The easiest way I've found to do this is to zip up the whole folder (your main [usually index.js] function file, along with package.json and your node_modules folder), and then upload it using the aws-cli:

aws lambda update-function-code--function-name your-function-name-here --zip-file your-zipped-project-directory.zip

Finally, make sure that your database is able to accept connections from the AWS Lambda function. The best way to do this is to use a combination of AWS's VPC, NAT, and Elastic IP settings - and example of which is described in this blog post: https://medium.com/@matthewleak/aws-lambda-functions-with-a-static-ip-89a3ada0b471

Solution 2:

Yes, you can connect from lambda to sql server, and as far as I know, just about anything other database. There are very few limitations on what you can do inside a lambda function.