How to handle multiple database connections for 2 or 3 SELECT queries in AWS Lambda with nodejs?

Question

The lambda's job is to see if a query returns any results and alert subscribers via an SNS topic. If no rows are return, all good, no action needed. This has to be done every 10 minutes.

For some reasons, I was told that we can't have any triggers added on the database, and no on prem environment is suitable to host a cron job

Here comes lambda.

This is what I have in the handler, inside a loop for each database.

sequelize.authenticate()
      .then(() => {
        for (let j = 0; j < database[i].rawQueries[j].length; j++) {
          sequelize.query(database[i].rawQueries[j] => {
            if (results[0].length > 0) {
              let message = "Temporary message for testing purposes" // + query results
              publishSns("Auto Query Alert", message)
            }
          }).catch(err => {
            publishSns("Auto Query SQL Error", `The following query could not be executed: ${database[i].rawQueries[j])}
${err}`)
          })
        }
      })
      .catch(err => {
        publishSns("Auto Query DB Connection Error", `The following database could not be accessed: ${databases[i].database}
${err}`)
      })
      .then(() => sequelize.close())

// sns publisher
function publishSns(subject, message) {
  const params = {
    Message: message,
    Subject: subject,
    TopicArn: process.env.SNStopic
  }

  SNS.publish(params).promise()
}

I have 3 separate database configurations, and for those few SELECT queries, I thought I could just loop through the connection instances inside a single lambda.

The process is asynchronous and it takes 9 to 12 seconds per invocation, which I assume is far far from optimal

The whole thing feels very very sub optimal but that's my current level :)

To make things worse, I now read that lambda and sequelize don't really play well together:

I am using sequelize because that's the only way I could get 3 connections to the database in the same invocation to work without issues. I tried mssql and tedious packages and wasn't able with either of them

It now feels like using an ORM is an overkill for this very simple task of a SELECT query, and I would really like to at least have the connections and their queries done asynchronously to save some execution time

I am looking into different ways to accomplish this and i went down the rabbit hole and I now have more questions than before! Generators? are they still useful? Observables with RxJs? Could this apply here? Async/Await or just Promises? Do I even need sequelize?

Any guidance/opinion/criticism would be very appreciated

user_13266781 · Answer

I'm not familiar with sequelize.js but hope I can help. I don't know your level with RxJS and Observables but it's worth to try.

I think you could definitely use Observables and RxJS.

I would start with an interval() that will run the code every time you define. You can then pipe the interval since it's an Observable, do the auth bit and do a map() to get an array of Observables (for each .query call, I am assuming all your calls, authenticate and query, are Promises so it's possible to transform them into Observables with from()). You can then use something like forkJoin() with the previous array to get a response after all calls are done.

In the .subscribe at the end, you would make the publishSns().

You can pipe a catchError() too and process errors.

The map() part might be not necessary and do it previously and have it stored in a variable since you don't depend on an authenticate value.

I'm certain my solution isn't the only one or the best but i think it would work.

Hope it helps and let me know if it works!

How to handle multiple database connections for 2 or 3 SELECT queries in AWS Lambda with nodejs?

1 Answers