Disconnect from PostgreSQL when close R Shiny App

Question

This question is admittedly similar to this question but the answer provided is not sufficient for my example.

I am creating a Shiny App that connects to a database. The user can provide some inputs and some 'canned' queries will run. Now, I would like to make certain the connection to the database is closed on exit from the app. I could use a button and the stopApp function but this assumes users will be good and always click said button. Users are likely to sometimes close the window leaving the database connection open so I would like to find a way to explicitly close the connection when the user closes the app window.

I suppose one solution would be to instantiate a new connection and close at the end of each query but that seems inefficient.

Here is some working code to demonstrate the problem. If I run this App from Rstudio and then close the window, I still have an existing connection as per dbListConnections(PostgreSQL()). Appending dbDisconnect(con) to the end of the file does not help.

library(shiny)
library(RPostgreSQL)

server <- shinyServer(function(input, output){
  # Read in the dataset
  connectDB <- eventReactive(input$connectDB, {

    if(input$drv != "postgresql"){
      stop("Only 'postgresql' implemented currently")
    }else{
      drv <- dbDriver("PostgreSQL")
      port = 5432
    }

    con <- dbConnect(drv, user = input$user, password = input$passwd, 
                     port = port, host = input$server, dbname=input$db_name)
    con
  })

  output$test <- renderText({
    con <- connectDB()
    "connection success"
  })
})


ui <- shinyUI(
  fluidPage(
    titlePanel("Test Connection"),

    sidebarLayout(
      sidebarPanel(
        textInput("drv", "Database Driver", value="postgresql"),
        textInput("user", "User ID"),
        textInput("server", "Server", value="server"),
        textInput("db_name", "Database Name", value="dbName"),
        passwordInput("passwd", "Password"),
        actionButton("connectDB", "Connect to DB")
      ),

      mainPanel(
        textOutput("test")
      )
    )
  )
)

shinyApp(ui=ui, server=server)

perhaps use `session$onSessionEnded`? – hrbrmstr Jul 02 '15 at 18:41 — hrbrmstr, Jul 02 '15 at 18:41

DeanAttali · Accepted Answer · 2015-07-02T19:59:44.543

7

Do you know about the onSessionEnded of the session variable?

Here's a very basic example that sets a random session id to each user and when they close the window, a function runs that says what session id was closed. It's just a proof of concept, you can tailor it to your database needs trivially I hope

runApp(shinyApp(
  ui = fluidPage(
    textOutput("sessionId")
  ),
  server = function(input, output, session) {
    sessionId <- as.integer(runif(1, 1, 100000))
    output$sessionId <- renderText(paste0("Session id: ", sessionId))
    session$onSessionEnded(function() {
      cat(paste0("Ended: ", sessionId))
    })
  }
))

Edit:

Op said he needs the variable to be reactive, so here's a modification that I believe would allow him what he wants

runApp(shinyApp(
  ui = fluidPage(
    textOutput("sessionId")
  ),
  server = function(input, output, session) {
    values <- reactiveValues(sessionId = NULL)
    values$sessionId <- as.integer(runif(1, 1, 100000))
    output$sessionId <- renderText(paste0("Session id: ", values$sessionId))
    session$onSessionEnded(function() {
      observe(cat(paste0("Ended: ", values$sessionId)))
    })
  }
))

Disclaimer: I'm not sure if putting the observe inside the onSessionEnded callback is the best approach. This works, but I can't vouch for it's "correctness".

edited Jul 02 '15 at 19:59

answered Jul 02 '15 at 18:47

DeanAttali

25,268
10
92
118

Thank you, I was not aware of the `onSessionEnded` variable. Should have thought to look at the session object documentation. To completely solve my problem I needed to create a global variable to refer to the existing connection but this allowed me to do it. – cdeterman Jul 02 '15 at 19:02
If you only have one user connected at a time that's fine. If you're allowing multiple users to connect, then when the first user leaves, I think that would cause all connections to die since all sessions are sharing the same global connection – DeanAttali Jul 02 '15 at 19:49
a valid point, did you have another idea how to get the reactive connection object? – cdeterman Jul 02 '15 at 19:50
Would just moving the `con` object inside of the `shinyServer` be sufficient? – cdeterman Jul 02 '15 at 19:55
Why not just use `on.exit()`? – hadley Jul 02 '15 at 21:58
@hadley where would the `on.exit()` go? If it's inside the server function, it'll just be called after the app initializes. Would you write a wrapper around `shinyApp()` and add it there? – DeanAttali Jul 02 '15 at 22:39
Good point - I thought the function was long running, but indeed it terminates quickly. – hadley Jul 03 '15 at 10:55

Disconnect from PostgreSQL when close R Shiny App

1 Answers1