[R] best way to handle database connections from within a package

[Luca Cerone]

Dear all,
I am writing a package that is a collection of queries to be run
against a postgresql database,
so that the users do not have to worry about the structure of the database.

In my package I import dbDriver, dbUnloadDriver, dbConnect,
dbDisconnect from the package DBI
and dbGetQuery from the package RPostgreSQL.

All the function in a function in my package have the same structure:

getFancyData <- function( from, to) {
    on.exit( dbDisconnect(con), add=TRUE)
    on.exit( dbUnloadDriver(drv), add=TRUE)
    drv <- dbDriver("PostgreSQL")
    con <- dbConnect(drv,
                     user=pkguser,
                     host=pkghost,
                     password=pkgpassword,
                     port = pkgport)

    query <- sprintf("select * from fancyTable where dt between '%s'
and '%s'", from, to)
    res <- dbGetQuery(con,query)
    return(res)
}

The various access details are read from an encrypted profile that the
user has to
create when she installs the package.

Such functions work perfectly fine, but I have to replicate a lot of
times loading and unloading the driver and connecting and
disconnecting from the database.

I am wondering if there is a better way to do this job, like loading
the driver and opening the connection only once when the package is
loaded. However I have to make sure that
if R crashes or the code where the function is called contains an
error then the connection
with the database is closed. How would you implement this?


Also how would you write a functional that would at least allow me to
avoid replicating
the boilerplate code to load and unload the drivers?

I am thinking something on the lines of:

querybuild <- function(query, ....)
    on.exit( dbDisconnect(con), add=TRUE)
    on.exit( dbUnloadDriver(drv), add=TRUE)
    query <- sprintf(query, ... )
    res <- dbSendQuery(query)
    return(res)
}

and then define

getFancyData <- function(from, to) querybuild("select * from
fancyTable where dt between '%s' and '%s'", from, to)

Do you see a better way?

Thanks a lot in advance for your help and advice on this!

Cheers,
Luca