Best practice for SQL-Connection in reactive Shiny App

shiny
,
1

When i use a SQL-connection in a reactive Datatable in Shiny. Should i disconnect the connection anywhere? And if yes, at what point? Or should i use pool?

I asked this on twitter Link to Tweet and some fellow #rstats-people pointed me to this board, so we can talk about the best way to approach this.

Here´s some code i worte as example

library("shiny")
library("shinydashboard")
library("tidyverse")
library("RMariaDB")
library("DBI")
library("DT")


ui <- dashboardPage(
  dashboardHeader(title = "Basic dashboard", titleWidth = 450),
  dashboardSidebar(disable = TRUE),
  dashboardBody(
    fluidRow(
      box(
        DT::DTOutput('mytable') #dataTableOutput
      )
    )
  )
)

server <- function(input, output, session){

  # Run every 30 seconds
  QueriedData <- reactivePoll(30000,session, 
                              
 #A function whose values over time will be tested for equality; inequality indicates that the underlying value has changed and needs to be invalidated and re-read using valueFunc
  checkFunc = function(){ 
  
    # connect
    con <- DBI::dbConnect(RMariaDB::MariaDB(), 
                          #RMySQL::MySQL(),
                          host = '192.168.0.0',
                          user = 'xkcd',
                          password = 'correcthorsebatterystaple',
                          dbname = 'mydb')
   
    # This returns the current rowcount of the mysqltable 
    rowcount <- dbGetQuery(con, "SHOW TABLE STATUS;") %>% filter(Name == "mysqltable") %>% pull(Rows)
    },
  valueFunc = function() {
    test_db <- dbReadTable(con, "mysqltable")
    })
  
  output$mytable  <- DT::renderDT({ 
    test_db <- QueriedData() %>% as.data.frame()
    DT::datatable(test_db)
  })
}

shinyApp(ui, server)
1 Like
2

Hi, yes, you can place it here: onStop(function() {dbDisconnect(con)}). You may also wish to consider using the pool package: http://db.rstudio.com/pool/

5 Likes
3

Thanks, Edgar!
Here´s how it looks afterwards:


library("shiny")
library("shinydashboard")
library("tidyverse")
library("RMariaDB")
library("DBI")
library("DT")


ui <- dashboardPage(
  dashboardHeader(title = "Basic dashboard", titleWidth = 450),
  dashboardSidebar(disable = TRUE),
  dashboardBody(
    fluidRow(
      box(
        DT::DTOutput('mytable') #dataTableOutput
      )
    )
  )
)

server <- function(input, output, session){

  # Run every 30 seconds
  QueriedData <- reactivePoll(30000,session, 
                              
 #A function whose values over time will be tested for equality; inequality indicates that the underlying value has changed and needs to be invalidated and re-read using valueFunc
  checkFunc = function(){ 
  
    # connect
    con <- DBI::dbConnect(RMariaDB::MariaDB(), 
                          #RMySQL::MySQL(),
                          host = '192.168.0.0',
                          user = 'xkcd',
                          password = 'correcthorsebatterystaple',
                          dbname = 'mydb')
   
    # This returns the current rowcount of the mysqltable 
    rowcount <- dbGetQuery(con, "SHOW TABLE STATUS;") %>% filter(Name == "mysqltable") %>% pull(Rows)

 # disconnect database
 DBI::dbDisconnect(con
    },
  valueFunc = function() {

 # connect
    con <- DBI::dbConnect(RMariaDB::MariaDB(), 
                          #RMySQL::MySQL(),
                          host = '192.168.0.0',
                          user = 'xkcd',
                          password = 'correcthorsebatterystaple',
                          dbname = 'mydb')
    test_db <- dbReadTable(con, "mysqltable")
    })
  
  output$mytable  <- DT::renderDT({ 
    test_db <- QueriedData() %>% as.data.frame()
    DT::datatable(test_db)
  })
}

shinyApp(ui, server,  onStop(function() {dbDisconnect(con)}))
1 Like