<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/database-bulk-update-and-inline-editing-in-a-shiny-application/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://www.mango-solutions.com/blog/database-bulk-update-and-inline-editing-in-shiny-application"> Mango Solutions</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p> <img sizes="100vw" src="https://i1.wp.com/www.mango-solutions.com/blog/images/4/f/2/b/1/4f2b1179fb0d527038b8a066aef3abe49f13a375-database-bulk-update-and-inline-editing-in-shiny-applications.png?w=456&ssl=1" data-recalc-dims="1"></p>
<h5>Ava Yang, Data Scientist</h5>
<p>There are times when it costs more than it should to leverage javascript, database, html, models and algorithms in one language. Now maybe is time for connecting some dots, without stretching too much.</p>
<ul>
<li>If you have been developing shiny apps, consider letting it sit on one live database instead of manipulating data I/O by hand?</li>
<li>If you use DT to display tables in shiny apps, care to unleash the power of interactivity to its full?</li>
<li>If you struggle with constructing SQL queries in R, so did we.</li>
</ul>
<p>Inspired (mainly) by the exciting new inline editing feature of <a rel="nofollow" href="https://blog.rstudio.com/2018/03/29/dt-0-4/" target="_blank">DT</a>, we created a minimal shiny app demo to show how you can update multiple values from DT and send the edits to database at a time.</p>
<p>As seen in the screenshot, after double clicking on a cell and editing the value, Save and Cancel buttons will show up. Continue editing, the updates are stored in a temporary (reactiveValue) object. Click on Save if you want to send bulk updates to database; click on Cancel to reset.</p>
<p><img alt="" sizes="100vw" src="https://i1.wp.com/www.mango-solutions.com/blog/user/pages/01.blog/180.database-bulk-update-and-inline-editing-in-shiny-application/image1.JPG?w=456&ssl=1" data-recalc-dims="1"></p>
<h4>Global</h4>
<p>On the global level, we use <code>pool</code> to manage database connections. A database connection pool object is constructed. With the <code>onStop()</code> function, the pool object gets closed after a session ends. It massively saves you from worrying about when to open or close a connection.</p>
<pre><code># Define pool handler by pool on global level
pool &lt;- pool::dbPool(drv = dbDriver(&quot;PostgreSQL&quot;),
                     dbname=&quot;demo&quot;,
                     host=&quot;localhost&quot;,
                     user= &quot;postgres&quot;,
                     password=&quot;ava2post&quot;)

onStop(function() {
  poolClose(pool)
}) # important!</code></pre>
<p>The next job is to define a function to update database. The <code>glue_sql</code> function puts together a SQL query in a human readable way. Writing SQL queries in R was bit of a nightmare. If you used to assemble a SQL clause by <code>sprintf</code> or <code>past</code>, you know what I’m talking about. The glued query is then processed by <code>sqlInterpolate</code> for SQL injection protection before being executed.</p>
<pre><code>updateDB &lt;- function(editedValue, pool, tbl){
  # Keep only the last modification for a cell
  editedValue &lt;- editedValue %>% 
    group_by(row, col) %>% 
    filter(value == dplyr::last(value)| is.na(value)) %>% 
    ungroup()

  conn &lt;- poolCheckout(pool)

  lapply(seq_len(nrow(editedValue)), function(i){
    id = editedValue$row[i]
    col = dbListFields(pool, tbl)[editedValue$col[i]]
    value = editedValue$value[i]

    query &lt;- glue::glue_sql(&quot;UPDATE {`tbl`} SET
                          {`col`} = {value}
                          WHERE id = {id}
                          &quot;, .con = conn)

    dbExecute(conn, sqlInterpolate(ANSI(), query))
  })

  poolReturn(conn)
  print(editedValue)  
  return(invisible())
}</code></pre>
<h4>Server</h4>
<p>We begin with server.R from defining a couple of reactive values: <strong>data</strong> for most dynamic data object, <strong>dbdata</strong> for what’s in database, <strong>dataSame</strong> for whether data has changed from database, <strong>editedInfo</strong> for edited cell information (row, col and value). Next, create a reactive expression of source data to retrieve data, and assign it to reactive values.</p>
<pre><code># Generate reactive values
rvs &lt;- reactiveValues(
  data = NA, 
  dbdata = NA, 
  dataSame = TRUE, 
  editedInfo = NA 
)

# Generate source via reactive expression
mysource &lt;- reactive({
  pool %>% tbl(&quot;nasa&quot;) %>% collect()
})

