Render data.frame from SQLite after update

[lgschuck]

Hello there.

I want to insert values from Shiny UI in my SQLite table. And after the update render "again" with DT. I managed to insert new values in the SQLite table, but my query does not update unless 1 hit F5 to update my browser...

I tried this in the server:

====================================================

df_client <- reactive({
dbGetQuery(RSQLITE_DB, 'SELECT * FROM CLIENT')
})

observeEvent(input$btn_insert_client,{

  df_new_client <-
    data.frame(NAME = input$insert_client_name,
             CELL = input$insert_client_cell,
             EMAIL = input$insert_client_email,
             CITY = input$insert_client_city)

  dbAppendTable(RSQLITE_DB, "CLIENT", df_new_client )
}

output$client_table <-
renderDataTable(
df_client (),
options = list(pageLength = 6, dom = 'ftp')
)

====================================================

In the UI i used:

dataTableOutput('client_table')

====================================================

Thanks for the help in advance...

[scottyd22]

Hi @lgschuck. Below is one way I believe achieves the desired outcome. Rather than reading the database into a reactive(), you can try reading it into an initial object within the server. Then, df_client is created using reactiveValues(), setting the initial value equal to the initial database object. In the observeEvent(), once the database is appended with the new input values, one more line is added to update df_client$d. Finally, by referencing df_client$d in the output, the table should update without the need for hitting F5.

server <- function(input, output, session) {
  
  # read in the initial state of the DB
  initial_db = dbGetQuery(RSQLITE_DB, 'SELECT * FROM CLIENT')
  
  # start off setting this reactiveValue equal to the initial DB value
  df_client = reactiveValues(d = initial_db)
  
  observeEvent(input$btn_insert_client,{
    
    df_new_client <-
      data.frame(NAME = input$insert_client_name,
                 CELL = input$insert_client_cell,
                 EMAIL = input$insert_client_email,
                 CITY = input$insert_client_city)
    
    dbAppendTable(RSQLITE_DB, "CLIENT", df_new_client )
    
    # update the reactiveValue with df_new_client appended
    df_client$d <<- bind_rows(df_client$d, df_new_client)
  })
  
  output$client_table <-
    renderDataTable(
      df_client$d,
      options = list(pageLength = 6, dom = 'ftp')
    )

  }

[ismirsehregal]

Here you can find a related answer using reactivePoll.