Best place to create database connection

Can anyone please advise which is the best place to create a reusable database connection within a Web application. I have seen it happen at the App class and also within Session. What is the difference and which is “best practice”?

Thanks.

Put it as a property in Session. That way all connected users get their own connection to the database.
If you put it in, App all connected users will share the same database connection.

Thanks Albin. Makes perfect sense.

What we do is have two permanent connections. The first is the App object. The second is the Session object. We do have cases where the app can do database work outside of the session.

We also have an edge case with threads. Each thread doing database work should have its own connection so we have a generic get connection method that checks for this. If the Session is available we use that one. If it’s on the main thread (application) we use that one. If it’s in a thread (not session or app) we create a new connection and use that one for that thread.

If you PM me I can probably share some code on this.

[quote=188581:@Kevin Russell]We also have an edge case with threads. Each thread doing database work should have its own connection so we have a generic get connection method that checks for this. If the Session is available we use that one. If it’s on the main thread (application) we use that one. If it’s in a thread (not session or app) we create a new connection and use that one for that thread.

[/quote]

I don’t know much about the web framework, but it’s my understanding that each session runs in a thread. If that’s the case, you would definitely want a connection per session. You can reduce database performance issues by using a connection pool. If the database supports such (e.g. PostgreSQL + pgbouncer), that’s the place to do it.