Setting up ODBC Drivers

Using Posit Professional Drivers

When working with databases on RStudio Desktop Pro and other Posit professional products, it is strongly recommended to use the Posit Professional Drivers. Not only these come with full support, but also they simplify the installation and configuration process is most cases, not requiring many of the steps detailed below.

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

For Linux and MacOS, ODBC drivers should be compiled against unixODBC. Drivers compiled against iODBC may also work, but are not fully supported.

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().

Microsoft Windows

Database Drivers

Windows is bundled with ODBC libraries; however, drivers for each database need to be installed separately. Windows ODBC drivers typically include an installer that must be run to install the drivers in the proper locations.

Administration

The ODBC Data Source Administrator application is used to manage ODBC data sources on Windows.

Apple MacOS

Installation

  1. Install homebrew to install database drivers on MacOS

  2. Install UnixODBC, which is required for all databases

# Install the unixODBC library
brew install unixodbc
  1. Install common DB drivers (optional)
# SQL Server ODBC Drivers (Free TDS)
brew install freetds --with-unixodbc

# PostgreSQL ODBC ODBC Drivers
brew install psqlodbc

# MySQL ODBC Drivers (and database)
brew install mysql

# SQLite ODBC Drivers
brew install sqliteodbc

Next, follow the instructions in Setting up database connections to configure your database connections.

Linux Debian / Ubuntu

Installation

The apt-get command can be used to install database drivers on Linux distributions that support it, such as Debian and Ubuntu.

  1. Install UnixODBC, which is required for all databases
# Install the unixODBC library
apt-get install unixodbc unixodbc-dev --install-suggests
  1. Install common DB drivers (optional)
# SQL Server ODBC Drivers (Free TDS)
apt-get install tdsodbc

# PostgreSQL ODBC ODBC Drivers
apt-get install odbc-postgresql

# MySQL ODBC Drivers
apt-get install libmyodbc

# SQLite ODBC Drivers
apt-get install libsqliteodbc

Next, follow the instructions in Setting up database connections to configure your database connections.

Setting up database connections

On MacOS and Linux, there are two separate text files that need to be edited. UnixODBC includes a command-line executable called odbcinst, which can be used to query and modify the DSN files. However, these are plain text files you can also edit by hand if desired.

There are two different files used to set up the DSN information:

  • odbcinst.ini defines driver options

  • odbc.ini defines connection options

odbcinst.ini

This file contains the driver information, particularly the name of the driver library. Multiple drivers can be specified in the same file.

[PostgreSQL Driver]
Driver          = /usr/local/lib/psqlodbcw.so

[SQLite Driver]
Driver          = /usr/local/lib/libsqlite3odbc.dylib

On MacOS aarch64 machines, drivers installed via homebrew are in a different location, as seen below.

[PostgreSQL Driver]
Driver          = /opt/homebrew/lib/psqlodbcw.so

[SQLite Driver]
Driver          = /opt/homebrew/lib/libsqlite3odbc.dylib

odbc.ini

This file contains the connection information, particularly the username, password, database and host information. The Driver line corresponds to the driver defined in odbcinst.ini.

[PostgreSQL]
Driver      = PostgreSQL Driver
Database    = test_db
Servername  = localhost
UserName    = postgres
Password    = password
Port        = 5432

[SQLite]
Driver      = SQLite Driver
Database    =/tmp/testing

See also: unixODBC without the GUI for more information and examples.

Location

The DSN configuration files can be defined globally for all users of the system, often at /etc/odbc.ini, /opt/local/etc/odbc.ini, or /opt/homebrew/etc/odbc.ini. The file location depends on what option was used when compiling unixODBC; odbcinst -j can be used to find the exact location. Alternatively, the ODBCSYSINI environment variable can beused to specify the location of the configuration files. Ex. ODBCSYSINI=~/ODBC

A local DSN file can also be used with the files ~/.odbc.ini and ~/.odbcinst.ini.

MacOS aarch64

If ODBC has trouble locating your system data source names, you may need to override the default location where ODBC looks for your configuration files.

To specify the location of your DSN configuration files, use one of the following options below:

Option 1: Save Setting to ~/.Renviron
  1. Create or open the ~/.Renviron file.
  2. Add ODBCSYSINI=/opt/homebrew/etc to your ~/.Renviron file and save your changes.
  3. Before connecting to a database, restart any open R sessions.
Option 2: Set ODBCSYSINI Environment Variable
  1. Set the ODBCSYSINI environment variable, eg. ODBCSYSINI=/opt/homebrew/etc.
  2. Before connecting to a database, restart any open R sessions.

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

# Or the the development version from GitHub:
# install.packages(devtools)
devtools::install_github("rstats-db/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(odbc) 

con <- DBI::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(odbc) 

con <- dbConnect(odbc::odbc(), "PostgreSQL")
import pyodbc 

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

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

Back to top