0

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?

  • Are you perchance using a database that supports changing roles or session authorizations, such as PostreSQL's SET SESSION AUTHORIZATION bob / SET SESSION AUTHORIZATION DEFAULT? – Blrfl Jan 18 at 15:47
0

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
0

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.

  • Yes - I was keeping the connection open in solution #1 - that's the main comparison point actually. Can you explain, expensive in terms of what? – Mavi Domates Jan 18 at 16:41
  • Your system may be small, so all of this might be unnoticeable to you, but connections usually tie memory and other types of resources (including network usage) that can very easily grow if several concurrent users log into the application. If you have a very small number of users (let's say, less or around 5), this could practically be a non issue, but growing beyond that (depending on the technology and memory available) it will become a problem. Here is a very good answer about this topic: stackoverflow.com/questions/34303678/… – Juan Carlos Eduardo Romaina Ac Jan 18 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.