r-data-database

R Database Connections

SQL and NoSQL database access from R.

DBI (Common Interface)

library(DBI)

# Connect
con <- dbConnect(RSQLite::SQLite(), "database.db")
con <- dbConnect(RPostgres::Postgres(),
  host = "localhost", dbname = "mydb",
  user = "user", password = "pass"
)

# Query
result <- dbGetQuery(con, "SELECT * FROM table WHERE x > 10")

# Execute (no return)
dbExecute(con, "INSERT INTO table VALUES (1, 'a')")
dbExecute(con, "UPDATE table SET x = 10 WHERE id = 1")

# Parameterized queries (safe)
dbGetQuery(con, "SELECT * FROM table WHERE id = ?", params = list(123))

# Tables
dbListTables(con)
dbExistsTable(con, "mytable")
dbWriteTable(con, "newtable", df)
dbReadTable(con, "mytable")
dbRemoveTable(con, "mytable")

# Disconnect
dbDisconnect(con)

dbplyr (dplyr for databases)

library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), "database.db")

# Lazy table reference
tbl_db <- tbl(con, "mytable")

# dplyr operations (translated to SQL)
result <- tbl_db %>%
  filter(x > 10) %>%
  select(a, b, c) %>%
  group_by(category) %>%
  summarise(mean = mean(value)) %>%
  collect()  # Execute and bring to R

# View SQL
tbl_db %>% filter(x > 10) %>% show_query()

# Copy local data to database
copy_to(con, df, "temp_table", temporary = TRUE)

SQLite

library(RSQLite)
con <- dbConnect(SQLite(), "mydb.sqlite")

# In-memory database
con <- dbConnect(SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)
dbGetQuery(con, "SELECT * FROM mtcars WHERE mpg > 20")
dbDisconnect(con)

PostgreSQL

library(RPostgres)
con <- dbConnect(Postgres(),
  host = "localhost",
  port = 5432,
  dbname = "mydb",
  user = "user",
  password = "password"
)

# With connection string
con <- dbConnect(Postgres(),
  "postgresql://user:pass@host:5432/dbname"
)

MySQL/MariaDB

library(RMariaDB)
con <- dbConnect(MariaDB(),
  host = "localhost",
  dbname = "mydb",
  user = "user",
  password = "password"
)

ODBC (Universal)