The {DBI} package defines a common interface between the R and database management systems (DBMS). Hence the name: DBI stands for DataBase Interface.

Using DBI, developers can focus on the functionalities of their code, instead of setting up the infrastructure depending on the underlying database. This DBMS-agnostic approach is possible, because DBI works best with several other packages that act as drivers to absorb the peculiarities of the specific DBMSs.

These packages import {DBI} and implement its methods depending on the specific database management system.

Currently, DBI works with the many different database management systems, e.g.:

  • MySQL, using the R-package RMySQL
  • MariaDB, using the R-package RMariaDB
  • Postgres, using and the R-package RPostgres
  • SQLite, using and the R-package RSQLite

DBI offers a set of classes and methods that define what operations are possible and how they are performed:

  • 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)

Examples

To showcase DBI capabilities, we create a in-memory RSQLite database

library(DBI)

con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")
con
## <SQLiteConnection>
##   Path: :memory:
##   Extensions: TRUE

The function dbListTables() displays the names tables in the remote database. Since we haven’t pushed any data to the database, there are no tables to show.

## character(0)

We can write the famous data mtcars dataset to the remote database by using dbWriteTable(). Calling dbListTables() displays the table name:

dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)
## [1] "mtcars"

To get all columns names of a remote table, use dbListFields(). It returns a character vector with all column names in the same order as in the database:

dbListFields(con, "mtcars")
##  [1] "mpg"  "cyl"  "disp" "hp"   "drat" "wt"   "qsec" "vs"   "am"   "gear"
## [11] "carb"

If you want to import database table from the DBMS as a data frame, dbReadTable() helps to do that. Basically, it is the result of the most generic SQL call SELECT * FROM <name>.

dbReadTable(con, "mtcars")
##     mpg cyl  disp  hp drat    wt  qsec vs am gear carb
## 1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
## 2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
## 3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
## 4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
## 5  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
## 6  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
## 7  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4
## 8  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
## 9  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
## 10 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
## 11 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4
## 12 16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3
## 13 17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3
## 14 15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3
## 15 10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4
## 16 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4
## 17 14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4
## 18 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
## 19 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
## 20 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
## 21 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
## 22 15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2
## 23 15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2
## 24 13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4
## 25 19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2
## 26 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
## 27 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
## 28 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
## 29 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
## 30 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
## 31 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8
## 32 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2

Of course, you can run more specific SQL queries, too. dbGetQuery() is the function to send a query to a database and retrieve the result as a data frame. Especially when working with large datasets, it is important to free the resources associated with retrieving the result. dbGetQuery() cares about this, too.

df <- dbGetQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
df
##     mpg cyl  disp  hp drat    wt  qsec vs am gear carb
## 1  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
## 2  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
## 3  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
## 4  32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
## 5  30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
## 6  33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
## 7  21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
## 8  27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
## 9  26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
## 10 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
## 11 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2

Behind the scences, dbGetQuery() is a combination of dbSendQuery(), dbFetch() and dbClearResult(). The following snippet leads to the same result as dbGetQuery() above:

res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
df <- dbFetch(res)
dbClearResult(res)
df
##     mpg cyl  disp  hp drat    wt  qsec vs am gear carb
## 1  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
## 2  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
## 3  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
## 4  32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
## 5  30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
## 6  33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
## 7  21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
## 8  27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
## 9  26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
## 10 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
## 11 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2

When working with large datasets it might be smart to fetch the result step by step, not in one big chunk. This can be implemented with a while loop and a dbFetch() call that defines a maximum number of records to retrieve per fetch, here n = 5. There are eleven cars with four cylinders, so we expect two chunks of five rows and one chuck of one row:

res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
while(!dbHasCompleted(res)){
  chunk <- dbFetch(res, n = 5)
  print(nrow(chunk))
}
## [1] 5
## [1] 5
## [1] 1

Again, call dbClearResult() and disconnect from the connection with dbDisconnect(), when you are done:

Further Reading