postgresqlamazon-web-servicesaws-lambdaaws-api-gateway

Reputation: 393

How to get data from Amazon RDS using a Lambda function in Node.js?

I have a database in Aurora PostgreSQL and I’m using an API Gateway to invoke the Lambda functions made in Node.js for the APIs. Here is my code for a simple GET request with no URL parameters:

var pg = require("pg");

exports.handler = function(event, context) {

    var conn = “//Connection string";
    var client = new pg.Client(conn);
    client.connect();

    //var id = event.id;
    console.log('Connected to PostgreSQL database');
    var query = client.query("SELECT * from USERS;");
    query.on("row", function (row, result) {
        result.addRow(row);
    });
    
    query.on("end", function (result) {
        var jsonString = JSON.stringify(result.rows);
        var jsonObj = JSON.parse(jsonString);
        console.log(jsonString);
        client.end();
        context.succeed(jsonObj);
    });
};

I am able to get all the records from this table successfully. What changes must I make to the code and to the API Gateway itself to make a GET request with a parameter for a WHERE clause to select a specific user from their username, and a POST request to insert new users into the table?

Upvotes: 2

Answers (3)

Ajay

Reputation: 197

Accessing table data from RDS using lambda function with encrypted key (KMS) and Environment variable

Step 1 :- First Enable key in KMS(Key Management Service (KMS))

Review your key Policy and Done! with KMS creation

{
"Id": "key-consolepolicy-3",
"Version": "2012-10-17",
"Statement": [
    {
        "Sid": "Enable IAM User Permissions",
        "Effect": "Allow",
        "Principal": {
            "AWS": "arn:aws:iam::163806924483:root"
        },
        "Action": "kms:*",
        "Resource": "*"
    },
    {
        "Sid": "Allow access for Key Administrators",
        "Effect": "Allow",
        "Principal": {
            "AWS": "arn:aws:iam::163806924483:user/[email protected]"
        },
        "Action": [
            "kms:Create*",
            "kms:Describe*",
            "kms:Enable*",
            "kms:List*",
            "kms:Put*",
            "kms:Update*",
            "kms:Revoke*",
            "kms:Disable*",
            "kms:Get*",
            "kms:Delete*",
            "kms:TagResource",
            "kms:UntagResource",
            "kms:ScheduleKeyDeletion",
            "kms:CancelKeyDeletion"
        ],
        "Resource": "*"
    },
    {
        "Sid": "Allow use of the key",
        "Effect": "Allow",
        "Principal": {
            "AWS": [
                "arn:aws:iam::163806924483:user/[email protected]",
                "arn:aws:iam::163806924483:user/[email protected]",
                "arn:aws:iam::163806924483:user/[email protected]"
            ]
        },
        "Action": [
            "kms:Encrypt",
            "kms:Decrypt",
            "kms:ReEncrypt*",
            "kms:GenerateDataKey*",
            "kms:DescribeKey"
        ],
        "Resource": "*"
    },
    {
        "Sid": "Allow attachment of persistent resources",
        "Effect": "Allow",
        "Principal": {
            "AWS": [
                "arn:aws:iam::163806924483:user/[email protected]",
                "arn:aws:iam::163806924483:user/[email protected]",
                "arn:aws:iam::163806924483:user/[email protected]"
            ]
        },
        "Action": [
            "kms:CreateGrant",
            "kms:ListGrants",
            "kms:RevokeGrant"
        ],
        "Resource": "*",
        "Condition": {
            "Bool": {
                "kms:GrantIsForAWSResource": "true"
            }
        }
    }
]
}

Step:- 2 Create a policy in IAM for KMS assign to ur each lambda function

"StringEquals": {
            "kms:EncryptionContext:LambdaFunctionName": [
                "LambdaFunction-1",
                "LambdaFunction-2",
                "LambdaFunction-3"
            ]
        }

Step 3:- Assign a Policy created in Step-2 to ur default lambda Role(1st Lambda need to be created to get default lambda role)

Step 4:- Create lambda Function

Node.js Code for lambda Function

const mysql = require('mysql');
const aws = require("aws-sdk");


const functionName = process.env.AWS_LAMBDA_FUNCTION_NAME;
let res;
let response={};
exports.handler = async(event) => {
reset_globals();

// load env variables
const rds_user = await kms_decrypt(process.env.RDS_USERNAME);
const rds_pwd = await kms_decrypt(process.env.RDS_PASSWORD)

// setup rds connection
var db_connection = await mysql.createConnection({
    host: process.env.RDS_HOSTNAME,
    user: rds_user,
    password: rds_pwd,
    port: process.env.RDS_PORT,
    database: process.env.RDS_DATABASE
});

var sqlQuery = `SELECT doc_id from documents`;
await getValues(db_connection,sqlQuery);

}

