How to implement a cleanup routine in R Shiny?

Question

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.

highlighting the comment by Brian D on the accepted answer - https://shiny.rstudio.com/articles/pool-basics.html gives an example of the best way I've seen to manage db connections in shiny (as of 2019) — lunguini, Jul 18 '19 at 14:35

Matthew Plourde · Accepted Answer · 2014-10-16T15:07:46.293

34

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.

edited Oct 16 '14 at 15:07

answered Oct 16 '14 at 13:31

Matthew Plourde

43,932
7
96
113

This works for me. If you only need the function to be called on exit, then you do not need to give it a name. – sdgfsdh May 18 '15 at 13:02
how does this work in the current version of shiny, where you have one script (e.g., app.R with ui and server defined in it). Does the connection go outside the server function definition or inside? How do you appropriately disconnect in both cases? – Brian D Oct 25 '17 at 17:32
1

answer is here: https://shiny.rstudio.com/articles/pool-basics.html create a connection using `pool` outside of both ui and server functions – Brian D Oct 25 '17 at 20:18
2

I HIGHLY recommend against doing this. This means that if you have two users using the app and one user closes their tab, the other user also loses the connection. – DeanAttali Jan 02 '21 at 23:48
@DeanAttali Do you have an example of this ? If you create your connection within your `server()` shouldn't each user lose their own connection ? – Trusky Apr 16 '21 at 01:34
It depends on your architecture. If you create a separate db connection for each user, that's fine. But for real life applications it's often impractical to have a separate connection per user. – DeanAttali Apr 16 '21 at 04:23

score 18 · Answer 2 · answered Oct 30 '17 at 11:23

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")
    })
  }
)

onStop is how i've been doing it, and it works great in production. Note that it can result in excess unclosed db connections when testing the app locally in Rstudio. The Rstudio "Stop" button runs onStop. However, if you are developing an app and hit "RunApp" after each change to reload the app, a new connection is made each time but onStop is never run to clean up the older connections. So make sure you hit Stop and then RunApp after each change or you'll have a bunch of zombie db connections. — Brian D, Jun 15 '22 at 16:50

score 6 · Answer 3 · answered Sep 23 '16 at 13:20

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
})

score 0 · Answer 4 · answered Apr 24 '14 at 18:55

0

You can use on.exit(dbDisconnect(db)) inside you shinyServer function definition.

answered Apr 24 '14 at 18:55

tmpname12345

2,891
18
20

1

In that case, should I also put `db <- dbConnect("SQLite", DB_PATH)` inside `shinyServer`? I presume anything inside `shinyServer` is executed once per client, and anything outside `shinyServer` is only executed once by the server. – mchen Apr 24 '14 at 19:01
I don't think it makes a difference. In either case it would be executed once for each client connection, whether it's inside the `shinyServer` definition or not. – tmpname12345 Apr 24 '14 at 19:09
4

@tmpname12345, I tested it and it actually does not work: shinyServer will be called once when the runApp is launched. In that case, the connection will be closed in the first run, and would not sustain during the shiny session. – Gang Liang Oct 06 '14 at 04:33

How to implement a cleanup routine in R Shiny?

4 Answers4

Linked

Related