TLDR: Check out the complete code on Github.

In this post, we will use AWS Lambda, AWS Secrets Manager, and the node pg package. I'll show you how to query a Postgres database using these resources.

In my previous post, we used the AWS CDK to spin up a Virtual Private Cloud (VPC) and a Relational Database Service (RDS) Postgres database. We stored all the information we needed to log in to our RDS instance in AWS Secrets Manager.

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 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 setup. I will be writing this up in a subsequent blog post. Please read this AWS document. It details the networking needed two allow communication between AWS Lambda and an RDS instance.

☁️ Contents ☁️

• 1. Initialize the Project
• 2. Create Folders for Lambda Handler (Optional)
• 3. Install pg
• 4. Create a Lambda Handler
• 5. Load Secret
• 6. Create Database Connection
• 7. Query the Database
• 8. Deploy
• Relational Database Serverless Notes
• Conclusion

1. Initialize the Project

You can follow my previous post on how to deploy a VPC and RDS database using the AWS CDK. Also, I have a post on using AWS Serverless Application Model (SAM). 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 you can follow this tutorial 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.

    // 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, The Serverless Framework, or manually copy and paste the code using the AWS Console.

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 which will manage all the connection pooling for you. Or you can give Amazon 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! If you have any questions feel free to DM me on Twitter!

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