4.1 DBI

Before going into the details of dbplyr, we should briefly explain the DBI package. This is just an overview - we’ll see a basic example of working with DBI in the next section.

DBI is a generic database access package allowing you to perform most common database operations from R. If you’ve used database packages like RODBC the general approach should be familiar.

DBI’s big strength is that it can connect to many different database types using “database backends” that are provided by separate R packages. A few examples are:

• RPostgres for PostgreSQL
• RMariaDB for MariaDB or MySQL
• RSQLite for SQLite
• odbc for databases that you can access via ODBC (commonly used for MS SQL Server)
• bigrquery for Google BigQuery

These backend packages handle the translation of the standard DBI functions (e.g. running queries, creating and updating tables) to lower level interaction with the database, taking account of any differences in the database implementation and removing these from the view of the user.

This means that we can use the same set of functions for many backends. If, for example, we moved our database from a PostgreSQL server to MariaDB we could continue to use the same R code while simply changing the connection settings.

A full overview of working with database connections is out of the scope of this book. We would recommend the RStudio Databases using R guide and the DBI documentation as a starting point for connecting to your database of choice.