Execute SQL Query on a MySQL RDS Instance from a Web App with AWS Lambda as Backend

How to execute SQL query on a MYSQL RDS instance from an HTML-JavaScript-AJAX-based web app with AWS Lambda as a backend.

Let us start by creating a dataset for this demo, I am using Zomato Restaurants Hyderabad dataset. We’ll start by uploading the CSV to our RDS instance using MySQL workbench. This will quickly create a dataset for us to experiment with. Expand on schema_name → Tables → Right-click → Table data import wizard. Follow the self-explanatory steps. Run this select query and you should see your table in the DB.

We are going to query something like this from the web app →

SELECT cuisine FROM webdata.Restaurant where Name = ‘xxx’;

Let us create the backend service which will query the DB taking a parameter from the web app. Here is the lambda-code

Note: you need to install pymysql to connect to MYSQL instance. Simply install on your project folder —

pip install pymysql -t .

The project structure should look like this after installing pymysql —

├───lambda_function.py
├───pymysql
└───PyMySQL-0.9.3.dist-info

Python — lambda_function.py

import pymysql.cursors
#rds settings
rds_host  = "db_name.xxxx.us-east-1.rds.amazonaws.com"
name = "admin"
password = "pass"
db_name = "db_name"
conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
def lambda_handler(event, context):
    rest_name = event['queryStringParameters']['rest_name']
    cursor = conn.cursor()
    sql = "select Cuisines from webdata.Restaurant where Name='"+rest_name+"'"
    response = {}
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        print(result)
        response['status']=200
        response['Cuisines']=result[0]['Cuisines']
        response['message']="Status OK"
        
    except:
        response['status']=404
        response['Cuisines']="-"
        response['message']="Name Not Found"
        
    return response

The event input is to test —

{
  "queryStringParameters": {
    "rest_name": "Beyond Flavours"
  }
}

Here is the test output —

Response:
{
  "status": 200,
  "Cuisines": "Chinese, Continental, Kebab, European, South Indian, North Indian",
  "message": "Status OK"
}

Package this lambda_function.py and the pymysql installation files into a zip and upload it to lambda in aws.

Now we simply need to hit this microservice, fetch the response, and display in the web app. Proceed by attaching an API gateway to AWS lambda.

lambda-function-name-API
arn:aws:execute-api:us-east-1:xxxx:xxxx/*/*/lambda-function-name
Details
API endpoint: https://xxxxx.execute-api.us-east-1.amazonaws.com/default/query-db
API type: HTTP
Authorization: NONE
Cross-origin resource sharing (CORS): Yes
Enable detailed metrics: Yes
Method: ANY
Resource path: /lambda-function-name
Stage: default

Html —

<div class="row">
<div class="col-lg-4 text-center text-lg-left">
<input type="text" class="form-control" name="restName" id="rest_name" placeholder="Restaurant Name"  data-aos="fade-right" data-aos-delay="200" data-aos-offset="-500"  />
</div>
<div class="col-lg-8 text-center text-lg-left">
<p id="response"></p>
</div>
</div>

Javascript —

var typingTimer;                
var doneTypingInterval = 5000; // decrease this timer if you want
var $input = $('#rest_name');
$input.on('keyup', function () {
  clearTimeout(typingTimer);
  typingTimer = setTimeout(doneTyping, doneTypingInterval);
});
$input.on('keydown', function () {
  clearTimeout(typingTimer);
});
function doneTyping () {
    rest_name = document.getElementById("rest_name").value;
 if (rest_name) {
  var settings = {
   "url": "https://api-gateway/default/lambda-function-name?rest_name="+rest_name,
   "method": "GET",
   "timeout": 0,
  };
 
  $.ajax(settings).done(function (response) {
   update_response_text(response)
  });
 }
};
function update_response_text(response){
 if (response.status == 200){
  var text = "This restautant serves - " + response.Cuisines;
 } else {
  var text = response.message;
 }
 
 document.getElementById('response').innerText = text;
}

Demo —

Wow! I know its tasty. Go give it a try and drop me a 👏 if you liked it. Until next time save time — keep automating.

Add on —

1. If your RDS instance(MYSQL) is in a VPC and security groups have a long list of IPs that it can access, then add the same VPC and subnet to Lambda configuration. You can choose the subnet as per the region of the rds instance. It should look like the picture below.
2. Use the local endpoint of the vpc instead of the endpoint host to connect from the Workbench in your machine.

lambda vpc config

More content at plainenglish.io