How to implement a cleanup routine in R Shiny?

For instance, my shiny app might open a DB connection

# server.R
db <- dbConnect("SQLite", DB_PATH)
shinyServer(
    ...  # things involving db
)

Now, how to ensure that the connection db is closed properly (via dbDisconnect(db)) when the Shiny session ends? Indeed, should cleanup be performed for each client that connects to the server, or just once?

I simply fear that with multiple users connecting and disconnecting to the Shiny app all the time, they'll leave dangling DB connections if not properly cleaned up. Indeed, clients may disconnect without warning simply by closing their browsers.

Solution 1:

The correct way to do this is to assign a function that performs your clean-up with session$onSessionEnded. For example, in server.R:

cancel.onSessionEnded <- session$onSessionEnded(function() {
    dbDisconnect(db)
})

You can then call cancel.onSessionEnded to undo the assignment.

Solution 2:

The pre-existing answers don't seem right to me.

• session$onSessionEnded could close the connection when each user disconnects, but in the original question there is only one connection for all the users. Especially when using pool, you don't want to start/stop separate connections for each user.
• on.exit runs straightaway, not waiting until the server exits.

I think the correct answer is to use onStop (https://shiny.rstudio.com/reference/shiny/latest/onStop.html).

Example usage, from the documentation:

library(shiny)
shinyApp(
  ui = basicPage("onStop demo"),

  server = function(input, output, session) {
    onStop(function() cat("Session stopped\n"))
  },

  onStart = function() {
    cat("Doing application setup\n")

    onStop(function() {
      cat("Doing application cleanup\n")
    })
  }
)

Solution 3:

Rstudio published a series of article back in june about best practices to connect to a database. The simple answer is to use pools (see here and here). To make it simple, you define your pool once, and it will handle and manage the connections, opening and closing them as needed. Once the app is disconnected, the pool will close all the connections automatically.

Unfortunately, the pool package doesn't work with SQL Server and ODBC. For that situation (or if you don't want to use pool), they advice to use on.exit inside your server functions.

for example:

getData <- reactive({
cnxn <- dbConnect(...)
on.exit(dbDisconnect(cnxn))

... # your stuff here
})