Best way to disconnect database in shiny


#1

I have an app where I am trying to get the data into a sqlite database. somehow I am not able to dbdisconnect the database by using onsessionended and I think this is the reason next time when I run the app and try to create a table it doesn't work properly.

I can paste the entire app and you can run it. It is fine and simple

Just let me know if there is anything I am doing wrong.

I just want to

1> upload the file in db
2> disconnect database
3> reset the fileinput parameter.

But somehow it doesn't work for me.

Please answer me anything you know about these things.


# Load_Libraries ----------------------------------------------------------

library(RPostgres)
library(DBI)
library(data.table)
library(shiny)
library(shinyBS)
library(shinydashboard)
library(dashboardthemes)
library(tidyverse)
library(shinyjs)
library(DT)
library(RSQLite)
library(DBI)

options(shiny.maxRequestSize=200*1024^2) 
dir.create(file.path("D:/", "HRdata"), showWarnings = FALSE)


# Header ------------------------------------------------------------------

main_header<- dashboardHeader(
    title = shinyDashboardLogoDIY(
        boldText="HR",
        mainText="Dashboard"
        ,badgeText = "Beta"
        ,textSize = 17
        ,badgeTextColor = "white"
        ,badgeTextSize = 2
        ,badgeBackColor = "#40E0D0"
        ,badgeBorderRadius = 3
    )
    
)


# Sidebar -----------------------------------------------------------------

main_siderbar<- dashboardSidebar(
    sidebarMenu(
        menuItem(id='file_menu',text = 'upload file',
                 icon = icon('file-excel-o',"fa-2x",lib='font-awesome'),
        
        radioButtons("file_type", label = ("Please Select the Type of File uploaded"),
                     choices = list("XLSX" = 'XLSX', "CSV" = 'CSV'), 
                     selected = "XLSX"),
        
        fileInput("file_upload", "Choose a File",
                  accept = c(
                      "text/csv",
                      "xlsx",
                      "xls",
                      "text/comma-separated-values,text/plain",
                      ".csv")),
        
        uiOutput('select_sheet'),
        
        selectInput('table_sql',label = 'Please select the Table',
                    choices =c('casual','permanent','attendence'),
                    multiple = FALSE
                        ),
        
        actionButton('file_upload_sql','  upload in database',
                     icon = icon('database',"fa-2x",lib='font-awesome'),
                     width = '90%')
        
                    )
        )
)
# Body --------------------------------------------------------------------

main_body<- dashboardBody(
    

# themes ------------------------------------------------------------------
            shinyDashboardThemes(
                theme = "purple_gradient"
            ),


# content -----------------------------------------------------------------

       DTOutput('file_data')

   
    
)


# Ui_function -------------------------------------------------------------

main_ui<- dashboardPage(
    
    main_header,
    main_siderbar,
    main_body
    
)


# Server_Function ---------------------------------------------------------


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

# connect_database --------------------------------------------------------
    
    sql<- reactive({
        dbConnect(SQLite(),
                  dbname='d:/HRdata/maindata.sqlite'
        )
    })
    
    
# Check number of Sheets --------------------------------------------------
    
    output$select_sheet<-renderUI({
        req(input$file_upload)
        if(input$file_type == 'XLSX'){
            if(input$file_upload$name %>% str_detect('.xlsx')){
                
                selectInput('i_select_sheet','Select Sheet',
                        choices =excel_sheets(input$file_upload$datapath),
                        multiple = FALSE,selected = 1
                )}else{
                    return(NULL)
                }}else{
                    return(NULL)
                    }
    })   
    
    
# create_data -------------------------------------------------------------

    file_table<-reactive({
    req(input$file_upload)
        
        if(input$file_type == 'XLSX'){
            if(input$file_upload$name %>% str_detect('.xlsx')){
                req(input$i_select_sheet)
                setDT(read_excel(input$file_upload$datapath,sheet = input$i_select_sheet))
            } else{
                return(NULL)
            }
        }else{
            if(input$file_upload$name %>% str_detect('.csv')){
            fread(input$file_upload$datapath)
            }else{
                    return(NULL)
                }
        }
})


# Render_Table ------------------------------------------------------------

output$file_data<-renderDT({
    file_table()
})    





# Upload_in_SQL -------------------------------------------------------------

observeEvent(input$file_upload_sql,
        function(){
            dbWriteTable(sql(),
                         input$table_sql,
                         value =file_table(),
                         append=TRUE
            )
        }
)


# reset_fileinput ---------------------------------------------------------------

observeEvent(input$file_upload_sql,{
    reset('file_upload')
})


# show_message ------------------------------------------------------------

observeEvent(input$file_upload_sql,{
    showModal(modalDialog(
        title = "Important message",
        "Please Don't click upload again untill you change the file!",
        easyClose = FALSE
    ))
})



# Session_close -----------------------------------------------------------

     session$onSessionEnded(function(session){
       dbDisconnect(sql())
    })
    

# endServer ---------------------------------------------------------------


}

# run_app -----------------------------------------------------------------

shinyApp(main_ui,main_server)


#2

For shiny app and database, I kept in mind from several talks to use the {pool} :package: see http://db.rstudio.com/pool/

There are also some very interesting documentation on shiny website about data management in shiny apps, with specifity when using databases. see http://shiny.rstudio.com/articles/#data