In r-dbi/DBI: R Database Interface

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "man/figures/README-",
  out.width = "100%"
)

DBI

The DBI package helps connecting R to database management systems (DBMS). DBI separates the connectivity to the DBMS into a "front-end" and a "back-end". The package defines an interface that is implemented by DBI backends such as:

• RPostgres,
• RMariaDB,
• RSQLite,
• odbc,
• bigrquery,

and many more, see the list of backends. R scripts and packages use DBI to access various databases through their DBI backends.

The interface defines a small set of classes and methods similar in spirit to Perl's DBI, Java's JDBC, Python's DB-API, and Microsoft's ODBC. It supports the following operations:

• connect/disconnect to the DBMS
• create and execute statements in the DBMS
• extract results/output from statements
• error/exception handling
• information (meta-data) from database objects
• transaction management (optional)

Installation

Most users who want to access a database do not need to install DBI directly. It will be installed automatically when you install one of the database backends:

• RPostgres for PostgreSQL,
• RMariaDB for MariaDB or MySQL,
• RSQLite for SQLite,
• odbc for databases that you can access via ODBC,
• bigrquery,
• ... .

You can install the released version of DBI from CRAN with:

install.packages("DBI")

And the development version from GitHub with:

# install.packages("devtools")
devtools::install_github("r-dbi/DBI")

Example

The following example illustrates some of the DBI capabilities:

library(DBI)
# Create an ephemeral in-memory RSQLite database
con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")

dbListTables(con)
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)

dbListFields(con, "mtcars")
dbReadTable(con, "mtcars")

# You can fetch all results:
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(res)
dbClearResult(res)

# Or a chunk at a time
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
while (!dbHasCompleted(res)) {
  chunk <- dbFetch(res, n = 5)
  print(nrow(chunk))
}
dbClearResult(res)

dbDisconnect(con)

Class structure

There are four main DBI classes. Three which are each extended by individual database backends:

• DBIObject: a common base class for all DBI.

• DBIDriver: a base class representing overall DBMS properties. Typically generator functions instantiate the driver objects like RSQLite(), RPostgreSQL(), RMySQL() etc.

• DBIConnection: represents a connection to a specific database

• DBIResult: the result of a DBMS query or statement.

All classes are virtual: they cannot be instantiated directly and instead must be subclassed.

Further Reading

• Databases using R describes the tools and best practices in this ecosystem.

• The DBI project site hosts a blog where recent developments are presented.

• A history of DBI by David James, the driving force behind the development of DBI, and many of the packages that implement it.

---

Please note that the DBI project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.

r-dbi/DBI documentation built on May 6, 2024, 12:03 a.m.