Preferred approach for managing database connections in a quarto shiny document

[jhelvy]

Description

Hi, we're working on a survey project (surveydown) that uses quarto to render to a shiny app. One question we had was about the preferred approach for managing database connections. When I read about the pool package and the preferred approach for database connections (e.g. this article), the examples are all traditional shiny apps. In them, the database pool is created outside of the ui or server, like this:

library(shiny)
library(DBI)
library(pool)

pool <- dbPool(
  drv = RMySQL::MySQL(),
  dbname = "shinydemo",
  host = "shiny-demo.csa7qlmguqrf.us-east-1.rds.amazonaws.com",
  username = "guest",
  password = "guest"
)

ui <- fluidPage(
  textInput("ID", "Enter your ID:", "5"),
  tableOutput("tbl"),
  numericInput("nrows", "How many cities to show?", 10),
  plotOutput("popPlot")
)

server <- function(input, output, session) {
  output$tbl <- renderTable({
    pool %>% tbl("City") %>%
      filter(ID == input$ID)
  })
  output$popPlot <- renderPlot({
    df <- pool %>% tbl("City") %>%
      head(as.integer(input$nrows)[1]) %>% collect()
    pop <- df$Population
    names(pop) <- df$Name
    barplot(pop)
  })
}

shinyApp(ui, server)

What I'm trying to understand is what is the equivalent approach for a quarto shiny document? My guess is that the pool connection should go in the setup code chunk, but I'm not 100% sure. Using the same example, this is how I am assuming it should be set up. Can someone confirm if this is appropriate?

---
server: shiny
filters: [surveydown]
---

```{r}
#| context: setup
#| echo: false
#| warning: false
#| message: false

library(shiny)
library(DBI)
library(pool)

pool <- dbPool(
  drv = RMySQL::MySQL(),
  dbname = "shinydemo",
  host = "shiny-demo.csa7qlmguqrf.us-east-1.rds.amazonaws.com",
  username = "guest",
  password = "guest"
)
```

```{r}
textInput("ID", "Enter your ID:", "5")
tableOutput("tbl")
numericInput("nrows", "How many cities to show?", 10)
plotOutput("popPlot")
```

```{r}
#| context: server

output$tbl <- renderTable({
pool %>% tbl("City") %>%
  filter(ID == input$ID)
})

output$popPlot <- renderPlot({
  df <- pool %>% tbl("City") %>%
    head(as.integer(input$nrows)[1]) %>% collect()
  pop <- df$Population
  names(pop) <- df$Name
  barplot(pop)
})
```

[gadenbuie]

@jhelvy That's certainly where I would put it :) I'm fairly certain that Quarto + Shiny uses more or less the same execution model as R Markdown + Shiny, in which case you can think of the context: setup chunk like global.R code or like global logic loaded outside of the server function in an app.R file (the block that usually but not necessarily appears at the top of the app.R file).

[jhelvy]

Okay great, I just realized today that we weren't quite setting this up correctly. Everything was still working, but I wanted to make sure this is the expected way to set this up. Thanks for the quick reply!