Setting up data refreshing in Shiny app connected to PostgreSQL

Question

I've looked at this and this thread and a few others but haven't been able to figure out my solution.

I have built a dashboard using R and Shiny, and said dashboard pulls in data from a Postgres db using the RPostgreSQL package. Right now, the code for all the data pulling and analysis is done outside of the shinyServer function, and only the displaying part (the output and render functions) is in the shinyServer portion. I'd like to set it up so that the dashboard data is periodically refreshed and the charts are updated. I've looked into reactivePoll and invalidateLater and understand them, but can't quite figure out how to implement it in my code.

Here's a simplified example server.R code:

library(RPostgreSQL)

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host='host', port='12345', dbname='mydb',
                 user='me', password='mypass')

myQuery <- "select * from table"
myTable <- dbGetQuery(con, myQuery)

foo <- nrow(myTable)
bar <- foo * 2

shinyServer(
  function(input, output, session) {
    output$foo <- renderText(foo)
    output$bar <- renderText(bar)

    session$onSessionEnded(function(){
      dbDisconnect(con)
      dbDisconnect(con2)
      dbUnloadDriver(drv)
    })
  }
)

Now if I want foo to update periodically, that requires me to refresh the dbGetQuery command I have too, and I can't figure out how to make them all work together. Do I need to reformat things and put everything inside the shinyServer function? I have about 250 lines of code and it feels wrong to throw them all in there, and just putting the data pull part in there could mess with the order of things. Any help is appreciated.

Look into `invalidateLater` which you use with a time argument. We use this to fresh plots automatically. — Dirk Eddelbuettel, Oct 10 '15 at 04:49
Did the solution from @Thomas K worked for your problem? I have the same issue and would be glad about some working code example. Thanks in advance! — Blind0ne, Jan 19 '18 at 10:28
@Blind0ne This question is from over two years ago so to be honest I really don't remember any details, but I marked it as being correct so it must have worked at least partially. — NeonBlueHair, Feb 05 '18 at 20:10

Thomas K · Accepted Answer · 2018-01-19T13:45:09.613

I would use reactivePoll instead of invalidateLater, because it will only re-fetch the whole data in case there is new data.

There is however no way around putting the code to fetch the data inside shinyServer, since your subsequent calculations depend on the (reactive) data.

Disclaimer: I have no experience with SQL whatsoever, and I couldn't test my code due to the lack of a suitable database, but from my understanding of shiny the following code should work.

library(RPostgreSQL)

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host='host', port='12345', dbname='mydb',
                 user='me', password='mypass')

check_for_update <- function() {
  dbGetQuery(con, "SELECT MAX(timestamp) FROM table") # edit this part in case
  # the syntax is wrong. the goal is to create an identifier which changes
  # when the underlying data changes
}
get_data <- function() {
  dbGetQuery(con, "select * from table")
}
close_connection <- function() {
  dbDisconnect(con)
  dbUnloadDriver(drv)
}

shinyServer(
  function(input, output, session) {
    # checks for new data every 10 seconds
    data <- reactivePoll(10000, session,
                         checkFunc = check_for_update,
                         valueFunc = get_data)

    # the outputs will only be refreshed in case the data changed
    output$foo <- renderText({
      nrow(data())
    })
    output$bar <- renderText({
      bar <- data() * 2
    })

    session$onSessionEnded(close_connection)
  }
)

Depending on the structure of your app it could be helpful to wrap the calculations into a separate reactive, which you can reuse in several places.

Some notes on code execution with shinyApps can be found in this tutorial.

If you are running into any issues, please leave a comment and I will try to update my post accordingly.

Thanks Thomas, I feel like your code would work, but what I'm concerned about is how it will affect the calculations I do on the data outside of the shinyServer, if I refresh it on the inside. I edited my example to include a line for "bar" to make it better. In this case, the data gets refreshed inside the function, but the calculation that gives us the value for bar only happens once outside it. So would I have to move everything inside and have it run every time the data is refreshed? — NeonBlueHair, Oct 10 '15 at 00:21
@NeonBlueHair I think there is no way around pulling the calculations into `shinyServer`. I don't know what you are doing within your "calculations", but obviously you need to rerun any transformations done on the data if the data itself changes. — Thomas K, Oct 10 '15 at 08:00

Setting up data refreshing in Shiny app connected to PostgreSQL

1 Answers1

Linked