pg-promise in AWS lambda

Question

I am having quite a number of issues using pg-promise with AWS lambda. I wanted to understand how do I solve these issues.

The library advises you to create one instance of the Database object and then export it from a module. There should only one instance of the object created. Something like:

const db = pgp({
  host: process.env.DATABASE_HOST,
  port: process.env.DATABASE_PORT,
  database: process.env.DATABASE_NAME,
  user: process.env.DATABASE_USERNAME,
  password: process.env.DATABASE_PASSWORD,
  poolSize: 0,
  poolIdleTimeout: 10,
});
module.exports = db;

I understand that this just an object and no connection is created here. A connection will be created lazily when you run anything on this db object such as a db.query().

Since we have put in the pool size as 0, there would only ever be one connection created. Which is what we need as at the start of every lambda function we need to create a connection and then close the connection when the lambda finishes.

The issues we are facing are:

1. How do we release a connection? AWS lambdas re-use containers. Meaning it will call on the same node code that is already initialised and re-run the same function if a call to the lambda happens soon after a prior run. This means that the db object will be the same for the next call of the lambda. After our first lambda completes, if we call pgp.end() the documentation says the connection pool closes. It also says we cant use the pgp library in the same process after that. But the library will be used as the db object is still alive and will be used in a subsequent run.

2. How do we retry getting a new connection?
   The other issue that AWS lambda poses, is that when you run a lambda within a VPC and your Postgres instance is also running inside a VPC, it takes time for the postgres database's DNS to resolve. So if you try to connect you might get an ENOTFOUND error. The advice from AWS is to retry acquiring the connection. Using the pg-promise how do we retry acquiring a connection?

The way I would like to implement it is:

module.exports.handler = (event, context, callback) => {
 let connection;
 try {
  connection = /*gets connection and retries if it failed the first time*/
  // run db queries and transactions.. etc.
  callback(null, result);
 } finally {
  connection.close();
 }

}

Answer 1

This is what we landed up doing.

The jist is create a new connection before the start of every lambda and then close it before returning back from the lambda

// your lambda entry point
module.exports.handler = (event, context, callback) =>  
getConnection(async (connection) => {
    let result;
    try {
        // work with your connection
    } catch (error) {
    }
    callback(null, result);
})


// db connection 

const getConnection = async (callback) => {
const dbConnection = new DBConnection();
try {
    const connection = await dbConnection.create();
    await callback(connection);
} finally {
    dbConnection.close();
}
};

const MAX_RETRY = 3;

const options = {
// global event notification;
error: (error, e) => {
    if (e.cn) {
    // A connection-related error;
    //
    // Connections are reported back with the password hashed,
    // for safe errors logging, without exposing passwords.
    logger.error('CN:', e.cn);
    logger.error('EVENT:', error.message || error);
    }
},
};

const pgp = require('pg-promise')(options);

const connectionParams = {
host: process.env.DATABASE_HOST,
port: process.env.DATABASE_PORT,
database: process.env.DATABASE_NAME,
user: process.env.DATABASE_USERNAME,
password: process.env.DATABASE_PASSWORD,
poolSize: 0,
poolIdleTimeout: 10,
};

const db = pgp(connectionParams);

class DBConnection {

async create() {
    let retry = 0;
    while (retry < MAX_RETRY) {
    try {
        logger.debug(`Acquiring a new DB connection Attempt: ${retry}/${MAX_RETRY}`);
        this.connection = await db.connect({ direct: true });
        break;
    } catch (error) {
        logger.error(`Error occurred while getting DB connection ${error}. Retrying ${retry}/${MAX_RETRY}`);
        retry += 1;
    }
    }

    if (!this.connection) {
    throw Error(`Unable to obtain DB connection after ${MAX_RETRY} retries`);
    }

    return this.connection;
}

close() {
    if (this.connection) {
    logger.debug('Closing DB Connection');
    this.connection.done();
    }
}
}

Answer 2

How do we release a connection?

You don't. Connections are communicated with the connection pool automatically. Once a query ends executing, the connection goes back into the pool, to be available for the next query that requests it.

When executing more than one query at a time you should use tasks. See Chaining Queries.

How do we retry getting a new connection?

Connection pool creates a new physical connection automatically, when it is necessary, according to the connection demand and the maximum pool size. When a connection goes down, it is automatically re-created.

---

If a single connection is all you've got within AWS lambda, then maybe the best pattern for you is to create and maintain a single global connection?

If that's the case, the Robust Listeners example might be of use to you. It shows how to maintain a single global connection outside of the connection pool, and how to keep it alive at all times.

But that's more like the last resort. I believe simply by using the automatic connection pool should be sufficient.