Update Shiny DataTable & SQLite database after values changed in editable renderDT object

shiny
, , , ,
1

I have created a [Shiny app] for an editable SQLite membership database dashboard. My plan is to use the DT and DBI packages to edit specific values, then push the updates back to the SQLite table by dropping the old one and writing the new one in its place (haven't coded the drop table yet, but will go into button actions).

The UI side behaves as expected, but when I edit a page and hit my Write to Table button, then reload or close out of the app, it does not look like the DT or database has updated.

My questions (beyond "How can this work?"):

  1. When the editable function works right, is it editing long_sepal or data?
  2. Should I be able to see the updates to the dataframe in the Global environment as they are made?

I included a reprex using Iris data below.

Thank you! -Justin

library(shiny)
require(shinydashboard)
library(dplyr)
library(RSQLite)
library(DBI)
library(dbplyr)
library(dplyr)
library(DT)

#create SQLite connection and open it
con <- DBI::dbConnect(RSQLite::SQLite(), 
                      "/Users/admin/ICG/iris.db")

src_dbi(con)

#split SQLite datatable into two seperate datatables
data <- tbl(con, "data")
data <- data %>% as.data.frame()
long_sepal <- filter(data, sepal_length>5.5)
short_sepal <- filter(data, sepal_length<5.5)

header <- dashboardHeader(title="Iris Dashboard")

#sidebar with "Write to DB" reactive button
sidebar <- dashboardSidebar(sidebarMenu(
  div(style="display:inline-block;width:32%;text-align: 
      center;",actionButton("action", label = "Write to 
                            DB"))))

#Body element of the dashboard
frow1 <- fluidRow(
  
  box(
    title = "Long Sepal"
    ,status = "primary"
    ,solidHeader = TRUE 
    ,collapsible = TRUE 
    ,DTOutput("table1", height = "300px")
  )
  
  ,box(
    title = "Short Sepal"
    ,status = "primary"
    ,solidHeader = TRUE 
    ,collapsible = TRUE 
    ,DT::dataTableOutput(outputId = "table2", height = 
                           "300px")
  )    
)
# merge into dashBoard body
body <- dashboardBody(frow1)

# Define UI for dashboard page and 
ui <- dashboardPage(title = 'Iris Dashboard', 
                     header, sidebar,
                    body, skin = 'black'
)

# Define server logic required for editable tables
server <- function(input, output) {  
  
  output$table1 <- renderDT(long_sepal, options = 
                              list(scrollX = TRUE), editable = TRUE)
  output$table2 <- renderDT(short_sepal, options = 
                              list(scrollX = TRUE), editable = TRUE)
  
  #necessary code to replace data once edited
  proxy1 = dataTableProxy('table1')
  
  observeEvent(input$x2_cell_edit, {
    info = input$x2_cell_edit
    str(info)
    i = info$row
    j = info$col
    v = info$value
    long_sepal[i, j] <<- DT::coerceValue(v, 
                                            long_sepal[i, j])
    replaceData(proxy1, long_sepal, resetPaging = 
                  TRUE)  # important
  })
  
  #Write to SQLite database
  data <- eventReactive(input$action, {
    dbWriteTable(con, "data", data.frame(data), append 
                 = TRUE)
    data <- dbReadTable(con, "data")
    return(data)
  })  
}

# Run the application 
shinyApp(ui = ui, server = server)```
2
  1. When the editable function works right, is it editing long_sepal or data?

In observeEvent(input$x2_cell_edit, { the line long_sepal[i, j] <<- DT::coerceValue is updating long_sepal. This will not update the upper definition of data as well.

  1. Should i be able to see the updates to the dataframe in the Global environment as they are made?

Once the setting of long_sepal[i,j] <<- is completed, it should be updated globally. If you'd like to save to the data base immediately, you could do a database update call right after you call replaceData. Since you know the row and column value, it could be a surgical update to the data base (using a WHERE clause), rather than requiring a complete rewrite of the whole data set.

Problem viewing edits when webpage is refreshed

Move the R lines at the top from # create SQLite connection and open it to short_sepal <- filter(data, sepal_length<5.5) to inside the server function. This will create a new connection for each user and will always refresh from the database on webpage load.

If you run into max connection issues, please check out the pool R package.

3

Thank you barret! I moved the database update lines into the server function.

That change still doesn't yield changes on reload or in the database after clicking the button.

Based on your answer it sounds like it could be because I am re-writing data which long_sepal does not update. What would be the most efficient way for me to update the parent database as well as the view?

4

I've updated your cell edit observeEvent to update data as well.

This once the action button is hit, the updated data will be saved to the table. (Did not test on a db)

long_sepal_rows <- which(data$sepal_length > 5.5)

observeEvent(input$x2_cell_edit, {
  info = input$x2_cell_edit
  str(info)
  i = info$row
  j = info$col
  v = info$value

  # get new value
  new_value <- DT::coerceValue(v, long_sepal[i, j])

  # update local copy of long_sepal
  long_sepal[i, j] <<- new_value

  # update local copy of data
  data[long_sepal_rows[i], j] <<- new_value

  # update browser
  replaceData(proxy1, long_sepal, resetPaging = TRUE)  # important
})
  
#Write to SQLite database
### do not set `data` here
### since we are not setting, could call `observeEvent` instead of `eventReactive`
observeEvent(input$action, {
  dbWriteTable(con, "data", data.frame(data), append = TRUE)
  ### since you are only setting, there is no need to re-read the data 
  ### as the local value is true to begin with
})