title: How to manage Postgres connection in concurrent AWS lambda function?
body: <p>Anybody who has experience building concurrent AWS Lambda Function with Postgres? </p>
  
  <p>I have to build a lambda cron that will ingest thousands of invoices into a Postgres database. I have to call the ingestion lambda function concurrently for each invoices. The problem is, because the it is concurrent,  each instance of the ingestion function will create a connection to the database. Which means,  if I have a 1000 invoice to ingest, each invoice will invoke a lambda function, that will create 1000 database connection. This will exhaust the max connection that Postgres can handle. Some instance of the lambda function invoked will return an error saying that there are no more connection available. </p>
  
  <p>Any tips you can give how to handle this problem?</p>
  
  <p>Here are some snippets of my code:</p>
  
  <p>ingestInvoiceList.js</p>
  
  <pre><code>var AWS = require('aws-sdk');
  var sftp = require('ssh2-sftp-client');
  
  var lambda = AWS.Lambda();
  
  exports.handler = async (evenrt) =&gt; {
     ...
  
          let folder_contents;
          try {
              // fetch list of Zip format invoices
              folder_contents = await sftp.list(client_folder);
          } catch (err) {
              console.log(`[${client}]: ${err.toString()}`);
              throw new Error(`[${client}]: ${err.toString()}`);
          }
  
          let invoiceCount = 0;
  
          let funcName = 'ingestInvoice';
  
  
          for (let item of folder_contents) {
              if (item.type === '-') {
                  let payload = JSON.stringify({
                      invoice: item.name
                  });
                  let params = {
                      FunctionName: funcName,
                      Payload: payload,
                     InvocationType: 'Event'
                  };
  
  
                  //invo9ke ingest invoice concurrently
                  let result = await new Promise((resolve) =&gt; {
                      lambda.invoke(params, (err, data) =&gt; {
                          if (err) resolve(err);
                          else resolve(data);
                      });
                  });
  
                  console.log('result: ', result);
  
                  invoiceCount++;
              }
          }
     ...
  }
  </code></pre>
  
  <p>ingestInvoice.js</p>
  
  <pre><code>var AWS = require('aws-sdk');
  var sftp = require('ssh2-sftp-client');
  var DBClient = require('db.js')l
  
  var lambda = AWS.Lambda();
  
  exports.handler = async (evenrt) =&gt; {
     ...
  
     let invoice = event.invoice;
     let client = 'client name';
  
     let db = new DBClient();
  
     try {
          console.log(`[${client}]: Extracting documents from ${invoice}`);
  
          try {
              // get zip file from sftp server
              await sftp.fastGet(invoice, '/tmp/tmp.zip', {});
          } catch (err) {
              throw err;
          }
  
  
          let zip;
          try {
              // extract the zip file...
              zip = await new Promise((resolve, reject) =&gt; {
                  fs.readFile("/tmp/tmp.zip", async function (err, data) {
                      if (err) return reject(err);
  
                      let unzippedData;
                      try {
                          unzippedData = await JSZip.loadAsync(data);
                      } catch (err) {
                          return reject(err);
                      }
  
                      return resolve(unzippedData);
                  });
              });
  
          } catch (err) {
              throw err;
          }
  
          let unibillRegEx = /unibill.+\.txt/g;
  
          let files = [];
          zip.forEach(async (path, entry) =&gt; {
              if (unibillRegEx.exec(entry.name)) {
                  files['unibillObj'] = entry;
              } else {
                  files['pdfObj'] = entry;
              }
          });
  
  
          // await db.getClient().connect();
          await db.setSchema(client);
          console.log('Schema has been set.');
  
          let unibillStr = await files.unibillObj.async('string');
  
          console.log('ingesting ', files.unibillObj.name);
  
          //Do ingestion queries here...
          ...
  
          await uploadInvoiceDocsToS3(client, files);
  
      } catch (err) {
          console.error(err.stack);
          throw err;
      } finally {
          try {
              // console.log('Disconnecting from database...');
              // await db.endClient();
              console.log('Disconnecting from SFTP...');
              await sftp.end();
          } catch (err) {
              console.log('ERROR: ' + err.toString());
              throw err;
          }
      }
     ...
  }
  </code></pre>
  
  <p>db.js</p>
  
  <pre><code>var { Pool } = require('pg');
  
  module.exports = class DBClient {
      constructor() {
      this.pool = new Pool();
     }
  
     async setSchema(schema) {
        await this.execQuery(`SET search_path TO ${schema}`);
     }
  
     async execQuery(sql) {
        return await this.pool.query(sql);
     }
  }
  </code></pre>
  
  <p>Any answer would be appreciated, thank you!</p>