async function getValues(db_connection,sql) {
await new Promise((resolve, reject) => {
    db_connection.query(sql, function (err, result) {
        if (err) {
            response = {statusCode: 500, body:{message:"Database Connection Failed", 
             error: err}};
            console.log(response);
            resolve();
        }
        else {
           
            console.log("Number of records retrieved: " + JSON.stringify(result));
            res = result;
           
            resolve();
        }
    });
    });
}

async function kms_decrypt(encrypted) {
const kms = new aws.KMS();
const req = { CiphertextBlob: Buffer.from(encrypted, 'base64'), EncryptionContext: { 
LambdaFunctionName: functionName } };
const decrypted = await kms.decrypt(req).promise();
let cred = decrypted.Plaintext.toString('ascii');
return cred;
}


function reset_globals() {
res = (function () { return; })();
response = {};
}

Now u should see KMS in Lambda.

Step 5:- Set Environment Variable and encrypt it.

Lambda ->Functions -> Configuration -> Environment Variable -> Edit

RDS_DATABASE docrds

RDS_HOSTNAME docrds-library.c1k3kcldebmp.us-east-1.rds.amazonaws.com

RDS_PASSWORD root123

RDS_PORT 3306

RDS_USERNAME admin

In Lambda Function to decrypt the encrypted environment variabled use below code

function kms_decrypt(encrypted) {
const kms = new aws.KMS();
const req = { CiphertextBlob: Buffer.from(encrypted, 'base64'), EncryptionContext: { 
LambdaFunctionName: functionName } };
const decrypted = await kms.decrypt(req).promise();
let cred = decrypted.Plaintext.toString('ascii');
return cred;
}

My RDS document table looks like:-

I am accessing column doc_id using sqlQuery in lambda function

var sqlQuery = `SELECT doc_id from documents`;

After testing the lambda function, I get below output.

If u gets SQL import Error, then can must add a layer.

errorType": "Runtime.ImportModuleError",
"errorMessage": "Error: Cannot find module 'mysql'\nRequire stack:\n- 
/var/task/index.js\n- /var/runtime/UserFunction.js\n- /var/runtime/index.js",
 "trace": [
"Runtime.ImportModuleError: Error: Cannot find module 'mysql'",

You can configure your Lambda function to use additional code and content in the form of layers. A layer is a ZIP archive that contains libraries, a custom runtime, or other dependencies. With layers, you can use libraries in your function without needing to include them in your deployment package.

To include libraries in a layer, place them in the directory structure that corresponds to your programming language.

Node.js – nodejs/node_modules

Python – python

Ruby – ruby/gems/2.5.0

Java – java/lib

First create a zip archieve that contain mysql archieve.

First create a folder with name same as lambda function in any react-project . Let say for example:- mkdir lambda-function

Then in terminal $project-path > lambda-function-folder > # npm init

Then $project-path > lambda-function-folder > # npm install mysql

You should see node_modules folder created.

Create another folder name nodejs inside lambda-function-folder [mkdir nodejs]

Move the node_modules inside nodejs folder

Zip nodejs folder [nodejs.zip] and upload on layer as shown below.

Then, Goto Lambda--> Layer-->Create layer.

Upvotes: 1

smac2020

Reputation: 10734

You can code your Lambda function to connect to a RDS database. Now when you do this, there are some things to consider.

You need to configure your Lambda function to use the same VPC as the security group that RDS is using. This is discussed here: https://docs.aws.amazon.com/lambda/latest/dg/configuration-vpc.html.
Next from your Lambda function, you still have to set a connection to the database. How this is done is dependent on the programming language that you are using. For example, if you are using the Lambda Java runtime API, you can use a Java connection. See below.

Once you do this - you can connect to RDS and perform SQL statements. This makes it possible to write Lambda functions that can query data from RDS and then use that Lambda function within a larger cloud based workflow using AWS Step Functions.

Code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
    
public class ConnectionHelper {
    
    private String url;
    private static ConnectionHelper instance;
   
    private ConnectionHelper() {
            url = "jdbc:mysql://formit.xxxxxxshym6k.us-west-2.rds.amazonaws.com:3306/mydb?useSSL=false";
        }
    
        public static Connection getConnection() throws SQLException {
            if (instance == null) {
                instance = new ConnectionHelper();
            }
            try {
    
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                return DriverManager.getConnection(instance.url, "root","root1234");
            } catch (SQLException | ClassNotFoundException | InstantiationException | IllegalAccessException e) {
                e.getStackTrace();
            }
            return null;
        }
        public static void close(Connection connection) {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Upvotes: 2

Marcin

Reputation: 238517

For proxy lambda integration, all the GET and POST parameters submitted into the API Gateway will be available in in the event object. Thus, you have to get the values submitted for WHERE and INSERT from the event.

The event structure is shown in:

Input format of a Lambda function for proxy integration

You will also need to ensure correct return data from the lambda. Return data also requires proper format:

Output format of a Lambda function for proxy integration

Upvotes: 2

How to get data from Amazon RDS using a Lambda function in Node.js?

Answers (3)

Related Questions