# AWS Lambda - Query Amazon RDS using Secrets

> TLDR: Check out the complete code on  [Github](https://github.com/fourgates/aws-cdk-rds-lambda-ssm/tree/main/src/get-query).

In this post, we will use [AWS Lambda](https://aws.amazon.com/lambda/),  [AWS Secrets Manager](https://aws.amazon.com/secrets-manager/), and the [node  pg](https://github.com/brianc/node-postgres) package. I'll show you how to query a [Postgres](https://www.postgresql.org) database using these resources. 

In my [previous post](https://blog.phillipninan.com/provision-an-rds-instance-using-the-aws-cdk-and-secrets), we used the [AWS CDK](https://aws.amazon.com/cdk/) to spin up a Virtual Private Cloud ([VPC](https://aws.amazon.com/vpc/)) and a Relational Database Service ([RDS](https://aws.amazon.com/rds/)) Postgres database. We stored all the information we needed to log in to our RDS instance in AWS Secrets Manager. 

![query-rds-lambda.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1627356785299/mwfiV8dBA.png)

This is what we will be building. ☝🏽

## Word to The Wise
Using a serverless technology to query a relational database should be used cautiously. Constantly opening and closing database connections may be costly. Please read my [Relational Database Serverless Notes](#relational-database-serverless-notes) if you plan on uses a serverless technology to heavily query a relational database. My use case was a CRON that would execute periodically to check for errors. 

> Note, if your database is not publically accessible this Lambda will need to be deployed in the VPC with proper [VPC endpoints](https://docs.aws.amazon.com/vpc/latest/privatelink/vpc-endpoints.html) setup. I will be writing this up in a subsequent blog post. Please read this [AWS document](https://aws.amazon.com/premiumsupport/knowledge-center/connect-lambda-to-an-rds-instance/). It details the networking needed two allow communication between AWS Lambda and an RDS instance.

## ☁️ Contents ☁️
- [1. Initialize the Project](#1-initialize-the-project)
- [2. Create Folders for Lambda Handler (Optional)](#2-create-folders-for-lambda-handler--optional-)
- [3. Install `pg`](#3-install--pg-)
- [4. Create a Lambda Handler](#4-create-a-lambda-handler)
- [5. Load Secret](#5-load-secret)
- [6. Create Database Connection](#6-create-database-connection)
- [7. Query the Database](#7-query-the-database)
- [8. Deploy](#8-deploy)
- [Relational Database Serverless Notes](#relational-database-serverless-notes)
- [Conclusion](#conclusion)

## 1. Initialize the Project
You can follow my [previous post](https://blog.phillipninan.com/provision-an-rds-instance-using-the-aws-cdk-and-secrets) on how to deploy a VPC and RDS database using the [AWS CDK](https://aws.amazon.com/cdk/). Also, I have a post on using [AWS Serverless Application Model (SAM)](https://blog.phillipninan.com/deploy-a-lambda-function-using-aws-sam-in-5-minutes). You can use this to start a boilerplate Lambda in 5 minutes, but that is not required.

## 2. Create Folders for Lambda Handler (Optional)
If you are following along with the AWS CDK blog post, we will be writing the Lambda function code in `src/get-query`. This folder will contain `app.js`. This is the code that the Lambda is going to execute. There will also be `package.json` and `node_modules` to store dependencies. (We could use a Lambda layer, but I'll leave that for a future post)
```
mkdir -p src/get-query
touch app.js
npm init
```

## 3. Install `pg`
```
npm i pg
```

## 4. Create a Lambda Handler
The Lambda handler is the code that gets executed when a Lambda function is invoked. Let's start with boilerplate Lambda code. We are going to import the `pg` client to connect and query the database. Then we will instantiate a `SecretsManager` client to get the database info.

```
const pg = require('pg')
var AWS = require('aws-sdk'),
    region = "us-east-1",
    secretParams,
    pgClient;

// Create a Secrets Manager client
var client = new AWS.SecretsManager({
    region: region
});

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

    return {
        "isBase64Encoded": false,
        "statusCode": 200,
        "body": "hello!"
    }
}

```

## 5. Load Secret
> If you are not following along with my [AWS CDK post](https://blog.phillipninan.com/provision-an-rds-instance-using-the-aws-cdk-and-secrets) you can follow [this tutorial](https://docs.aws.amazon.com/secretsmanager/latest/userguide/tutorials_basic.html) on how to add a secret to AWS Secrets Manager. 

Next, let's update the handler code. We will load the secret from Secrets Manager. We use a reference from an environment variable containing the name of our secret. You only need to do this if the Lambda is cold starting. Otherwise, we will reuse the cached credentials. Note, if you are deploying this in a private VPC you will need to deploy an [SSM VPC Endpoint](https://docs.aws.amazon.com/systems-manager/latest/userguide/setup-create-vpc.html).
```
    // cache the secret
    if(!secretParams){
        // get secret string from secret manager
        const secretValue = await client.getSecretValue({ SecretId: process.env.SECRET_NAME }).promise();
        secretParams = JSON.parse(secretValue.SecretString);
    }
```

## 6. Create Database Connection
Next, use the `secretParams` and `pg` to make a database connection! Again, the client will be reused on subsequent executions.
```
    // cache the client
    if(!pgClient){
        // https://node-postgres.com/api/client
        var connectionInfo = {
            user: secretParams.username,
            password: secretParams.password,
            host: secretParams.host,
            database: "postgres",
            port: secretParams.port
        };
        pgClient = new pg.Client(connectionInfo);
        await pgClient.connect();
    }
```

## 7. Query the Database
Finally, you can use the client to perform a query and return it to the user!

```
    const queryResult = await pgClient.query("SELECT 'Eli' as player_name");
    return {
        "isBase64Encoded": false,
        "statusCode": 200,
        "body": JSON.stringify(queryResult.rows ? queryResult.rows : undefined)
    }
```

## 8. Deploy
To deploy you can read [my post to use AWS SAM](https://blog.phillipninan.com/deploy-a-lambda-function-using-aws-sam-in-5-minutes), [The Serverless Framework](https://www.serverless.com/framework/docs/providers/aws/guide/deploying/), or manually copy and paste the code using the [AWS Console](https://www.serverless.com/framework/docs/providers/aws/guide/deploying/). 

## Relational Database Serverless Notes
Using a serverless technology to query a relational database can cause a lot of overhead with managing connections. If this is done on a regular basis it may be worth having a layer in between your Lambda and RDS. You can use [RDS Proxy](https://aws.amazon.com/rds/proxy/) which will manage all the connection pooling for you. Or you can give [Amazon Aurora Serverless](https://aws.amazon.com/rds/aurora/serverless/) a try! It's the only serverless relational database that I have come across.

## Conclusion
That's it! Deploy your Lambda or test it locally. You should be able to communicate with RDS (if it's publically accessible). We set this up without having to use any plaintext credentials. Secrets Manager gives you a secure way of storing and retrieving sensitive information.

Check out the complete code on [Github](https://github.com/fourgates/aws-cdk-rds-lambda-ssm/tree/main/src/get-query)! If you have any questions feel free to DM me on [Twitter](https://twitter.com/ninan_phillip)! 

I have another post on deck utilizing the AWS CDK to deploy these resources (VPC, RDS instance, Secrets Manager, Lamda, API Gateway). Stay tuned!