AWS Lambda and SQL Server Integration

Question

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, application uses Sql Server. So I am not sure if AWS Lambda supports connection with Sql Server.

Any comments/ links will be helpful.

Solution

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 config
    const 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 Connection
        let pool = await sql.connect(config)

        // Query Database
        let 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 query
        console.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 Query
        console.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

Comments

[D Deshmane]

Can you please share URLs where I can read more on it. Also does Lambda supports C#.Net ? Thanks

[E.J. Brennan]

Lambda does not support C#. Just NodeJS, Java and Python at the current time. I have no links, but if you can code it in one of those 3 languages, you can run it in Lambda.

[D Deshmane]

If I can not write my Lambda function in C#, how I can connect to SQL DB then ? Is there any libraries available with JAVA, Python or NodeJS that I can use to do so ?

[E.J. Brennan]

you don't need C# to connect to sql server. Here is a python to sql server link, for example: pypi.python.org/pypi/pymssql

[D Deshmane]

OK. Thanks for all of the help !

[Lorien]

I want to clarify something here Lambda does indeed support c# but you have to make a .net Core Lambda. Well the previous answers are not wrong because c# was added as of December 1st 2016 and those answer where in October. aws.amazon.com/blogs/compute/…