R - Database

R - Modern Database Interaction

While R is powerful for in-memory analysis, most real-world data resides in relational databases (like PostgreSQL, MySQL, SQL Server). Modern R provides a seamless, efficient, and secure way to interact with these databases using a standardized framework.

The DBI and dbplyr Framework

Interaction is based on two key packages:

• DBI (Database Interface): Provides a universal, consistent set of functions (dbConnect, dbGetQuery, dbWriteTable) for all databases. You write the same R code, regardless of the database backend.
• A backend driver: A specific package that translates DBI commands for a particular database. For MySQL/MariaDB, the modern standard is RMariaDB.
• dbplyr: A dplyr backend that allows you to write familiar dplyr code (e.g., filter(), group_by()) which it translates into SQL for you. This lets you leverage the database’s processing power without writing SQL by hand.

Setup: Installing Packages

# The standardized database interface
install.packages("DBI")

# The modern driver for MySQL and MariaDB
install.packages("RMariaDB")

# For using dplyr verbs with databases
install.packages("tidyverse")

# For managing credentials securely
install.packages("config")

Connecting to a Database

Security Best Practice: Never hard-code credentials (username, password, host) in your script. Use a configuration file or environment variables. We will demonstrate using the config package.

1. Create a config.yml file

In your project directory, create a file named config.yml with your database credentials:

config.yml

default:
  dbname: "sakila" # A sample database for MySQL
  host: "127.0.0.1"
  user: "root"
  password: "your_password_here"

2. Connect from R

library(DBI)
library(RMariaDB)

# Load credentials securely from the config file
conf <- config::get()

# Create the connection object
# Use a tryCatch block to handle connection errors gracefully
con <- tryCatch({
  dbConnect(
    RMariaDB::MariaDB(),
    dbname = conf$dbname,
    host = conf$host,
    user = conf$user,
    password = conf$password
  )
}, error = function(e) {
  stop("Database connection failed: ", e$message)
})

cat("Successfully connected to the database!\n")

# List tables to verify the connection
print(dbListTables(con))

Querying Data with DBI

The dbGetQuery function sends a SQL query and retrieves the full result set as a data frame. For security, always use parameterized queries with ? placeholders to prevent SQL injection.

# Basic query to get the first 5 actors
actors_df <- dbGetQuery(con, "SELECT * FROM actor LIMIT 5")
print(actors_df)

# Secure parameterized query
last_name_filter <- "TORN"
query <- "SELECT actor_id, first_name, last_name FROM actor WHERE last_name = ?"

torn_actors_df <- dbGetQuery(con, query, params = list(last_name_filter))
print(torn_actors_df)

Querying Data with dbplyr

The most powerful modern workflow is using dbplyr to write dplyr code that runs on the database.

library(dplyr)

# 1. Create a 'remote' table object. This does NOT pull data into R.
remote_film_table <- tbl(con, "film")

# 2. Write dplyr code. dbplyr translates it to SQL.
long_films_query <- remote_film_table %>%
  filter(length > 180, rating == "R") %>%
  select(film_id, title, length, rating) %>%
  arrange(desc(length))

# 3. Use `show_query()` to see the generated SQL
show_query(long_films_query)

# 4. Use `collect()` to execute the query and pull the results into R
long_films_df <- collect(long_films_query)
print(head(long_films_df))

Writing Data and Managing Tables

The DBI package provides safe and consistent functions for writing data frames to tables and for managing them.

# Example: Write the R built-in 'mtcars' dataset to the database
# `overwrite = TRUE` will drop the table if it exists. Use with caution.
if (dbExistsTable(con, "mtcars_r")) {
  cat("Table 'mtcars_r' already exists. Removing it first.\n")
  dbRemoveTable(con, "mtcars_r")
}
dbWriteTable(con, "mtcars_r", mtcars, row.names = TRUE)
cat("Successfully wrote 'mtcars' to table 'mtcars_r'.\n")

# Modify data using a secure, parameterized statement
# dbExecute is for statements that don't return results (UPDATE, INSERT, DELETE)
update_statement <- "UPDATE mtcars_r SET disp = ? WHERE hp = ?"
dbExecute(con, update_statement, params = list(170.0, 110))
cat("Updated 'mtcars_r' table.\n")

# Drop the table when done
dbRemoveTable(con, "mtcars_r")
cat("Removed 'mtcars_r' table.\n")

# Always disconnect when you are finished
dbDisconnect(con)
cat("Database connection closed.\n")