How does R handle closing of data base connections

Question

If I create a data base connection within a function, the connection objects gets destroyed when the function finished executing. Does this reliably close the data base connection, or would it better to to close it manually first?

Why I need to know this:

I am working on a package that creates data base connections on the fly with either RODBC or RJDBC as backend. I designed my function interfaces so that you can pass in either username and password, or a connection object. Usually, when I pass in a connection object I do not want the connection to be closed on the termination of the function, while when I pass in username and password I want it to be closed.

If I do not have to worry about open connections, it would simplify things a lot for me and also save me a lot of headaches.

Answer & More:

I markded Benjamin's answer as the answer since it gives good advice, though actually what I was looking for is more Marek's comment that (paraphrased:) connections can be open after the connection object is destroyed and there is no way to access them from R any more.

I ended up going for a solution that involves creating an R6 class and defining finalize() method that closes the connection (it's a more powerful than on.exit()), but that is beyond the scope of this Question.

Answer 1

I write a lot of functions that create ODBC connections when they are called. My standard practice is:

conn <- RODBC::odbcDriverConnect(...)
on.exit(RODBC::odbcClose(conn))

By putting the creation of the object and the code for its closing next to each other, I know that the connection will be closed when the function is finished. Using on.exit has the added advantage of closing the connection even if the function stops on an error.

Edit:

In the problem as your edit has described it, I think the same pattern is relevant. You need to declare on.exit in a manner that it only gets called if you don't provide the connection object.

db_function <- function(conn = NULL, uid, pwd){
  if (is.null(conn)){
    conn <- RODBC::odbcDriverConnect(...) # Build conn with uid and pwd
    on.exit(RODBC::odbcClose(conn))
  }
}

A trivial example to show by-passing on.exit

test_fun <- function(on_exit = FALSE){
  if (on_exit) on.exit(print("hello world"))

  "Look at me"
}

test_fun()

test_fun(TRUE)