How to use param passing in Lambda / API Gateway

Question

I am trying to build an API using API Gateway, Lambda, and RDS. So you type in the URL and get the specific rows from RDS back in a JSON format.

So this is what I would like to achieve.

• /production/myfootballapi?table=football -> all rows in the table should be returned (not working)
• /production/myfootballapi?table=football&id=2 -> only the id 2 row should be returned (only this case works)

The main problem that I am having is that I currently can't show all rows because I always have to define the id otherwise an error occurs.

My API Gateway Body Mapping Template:

{
"table" : "$input.params('table')",
"id" : $input.params('id')
}

My Lambda function

var mysql = require('mysql2');
var config = require('./config.json');

var pool  = mysql.createPool({
host     : config.dbhost,
user     : config.dbuser,
password : config.dbpassword,
database : config.dbname,
});


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


//prevent timeout from waiting event loop
context.callbackWaitsForEmptyEventLoop = false;

pool.getConnection(function(err, connection) {
if (err) throw err; 
// Use the connection

var table = event.table;
var id = event.id;

// This if-function is just a try to solve this problem but it doesn't work
if (id !== null) { 
  var sql = "SELECT * FROM ?? WHERE id = ?";
  var inserts = [table, id];

} else {
  var sql = "SELECT * FROM ?";
  var inserts = table;
}

sql = mysql.format(sql, inserts);

connection.query(sql, function (error, results, fields) {
  // And done with the connection.
  connection.release();
  // Handle error after the release.
  if (error) callback(error);
  else callback(null,results);
  });
});
};

If you need further information, please leave a comment.

THANKS!

Answer 1

You should change the if-else statement to :

if (id !== "") { 
  var sql = "SELECT * FROM ?? WHERE id = ?";
  var inserts = [table, id];
} else {
  var sql = "SELECT * FROM ?";
  var inserts = table;
}

Since you have set the body mapping template the id will be set to "" when you don't pass it as parameter. You can see its value in logs or you can try to log the id before you enter the if-else statement.

Answer 2

First of all, many thanks to everyone for helping me. I fixed it myself but I will also document this if someone has a similar question.

> API Gateway <

Go to Integration Request

Add the following Body Mapping Template

{
#set($queryMap = $input.params().querystring)

#foreach( $key in $queryMap.keySet())
  "$key" : "$queryMap.get($key)"
  #if($foreach.hasNext),#end
#end
}

Go to Method Request

Add the parameters

> Lambda <

var mysql = require('mysql2');
var config = require('./config.json');

var pool  = mysql.createPool({
    host     : config.dbhost,
    user     : config.dbuser,
    password : config.dbpassword,
    database : config.dbname,
  });


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

  //prevent timeout from waiting event loop
  context.callbackWaitsForEmptyEventLoop = false;

  pool.getConnection(function(err, connection) {
    if (err) throw err; 
    // Use the connection

   var table = event.table;
   var id = event.id;

    if (isNaN(id)) { 
       var sql = "SELECT * FROM ??";
       var inserts = table;
     } else {
       var sql = "SELECT * FROM ?? WHERE id = ?";
       var inserts = [table, id];
     }

    sql = mysql.format(sql, inserts);

  connection.query(sql, function (error, results, fields) {
      // And done with the connection.
      connection.release();
      // Handle error after the release.
      if (error) callback(error);
      else callback(null,results);
    });
  });
};

Source: https://aws.amazon.com/de/blogs/compute/using-api-gateway-mapping-templates-to-handle-changes-in-your-back-end-apis/

I think, it is probably not the best code in lambda but it works for now. I will improve it later. :D

Answer 3

I believe your problem may be your if (id !== null) statement. I mocked up your example, added console.log(sql); and found that even when no id was sent in the request, the SQL query returned was SELECT * FROM '\"Customer\"' WHERE id = NULL.

I changed your if statement to the following.

if (id) { 
  var sql = "SELECT * FROM ?? WHERE id = ?";
  var inserts = [table, id];

} else {
  var sql = "SELECT * FROM ?";
  var inserts = table;
}

After making this change, the SQL query was SELECT * FROM '\"Customer\"' as expected when no ID is in the request.

For completeness, I did make one other modification. I did not use the Body Mapping Template at all. I used the "Use Lambda Proxy Integration" setting in the Integration Request configuration instead. This will eliminate the need to use Body Mapping Templates.

In the Method Request configuration, define the URL Query String Parameters

In your Lambda function, you will be able to access your table and id as event.queryStringParameters.table and event.queryStringParameters.id respectively. Then perform whatever actions you want in your Lambda function.

var table = JSON.stringify(event.queryStringParameters.table);
var id = JSON.stringify(event.queryStringParameters.id);

console.log("Table: " + table);
console.log("ID: " +id);

----------- EDITED ---------------

Make sure your callback in your Lambda function sends a valid response that can be handled by the API gateway. Here is how I formatted my sample code that I mocked up to test this.

var response = {
    "statusCode": 200,
    "headers": {
        "my_header": "my_value"
    },
    "body": JSON.stringify(results),
    "isBase64Encoded": false
};
callback(null, response);

Hope this helps.

Answer 4

I think your mapping template is probably too simple. If you get that to work I'd love to know how because my template requires a great deal of code and it was a pain to get right but I've accepted it. I think without more processing you'll probably run into other issues when certain values or put into your parameters. You'll have to deal with escaping etc etc.

This is the template I use to map API gateway to lambda. I pulled it out of my cloudformation template so forgive me if random json or extra quotes are still present. If you rather see the cloudformation template I can show that.

#set($allParams = $input.params()) {
       \"params\" : {
                #foreach($type in $allParams.keySet())
                #set($params = $allParams.get($type))
                \"$type\" : {
                    #foreach($paramName in $params.keySet())
                   \"$paramName\" : 
                   \"$util.escapeJavaScript($params.get($paramName))\
                    if($foreach.hasNext),#end\
                   #end
                }\
                #if($foreach.hasNext),#end
                #end\
       }
}