Fork me on GitHub

This app is a demonstration of how to store data from a shiny app in a persistent way so that the data can be retrieved again later.

For more information on this topic, read the associated tutorial on persistent data storage.

In this app, the data that is saved and restored is user-submitted responses to a form. This is a practical use case of saving data from a shiny app. For a guide on how to build forms with Shiny, see this tutorial.


Overview

The easiest way to store submitted responses is by simply saving each response locally as a separate text file. Locally means that the files get saved on the same machine that is running the shiny app.

Since the responses are saved locally, this can only be used if you have access to the machine hosting the app and if you trust its filesystem. If you don't know what machine the app is hosted on, or if the files could get deleted, do not use this method. This approach will not work if hosting on shinyapps.io because of those reasons.

Since each response is its own file, different responses can have different fields, so it's easy to change the form to have different fields (though doing that will make aggregating all the responses more tricky).

Setup

This approach is very simple and easy to implement. The only required setup is to create a directory where you want the responses to be saved, and ensure the shiny app has write permissions.

Details

When saving the files, it is important to ensure that files get different names (so that two responses won't overwrite each other). My simple solution is to include 3 things in the filename for each response: the md5 hash of the response's content, some random number, the time of submission.

When loading the responses, simply read each file separately and concatenate them all into one dataframe.

Overview

You can store submitted responses in a SQLite database. SQLite is a very simple and light-weight relational database that is very easy to set up. SQLite is serverless, which means it stores the database locally on the same machine that is running the shiny app.

Since the responses are saved locally, this can only be used if you have access to the machine hosting the app and if you trust its filesystem. If you don't know what machine the app is hosted on, or if the files could get deleted, do not use this method. This approach will not work if hosting on shinyapps.io because of those reasons.

Since SQL tables have a schema, all the responses must have exactly the same fields.

Setup

First, you need to have SQLite installed on your machine. Installation is fairly easy: for a DigitalOcean Ubuntu machine, you just need to run sudo apt-get install sqlite3 libsqlite3-dev.

You also need to create a database and a table that will store all the responses. Make sure the table contains all the column names that your form has. For example, if your form has fields “name” and “email” then you can create the SQL table with CREATE TABLE mytable(name TEXT, email TEXT);. Make sure the shiny app has write permissions on that file and its parent directory.

Details

You can use the RSQlite package to interact with SQLite from R. To connect to the database you just need to provide the path to the database file. Note that R does not currently have support for prepared statements, so the SQL statements have to be constructed manually.

Overview

You can store submitted responses in a MySQL database. MySQL is a popular relational database that can be hosted either locally or remotely.

Since SQL tables have a schema, all the responses must have exactly the same fields.

Setup

You need to create a MySQL database and a table that will store all the responses. You can either install MySQL locally, install MySQL on a remote machine, or just find a web service that hosts MySQL databases. Make sure the table contains all the column names that your form has. For example, if your form has fields “name” and “email” then you can create the SQL table with CREATE TABLE mytable(name TEXT, email TEXT);.

Details

You can use the RMySQL package to interact with MySQL from R. To connect to the database you need to provide the following: host, port, dbname, user, password. Note that R does not currently have support for prepared statements, so the SQL statements have to be constructed manually.

Overview

You can store submitted responses in a mongoDB database. MongoDB is a popular NoSQL database that can be hosted either locally or remotely.

Being NoSQL means that the database is schema-less. That means that different responses can have different fields, so it's easy to change the form to have different fields (though doing that will make aggregating all the responses more tricky).

Setup

All you need to do is create a mongoDB database. Since there is no schema, it's not mandatory to create a collection (collection in mongoDB = table in SQL) before populating it. You can either install mongoDB locally, remotely, or use a web service that provides mongoDB hosting such as MongoLab.

Details

You can use the mongolite package to interact with mongoDB from R. To connect to the database you need to provide the following: db, host, username, password.

Overview

You can store submitted responses in a Google Sheet. Google Sheets are great because they're extremely easy to access and edit from anywhere. For example, the Google Sheet used in this app can be viewed by anyone and you can see it getting populated immediately after submitting a response.

Since all responses will be recorded in the same sheet, all the responses must have exactly the same fields.

Setup

All you need to do is create a Google Sheet and set the top row with the names of the fields. You can either do that via the web or using the googlesheets package. You also need to have a Google account, which I can safely assume you do.

Details

You can use the googlesheets package to interact with Google Sheets from R. To connect to a specific sheet, you will need either the sheet's title or key (preferably key, as it's unique).

Overview

You can save each submitted response in a separate text file and store the files remotely. There are many options for online file storage, and this method uses Dropbox. Dropbox allows you to host any type of file, as long as your account has enough free space. This is very similar to the “Text file” method, but the files are stored online instead of locally.

Since each response is its own file, different responses can have different fields, so it's easy to change the form to have different fields (though doing that will make aggregating all the responses more tricky).

Setup

You need to have a Dropbox account and create a folder that will contain all the responses. Dropbox gives you some free space, which should be plenty if all you're doing is hosting text files.

Details

You can use the rdrop2 package to interact with Dropbox from R.

Saving and loading the responses are both very similar to the approaches taken with the “Text file” method. The only difference is that now the responses are being saved to and loaded from Dropbox instead of the local filesystem.

Overview

Similarly to using Dropbox, another option that takes advantage of online file storage is Amazon S3. S3 stands for “simple storage service” and that's exactly why I decided to try it out - simple. Just like with Dropbox, you can host any type of file on S3.

Since each response is its own file, different responses can have different fields, so it's easy to change the form to have different fields (though doing that will make aggregating all the responses more tricky).

Setup

You need to have an Amazon Web Services account and create an S3 bucket. AWS gives you free hosting for your first year if you stay under certain usage limits.

Details

You can use the aws.s3 package to interact with S3 from R. Note that the package is not yet on CRAN so you will have to look at its README for installation instructions.

When saving the files, it is important to ensure that files get different names (so that two responses won't overwrite each other). My simple solution is to include 3 things in the filename for each response: the md5 hash of the response's content, some random number, the time of submission.

When loading the responses, simply read all files in the bucket separately and concatenate them all into one dataframe.

The code below is the actual code that this app uses to save/load responses

Code to save new responses:

                
Code to read all responses:

              

Submitting...

Error:
More apps by Dean