# Observe the source, update reactive values accordingly
observeEvent(mysource(), {

  # Lightly format data by arranging id
  # Not sure why disordered after sending UPDATE query in db    
  data &lt;- mysource() %>% arrange(id)

  rvs$data &lt;- data
  rvs$dbdata &lt;- data

})</code></pre>
<p>We then render a DataTable object, create its proxy. Note that the <strong>editable</strong> parameter needs to be explicitly turned on. Finally with some format tweaking, we can merge the cell information, including row id, column id and value, with DT proxy and keep all edits as a single reactive value. See <a rel="nofollow" href="https://github.com/rstudio/DT/pull/480" target="_blank">examples</a> for details.</p>
<pre><code># Render DT table and edit cell
# 
# no curly bracket inside renderDataTable
# selection better be none
# editable must be TRUE
output$mydt &lt;- DT::renderDataTable(
  rvs$data, rownames = FALSE, editable = TRUE, selection = 'none'
)

proxy3 = dataTableProxy('mydt')

observeEvent(input$mydt_cell_edit, {

  info = input$mydt_cell_edit

  i = info$row
  j = info$col = info$col + 1  # column index offset by 1
  v = info$value

  info$value &lt;- as.numeric(info$value)

  rvs$data[i, j] &lt;- DT::coerceValue(v, purrr::flatten_dbl(rvs$data[i, j]))
  replaceData(proxy3, rvs$data, resetPaging = FALSE, rownames = FALSE)

  rvs$dataSame &lt;- identical(rvs$data, rvs$dbdata)

  if (all(is.na(rvs$editedInfo))) {
    rvs$editedInfo &lt;- data.frame(info)
  } else {
    rvs$editedInfo &lt;- dplyr::bind_rows(rvs$editedInfo, data.frame(info))
  }
})</code></pre>
<p>Once Save button is clicked upon, send bulk updates to database using the function we defined above. Discard current edits and revert DT to last saved status of database when you hit Cancel. Last chunk is a little trick that generates interactive UI buttons. When dynamic data object differs from the database representative object, show Save and Cancel buttons; otherwise hide them.</p>
<pre><code># Update edited values in db once save is clicked
observeEvent(input$save, {
  updateDB(editedValue = rvs$editedInfo, pool = pool, tbl = &quot;nasa&quot;)

  rvs$dbdata &lt;- rvs$data
  rvs$dataSame &lt;- TRUE
})

# Observe cancel -> revert to last saved version
observeEvent(input$cancel, {
  rvs$data &lt;- rvs$dbdata
  rvs$dataSame &lt;- TRUE
})

# UI buttons
output$buttons &lt;- renderUI({
  div(
    if (! rvs$dataSame) {
      span(
        actionButton(inputId = &quot;save&quot;, label = &quot;Save&quot;,
                     class = &quot;btn-primary&quot;),
        actionButton(inputId = &quot;cancel&quot;, label = &quot;Cancel&quot;)
      )
    } else {
      span()
    }
  )
})</code></pre>
<h4>UI</h4>
<p>The UI part is exactly what you normally do. Nothing new.</p>
<h4>Bon Appétit</h4>
<ol>
<li>Set up a database instance e.g. PostgreSQL, SQLite, mySQL or MS SQL Server etc.</li>
<li>Download/clone the <a rel="nofollow" href="https://github.com/MangoTheCat/dtdbshiny" target="_blank">GitHub repository</a></li>
<li>Run through script <code>app/prep.R</code> but change database details to one’s own. It writes to DB our demo dataset which is the <em>nasa</em> dataset from dplyr with an index column added</li>
<li>
<p>Also update database details in <code>app/app.R</code> and run</p>
<pre><code>shiny::runApp(&quot;app&quot;)</code></pre>
</li>
</ol>
<h5>Acknowledgements</h5>
<p>Workhorse functionality is made possible by:</p>
<ul>
<li>DBI: R Database Interface</li>
<li>RPostgreSQL: R Interface to PostgreSQL (one of many relational database options)</li>
<li>pool: DBI connection object pooling</li>
<li>DT: R Interface to the jQuery Plug-in DataTables (requires version >= 0.2.30)</li>
<li>Shiny: Web Application Framework for R</li>
<li>dplyr: Data manipulation</li>
<li>glue: Glue strings to data in R. Small, fast, dependency free interpreted string literals (requires version >= 1.2.0.9000. Blank cell crashes the app with version 1.2.0)</li>
</ul>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://www.mango-solutions.com/blog/database-bulk-update-and-inline-editing-in-shiny-application"> Mango Solutions</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/N9pJ-c1VHKo" height="1"></a></div>