Using `pool` in an R Shiny package

Question

How can I implement the use of a pool object in an R Shiny application structured like a package?

I have structured my package according to the layout described by Hadley Wickham in "Mastering Shiny:

├── DESCRIPTION
├── NAMESPACE
├── R
  ├── app.R
  ├── modules.R
├── app.R
├── inst
└── tests

My Shiny application lives in R/app.R wrapped in a function called callApp(). The file ./app.R simply calls callApp().

The file modules.R represents actually many .R files that contain various functions and modules that comprise my Shiny application. My Shiny application looks like:

callApp <- function() {
  pool <- pool::dbPool(
    RMySQL::MySQL(),
    dbname = config::get("db_name"),
    host = config::get("db_host"),
    username = Sys.getenv("DB_USERNAME"),
    password = Sys.getenv("DB_PASSWORD")
  )

  onStop(function() {
    pool::poolClose(pool)
  })

  header <- dashboardHeader(
    title = "title"
  )

  sidebar <- dashboardSidebar(
      ....
  )

  body <- dashboardBody(
      ....
  )

  ui <- dashboardPage(header, sidebar, body)

  server <- function(input, output, session) {
    modulefunction_server("module1")
    modulefunction_server("module2")
  }

  shiny::shinyApp(ui, server)
}

As you can see, I am using the pool package to connect to a MySQL datatbase and retrieve my data. I have placed pool at the top of the main app.R as described in the pool documentation. However, the problem I am having is in regards to my function modulefunction_server():

modulefunction_server <- function(id) {

  shiny::moduleServer(id, function(input, output, session) {

      dates <- datepicker_server("datepicker1")

      data <- getData(startDate = dates$date_from(),
                      endDate = dates$date_to())

   
      output$report <- DT::renderDT({
        make_table(data)
      })
   })
 }

It is actually the function getData() that requires the pool object. And getData() is stored in my modules.R file:

getData <- function(startDate, endDate) {
  sql <- getSQLFromFile(system.file("sql","query.sql", package = "mypackage"))
  query <- DBI::sqlInterpolate(pool, sql, startDate = startDate, endDate = endDate)
  DBI::dbGetQuery(pool, query)
}

When I run my application, I receive the error:

 error in evaluating the argument 'conn' in selecting a method for function 'sqlInterpolate': object 'pool' not found

What is the recommended way of providing access to pool for all functions in a Shiny application? I suppose I could pass pool as an argument to each of the functions, but this becomes very confusing and messy when attempting to retrieve data from pool via a function that is nested 5, 10, or 15 functions deep into a complex Shiny application.

UPDATE:

I have found this related GitHub issue and have attempted to implement the solution:

I have moved pool into a globals.R file. I source globals.R at the beginning of my app.R I move the onStop() function into shinyApp() function:

callApp <- function() {
  source("R/globals.R")

  onStop(function() {
    pool::poolClose(pool)
  })

  header <- dashboardHeader(
    title = "title"
  )

  sidebar <- dashboardSidebar(
      ....
  )

  body <- dashboardBody(
      ....
  )

  ui <- dashboardPage(header, sidebar, body)

  server <- function(input, output, session) {
    modulefunction_server("module1")
    modulefunction_server("module2")
  }

  shiny::shinyApp(ui = ui, server = server, onStart = function() {
    onStop(function() {
       pool::poolClose(pool)
     })
  })
}

Unfortunately, while this allows pool to be visible to all functions, I am still left with a Warning in (function (e) : You have a leaked pooled object every time I rebuild my package.

Answer 1

I chose to use a global variable, bearing in mind I'm working on a Plumber application, not a Shiny application and my application is quite simple so YMMV...

In the top level script where Plumber is instantiated

library(plumber)
library(pool)
library(RMySQL)

# store mysqlPool in global scope,
# knowing this is the entry-point of the application...
mysqlPool <<- dbPool(
    MySQL(),
    # connection info ...
)

pr <- plumb(
    # ...
)

pr$registerHooks(
    list(
        'exit' = function() {
            poolClose(mysqlPool)
        }
    )
)

Then in a lower level "library" script, to access the connection, something like this:

library(RMySQL)

conn <- poolCheckout(mysqlPool)

query <- sqlInterpolate(
    conn,
    "INSERT INTO my_table (field1, field2) VALUES (?field1, ?field2)",
    field1,
    field2
)

dbSendQuery(conn, query)

poolReturn(conn)