how do i close a connection after i finish running the script?

[FinotiLucas]

Issue Description and Expected Result

how do i close a connection after i finish running the script?

[shrektan]

This is actually a great question and this is my practice:

I've posted it before on my blog Create a database connection that can be disconnected automatically

1. Define a wrapper connection-build function in your internaly package or .Rprofile
2. This wrapper function will build the connection and ensure it gets disconnected whenever it should (end of a function execution or garbage collection or end of R process)
3. Use this wrapper function everywhere rather than use the DBI::dbConnect() function directly

The wrapper function looks like below:

reg_conn_finalizer <- function(conn, envir) {
  is_parent_global <- identical(.GlobalEnv, envir)
  if (isTRUE(is_parent_global)) {
    env_finalizer <- new.env(parent = emptyenv())
    env_finalizer$conn <- conn
    attr(conn, 'env_finalizer') <- env_finalizer
    reg.finalizer(env_finalizer, function(e) {
      try(DBI::dbDisconnect(e$conn))
    }, onexit = TRUE)
  } else {
    withr::defer(try(DBI::dbDisconnect(conn)), envir = envir, priority = "last")
  }
  conn
}

db_con <- function(...) {
  stopifnot(is_string(x))
  param <- list(...) # put your connection string inside
  con <- do.call(DBI::dbConnect, c(list(drv = ROracle::Oracle()), param))
  con <- reg_conn_finalizer(con, envir = parent.frame())
  con
}

1. You can use the db_con() directly in your script and it will be releaseed in GC or end of the R process
2. Or you can use the db_con() inside of a function or local({...}) and it will be disconnected immediately after the environment of evaluated code gets released

[hadley]

Thanks @shrektan !