Shiny Database App (CRUD) 17


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.


Leave a Reply

17 thoughts on “Shiny Database App (CRUD)

  • Dean Attali (@daattali)

    This is a great post, very useful, thank you for this! Nice proof of concept.

    A few comments:

    1. It looks like this the basic idea of the form+table came from my blog post about making forms with Shiny? http://deanattali.com/2015/06/14/mimicking-google-form-shiny/

    2. instead of disabling the id input in the server, it’s also possible to initialize the input as disabled straight in the UI: shinyjs::disabled(textInput("id", "Id", "0"))

    3. It’s a little weird that whenever you add a new entry, the “id” field always has the value “0” instead of showing the next id

    4. I’m not sure if others are having this problem as well, but when I run this locally, the table never shows the most recent entry, only the previous entries (for example if I submit 3 entries, the table shows the first 2)

    5. The submit/update and delete buttons are great, but I don’t understand what the “new” button does?

    • gluc Post author

      Thanks for this. Here’s some answers:
      1. Not really, the starting point was http://shiny.rstudio.com/articles/persistent-data-storage.html , but happy to link back, as your article is useful
      2. Indeed, better, adjusted accordingly
      3. This is as it would work in a concurrent-user set-up, where you wouldn’t know the next ID until you actually save a record. Same applies in a db setup, where the next ID is not known in advance, but only returned once the record is added to the table. And empty ID might be more user-friendly to some, though.
      4. Yes, there was a problem. I fixed it by adding a priority arg to the update/create event
      5. The New is intended to let a user add a new record after clicking through the records in the table.

      • Dean Attali (@daattali)

        1. Ah makes sense, those two articles are a 2-article series (they link to each other), they use the same code 🙂

        3. Got it, thanks. I agree with the last statement, I think simply showing a blank input rather than showing a “0” will make more sense

        5. Oh, I missed that part in the text before. It might be clearer if the submit button says “update” when a row is selected. Though I understand this is just a demonstration, it doesn’t have to be perfect, it just confused me without reading the text. Thanks for the fast reply! (by the way, I linked to this post from my post)

  • Hansi

    I don’t see why people are trying to reinvent the wheel so aggressively with Shiny?

    I see your reasoning in your post but I guess I just think that if a person can get R to a great enough degree to build something of sufficient calibre where they need an actual CRUD interface in their dashboard putting in the time to learn the technologies required for a better solution is clearly worth it.

    I’ve been creating full fledged web apps for years using HTML, CSS and JS + a R backends; it’s allowed much easier to write, maintain and expand solutions then anything I’ve seen done in Shiny.

    Once you start doing anything other then a trivial thing with Shiny you need to know HTML, CSS and JS anyway so why not keep the separation of concerns mantra alive and do things in the manner that’s easier to build, maintain and expand.

    I’ve worked with solutions using rApache, OpenCPU, DeployR, Rserve, R.NET via WebAPI, and a few custom solutions built by me and others including just using the R build in HTTP server.

    • gluc Post author

      Thanks for your comment. Let me start by saying that I’m a VERY big fan of OpenCPU, and I’ve done projects with rApache and Rserve as well. Also, I’ve been a .NET developer for years, so R.NET has been on my radar for some time. I also believe that you fight for the right cause: To find the best tool to resolve a given problem.
      Having said this, in my opinion the answer is not always as clear-cut as you seem to think. The “right tool” is not an absolute choice, but it depends on both the problem at hand and on the available level of know-how and sophistication. While I’m glad for you that you master all these technologies, many of my customers do not, and the learning curve (in terms of development, deployment and operations) may just be too steep. I’m trying to empower my customers, not just to do contract work for them. So, when I’m facing a small hedge fund with 3 veteran R cracks that have taught themselves Shiny over the course of the last few months, proposing to develop a REST API with an AngularJS frontend is simply not going to work. They don’t care for perfection, but they just need to get their job done. They surprisingly often get by without JS, and they don’t care much if the result is clumsy, as they are the only users of the software.
      Obviously, if you are developing a web application that will be publicly available, usability is much more important. So I’m not saying your point is invalid, just that it depends on the circumstances.

      • Hansi

        Yeah, like I said I understand the situation you describe amd it has potential in that exact situation where the users have upskilled themselves in Shiny it is useful. But I think in our current environment is might be better for them long term to hire a dev or outsource the development.

        One alternative we’ve used for portfolio managers in similar situation is to use RExcel (http://rcom.univie.ac.at/). Where they build the dashboard in Excel and link it via VBA to R. Excel to one of the above APIs is an option too.

    • gluc Post author

      Unfortunately, I don’t have time to look into this right now. However, I encourage all the readers to have a look at the stackoverflow question!

  • Jared

    Thank you for publishing this step by step guide. I am interested in implementing a similar version of this for a project for school. My R knowledge is not quite up to par, however. Would you be willing to provide an example of Create / Read / Update / Delete functions for MySQL?

    • gluc Post author

      Hi, yes, that would be useful, but unfortunately, I won’t have time for at least a few weeks. If you contact me privately, though, I can give you some hints.

  • Jake

    Looking forward to seeing how you would generalize it for multiple tables. I’m struggling with something similar now.

  • RLab

    This was a great read! Thank you! I am very knew to Shiny and programing as a whole. Love it though! I am creating a Shiny app, similar to this one, and I am planning to post it on Shiny IO. Can you give a example of how we can use google sheets to store the responses table. My code is very similar to yours, but the table is not visible nor do I have a real database set up.

  • John

    Great post! I’m trying to implement this but I need two different forms. The outputs go to the same table in the same relational database. I like the idea of using navBar but was wondering if there would be any caveats or anything that wouldn’t allow me to use this method in that way?