Using MySql.Data in .NET AWS Lambda with D.I

Question

I'm refactoring a .NET AWS Lambda project with MySql.Data in it for connecting to AWS Aurora. I implemented the DI process and registered MySqlConnection into DI container as Transient and injected it into the DataAccess service.

The registration of MySqlConnection and the DataAccess go something like this:

Startup:

services.AddTransient<MySqlConnection>(provider => 
{
    //Get the necessary parameters and build a connection string.

    return new MySqlConnection(connectionString);
});

services.AddSingleton<IDataAccess, DataAccess>();

DataAccess:

private readonly MySqlConnection _connection;

// inject the MySqlConnection.

async function() 
{
  await _connection.OpenAsync();
  await _connection.DoStuff();
  await _connection.CloseAsync();
}

My boss concerned that all of the DB interaction will be going through a single connection that is injected into the DataAccess class and she doesn't want the db to crash because I'm not cleaning up the connections properly.

I have a few questions:

I see the old code base has the using statement when it create a new MySqlConnection but I don't see one on the MySql documentation. Do I need the "using" statement when I create a new MySqlConnection?
Should I register MySqlConnection as Transient and use DI or simply create a new MySqlConnection instance inside the class that use it?
Is the DI of the DB connection going to leave a lot of open connections? Or, on the other extreme, that a single connection is going to slow down the lambda because it used to make numerous connections?

Thank you!

If you inject the connection as transient it will ALWAYS be a different injection, these will be automatically disposed after your program has left the called function. See [this](https://stackoverflow.com/a/38139500/18278998) post for a very clear explanation (it is a bit long but will give you some important info if you end up using transient). You are probably best off using `using` statements to ensure proper disposing of the connections. The second question is more dependant on your personal liking, so I can't give a definitive answer to that — Roe, May 26 '23 at 07:31

Ankush Jain · Accepted Answer · 2023-05-26T08:45:47.537

IMO, instead of using DI, you can go with using statements. SQL connections will be picked from pool that .NET maintain at application level, and will be returned once connection is closed. Refer this answer for more detail.

However, connection pooling won't help much in case of AWS Lambda, as entire Lambda compute is destroyed after idle timeout. So it is recommended to use RDS proxy to avoid connection related issues.

Using RDS Proxy, you can handle unpredictable surges in database traffic. Otherwise, these surges might cause issues due to oversubscribing connections or creating new connections at a fast rate. RDS Proxy establishes a database connection pool and reuses connections in this pool. This approach avoids the memory and CPU overhead of opening a new database connection each time. To protect the database against oversubscription, you can control the number of database connections that are created.

Also, you can checkout RDS Proxy for SQL Server with AWS Lambda and .NET 6

Using MySql.Data in .NET AWS Lambda with D.I

1 Answers1