Setting up ODBC Drivers

Using Posit Professional Drivers

When working with databases on RStudio Desktop Pro, Posit Workbench, or Posit Connect, we strongly recommend that you use the Posit Professional Drivers. Not only do these come with full support, but they also simplify the installation and configuration process in most cases.

Diagram of Using Posit's Professional Drivers. From R, the packages DBI and odbc connect to the System ODBC driver on the user's laptop or the server.

Diagram of Using Posit’s Professional Drivers

Using other drivers

See vignette("setup", package = "odbc") for how to install common ODBC drivers and configure them with a driver manager. For Linux and MacOS, ODBC drivers should be compiled against unixODBC. Drivers compiled against iODBC are not supported.

In most cases, after installation of the driver manager and driver, you will have to register the driver in a odbcinst.ini file for it to appear in odbc::odbcListDrivers().

Connecting to a Database in R and Python

Databases can be connected by specifying a connection string directly, or with DSN configuration files.

# Install the latest odbc release from CRAN:
install.packages("odbc")
# Install the latest pyodbc release from PyPI:
python -m pip install pyodbc

Connection Strings

Pass the connection parameters as arguments to dbConnect() or pyodbc.connect.

library(DBI)
con <- dbConnect(
  odbc::odbc(),
  driver = "PostgreSQL Driver",
  database = "test_db",
  uid    = Sys.getenv("DB_USER"),
  pwd    = Sys.getenv("DB_PASSWORD"),
  host   = "localhost",
  port   = 5432
)
import os
import pyodbc

con = pyodbc.connect(
  driver = 'PostgreSQL',
  database = 'test_db',
  server = 'localhost',
  port = 5432, 
  uid = os.getenv('DB_USER'),
  pwd = os.getenv('DB_PASSWORD')
)

For database-specific settings, go to the Databases section and look for the page that matches the desired database type.

DSN Configuration files

ODBC configuration files are another option to specify connection parameters; they allow you to use a Data Source Name (DSN) to make it easier to connect to a database.

library(DBI)
con <- dbConnect(odbc::odbc(), dsn = "PostgreSQL")
import pyodbc 

con = pyodbc.connect(DSN='PostgreSQL Test Database')

For more information about how DSNs can be used, check out the following articles in this site:

Back to top