DB-driven R Shiny App Visualization with R - from why DB to working code

As you analyze and visualize your data It's important to always reflect the latest datafor example. But updating the CSV file every time is cumbersome, and reloading the data every time you run the code is inefficient 😫.

Is this a good time to utilize a database? DB-based R ShinyApp Visualizationto store data efficiently and R Shiny You can pull it up in the app and visualize it! 🎉 .

Today we'll be using SQLiteto utilize the Generating 5 years of data, fetching and visualizing it in a Shiny appstep by step, of course! Explained in detail, including actual codeWe're going to do it, so stick with us until the end 😉 .

🔥 Why DB: Why use DB and not CSV?

The difference becomes clear when you compare CSV to a database (DB).

As you can see in the comparison above, When creating dynamic web applications like Shiny apps, utilizing a DB is much more efficient.is!

🚀 [Hands-on] Implementing database-enabled visualizations in R Shiny apps

1️⃣ Storing data in a SQLite DB

First, Randomly generate 5 years (2020-2024) of sales data and store it in SQLiteLet's write some code that does this

# Package Load
library(DBI)
library(RSQLite)

Connect to the # SQLite DB
conn <- dbConnect(RSQLite::SQLite(), "sales_data.sqlite")

Generate # data
set.seed(123)
years <- rep(2020:2024, each = 12)
months <- rep(1:12, times = 5)
sales <- sample(100:500, 60, replace = TRUE) + (years - 2020) * 20 # Reflect incremental sales by year

sales_data <- data.frame(year = years, month = months, sales = sales)

# Delete and save the existing table
dbExecute(conn, "DROP TABLE IF EXISTS sales")
dbWriteTable(conn, "sales", sales_data, overwrite = TRUE, row.names = FALSE)

Disconnect the # DB
dbDisconnect(conn)

📝 Code commentary

1. Connecting to a SQLite database: dbConnect()to create and connect DB files.
2. Generate random data: years, months, sales Create data to generate five years of data.
3. Save to DB: After dropping an existing table dbWriteTable()to store the data.
4. Disconnecting from the DB: dbDisconnect()to terminate the connection.

2️⃣ Fetching and visualizing DB data in a Shiny app

Now, the Pull data from SQLite with a Shiny app and visualize it with ggplot2Let's take a look at the process 🔥

library(shiny)
library(DBI)
library(RSQLite)
library(ggplot2)

Define the # UI
ui <- fluidPage(
  titlePanel("📊 DB-based R ShinyApp visualization"),
  sidebarLayout(
    sidebarPanel(selectInput("year", "Select year:", choices = 2020:2024, selected = 2024)),
    mainPanel(plotOutput("salesPlot"))
  )
)

# server logic
tserver <- function(input, output) {
  conn <- dbConnect(RSQLite::SQLite(), "sales_data.sqlite")
  
  sales_data_reactive <- reactive({
    query <- paste0("SELECT * FROM sales WHERE year = ", input$year)
    dbGetQuery(conn, query)
  })

  output$salesPlot <- renderPlot({
    df <- sales_data_reactive()
    ggplot(df, aes(x = month, y = sales)) +
      geom_line(color = "blue") +
      geom_point(color = "red") +
      geom_smooth(method = "lm", formula = y ~ poly(x, 2), color = "darkred", linetype = "dashed") +
      theme_minimal()
  })
  
  onStop(function() { dbDisconnect(conn) })
}

shinyApp(ui, server)

📝 Code commentary

1. Configure the UI to allow users to select a year (selectInput() use)
2. Connecting to a DB on a Shiny server and fetching data for a given year
3. Visualization with ggplot2 (geom_line() + geom_smooth()to add a trendline)

🔄 How to update additional DB data

To add a new year of data to the DB, you can use the Update by appending dataFor example, if you wrote code to add data for 2025, it would look like this

conn <- dbConnect(RSQLite::SQLite(), "sales_data.sqlite")

Create # 2025 data
set.seed(125)
years <- rep(2025, each = 12)
months <- 1:12
sales <- sample(100:500, 12, replace = TRUE) + (2025 - 2020) * 20

new_data <- data.frame(year = years, month = months, sales = sales)

# Append to an existing table
dbWriteTable(conn, "sales", new_data, append = TRUE, row.names = FALSE)

# Disconnect DB
dbDisconnect(conn)

📝 Code commentary

1. Generate data for a new year: set.seed(125)and generate data for 2025.
2. Configure dataframes: Creates a dataframe with the same structure as before.
3. Adding data to the DB: dbWriteTable()to append = TRUEto add new data to existing data.
4. Disconnecting from the DB: dbDisconnect()to terminate the connection.

Now, after you add the 2025 data, you can select it in your Shiny app by adding the selectInput()you can also add 2025 to it. 📊

📌 In closing...

Now we have a How to visualize DB-driven data in R Shiny appsYou've learned!

• Understand why it's a good idea to use DB
• Learn to store data in SQLite
• The process of fetching and visualizing data in a Shiny app Cook

In real-world projects, you can use not only SQLite but also MySQL, PostgreSQL, and more DBscan also be utilized. 😊

📚 Glossary

• ShinyA package that makes it easy to create web applications using R.
• SQLiteLightweight database that can be managed as a single file, ideal for simple data storage.
• ggplot2: An R package for data visualization that allows you to create a variety of graphs.
• reactive(): Functions that allow data to change dynamically in Shiny.