DBI

DBI

Database interface.

Connection

library(DBI)

# SQLite
con <- dbConnect(RSQLite::SQLite(), "database.sqlite")
con <- dbConnect(RSQLite::SQLite(), ":memory:")

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

# MySQL/MariaDB
con <- dbConnect(
  RMariaDB::MariaDB(),
  dbname = "mydb",
  host = "localhost",
  user = "user",
 password = "password"
)

# Disconnect
dbDisconnect(con)

Queries

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

# Execute statement (no return)
dbExecute(con, "UPDATE table SET x = 1 WHERE id = 5")
dbExecute(con, "DELETE FROM table WHERE id = 5")

# Parameterized queries (safe from SQL injection)
dbGetQuery(con, "SELECT * FROM table WHERE id = ?", params = list(5))
dbGetQuery(con, "SELECT * FROM table WHERE name = $1", params = list("John"))

# Send query (for large results)
res <- dbSendQuery(con, "SELECT * FROM large_table")
while (!dbHasCompleted(res)) {
  chunk <- dbFetch(res, n = 1000)
  # Process chunk
}
dbClearResult(res)

Tables

# List tables
dbListTables(con)

# Check if table exists
dbExistsTable(con, "table_name")

# Read table
df <- dbReadTable(con, "table_name")

# Write table
dbWriteTable(con, "new_table", df)
dbWriteTable(con, "table", df, append = TRUE)
dbWriteTable(con, "table", df, overwrite = TRUE)

# Remove table
dbRemoveTable(con, "table_name")

# List fields
dbListFields(con, "table_name")

Transactions

# Begin transaction
dbBegin(con)

tryCatch({
  dbExecute(con, "INSERT INTO table VALUES (1, 'a')")
  dbExecute(con, "INSERT INTO table VALUES (2, 'b')")
  dbCommit(con)
}, error = function(e) {
  dbRollback(con)
  stop(e)
})

# With transaction helper
dbWithTransaction(con, {
  dbExecute(con, "INSERT INTO table VALUES (1, 'a')")
  dbExecute(con, "INSERT INTO table VALUES (2, 'b')")
})

Metadata

# Connection info
dbGetInfo(con)

# Query info
res <- dbSendQuery(con, "SELECT * FROM table")
dbGetInfo(res)
dbColumnInfo(res)
dbGetRowCount(res)
dbGetRowsAffected(res)
dbClearResult(res)