R,  Data Science,  Web Development

A way to build CRUD apps with R Shiny and SQLite

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:

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.

shinelite vm

# 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:

shinelite app

Checking the DB

Just to show how to confirm that the DB was written to using the CLI.

shinelite sqlite

Adding documentation (optional)

If you want to render out a site for your package you can use package down.

pkgdown::build_site()