R: R Database Connection

Q: How does dplyr automatically translate SQL?

Use tbl(con, "table") to create a "lazy" reference; dplyr operations will automatically be translated into SQL and executed in the database. Finally, use collect() to bring the results back into R memory. This is essential for analyzing large datasets.

Q: Should I use dbWriteTable or SQL to write to the database?

dbWriteTable(con, "table", df, overwrite = TRUE) is simple and convenient; for complex writes, use dbExecute(con, "INSERT...") or parameterized SQL.

Q: How can I prevent connection leaks?

Use the on.exit(dbDisconnect(con)) function to automatically close connections; or use the connection pool in the pool package (recommended for production).

Last updated: 2026-08-26

In the previous three lessons, we learned about file-based data (CSV, Excel, JSON). However, 90% of enterprise-level data is stored in databases—such as MySQL, PostgreSQL, SQL Server, and Oracle. In this lesson, we’ll learn how to connect R to a database, query data using SQL, and write data frames to a database.

After completing this lesson, you’ll be able to query a database table with millions of rows using R and write the analysis results back to the database.

1. What You'll Learn

Database Fundamentals (Relational Databases, SQL)
DBI Interface Specification and ODBC Drivers
RSQLite local database (no server required) Connect to MySQL/PostgreSQL/SQL Server
dbGetQuery / dbExecute / dbReadTable
dbWriteTable: Write to a data frame
dplyr automatically translates SQL
Production Practice: Connection Pool, Disconnecting Connections

2. The Story of a Data Analyst

(1) Pain Point: Data is stored in the database

Bob is an analyst who needs to retrieve data from the company's MySQL database:

SQL

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 100;

He runs SQL in Navicat → exports a CSV file → reads the CSV file in R. This takes him 5 minutes every time. If he connected R directly to the database—

(2) Solution using R

library(DBI)
library(RMySQL)

# 1. Connect to the Database
con <- dbConnect(RMySQL::MySQL(),
                 dbname = "shop",
                 host = "localhost",
                 user = "root",
                 password = "secret")

