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 is no longer here
– Dirk is no longer here, Commented Oct 10, 2015 at 4: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
– Blind0ne, Commented Jan 19, 2018 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
– NeonBlueHair, Commented Feb 5, 2018 at 20:10

Thomas K · Accepted Answer · 2018-01-19 13:45:09Z

3

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.

edited Jan 19, 2018 at 13:45

answered Oct 9, 2015 at 23:07

Thomas K

3,35117 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NeonBlueHair Over a year ago

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?

Thomas K Over a year ago

@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.

Collectives™ on Stack Overflow

Setting up data refreshing in Shiny app connected to PostgreSQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related