Reuse database connection across multiple AWS Lambda invocations

Question

I have a lambda to connect to SQL Server database like this.

module.exports = async (event) => { 
const sqlClient = getConnection(); // connection to ms SQL
doMyWork(sqlConnection) // my core lambda logic
closeConnection(sqlConnection) // closing Connection
}

when ever lambda is triggered, my SQL Server gets connected, work is done and connection is closed. Isn't there a way that i can use the connection object across multiple invocations of the lambda so that i can reduce a) no. of connection / disconnection attempts to server b) reduce the overall execution time of the lambda?

I have mentioned SQL Server here as I am currently using it here. as such I have to connect to MySQL and redis. what is the recommended way for connecting to databases (especially which support pools)?

Please suggest.

I've edited the question title because it seems that connection reuse is the key here, rather than MS SQL support. Feel free to roll back the edit if you disagree. — IMSoP
– IMSoP, Commented Jan 24, 2020 at 12:30

ubi · Accepted Answer · 2020-01-24 12:45:47Z

11

Generally, there are 2 ways to address this problem.

Store the connections/connection pool in a variable that persists across Lambda invocations. How to do it depends on the runtime/language you use. An example with node.js is here.
Use an external application that is long running and is able to persist a connection pool. Another Lambda function cannot do this and you'd need an EC2 or another long running compute instance. Thankfully AWS recently introduced RDS Proxy, which is a managed service that achieves this. It is however, still in preview.

For MSSQL/MySQL on RDS, you'll be able to use option 1 or 2 but for Redis you'll have to use option 1.

answered Jan 24, 2020 at 12:45

ubi

4,4794 gold badges41 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Lakshman Pilaka Over a year ago

Option 1 appears to be universal for all dbs that i need to work. will attempt that

IMSoP Over a year ago

Could you edit in a brief summary of that blog post, so that the answer will still be useful if the link breaks in future, or is inaccessible to some users for whatever reason?

Koushik Shom Choudhury Over a year ago

December 2020, although AWS RDS Proxy has been made available publicly, AWS confirmed me that there are still bugs in play. The connections were not being reused, and wasted dramatically.

Ivan Nikolchov Over a year ago

The big problem with RDS Proxy (in my case) is the connection pinning. Any prepared statement will cause the connection to be pinned which drastically reduces performance. Better explained in their docs: docs.aws.amazon.com/AmazonRDS/latest/UserGuide/…

ccarstens Over a year ago

@IvanNikolchov agreed! Do you have a strategy for dealing with connection pinning? Is closing the connection to rds proxy after every execution the solution?

Collectives™ on Stack Overflow

Reuse database connection across multiple AWS Lambda invocations

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related