Greetings,
I would love to get some practical insight / overview for interacting with databases using dbplyr via R Shiny. Currently I have things set up where on one tab the user can view and filter the main table of the test database using RSQlite, dbplyr, and datatables. On another tab, the user can upload new data to be added to the database, and edit the uploaded data using another instance of datatables. I've also simulated how the added data will look in the database using head(), collect(), and bind_rows().
However, using a submit button to make the changes to the database, I can't figure out how to update the database data on the "view" tab of the Shiny app.
Should the lazy tbl be a reactiveValue() or be in a reactive() expression? Or should the tbl first be sent through collect() and then be turned into a reactive value? I've tried a couple different schemes, including calling the reactive value for the data within the submit button observeEvent expression, but just calling data_rct() inside the observeEvent for the submit button doesn't seem to actually update the referenced database table. I've tested this with print statements.
I would love to hear how (in general terms) one should go about updating and then displaying updated database data in an organized way in R Shiny. I have avoided sharing any code because I don't think it would help discussion, but I am happy share snippets if anyone is interested!
Basically all I am doing is simulating a new row and adding it using rows_insert.