I use YugabyteDB, it's compatible with PostgreSQL, so I can just use Npgsql. I have confusion with managing DbConnection. I know EntityFramework creates new connection per request, but I use Dapper and I have to fully control connection lifetime.
I'm confused how shall I manage these connections. To me, the nice way is to have one connection (singleton that always return the same connection). Another way I consider is to have one connection per one thread (so singleton but restricted to thread).
I would want to know what are disadvantages caused by single connection and whether there are any disadvantages (as I'm just using clear SQL to communicate with my DB), and which connection management strategy shall I implement for the best performance.
Dapper works on top of the ADO.NET:
Dapper works with an ADO.NET IDbConnection object, which means that it will work with any database system for which there is an ADO.NET provider.
And ADO.NET/database driver usually pools actual database connections, see this quote from the EF Core docs:
Note that context pooling is orthogonal to database connection pooling, which is managed at a lower level in the database driver.
And this github issue:
Unless you've disabled pooling (by setting Pooling=false in your connection string), opening and closing connections has negligible overhead and there shouldn't be any reason to avoid it. When a connection is closed it is returned to the pool, and when one is opened it is taken from the pool. This is the default/recommended way to work, rather than trying to implement your own connection reuse.
Npgsql provider supports set of settings to customize connection pooling for PostgreSQL.
Also checkout the pooling section of docs:
Opening and closing physical connections to PostgreSQL is an expensive and long process. Therefore, Npgsql connections are pooled by default: closing or disposing a connection doesn't close the underlying physical connection, but rather returns it to an internal pool managed by Npgsql. The next time a connection is needed, that pooled connection is returned again. This makes open and close extremely fast operations; do not hesitate to perform them a lot if needed, rather than holding a connection needlessly open for a long time.
For example single connection would not allow to perform parallel requests to database.
Just use the "default approach" (i.e. creating and disposing the connection when needed, you can move creation to some method for convenience) and only try to make adjustments only if you will encounter actual problems (and if you encounter any - try managing the connection settings first rather than manually working with connection object).