21.3 Connecting to a database

To connect to the database from R, you’ll use a pair of packages:

  • You’ll always use DBI (database interface) because it provides a set of generic functions that connect to the database, upload data, run SQL queries, etc.

  • You’ll also use a package tailored for the DBMS you’re connecting to. This package translates the generic DBI commands into the specifics needed for a given DBMS. There’s usually one package for each DBMS, e.g. RPostgres for Postgres and RMariaDB for MySQL.

If you can’t find a specific package for your DBMS, you can usually use the odbc package instead. This uses the ODBC protocol supported by many DBMS. odbc requires a little more setup because you’ll also need to install an ODBC driver and tell the odbc package where to find it.

con <- DBI::dbConnect(duckdb::duckdb())
  • When using duckdb in a project
con <- DBI::dbConnect(duckdb::duckdb(), dbdir = "duckdb")