A way to build CRUD apps with R Shiny and SQLite
Background
Often as software engineers working within organisations we are coerced into using a software stack that we may not have chosen if we were tasked with designing it from the start. This is part of the fun and part of what keeps our jobs interesting. Also we may be able to come to the conclusion that it makes sense to utilise a component in a system design that is less than optimal for it’s specific use case, but makes sense overall.
Take R Shiny for example. An R centric framework for full stack web applications. From my limited exposure, common use cases for R Shiny are read only dashboards, but often we require mutations to data such as create, update and delete operations.
Shiny helps you turn your analyses into interactive web applications without requiring HTML, CSS, or JavaScript knowledge.
There is some really great work that has been done in this space including but not limited to:
- Persistent data storage in Shiny apps
- Barbara Borges Ribeiro’s presentation on Interacting with Databases from R and Shiny
Although there is a fair bit of content out there already on this, none of it exactly met my use case, so I decided to give a go at a basic scaffold of a R package for a CRUD application using R Shiny and SQLite.
Welcome ShineLite.
A basic lite CRUD application framework for RShiny application development with a SQlite back end.
The extra things I wanted to build into it were:
- Environment life cycle to connect to different DBs based on development/production etc
- Package dependencies with renv
Setup
OS binaries
I started developing this on a vanilla Ubuntu VM and tried to capture all the things needed to get it up and running.
# Run this from your terminal (not console)
# Install all these OS binaries or you will get caught during the renv restore
sudo apt install build-essential
sudo apt install libcurl4-openssl-dev
sudo apt install libfontconfig1-dev
sudo apt install libharfbuzz-dev libfribidi-dev
sudo apt install libfreetype6-dev libpng-dev libtiff5-dev libjpeg-dev
sudo apt install libxml2-dev
The SQLite DB
The package has a configuration function within R/config.R
. It will return the path for where your SQLite DB file is hosted. In this case the path is /u01/data/shinelite/<env>/todo_app.db
. This would be changed per use case. There is probably a better way to do this, but it works for now.
#' Set environment
#'
#' @return config
#' @export
#'
#' @examples set_environment()
set_environment <- function() {
# Get the environment
env <- Sys.getenv("env")
# Define configurations for each environment
dev_config <- list(
db_path = "/u01/data/shinelite/dev/todo_app.db",
other_config_variable = "value"
)
prd_config <- list(
db_path = "/u01/data/shinelite/prd/todo_app.db",
other_config_variable = "value"
)
# Set configuration based on environment
if (env == "dev") {
return(dev_config)
} else if (env == "prd") {
return(prd_config)
} else {
stop("Unknown environment specified")
}
}
The next setup step is to initialise the SQLite DB in the development environment.
# Run this from your terminal (not console)
export env=dev
db_path="/u01/data/shinelite/dev/todo_app.db"
sqlite3 $db_path <<EOF
CREATE TABLE IF NOT EXISTS todos (
id INTEGER PRIMARY KEY,
todo TEXT,
done BOOLEAN DEFAULT FALSE
);
EOF
The R environment
Create a .Renviron
file in the root of the cloned repository and populate it with this:
env=dev
Start with the initial install of renv.
install.packages('renv')
Now install everything else with a renv restore
renv::restore()
We also need the devtools package to make all functions available.
devtools::load_all(".")
Run the RShiny application in dev mode
runShineLite()
Remember to add the two most important things to your new todo list:
Checking the DB
Just to show how to confirm that the DB was written to using the CLI.
Adding documentation (optional)
If you want to render out a site for your package you can use package down.
pkgdown::build_site()