using node-postgres (`pg`) in AWS Lambda

lambda.js

import λ from "apex.js";
import { Pool } from "pg";

// connection details inherited from environment
const pool = new Pool({
    max: 1,
    min: 0,
    idleTimeoutMillis: 120000,
    connectionTimeoutMillis: 10000
});

export default function λ(async (event, context) => {
  // https://github.com/brianc/node-postgres/issues/930#issuecomment-230362178
  context.callbackWaitsForEmptyEventLoop = false; // !important to reuse pool
  
  const client = await pool.connect();
  
  try {
    await client.query("SELECT NOW()");
  } finally {
    // https://github.com/brianc/node-postgres/issues/1180#issuecomment-270589769
    client.release(true);
  }
});

This is great and helped me a ton! Thanks for posting.

Very helpful. Thanks

What is the consequence of not calling the callback parameter?

great! thx!

Is it ok without rds proxy?

Thank you! This helped me A LOT because of the callbackWaitsForEmptyEventLoop that I didn't remember existed.

Hint: Try pool.query() directly. It'll internally deal with release and save you a try...finally

you are great !!!!

@streamich
My RDS/PostgreSQL is running and I have setup VPC, security group, subnets, and AIM role. This is Node 16.x. Any ideas why I'm getting a time out error?

Test Event Name
test

Response
{
  "errorMessage": "2022-08-19T09:48:27.942Z 91212131-af20-4a85-9697-66a4be0ae796 Task timed out after 10.01 seconds"
}

Function Logs
START RequestId: 91212131-af20-4a85-9697-66a4be0ae796 Version: $LATEST
END RequestId: 91212131-af20-4a85-9697-66a4be0ae796
REPORT RequestId: 91212131-af20-4a85-9697-66a4be0ae796	Duration: 10012.84 ms	Billed Duration: 10000 ms	Memory Size: 128 MB	Max Memory Used: 65 MB	Init Duration: 172.95 ms	
2022-08-19T09:48:27.942Z 91212131-af20-4a85-9697-66a4be0ae796 Task timed out after 10.01 seconds

Request ID
91212131-af20-4a85-9697-66a4be0ae796

const { Pool } = require('pg')

// connection details inherited from environment
const pool = new Pool({
    max: 1,
    min: 0,
    idleTimeoutMillis: 120000,
    connectionTimeoutMillis: 10000
});

exports.handler = async function(event, context, callback) {
  // https://github.com/brianc/node-postgres/issues/930#issuecomment-230362178
  context.callbackWaitsForEmptyEventLoop = false; // !important to reuse pool
  
  const client = await pool.connect();
  
  try {
    await client.query("SELECT NOW()");
  } finally {
    // https://github.com/brianc/node-postgres/issues/1180#issuecomment-270589769
    client.release(true);
  }
};

This one worked for me!

AWS Lambda -> Node.js 16
AWS RDS -> PostgreSQL 14
NPM dependency "pg" 8.7.0
Env variables:

PGHOST=xxx
PGUSER=xxx
PGPASSWORD=xxx
PGDATABASE=xxx
PGSSLMODE=require

index.js

const { Pool } = require('pg');

// connection details inherited from environment
const pool = new Pool();

exports.handler = async function(event, context, callback) {
  const client = await pool.connect();
  
  try {
    const res = await client.query('SELECT $1::text as message', ['Hello world!']);
    callback(null, res.rows[0].message);
  } finally {
    client.release(true);
  }
};

Execution Results

Test Event Name
test

Response
"Hello world!"

Function Logs
START RequestId: b8c5a220-3f29-4402-9678-30f2a37d2727 Version: $LATEST
END RequestId: b8c5a220-3f29-4402-9678-30f2a37d2727
REPORT RequestId: b8c5a220-3f29-4402-9678-30f2a37d2727	Duration: 1036.80 ms	Billed Duration: 1037 ms	Memory Size: 128 MB	Max Memory Used: 73 MB

Request ID
b8c5a220-3f29-4402-9678-30f2a37d2727

Am i suppose to create pool for each lambda function ?

Considering Password and connection details shouldn't be exposed in environment variables, I wouldn't follow this example. Please keep your connection (sensitive information) in a Secret manager and pull them in-code directly to avoid any exposition.

When you create a RDS Instance, AWS automatically save the connection details in a Secret Manager variable.

https://aws.amazon.com/blogs/compute/sharing-secrets-with-aws-lambda-using-aws-systems-manager-parameter-store/

Considering Password and connection details shouldn't be exposed in environment variables, I wouldn't follow this example. Please keep your connection (sensitive information) in a Secret manager and pull them in-code directly to avoid any exposition.

When you create a RDS Instance, AWS automatically save the connection details in a Secret Manager variable.

https://aws.amazon.com/blogs/compute/sharing-secrets-with-aws-lambda-using-aws-systems-manager-parameter-store/

Is indeed the right way to do it, but as the pg client needs these information to connect, and to get these info you need an async call to secret manager how do you pass them to the new Pool( config )??, I'm in the same situation as the example in the main post, lambda and pool pg client

This is perfect.
For anyone that may work with this, please don't forget:

finally{
    client.release();
}

Without that, after ~10 requests, your Lambda will throw an error.