Resources for Building R Shiny x PostgreSQL x AWS EC2 Apps

Main R Workflow

Persistent Data Storage by Dean Attali: https://deanattali.com/blog/shiny-persistent-data-storage/

The main R workflow uses the MySQL section of Dean Attali's Persistent Data Storage. This workflow allows you to get a Shiny app up-and-running where the shiny app collects data , sends results to a database, and displays data from database.

Swapping Out MySQL for PostgreSQL

Instead of using MySQL like in the Persistent Data Storage blog, I used PostgreSQL with the RPostgres R package.

Best Practices in Working with Databases > Databases > PostgreSQL: https://solutions.posit.co/connections/db/databases/postgresql/

Posit's Best Practices in Working with Databases has a short section that covers connecting to a PostgreSQL database using RPostgres.

Emily Rieder Using Databases with Shiny: https://www.emilyriederer.com/post/shiny-db/

This blog post shows how to use the RPostgres R package with the DBI R package. Shows useful syntax and discusses best practices and pitfalls.

Both resources above use the RPostgres x DBI R package combination.

Swapping Out DBI for pool

Posit's Why Pool: https://rstudio.github.io/pool/articles/why-pool.html

To swap DBI with pool, I used the 'Why Pool?' blog post by Posit. The other articles on the website have good examples as well.

Open for Communication

Helena Alexander's How to Configure PostgreSQL for Remote Connections: A Beginner’s Guide: https://blog.devart.com/configure-postgresql-to-allow-remote-connection.html

I used the instructions for Windows to make sure the .conf files and the Windows Firewall were set up correctly.

The Firewalls section of Posit's Accessing Databases with ODBC chapter of the shinyapps.io User Guide: https://docs.posit.co/shinyapps.io/guide/applications/#firewalls

Since I deployed with shinyapps.io, I used these instructions to whitelist the shinyapps.io IP addresses. I also used this same worfklow  for whitelisting IP addresses when I needed to test the app by running the Shiny app on one EC2 instance while the PostgreSQL database was on another instance.

.Renviron File

Posit forum's How to set a variable in .Renviron: https://forum.posit.co/t/how-to-set-a-variable-in-renviron/5029

I used cderv's response to the OPs question to set up a .Renviron file.

StackOverflow's shinyapps.io won't read environment variables from .Renviron: https://stackoverflow.com/questions/77579704/shinyapps-io-wont-read-environment-variables-from-renviron

I used the StackOverflow post to make sure the .Renviron file was stored in the correct place for deployment on shinyapps.io. I'm not too familiar with security so I don't know if this is good practice or not.

I combined the Posit x StackOverflow post when deploying to shinyapps.io by using the:

• usethis::edit_r_environ("project") will open the one in your project

bullet point from crderv's Posit forum response since my app.R file was in the main project folder for deployment.

Next Steps

The apps I made are currently on https://www.mightymetrika.com/citsci. The source code for these apps are on GitHub:

• https://github.com/mightymetrika/npboottprm/blob/master/R/replext_pgsql.R
• https://github.com/mightymetrika/mmirestriktor/blob/master/R/replext_pgsql.R

I hope to improve on these apps in the future by:

• Including user-logins
• Add some sort of graphical exploration of the databse or better filtering
• Learning more about best practices with security
• Learning more about the pool package to see if I can use it in a better way
• Add tool tips and information about data columns to serve as a type of interactive codebook to make the inputs and database columns easier to understand

In the coming weeks I'll have a few blog posts about the individual apps in order to document how the data is supposed to map onto the papers from which the replext (replication & extension) simulation tables are based.