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

[Mark Sharp]

I would simply separate the database connect and disconnect functions from the query functions. 

Mark
R. Mark Sharp, Ph.D.
msharp at TxBiomed.org





> On May 28, 2015, at 12:18 PM, Luca Cerone <luca.cerone at gmail.com> wrote:
> 
> 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
> 
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.