C#•2d ago

.NET 8.0, PostgreSql and left over Idle sessions

I have a .NET 8 webapp and we've done a dumb and manually handled postgres database connections throughout our classes.. I think this could be the root of my problems really... We new up connections and pass the connection around through our various classes, we end up sometimes passing the same connection down 2,3,4 times, re-using it, with the final disposal being quite some time later... making it hard to follow where idle connections are even coming from. One clue might be that the Idle connections don't even have a query associated with them... (making it really hard to pinpoint where they are coming from). I still feel like a noob C# developer (about 2 yr) and haven't touched things like dependency injection yet.. does anyone here know of any good resources for setting up and managing postgres DB connections that get implemented across many classes? My issues is we have leaky connections, even though i've been careful to go through the entire project and wrap ever instance of NpgsqlConnection/NpgsqlCommand in using statements and (tried to) implement the IDisposable interface for each class using the postgres connections. But i'm still finding some leaky connections/sessions building up over time. Initially I had an idle connection for every click on the front end... now I only get one every few minutes~. If anyone has any suggestion/tips i'd be hugely greatful! P.S. it could be that i just need to bite the bullet and convert to a singleton and add Transients for each class using the database.. thoughts? then go through every implementation of the DBConnection and just swap it out to the singleton instance hopefully to have better control/single point of connection creation. I am not looking for code implementatin specifically just a good idea of what the best way to maybe approach this issue. if you need any extra detail let me know i'll give us much as i can.

14 Replies

Jeremy•23h ago

A common approach would be to make repository interfaces for handling your CRUD operations on each table. Then make concrete classes that implement those interfaces, using your sql connections and commands. Other code should reference the interfaces, with the implementations injected using DI. Typically for a web app you'd want to use Scoped, so the service is the same for each request. Transient would create a new service every single time it's needed, even within one request. Usually the scope is the controller and you inject the repository to the controller constructor. If you have a bunch of business logic beyond simple CRUD, you can add another service layer between the controller and repositories. E.g. the CarController gets an ICarService, which coordinates all the appropriate calls to the ICarRepository, ILicensePlateRepository, IVehicleTitleRepository, etc.

ImpulsiveOP•18h ago

thanks this helps, I see what you mean here, this should also de-couple all the components as well right? With this kind of approach realistically we wouldn't have API call construction code + SQL + creating/disposing connections all in the one class. I think currently we as a team have muddied a lot of things up and we'd likely need to re-factor a bunch of stuff to swap to this design. Thanks for the info about transient too, I knew it creates a new instance of the transient component I figured if they took the singleton in it wouldn't be spawning a bunch of connections, but you are right, we do end up with several of the same object open at one time. really good food for thought. Many of the team are pretty new to C#, with not a huge amount of OO experience. I foresee a lot of re-factoring for us in the future 😉 i'll attempt to add time to the project to re-factor stuff as we add new features/enhance existing~

Angius•18h ago

The proper way to handle all of that would be to use EF or Dapper, use them with dependency injection, and just let the DI container handle everything about the lifetime and disposal You could probably rawdog NpgSqlConnection if you're a masochist, but there are more pleasant ways to engage in that kink

ImpulsiveOP•18h ago

We have a class just for Postgres, we pass it the connection string and it returns a NpgsqlConnection. we are masochists for sure! I was playing (studying?) today with using the Npgsql.DependencyInjection package and Npgsql It seems like an OK approach. This is a few snippets from that playground. From Program.cs:

builder.Services.AddNpgsqlDataSource(builder.Configuration.GetConnectionString("PostgresConnection"));
            builder.Services.AddTransient<GetBookDetails>();

builder.Services.AddNpgsqlDataSource(builder.Configuration.GetConnectionString("PostgresConnection"));
            builder.Services.AddTransient<GetBookDetails>();

I implemented this GetBookDetails class to get the count of rows from some sample data i found online on my home lab Postgres~ (RIP Tteck) When smashing the call from a razor page and I didn't see the database connection count increase even once after application startup.

using Npgsql;

namespace DemoProject.Respository;

public class GetBookDetails
{
    private readonly NpgsqlDataSource _dataSource;
    public GetBookDetails(NpgsqlDataSource dataSource)
    {
        _dataSource = dataSource;
    }
    public int GetBookCount()
    {
        const string query = "SELECT COUNT(*)::int FROM public.books";

        using var connection = _dataSource.OpenConnection();
        using var command = new NpgsqlCommand(query, connection);

        int bookCount = (int)command.ExecuteScalar();
        return bookCount;
    }
}

using Npgsql;

namespace DemoProject.Respository;

public class GetBookDetails
{
    private readonly NpgsqlDataSource _dataSource;
    public GetBookDetails(NpgsqlDataSource dataSource)
    {
        _dataSource = dataSource;
    }
    public int GetBookCount()
    {
        const string query = "SELECT COUNT(*)::int FROM public.books";

        using var connection = _dataSource.OpenConnection();
        using var command = new NpgsqlCommand(query, connection);

        int bookCount = (int)command.ExecuteScalar();
        return bookCount;
    }
}

Let's suppose i rename GetBookDetails to PostgresDB implement a few methods that open/close the connections for each query, probaly need to create a NpgsqlParameter implenentation as well methods for the below, likely with optional inputs for Parameterised queries (we have many) ExecuteQuery ExecuteNonQuery ExecuteScalar ... the rest of the types we use in the project today~ I Could probably get away with minimal re-factoring and have a far more efficient way of communicating with my database. I feel like i'd be re-inventing the wheel here though -.-

Angius•18h ago

Yes You're trying to make a Temu version of Dapper

ImpulsiveOP•18h ago

ohh really.. so what you are saying is screw all this off and use dapper? 😄

Angius•18h ago

Or EF, yes

ImpulsiveOP•18h ago

p.s. i've never heard of Temu before, a quick google shows it's a shitty knock off brand online store 😛

Angius•18h ago

Yep

ImpulsiveOP•18h ago

I understand EF reduces my interaction with SQL substantially~ i've heard a bit about Dapper from those like Nick chapsas and others online. I'll do some reasearch into Dapper and see where it leads, thanks for the guidance.

Angius•18h ago

Yeah, basically. Use Dapper if you really want to write SQL, use EF if you'd rather not

ImpulsiveOP•18h ago

sick! I'll do that. Thanks again 🙂

cap5lut•18h ago

u probably dont see any increase in db connections because the datasource is using connection pooling by default. not sure what npgsql doesn if u new up ur own NpgsqlConnections tho

ImpulsiveOP•18h ago

yes! i wasn't clear I was hoping/expecting to see none when using the singleton. In my work project I was seeing some leak when navigating the front end~ I went across every place an NpgsqlConnection was used and ensured it was wrapped in usings, but yea, some still leaking about, I've obviously missing someting, maybe an NpgsqlCommand or NpgsqlParams or something underneath the connection that must close fbefore the Connection is allowed to be released. Example~

using (var connection = new NpgsqlConnection(connectionString))
{
//code code code
}

using (var connection = new NpgsqlConnection(connectionString))
{
//code code code
}

Gaming

Programming

.NET 8.0, PostgreSql and left over Idle sessions