Databases & APIs

rstudio::conf(2022)
Building Production-Quality Shiny Applications

Eric Nantz

The R Process in Shiny

Powerup with Delegation

Databases and Shiny

SQL Jedi

{dplyr} Jedi

Interface to Databases: {DBI}

Unified set of methods & classes bridging interfacing R to database management systems (DBMS)

  • Connect and disconnect from DB
  • Execute queries
  • Extract results
  • Obtain metadata when available
  • Each DBMS supported by a dedicated package

All About Connections

You have used connections in R (and may not realize it)

read.csv("path/to/file.csv", stringsAsFactors = FALSE)

readr::read_csv("path/to/file.csv")

openxlsx::write.xlsx(df, file = "/path/to/file.xlsx")


Behind the scenes: connections are dynamically created (and terminated) during file input/output.

Database Options

  • Relational databases come in many flavors

SQLite


Written to file

Open access

{RSQLite}

Ideal for prototyping DB solutions

PostGreSQL


Hosted on server

Access via authentication

{RPostgres}

Ideal for production-grade DB workflows

Defining Connection

library(DBI)
library(RSQLite)

# initialize connection object
con <- dbConnect(
  drv = RSQLite::SQLite(),
  dbname = ":memory:"
)

# send data frame to a table
dbWriteTable(con, "sample_data", as.data.frame(sample_data))

# disconnect when done
#dbDisconnect(con)

Applying your {dplyr} Skillz

{dbplyr} provides automatic translation from dplyr syntax to SQL statements

  • Integrates with connection objects created by {DBI}
  • Calls are evaluated lazily: Only when you request results
  • Common dplyr verbs supported out of the box

Applying your {dplyr} Skillz

library(dplyr)

sample_db <- tbl(con, "sample_data")

sample_db %>%
  group_by(department) %>%
  summarize(total_objects = sum(n_objects, na.rm = TRUE))
# Source:   lazy query [?? x 2]
# Database: sqlite 3.39.1 [:memory:]
   department                                total_objects
   <chr>                                             <int>
 1 Ancient Near Eastern Art                            116
 2 Arms and Armor                                       68
 3 Arts of Africa, Oceania, and the Americas            88
 4 Asian Art                                            32
 5 Costume Institute                                   104
 6 Drawings and Prints                                  60
 7 Egyptian Art                                        128
 8 European Sculpture and Decorative Arts              360
 9 Greek and Roman Art                                 424
10 Islamic Art                                         164
# … with more rows

Your Turn: Exercise 1

In the application ex-1/app.R, pivot the backend to leverage a SQLite database. See the Exercise 1 page for additional details.

10:00

Connections in Shiny

Logical ways to manage connections when developing solo

Connections in Shiny

  • Many users creating connections
  • Potential for degraded performance in your app

Enter the {pool}!

Abstraction layer on top of database connection objects

  • Holds a group of connections to database
  • Knows to expand or reduce connections as needed

Let’s Dive In

con <- dbConnect(
  drv = RSQLite::SQLite(),
  dbname = ":memory:"
)
pool <- dbPool(
  drv = RSQLite::SQLite(),
  dbname = ":memory:"
)
  • pool object a drop-in replacement for con
  • Each query goes to the pool first, then fetches or initializes a connection
  • Not necessary to create connections yourself

Executing APIs from Shiny

AP - What?

Application Programming Interface: Method of communicating between different software services

Benefits of APIs

  • Communication using a standard set of web request protocols
  • Agnostic to the software or language performing the request
  • Capable of producing many types of output

How can we communicate (and create) APIs in R?

{httr2}

A ground-up rewrite of {httr} providing a pipeable API

  • Build a request object to facilitate different pieces of a request workflow
  • Ability to perform dry-runs before actually sending the request
  • Automatic conversion of HTTP errors to native R errors

Code-Along

Code-Along 1: Calling the MET Museum API using {httr2}

Your Turn: Exercise 3

Call the MET Museum API and a custom image overlay API within the Shiny app. See the Exercise 3 page for additional details.

10:00

Database Resources

API Resources

Cleaning Up

shinyprod.com