Using the 'pool' package with a remote database in Rhino

Question

I'm new to using Rhino to create Shiny applications and I'm trying to create my first application with a remote connection to a MariaDB database.

In order not to worry about connection handling and performance, I am using 'pool' to establish the connection to the database. It is the best option recommended by Shiny (here). This is an example code that simulates my application:

# Code in 'app/main.R'

# box::use
box::use(
  shiny[bootstrapPage, div, h1, moduleServer, NS, renderText, tags, textOutput],
)
box::use(
  DBI[dbConnect, dbGetQuery],
  pool[dbPool]
)
box::use(
  app/view/table
)

# Pool Connection
pool <- dbPool(
  drv = RMariaDB::MariaDB(),
  dbname = "shinydemo",
  host = "shiny-demo.csa7qlmguqrf.us-east-1.rds.amazonaws.com",
  username = "guest",
  password = "guest"
)

#' @export
ui <- function(id) {
  ns <- NS(id)
  
  bootstrapPage(
    h1("My Sample Dataset"),
      table$ui(ns("table"))
    )
}

#' @export
server <- function(id) {
  moduleServer(id, function(input, output, session) {
    data <- dbGetQuery(pool, "SELECT * FROM City LIMIT 10;")
    table$server("table", data = data)
  })
}

If I run the application with the above code, the application runs perfectly and retrieves the database query but I get a warning message saying:

"Warning in (function (e) : You have a leaked pooled object. Warning: call dbDisconnect() when finished working with a connection".

The thing is that I'm still getting used to the Rhino structure but I need to close the 'pool' connection when the application is closed. According to what I've read in Shiny's Reference (here) I can close it using this code (for example in Global.R):

onStop(function() {
  print("DB Closed")
  poolClose(pool)
})

But I don't know where or how to invoke the 'onStop' function in Rhino. Could you help me, please?.

Thank you very much,
Wardiam

Answer 1

I've finally found the solution myself and I'm sharing it in case someone new to Shiny and Rhino has the same problem.

I created a new script (e.g. DB_Connection.R) in the /app/logic folder with the following code:

# app/logic/DB_Connection.R

# Load the necessary libraries
box::use(
  shiny[onStop],
  pool[dbPool, poolClose]
)

pool <- dbPool(
  drv = RMariaDB::MariaDB(),
  dbname = "shinydemo",
  host = "shiny-demo.csa7qlmguqrf.us-east-1.rds.amazonaws.com",
  username = "guest",
  password = "guest"
)

  # To close the pool connection
  onStop(function() {
    print("DB Closed")
    pool::poolClose(pool)
  })

Then I called the script from the main.R file (before the UI and server sections) using the 'box' library:

# app/main.R
...
# box::logic
box::use(
  app/Logic/DB_Connection
)
...
# UI section
...
# server section
...

And that's it. Now when running your application in several windows of the web browser, you will see that if you close the windows nothing happens (the 'pool' does not close) until you close the first window that opens in RStudio.

To confirm that the DB closing has been executed correctly, you should see the message "DB Closed" in the console.

I hope this can help other people who are as "lost" as me. Good luck with your project!.

Wardiam