Managing multiple dynamic database connections

Question

I'm working on a server, which you can pass some form of authentication as input (like connection string) and it will connect you to your database. So the DB connection is going to be dynamic. There can be multiple users at the same time, connecting to different databases.

What I'm wondering is, is there a preferred way of managing the connections? Should the DB Client be stored in memory after authentication, so each user can immediately retrieve it using their session data / and execute queries against it? Or should I close / reopen the connection every time the user wanted to do something. I can use JS to figure out if the user is active on the page / or left and get rid of the connection object using users' state as well.

Approach 1

• User signs in to our web application.
• User enters the credentials to database (like the connection string)
• Server authenticates against the DB and we now have the connection client object. We keep it in a dictionary mapped to user id.
• User wants to run a query. We determine the user id from the request, fetch the client from the memory and run the query.
• When user leaves the page, we detect it through JS (unload event) send a request / or socket packet to server and close the client + remove it from the dictionary.

Approach 2

• User signs in to our web application.
• User enters the credentials to database (like the connection string)
• Server authenticates against the DB and we just confirm that connection worked. We don't keep the client object in memory.
• User wants to run a query. We re-connect to the database, run the query and close the connection. No dictionaries are kept in memory, we reconnect every time the user wants to do something.

Further design clarifications:

• This is a single page application.
• Although we don't have load balancing at the moment, depending on the user load we might end up adding it.
• We can assume only 1 user is going to connect to a particular database through this system.
• Sessions are managed through cookies and server side code.
• We don't really care about the distance between the SQL and our server. Given that it should be able to connect to any-given SQL - there isn't a favourable location unless it's fully distributed and we're not doing that at this stage.

What do you think?

Answer 1

Do not create your own connection cache

Your SQL connections are probably already being held in memory via a connection pool. Typically when your code "closes" a connection (e.g. by calling its Close() method) it is actually leaving it open and returning it to the pool. So there is very little value in keeping the connection in memory yourself; you would essentially be creating a double-layered pool for no purpose.

The standard operating procedure is to open the connection, use it, and "close" it (return it to the pool) as quickly as possible. This allows the operating system to manage your connections and open or close them as needed. If you never return your connections to the pool, you thwart that mechanism; unless you really know what you are doing, your application will probably run less efficiently, and may even run out of connections in some circumstances.

The fact that different users have different connection strings makes very little difference. The pool is meant to work in that sort of situation-- it will keep a separate pool for each version of the connection string you use.

Answer 2

With the new information provided, I would have done some think like this.

• Because its a single page app, i would use a websocket connection to track when the user is online/offline and do required task when they connect, disconnects and reconnects
• If you are thinking of load balancing, you need a way to share the websocket connections between the webservers or some rules to make sure the web app allways talk to the same server. this can be done either on the client side or server side.
• You can use sessions to handle what to do with new and expired sessions, but i dont think this is the best way, as a session might expire if the user just leave the computer for a while, but not actually closing the web page.

• The reason why i ask how far away the sql server is, is due to connection time. if you create a new connection for each request, you might end up with a very slow application if every task will require a new connection and disconnection with the database. I would anyway look into some connection pool, so you can keep connections open as long as they are needed.

  so the steps would be something like this:

• User login
• Connects websocket session
• When there is a new web connection
• Connects to SQL server
• Store the connection in a Dictionary like
• When the websocket disconnects
• Remove the connection from the Dictionary based on the UserId

Answer 3

If my understanding is correct (you may need to clarify this in your question), you are keeping the connection open in solution #1? This depends on the technology you're using, but keeping a connection open is usually bad as they are a very expensive resource.

Keeping the connection string in memory, opening when needed and closing as soon as your finished, would be the best approach.

Answer 4

Approach 1 is the only sane one for a generic SQL console tool. That is, if a user can start a transaction, run several queries, then commit or rollback, closing the session in the middle is out of question.

Approach 2 is fine if the queries are predetermined (even if parametrized), and no transactions or other session state is expected. That would be fine for a reporting / analytic tool.