AWS Lambda Query RDS then Publish SNS Message

index.js

const fs = require('fs'); // file reader
const pg = require('pg'); // node-postgres
const AWS = require("aws-sdk");    
AWS.config.update({ region: 'us-east-1' });

const functionName = process.env.AWS_LAMBDA_FUNCTION_NAME;
const encrypted = process.env['PASSWORD'];
let decrypted;

/**
* Lambda to query a Postgres DB in RDS, perform some business logic, then push an SNS message. 
* Once this is implmented you can create a CloudWatch Event / Amazon EventBridge rule to call this function on a cron / scheudle
* 
* Requirements:
* - lambda needs to be in the same VPC as the DB
* - the VPC will need an endpoint with each service: SNS(to publish notification) and KMS(to decrypt credentials)
*   (https://docs.aws.amazon.com/vpc/latest/userguide/vpc-endpoints.html)
* - the config for the db connection is in the env variables. the db password has been encrypted using kms
* - my db requires a ssl connection. therefore. you may need to download this pem and provide ssl options for the db connection (https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html)
* - if your db does not require ssl you can ommit the ssl related code and .pem file
* 
* Caveats
* - My use case for this lambda is to periodically exectue a single procedure. That procedure does a sanity check on some data. The current implementation could cause potential performance problems if 
*   this were used to generate significant traffic. If this code were to be used more regularly it would make sense to setup a RDS proxy to handle connection pooling.
*   Otherwise the database could become overwhelmed with new connections. RDS proxy can handle unpredictable surges in database traffic.
*
*/
exports.handler = async (event, context, callback) => {
    await decryptCredentials();
    const data = await executeProcedure(context);
    
    // TODO - perform some data manipulation to determine if a message needs to be pushed, and if so what should the message say
    return publishSNSMessage("We did it!").promise((err,data)=>{
        console.log ("We are in the callback!");
        if (err) {
            console.log('Error sending a message', err);
            context.fail(err);
        } else {
            console.log('Sent message:', data.MessageId);
            context.succeed("Done!");
        }      
    });

};
async function decryptCredentials(){
    if (!decrypted) {
        // Decrypt code should run once and variables stored outside of the
        // function handler so that these are decrypted once per container
        const kms = new AWS.KMS();
        try {
            const req = {
                CiphertextBlob: Buffer.from(encrypted, 'base64'),
                EncryptionContext: { LambdaFunctionName: functionName },
            };
            const data = await kms.decrypt(req).promise();
            decrypted = data.Plaintext.toString('ascii');
        } catch (err) {
            console.log('Decrypt error:', err);
            throw err;
        }
    }       
}
async function executeProcedure(context){
    const rdsCa = fs.readFileSync(__dirname + '/rds-combined-ca-bundle.pem');
    
    // https://node-postgres.com/api/client
    var connectionInfo = {
      user: process.env.USER,
      password: decrypted,
      host: process.env.HOST,
      database: process.env.DATABASE,
      port: process.env.PORT,
      ssl: {
        ca: [rdsCa, 'ascii']
      }
    }      
    const client = await new pg.Client(connectionInfo)
    await client.connect();
    try{
        // https://node-postgres.com/features/queries
      const out = await client.query('select event_id from public.events');
      console.log('out', out);  
      return out;
    }
    catch(err){
        console.log('err', err);
        context.fail(err);
    }    
}
function publishSNSMessage(message){
    // Create publish parameters
    var params = {
      Message: message,
      Subject: "Test SNS From Lambda",
      TopicArn: 'arn:aws:sns:us-east-1:XXXX:ME'
    };
    var sns = new AWS.SNS({ region: "us-east-1" });
    return sns.publish(params);
}