I'm actually programming a quite big application and I was wondering when I should open/close a database connection.
My first thought was to open it at the beginning and keep the same DB connection the whole time. But this approach seems to have risks of failing (not sure why tho).
My second idea was to open/close as soon as possible like mentioned here: Best Practice on initializing a Database Connection
but as mentionned by someone in comment: "For the database, opening a connection is a very expensive operation." So the question remains:
When do I open / close a DB connection?
For context: My application has multiple workspaces, for example, one workspace to see information about users, another one to process them, and so on. I had the idea to open the DB connection when I open a new workspace but when using the application it is possible to stay a very long time on the same workspace, which would be as bad as my first thought. But opening / closing the DB connection after seeing the info of one client isn't the smartest idea either.
Should I use a time-based refresh of the DB connection? Every 5 minutes I close and reopen a connection?
I'm not sure how to do this properly.
