Working with Databases

Much real-world data lives in databases. R connects to them through DBI, a common interface with a driver package for each database: RSQLite, RPostgres, RMariaDB, odbc (SQL Server and many others), duckdb, and more.

Connecting and querying

This example uses SQLite, which stores a whole database in one file (or, here, in memory) and needs no server:

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "orders", data.frame(
  id = 1:4,
  customer = c("Ada", "Linus", "Ada", "Grace"),
  total = c(120, 45, 80, 300)
))

dbListTables(con)
dbGetQuery(con, "SELECT customer, SUM(total) AS spent
                 FROM orders
                 GROUP BY customer
                 ORDER BY spent DESC")

[1] "orders"
  customer spent
1    Grace   300
2      Ada   200
3    Linus    45

dbGetQuery runs SQL and returns a data frame. (If you're new to SQL, the SQL course covers it from the ground up.)

Always use parameters for values

Never paste user-supplied values into SQL text — that allows SQL injection. Pass them as parameters instead:

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "orders", data.frame(customer = c("Ada", "Linus"), total = c(120, 45)))

min_total <- 50
dbGetQuery(con, "SELECT * FROM orders WHERE total > ?", params = list(min_total))
dbDisconnect(con)

  customer total
1      Ada   120

The ? placeholder is filled in safely by the driver.

dplyr on databases: dbplyr

With the dbplyr package, dplyr code runs inside the database — dplyr translates it to SQL, so large tables never have to be loaded into R:

library(dplyr)

orders_db <- tbl(con, "orders")

orders_db |>
  summarise(spent = sum(total), .by = customer) |>
  arrange(desc(spent)) |>
  show_query()     # see the generated SQL

result <- orders_db |> filter(total > 100) |> collect()   # bring results into R

collect() runs the query and returns a tibble — do the heavy filtering and aggregation in the database first, then collect only what you need.

Credentials

Don't write passwords in scripts. Read them from environment variables:

con <- dbConnect(RPostgres::Postgres(),
  host = Sys.getenv("DB_HOST"),
  dbname = "sales",
  user = Sys.getenv("DB_USER"),
  password = Sys.getenv("DB_PASSWORD")
)

Always call dbDisconnect(con) when you're done.

Common mistakes

• Pulling a huge table into R with SELECT * and then filtering. Filter and aggregate in the database.
• Building SQL with paste() and user input — use parameters.