CODEWITHSUNDEEP
sql-serveramazon-web-servicesaws-lambda
D Deshmane
D Deshmane

Reputation: 1265

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

Any comments/ links will be helpful.

Upvotes: 4

Views: 15754

Answers (2)

Sodman
Sodman

Reputation: 729

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

Upvotes: 1

E.J. Brennan
E.J. Brennan

Reputation: 46879

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.

Upvotes: 0

Related Questions