Useful guide & FAQ for Application+SQL [closed]

Question

Of course when you are making your first application for fun it doesn't matter that much how you deal with things. But when you need everything to be up to the current standards you don't always know where to find that precious useful information.

At the moment I'm making an application which deals with a DB connection. And I'm not sure when I want my functions to execute any queries do I pass the already made connection as a parameter to that function, or do I store the connection as a global variable, or do I just make a function for temporary connection establishment and then run that function every time before my query.

This applies to any language I guess, but at the moment I'm using Python & PostgreSQL. Just would like to know where to find this and similar useful info which all programers probably use on daily basis. I guess that not all of you are reading 300+ page books to find this info, so there probably should be some other places.

Thanks to you all in advance!

P.S. An example:

So rather than having something like this:

global somevar
randomfunc2()

def randomfunc1():
    _do_something_with_somevar

def randomfunc2():
    _do_something_with_somevar
    randomfunc1()

To have this:

somevar
randomfunc2(somevar)

def randomfunc1(somevar):
    _do_something_with_somevar

def randomfunc2(somevar):
    _do_something_with_somevar
    randomfunc1(somevar)

I assume the second one is better?

Are there any places where I can find these tips/guidances? :)

Answer 1

I would say the most advanced database access layer these days is Spring Data JPA. It builds fast, correct DAOs for you at startup time. I would check that out, maybe read some of the source, and gain inspiration from there.

As for connections, it depends on the app. For desktop to db apps, you can keep a connection open for the entire user session.

For web apps, you want to open a connection at the last possible moment, and close it as early as possible. You might read from 30 tables in a page request though, so use the same connection in that time.

Database connections are "expensive" in terms of memory and startup time, so one creates a pool of them. You don't really close your db connection, you just release it back to the pool.