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();
    }
}
}