What is the best practices to connect to postgresql from microservice

Question

I have a multi tenant c# project with 60 microservices connected to multiple postgresql databases. I'm using open/close connection on each transaction. I'm not sure that this is the best practices. Do I have to open one connection to each database on each microservice and use it on all my activities or open/close on each transaction

Answer 1

You should consider use Unit of Work pattern plus Dependency Injection practices.

I let you here a Microsoft document explaining the Unit of Work pattern

https://learn.microsoft.com/en-us/aspnet/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application

If this approach isn´t valid for you, using only ORMs like Dapper or Entity Framework should be another approach, but in this case I strongly recommend you to isolate this in "repositories" entities. This way, everytime you instance and use a repository (using Dependency Injection) you won´t have to deal with transaction details (the connection will be opened every time you instance this entity and closed when you call Dispose method).