Testing database connections in R

Avatar
Cecil Singh
Follow

You may wish to test database connections from within R, outside of Posit Workbench. To do so, you will need shell access to your Workbench server and a valid R installation. The steps to install R can be found here:

https://docs.posit.co/resources/install-r/

Launching an R session from the terminal

If R is available on the default system PATH variable, you can run the following:

R

Alternatively, you can specify the path to your R installation. For example:

/opt/R/${R_VERSION}/bin/R

Where ${R_VERSION} is the environment variable which specifies the version of R you wish to test with. If this is 4.0.5, then you will run the following:

/opt/R/4.0.5/bin/R 

If you've installed R from source, then the path to your R installation will be different to the above, and will need to be adjusted accordingly. If completed correctly, you will see a prompt similar to the below

Testing connectivity with your database

Once we've connected to an R session, we can create our connection string and run this in the R console. Depending on the database that you're connecting to, there are a variety of different packages and connection strings to use. The different methods for connecting to your database can be found here:

ODBC

https://solutions.posit.co/connections/db/r-packages/odbc/

DBI

https://solutions.posit.co/connections/db/r-packages/dbi/

Example Connection String

Whilst there are a number of different connection strings that can be used, an example can be seen below with a Postgres database:

library(DBI)
con <- dbConnect(odbc::odbc(),
  driver = "PostgreSQL Driver",
  database = "test_db",
  uid = "postgres",
  pwd = "password",
  host = "localhost",
  port = 5432)

Comments