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.
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
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.