# 2. Run straight ahead SQL
result <- dbGetQuery(con, "
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  WHERE order_date >= '2024-01-01'
  GROUP BY customer_id
  ORDER BY total DESC
  LIMIT 100
")

# 3. or use dplyr Translation SQL
library(dplyr)
result2 <- tbl(con, "orders") |>
  filter(order_date >= "2024-01-01") |>
  group_by(customer_id) |>
  summarise(total = sum(amount)) |>
  arrange(desc(total)) |>
  collect()  # Forget it R Memory

# 4. Disconnect
dbDisconnect(con)

1 line for the connection + 1 line of SQL. That’s the power of R database connections.

3. The R Database Ecosystem

(1) 4 core packages

Package	Function	Dependencies
DBI	Database Interface Specification (Unified API)	Pure R
RSQLite	SQLite driver (local database)	Pure R (no server required)
RMySQL / RMariaDB	MySQL/MariaDB driver	Requires MySQL client library
RPostgreSQL	PostgreSQL driver	Requires the PostgreSQL client library
odbc	ODBC Universal Interface	Requires installation of an ODBC driver
dbplyr	dplyr SQL translation	DBI

(2) Installation

# 1. Core Interfaces(Required)
install.packages("DBI")

# 2. Local Database(No server required,Recommended for beginners)
install.packages("RSQLite")

# 3. Production Database(On Demand)
install.packages("RMySQL")        # MySQL
install.packages("RMariaDB")      # MariaDB(Recommended Alternatives RMySQL)
install.packages("RPostgreSQL")   # PostgreSQL
install.packages("odbc")          # General ODBC

4. RSQLite: Local Database (Recommended for Beginners)

(1) What is SQLite?

graph LR
    A["SQLite"] --> B["Serverless"]
    A --> C["Single-File Storage"]
    A --> D["Embedded"]
    A --> E["Zero Configuration"]
    A --> F["Python/R/Excel Can be read"]
    
    style A fill:#d4edda
    style B fill:#cce5ff
    style C fill:#f8d7da
    style D fill:#e1d4ff

SQLite = file-based database (each file is a database), requiring no server and zero configuration. SQLite is built into smartphones, iPhones, Android devices, Python, and R.

(2) Create/Connect to a Database

library(DBI)
library(RSQLite)

# 1. Create/Connect(If the file does not exist, it will be created automatically.)
con <- dbConnect(SQLite(), "my_database.db")

# 2. Disconnect(Important!Disconnect after use)
dbDisconnect(con)

# 3. Temporary Database(In memory,Restart Failed)
con <- dbConnect(SQLite(), ":memory:")

(3) Writing to a DataFrame

# Prepare the data
sales <- data.frame(
  id = 1:5,
  product = c("A", "B", "A", "C", "B"),
  amount = c(100, 200, 150, 300, 250)
)

con <- dbConnect(SQLite(), "shop.db")

# Write to Table(Coverage:overwrite / Add:append)
dbWriteTable(con, "sales", sales, overwrite = TRUE)

# Verification
dbListTables(con)
# [1] "sales"

(4) Reading Data

# Method 1:Read the entire table
df <- dbReadTable(con, "sales")
print(df)

# Method 2:Execute SQL
result <- dbGetQuery(con, "SELECT * FROM sales WHERE amount > 150")
print(result)

# Method 3:dplyr(Lazy Query,Finally collect)
library(dplyr)
result2 <- tbl(con, "sales") |>
  filter(amount > 150) |>
  collect()

(5) Other Operations

# 1. List all tables
dbListTables(con)
# [1] "sales" "products" "customers"

# 2. Does the table exist?
dbExistsTable(con, "sales")
# [1] TRUE

# 3. Delete Table
dbRemoveTable(con, "sales")

# 4. View Table Fields
dbListFields(con, "sales")
# [1] "id" "product" "amount"

# 5. Commit Transaction
dbCommit(con)
dbRollback(con)

5. Connect to the Production Database

MySQL/MariaDB

# MySQL
library(RMySQL)
con <- dbConnect(MySQL(),
                 dbname = "shop",
                 host = "localhost",
                 port = 3306,
                 user = "root",
                 password = "your_password")

# MariaDB(Recommendations,Open Source)
library(RMariaDB)
con <- dbConnect(MariaDB(),
                 dbname = "shop",
                 host = "localhost",
                 port = 3306,
                 user = "root",
                 password = "your_password")

PostgreSQL

library(RPostgreSQL)
con <- dbConnect(PostgreSQL(),
                 dbname = "shop",
                 host = "localhost",
                 port = 5432,
                 user = "postgres",
                 password = "your_password")

(3) ODBC (Connecting to SQL Server/Oracle)

library(odbc)
con <- dbConnect(odbc(),
                 Driver = "SQL Server",
                 Server = "localhost",
                 Database = "shop",
                 UID = "sa",
                 PWD = "your_password")

⚠️ Note: For the production database, you must first install the client library for the corresponding database (e.g., libmysqlclient-dev for MySQL).

6. dbplyr: dplyr's Automatic SQL Translation

(1) Core Concept

graph LR
    A["dplyr Chain Operation"] --> B["dbplyr Translate to SQL"]
    B --> C["Database Execution"]
    C --> D["collect Pull back R Memory"]
    
    style A fill:#cce5ff
    style B fill:#d4edda
    style C fill:#f8d7da
    style D fill:#fff3cd

(2) Practical Application

library(dplyr)
library(dbplyr)  # Load Translator

# 1. Create a "lazy" table(Doesn't actually query the database)
orders <- tbl(con, "orders")

# 2. Write dplyr Code(Will not be executed immediately)
query <- orders |>
  filter(order_date >= "2024-01-01", amount > 100) |>
  group_by(customer_id) |>
  summarise(
    total = sum(amount),
    n_orders = n()
  ) |>
  arrange(desc(total)) |>
  head(100)

# 3. View the translation SQL
query |> show_query()
# SELECT `customer_id`, SUM(`amount`) AS `total`, COUNT(*) AS `n_orders`
# FROM `orders`
# WHERE (`order_date` >= '2024-01-01') AND (`amount` > 100.0)
# GROUP BY `customer_id`
# ORDER BY `total` DESC
# LIMIT 100

# 4. Forget it R Memory
result <- query |> collect()

💡 Tip: tbl() + dplyr + collect() is the golden combination for database analysis—no need to write SQL by hand; R code is automatically translated into SQL and executed in the database.

(3) Performance Advantages

Database Execution vs. Loading into R Memory:

Data Volume	Load into R	Execute in the database
100,000 rows of	0.1s	0.1s (no difference)
100,000 rows of	10s	0.5s (database has indexes)
100 million lines	Freeze	5s (database processing power)

💡 Tip: Always use tbl() + collect() for large table analysis—this pulls only the results back into R, not the entire table.

7. Writing a DataFrame to a Database

(1) dbWriteTable

# 1. Create/Overview Table
dbWriteTable(con, "sales_summary", summary_df, overwrite = TRUE)

# 2. Append to the existing table
dbWriteTable(con, "sales_log", new_data, append = TRUE)

# 3. Temporary Table(Automatically delete at the end of the session)
dbWriteTable(con, "temp_data", df, temporary = TRUE)

# 4. Line Name Processing
dbWriteTable(con, "df", df, row.names = FALSE)  # Do not include the line number

(2) Practical Application

# Read R Data → Cleaning → Write back to the database
library(dplyr)
library(readr)

# 1. Read CSV
sales <- read_csv("sales.csv")

# 2. Cleaning
clean_sales <- sales |>
  filter(!is.na(amount)) |>
  mutate(date = as.Date(date)) |>
  group_by(region, product) |>
  summarise(total = sum(amount))

# 3. Write back to the database
con <- dbConnect(SQLite(), "shop.db")
dbWriteTable(con, "sales_by_region_product", clean_sales, overwrite = TRUE)

# 4. Verification
result <- dbGetQuery(con, "SELECT * FROM sales_by_region_product LIMIT 5")
print(result)

8. Production Practice

(1) Best Practices for Connection Configuration

# 1. Use .Renviron Save Password(Don't put it in the code)
# Add to ~/.Renviron:
# DB_PASSWORD=your_password
password <- Sys.getenv("DB_PASSWORD")

# 2. Connection Configuration Encapsulation
db_connect <- function() {
  dbConnect(RMariaDB::MariaDB(),
            dbname = "shop",
            host = Sys.getenv("DB_HOST", "localhost"),
            port = as.integer(Sys.getenv("DB_PORT", 3306)),
            user = Sys.getenv("DB_USER", "root"),
            password = Sys.getenv("DB_PASSWORD"))
}

# 3. Use withConnection Pattern
con <- db_connect()
on.exit(dbDisconnect(con))  # Automatically disconnect when the function ends

(2) Error Handling

# 1. Simple tryCatch
result <- tryCatch(
  {
    con <- dbConnect(SQLite(), "shop.db")
    dbGetQuery(con, "SELECT * FROM sales LIMIT 10")
  },
  error = function(e) {
    message("Database Error:", e$message)
    NULL
  },
  finally = {
    if (exists("con") && !is.null(con)) dbDisconnect(con)
  }
)

# 2. Check the connection
if (dbIsValid(con)) {
  cat("Connection is normal\n")
} else {
  cat("Connection Lost\n")
}

(3) Batch Operations

# 1. Bulk Insert(Transactions)
dbBegin(con)
for (chunk in split(data, ceiling(seq_len(nrow(data)) / 1000))) {
  dbWriteTable(con, "big_table", chunk, append = TRUE)
}
dbCommit(con)

# 2. Progress Bar
library(progress)
pb <- progress_bar$new(total = nrow(data))
for (i in seq_len(nrow(data))) {
  dbExecute(con, "INSERT INTO log VALUES (?, ?)", params = list(data$id[i], data$msg[i]))
  pb$tick()
}

9. Complete Example: Analyzing the SQLite Sales Database

Below is an example of a complete workflow that ties together all the concepts covered in this lesson.

▶ Example: Comprehensive Analysis of the Local Sales Database

R 📖 Display only

# ============================================
# Comprehensive Analysis of the Local Sales Database
# Features:Use RSQLite Database Creation,Look up data,Analysis
# ============================================

library(DBI)
library(RSQLite)
library(dplyr)

# 1. Create a Database + Write Initial Data
con <- dbConnect(SQLite(), "shop_demo.db")
dbWriteTable(con, "customers", data.frame(
  id = 1:5,
  name = c("Alice", "Bob", "Charlie", "Diana", "Eve"),
  city = c("Beijing", "Shanghai", "Guangzhou", "Beijing", "Shenzhen"),
  register_date = as.Date("2023-01-01") + c(0, 30, 60, 90, 120)
), overwrite = TRUE)

dbWriteTable(con, "orders", data.frame(
  id = 1:20,
  customer_id = sample(1:5, 20, replace = TRUE),
  order_date = as.Date("2024-01-01") + sample(0:90, 20),
  amount = sample(100:2000, 20)
), overwrite = TRUE)

cat("=== Table Structure ===\n")
print(dbListTables(con))

# 2. SQL Search:Total Order Amount for Each Customer
cat("\n=== SQL Search(Total Customer Orders)===\n")
result_sql <- dbGetQuery(con, "
  SELECT c.name, c.city, COUNT(o.id) AS n_orders, SUM(o.amount) AS total
  FROM customers c
  LEFT JOIN orders o ON c.id = o.customer_id
  GROUP BY c.id
  ORDER BY total DESC
")
print(result_sql)

# 3. The same query using dplyr(dbplyr Translation SQL)
cat("\n=== dplyr Search(Automatic Translation SQL)===\n")
customers_tbl <- tbl(con, "customers")
orders_tbl <- tbl(con, "orders")

result_dplyr <- customers_tbl |>
  left_join(orders_tbl, by = c("id" = "customer_id")) |>
  group_by(id, name, city) |>
  summarise(
    n_orders = n(),
    total = sum(amount)
  ) |>
  arrange(desc(total)) |>
  collect()

print(result_dplyr)

# 4. Complex Analysis:Monthly Sales Trends
cat("\n=== Monthly Sales Trends ===\n")
monthly_sales <- orders_tbl |>
  mutate(month = format(order_date, "%Y-%m")) |>
  group_by(month) |>
  summarise(
    orders = n(),
    revenue = sum(amount),
    avg_amount = round(mean(amount), 2)
  ) |>
  collect()
print(monthly_sales)

# 5. Identify High-Value Customers
cat("\n=== High-value customers(Order Value > 2000)===\n")
vip_customers <- customers_tbl |>
  left_join(orders_tbl, by = c("id" = "customer_id")) |>
  group_by(id, name, city) |>
  summarise(total = sum(amount, na.rm = TRUE)) |>
  filter(total > 2000) |>
  arrange(desc(total)) |>
  collect()
print(vip_customers)

# 6. Write the analysis results back to the database
dbWriteTable(con, "vip_customers", vip_customers, overwrite = TRUE)
dbWriteTable(con, "monthly_sales", monthly_sales, overwrite = TRUE)
cat("\n=== The report has been saved to the database. ===\n")
print(dbListTables(con))

# 7. Verify the table to which data is written back
cat("\n=== Verification vip_customers ===\n")
vip_reloaded <- dbReadTable(con, "vip_customers")
print(vip_reloaded)

# 8. Disconnect
dbDisconnect(con)
cat("\n=== The database connection has been lost ===\n")

# 9. Clean Up Files
file.remove("shop_demo.db")

70 logic lines (exceeds 40-line limit, display only)

Expected Output (Excerpt):

TEXT 📖 Display only

=== SQL Search(Total Customer Orders)===
     name city n_orders total
1    Eve   Shenzhen        5  5180
2  Diana   Beijing        5  4643
3 Charlie Guangzhou        4  4156
4    Bob   Shanghai        3  3461
5  Alice   Beijing        3  3033

=== Monthly Sales Trends ===
    month orders revenue avg_amount
1  2024-01      4     5239    1309.75
2  2024-02      5     6491    1298.20
...

❓ FAQ

Q What is DBI? Why should it be installed first?

A DBI is the R database interface specification. All database drivers (RSQLite, RMySQL, RPostgreSQL) adhere to the DBI specification. Install DBI before installing the drivers.

Q Doesn't RSQLite require a server?

A Yes! SQLite is a file-based database—a single .db file constitutes an entire database. It requires zero configuration and is ideal for local development, prototyping, and educational purposes.

Q How do I connect to a remote MySQL server?

A dbConnect(RMariaDB::MariaDB(), dbname, host, user, password). Use the environment variable Sys.getenv("DB_PASSWORD") for the password to avoid hard-coding it.

Q How does dplyr automatically translate SQL?

A Use tbl(con, "table") to create a "lazy" reference; dplyr operations will automatically be translated into SQL and executed in the database. Finally, use collect() to bring the results back into R memory. This is essential for analyzing large datasets.

Q Should I use dbWriteTable or SQL to write to the database?

A dbWriteTable(con, "table", df, overwrite = TRUE) is simple and convenient; for complex writes, use dbExecute(con, "INSERT...") or parameterized SQL.

Q How can I prevent connection leaks?

A Use the on.exit(dbDisconnect(con)) function to automatically close connections; or use the connection pool in the pool package (recommended for production).

📖 Summary

DBI is an interface specification for R databases; specific drivers (RSQLite, RMariaDB, RPostgreSQL) implement this specification.
RSQLite: A Recommended Introduction—No server required, pure R, file-based database (a single .db file)
5 core functions: dbConnect Connect / dbDisconnect Disconnect / dbGetQuery Query SQL / dbWriteTable Write / dbReadTable Read Entire Table
dbplyr is a game-changer: tbl(con, "table") + dplyr + collect() Automatically translates R code into SQL
Write: Overwrite with dbWriteTable(con, "name", df, overwrite = TRUE) / Append with append = TRUE
Production Practice: Use the password Sys.getenv() and the transactions dbBegin/dbCommit/dbRollback and on.exit to disconnect.
For large table analysis, always use tbl() + collect()—calculate only in the database; do not load into R memory

📝 Exercises

Basic Exercise: Use RSQLite to create a local database test.db, insert one data frame (5 rows, 3 columns), and use dbReadTable to read it back to verify that the data is complete.
Basic Questions: Execute the following three SQL queries on the database from the previous question: ① SELECT * FROM table ② SELECT COUNT(*) FROM table ③ SELECT col, COUNT(*) FROM table GROUP BY col.
Basic Exercise: Use dbExecute to create a new table (containing the id, name, and age fields), use dbWriteTable to insert 5 rows of data, and use dbRemoveTable to delete them. Verify each operation.
Advanced Exercise: Using the sample sales database (two tables: customers and orders), use tbl() + dplyr + collect() to: ① Identify customers with more than 3 orders; ② Calculate monthly sales totals; ③ Identify the top 3 highest-spending customers. Take a screenshot and save it.
Challenge: Use dbplyr to translate a complex dplyr operation into SQL: ① multiple filters ② group_by + summarise ③ arrange + head ④ inner join of two tables. Use show_query() to view the SQL string and verify that it matches the handwritten SQL.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

R: R Database Connection

1. What You'll Learn

2. The Story of a Data Analyst

(1) Pain Point: Data is stored in the database

(2) Solution using R

3. The R Database Ecosystem

(1) 4 core packages

(2) Installation

4. RSQLite: Local Database (Recommended for Beginners)

(1) What is SQLite?

(2) Create/Connect to a Database

(3) Writing to a DataFrame

(4) Reading Data

(5) Other Operations

5. Connect to the Production Database

(3) ODBC (Connecting to SQL Server/Oracle)

6. dbplyr: dplyr's Automatic SQL Translation

(1) Core Concept

(2) Practical Application

(3) Performance Advantages

7. Writing a DataFrame to a Database

(1) dbWriteTable

(2) Practical Application

8. Production Practice

(1) Best Practices for Connection Configuration

(2) Error Handling

(3) Batch Operations

9. Complete Example: Analyzing the SQLite Sales Database

▶ Example: Comprehensive Analysis of the Local Sales Database

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好