Can't use dbplyr in a shiny app

[daattali]

Below is a simple reprex

library(shiny)
library(dplyr)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbWriteTable(con, "cars", cars)

ui <- fluidPage(
  numericInput("speed", "speed", 15),
  tableOutput("table")
)

server <- function(input, output, session) {
  my_speed <- reactive({
    input$speed
  })
  
  output$table <- renderTable({
    tbl(con, "cars") %>%
      filter(speed == my_speed()) %>%
      collect()
  })
}

shinyApp(ui, server)

It produces an error no such function: my_speed. It looks like dbplyr thinks that my reactive is a database table? 😕 Note that if I save the reactive value to a local variable earlier and use that variable, then it does work. But it seems strange that I won't be able to use reactives in my sql, and I couldn't find any documentation suggesting this is expected.

[hadley]

You'll need to use !! to cause dbplyr to evaluate locally.

[daattali]

I think this is something that should be mentioned explicitly in documentation regarding shiny. There is a lot of documentation from RStudio regarding using dplyr for running SQL queries and for using SQL in shiny apps, but there is 0 mentions that they don't work together. It's not intuitive behaviour.

I understand your point as to why it doesn't work, but most shiny users would expect this to just work.

[hadley]

They do work together; you just need to tell dbplyr that you want it to evaluate the functions locally, not on the database like all the other functions that your expecting to be translated.

[daattali]

Yes I do understand that.

But if you look at it from a typical user's perspective:

When doing an analysis interactively, they would use

tbl(con, "cars") %>%
      filter(speed == my_speed) %>%
      collect()

Then when placing this inside a shiny app, it's extremely natural (and good practice) to simply replace any variables with reactives. So that would become

my_speed <- reactive(...)

tbl(con, "cars") %>%
      filter(speed == my_speed()) %>%
      collect()

But this results in an error. And a non-useful confusing error too. I really believe this should be clearly documented for shiny usage. The default behaviour shiny users will try to do does not work and there is no documentation or useful error messages as to why. The normal shiny user doesn't understand the technical details of what dbplyr expects and how it's different than using regular dplyr.

[gshotwell]

@daattali am I right that you started using dplyr with database backends yesterday? It might be worth spending a bit more time with the framework to get a sense of what's tricky about this issue, and why it's sort of hard to warn or evaluate the function. Importantly I think you might be atypical in amount of time you've spent with shiny relative to dbplyr. I think most people probably use a database backend inside of, or in reference to, other functions before using it with shiny which makes things a bit more intuitive.

You have to be explicit with what you want to translate to the database whenever you're using a DB backend inside of, or in reference to, other functions. An example is below. The way I think of it is that reactive is a function which returns a value, so it 's not really the same as a variable in the global environment.

library(shiny)
library(dplyr)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbWriteTable(con, "cars", cars)

my_speed <- function() {
  return(4)
}

tbl(con, "cars") %>%
  filter(speed == my_speed()) 

tbl(con, "cars") %>%
  filter(speed == !!my_speed()) 

Another example is the following, what would you expect to happen here?

n <- reactive({
  return(4)
})

tbl(con, "cars") %>%
  filter(row_number() == n()) 

[daattali]

Thanks for the thorough comments @gshotwell

That all makes sense, it's very useful, really. However, I don't think this negates my point that shiny developers don't need to understand these technical details. In an ideal world, yes, this would be a non issue, but in practice people just take their code and put it inside shiny, and replace variables with reactives. It works in any other context. It's ok if it doesn't work here, but I don't see why there's pushback re: documenting it and warning shiny users (I don't mean warning during runtime, I mean warning in docs).

As further proof of how intuitive it is for shiny devs to try this, look at this page on RStudio's website describing how to use dplyr+databases in shiny. The code there no longer works because of this issue. In that code they use input$x rather than a reactive x(), so dplyr actually gave the user a warning. But if you use a reactive instead of a reactiveValues, you wouldn't get any warning. And the problem is that this is the default behaviour any shiny dev would try, and there is NO mention anywhere online that it won't work

I almost always say in github issues that "I don't feel too strongly - you're the dev so obviously it's your call". But in this case, I do feel very strongly that the fact that dplyr doesn't just "work" in shiny when using databases must be documented.

[Ploulack]

As game designer using R and shiny as dashboard to analyse games I work on, I can share my 'noob level' experience.
I initially did not understand why my reactives were interpreted as non supported functions in my bigquery queries.
What I landed on, was to create new local variables as in

my_s <- my_speed()
tbl(con, "cars") %>%
  filter(speed == my_s) %>%
collect()

This wasn't the end of the world... In retrospect i should have made the mental connection that I had to force local evaluation, after all you quickly need to grasp that concept when using Shiny.
but I'm super happy to have fumbled on this thread from the pool article on shiny.rstudio.com where I've now discovered:

tbl(con, "cars") %>%
  filter(speed == !!my_speed()) %>%
collect()

So as a noob level user I do support @daattali that it warrants a mention on the DBI article for Shiny 👍

I'm a big fan and promoter of R/Shiny in video games over the likes of Tableau / GA...huge thank for all the work, such a joy to use and see tools mature.

[hadley]

I've made this an error to be consistent with using (e.g.) input$x — hopefully that will lead to less head scratching.

[daattali]

I just tried it, and yes it's much more helpful now, thanks