Shiny Database App (CRUD)

In this post, we write a shiny database app that lets you display and modify data that is stored in a database table.

Shiny and Databases

Everybody loves Shiny, and rightly so. It lets you publish reproducible research, brings R applications to non-R users, and can even serve as a general purpose GUI for R code.

However, sometimes I’m surprised how difficult some basic tasks are. For example, creating a basic shiny database app that provides a CRUD screen (CRUD stands for Create, Read, Update and Delete), is often one of the first examples in web frameworks.

To be fair, working with databases is not what most R-people do, and writing business applications is not what Shiny has been built for either. However, multiple of my customers do exactly that: They have a database (mysql, MongoDB, or other), store data in it, and write lots of financial models in R. They use Shiny to build interactive dashboards and to verify the P&L during the trading day. Some of them have only R know-how, so it’s natural for them to ask to leverage that know-how by writing Shiny apps that let them maintain their data directly from their browser.

This post shows you step by step how to build a Shiny app that lets you create, update and delete data in a table.

You can see the app in action here: http://ipub.com/apps/shiny_crud01/. It’s also easy enough to deploy it locally in RStudio: simply source the source-code file from this gist.

Prerequisites

Basic understanding of Shiny and reactivity is certainly helpful to read this article. If you want to build this app yourself and play with it, all you need is RStudio and a recent version of R. You won’t need MySQL or MongoDB or any other database, because we abstracted that part away. In fact, our Shiny database app will work with an in-memory database, aka data.frame 😉

Shiny UI

Let’s start our Shiny database app with the UI, as this is very simple and straight forward: In the top part of our browser window, we want to display a DataTable reflecting the database table. Below it, we show a group of inputs (one for each column), that lets us create new records, or modify existing records. Finally, a few action buttons complement our CRUD screen:

The Submit button saves a new or updated record. The New button will fill the inputs with their default values. It can be used to add a new record after having clicked through existing records in the table.

Shiny Server

The server part of our Shiny database app is almost equally simple: We define reactive events for

  • the inputs, as a group
  • the action buttons
  • the DataTable row selection

Finally, we render the table.

The priority on lines 16 and 27 need to be set in order to make sure that the data is updated first, and only then the table is refreshed.

Helpers

If you read the above code, you’ll find a few methods that do … stuff. As this “stuff” is done in more than one location, we factor it out into helper methods.

This method casts from the inputs to a one-row data.frame. We use it, for instance, when the user creates a new record by typing in values into the inputs, and then clicks “Submit”:

This creates an empty record, to be used e.g. to fill the inputs with the default values when the user clicks the “New” button:

And this method takes the data as selected in the DataTable, and updates the inputs with the respective values:

This function finds the next ID of a new record. In mysql, this could be done by an incremental index, automatically. And then this method could be used to fetch the last insert ID. But here, we manage the ID ourselves:

CRUD Methods

The methods that mimic the actual CRUD functionality in our Shiny database app are then straight forward.

Create

Read

Update

Delete

The only thing that might not be straight forward is the GetTableMetadata function. We’ll use it as a starting point for further development, as described below. For now, it’s just a method that defines the names of the columns in our table:

 

Database Binding

In order to hook the Shiny database app with a “true” database, you will need to replace “responses” with your database. The good news: You can take whatever database, driver, and binding framework you want. The bad news: You will have to implement CreateData, ReadData, UpdateData, and DeleteData functions yourself.

Read this RStudio article for more about database binding.

Caveats

The Shiny database app will work in a multi-user environment, yet with limitations. For one thing, no transactions are implemented. As a result, the last person to click submit will simply win, and possibly overwrite another users changes without noticing. In many situations, this is ok, but it certainly is not in a public web-application which is used by thousands of simultaneous users.

Furthermore, reactivity does not work cross-user. If another user changes a record, your DataTable will not automatically update. You will need to hit F5 for that.

Further Developments

Many of the hard-coded mentioning of the fields and labels could be generalised. Ideally, we would like to re-use the code for different tables. This is less difficult than what you might think. You need to generalise the GetTableMetadata function and return information on the field types (Text, Number, Boolean, Choice, etc.), derive the Input type (textInput, sliderInput, etc.), and create this code generically. If there is enough interest, I will build such an app in a future post.

Credits

The featured image is the record label of the Detroit industrial rock n’roll band CRUD, whose members probably know neither R nor Shiny. The converse may not be true, however, even though I have not heard a single song from them. In any case, I hope for them that this free publicity brings them many new fans.

17 thoughts on “Shiny Database App (CRUD)”

Leave a Reply