In bborgesr/pool: Object Pooling

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
library(shiny)
onStop <- function(...) {
  shiny::onStop(...)
  invisible()
}

library(pool)

This article shows you how to use dbplyr with pool in your shiny app. To get started, make sure you have all the packages you need:

#| eval: false
install.packages(c("shiny", "DBI", "dbplyr", "dplyr", "pool")

Getting started

For the purposes of this article I'm going to start by creating a very simple in-memory SQLite database. This makes it easy to show you real code and the only difference from what you'll use is details of the database connection.

path <- tempfile()
con <- DBI::dbConnect(RSQLite::SQLite(), dbname = path)
DBI::dbWriteTable(con, "mtcars", mtcars)

First, let's consider how you might connect use a simple database just with dplyr:

library(dplyr, warn.conflicts = FALSE)

con <- DBI::dbConnect(RSQLite::SQLite(), dbname = path)
mtcars_db <- con %>% tbl("mtcars")

mtcars_db %>% 
  filter(cyl == 8) %>% 
  head()

Now, let's do the same thing using with a pool:

con <- pool::dbPool(RSQLite::SQLite(), dbname = path)
mtcars_db <- con %>% tbl("mtcars")

mtcars_db %>% 
  filter(cyl == 8) %>% 
  head()

As usually, all you need to do is change DBI::dbConnect() to pool::dbPool(), and pool will take care of the rest!

Shiny apps

Now lets show it in action in a shiny app:

library(shiny)
library(dplyr, warn.conflicts = FALSE)

pool <- pool::dbPool(RSQLite::SQLite(), dbname = path)
onStop(function() {
  pool::poolClose(pool)
})

ui <- fluidPage(
  textInput("cyl", "Enter number of cylinders:", "6"),
  numericInput("nrows", "How many rows to show?", 10),
  tableOutput("tbl")
)

server <- function(input, output, session) {
  output$tbl <- renderTable({
    pool %>% 
      tbl("mtcars") %>%
      filter(cyl == local(input$cyl)) %>% 
      head(input$nrows)
  })
}

if (interactive())
  shinyApp(ui, server)

There's one other important tool to note: dbplyr::local(). This tells dbplyr to run input$cyl locally (retrieving the value the user typed), rather than trying to convert it to SQL.

Note that there is no need to do your own input sanitizing for SQL injection prevention (i.e. no need to call a function like DBI::sqlInterpolate()) because dbplyr takes care of that for you.

bborgesr/pool documentation built on Feb. 16, 2024, 2:48 a.m.