odbc v1.0.1


Monthly downloads



by James Hester

Connect to ODBC Compatible Databases (using the DBI Interface)

A DBI-compatible interface to ODBC databases.



Project Status: Active – The project has reached a stable, usable state and is being actively developed. CRAN\_Status\_Badge Travis-CI Build Status Coverage Status AppVeyor Build Status

The goal of the odbc package is to provide a DBI-compliant interface to Open Database Connectivity.aspx) (ODBC) drivers. This gives a efficient, easy to setup connection to any database with ODBC drivers available, including SQL Server, Oracle, MySQL, PostgreSQL, SQLite and others. The implementation builds on the nanodbc C++ library.


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


Windows is bundled with ODBC libraries however drivers for each database need to be installed separately. Windows ODBC drivers typically include a installer that needs to be run and will install the driver to the proper locations.


homebrew can be used to easily install database drivers on MacOS.

UnixODBC - Required for all databases

# Install the unixODBC library
brew install unixodbc

Common DB drivers

# 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

Linux - Debian / Ubuntu

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

UnixODBC - Required for all databases

# Install the unixODBC library
apt-get install unixodbc unixodbc-dev

Common DB drivers

# 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


odbc is not yet available on CRAN, however devtools can be used to install the latest version.

# install.packages(devtools)

Connecting to a Database

Databases can be connect by specifying a connection string directly, or with DSN Configuration files.

Connection Strings

Specify a connection string as named arguments directly in the dbConnect() method.

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

Alternatively you can pass a complete connection string as the .connection_string argument. The Connection Strings Reference is a useful resource that has example connection strings for a large variety of databases.

con <- dbConnect(odbc::odbc(),
  .connection_string = "Driver={PostgreSQL Driver};Uid=postgres;Pwd=password;Host=localhost;Port=5432;Database=test_db;")

DSN Configuration files

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

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


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

MacOS / Linux

On MacOS and Linux there are two separate text files that need to be edited. UnixODBC includes an command line executable 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 setup the DSN information. - odbcinst.ini - which defines driver options - odbc.ini - which defines connection options

The DSN configuration files can be defined globally for all users of the system, often at /etc/odbc.ini or /opt/local/etc/odbc.ini, the exact 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 be used 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.


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

Driver          = /usr/local/lib/psqlodbcw.so

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

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

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

Driver          = SQLite Driver

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


All of the following examples assume you have already created a query con. See Connecting to a database for more information on establishing a connection.

Table and Field information

dbListTables() is used for listing tables in a database.


# List tables beginning with f
dbListTables(con, table_name = "f%")

# List all fields in the 'flights' database
dbListFields(con, "flights")


dbReadTable() will read a full table into an R data.frame().

data <- dbReadTable(con, "flights")


dbWriteTable() will write an R data.frame() to an SQL table.

data <- dbWriteTable(con, "iris", iris)


dbGetQuery() will submit a query and fetch the results. It is also possible to submit the query and fetch separately with dbSendQuery() and dbFetch(). If the n= argument to dbFetch() can be used to fetch only part of a query.

result <- dbSendQuery(con, "SELECT flight, tailnum, origin FROM flights ORDER BY origin")

# Retrieve the first 100 results
first_100 <- dbFetch(result, n = 100)

# Retrieve the rest of the results
rest <- dbFetch(result)


Odbc is often much faster than the existing RODBC and DBI compatible RODBCDBI packages.


Reading a from a PostgreSQL database with the nytflights13 'flights' database (336,776 rows, 19 columns).

# First using RODBC / RODBCDBI
rodbc <- dbConnect(RODBCDBI::ODBC(), dsn = "PostgreSQL")
system.time(rodbc_result <- dbReadTable(rodbc, "flights"))
#> Warning: closing unused RODBC handle 2
#>    user  system elapsed 
#>  19.203   1.356  21.724

# Now using odbc
odbc <- dbConnect(odbc::odbc(), dsn = "PostgreSQL")
system.time(odbc_result <- dbReadTable(odbc, "flights"))
#>    user  system elapsed 
#>   5.119   0.290   6.771

#> # A tibble: 336,776 × 20
#>    row.names  year month   day dep_time sched_dep_time dep_delay arr_time
#>        <chr> <int> <int> <int>    <int>          <int>     <dbl>    <int>
#> 1          1  2013     1     1      517            515         2      830
#> 2          2  2013     1     1      533            529         4      850
#> 3          3  2013     1     1      542            540         2      923
#> 4          4  2013     1     1      544            545        -1     1004
#> 5          5  2013     1     1      554            600        -6      812
#> 6          6  2013     1     1      554            558        -4      740
#> 7          7  2013     1     1      555            600        -5      913
#> 8          8  2013     1     1      557            600        -3      709
#> 9          9  2013     1     1      557            600        -3      838
#> 10        10  2013     1     1      558            600        -2      753
#> # ... with 336,766 more rows, and 12 more variables: sched_arr_time <int>,
#> #   arr_delay <dbl>, carrier <chr>, flight <int>, tailnum <chr>,
#> #   origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>, hour <dbl>,
#> #   minute <dbl>, time_hour <dttm>

identical(dim(rodbc_result), dim(odbc_result))
#> [1] TRUE
rm(rodbc_result, odbc_result, odbc, rodbc)
gc(verbose = FALSE)
#> Warning: closing unused RODBC handle 3
#>           used (Mb) gc trigger  (Mb) max used  (Mb)
#> Ncells  712236 38.1    1770749  94.6  1770749  94.6
#> Vcells 8991012 68.6   27225095 207.8 33776265 257.7


Writing the same dataset to the database.

#> [1] TRUE
#> [1] TRUE
#>           used (Mb) gc trigger  (Mb) max used  (Mb)
#> Ncells  712146 38.1    1770749  94.6  1770749  94.6
#> Vcells 8990784 68.6   27225095 207.8 33776265 257.7
# rodbc does not support writing timestamps natively.
rodbc <- dbConnect(RODBCDBI::ODBC(), dsn = "PostgreSQL")
system.time(dbWriteTable(rodbc, "flights2", as.data.frame(flights[, names(flights) != "time_hour"])))
#>    user  system elapsed 
#>   6.693   3.786  48.423

# Now using odbc
odbc <- dbConnect(odbc::odbc(), dsn = "PostgreSQL")
system.time(dbWriteTable(odbc, "flights3", as.data.frame(flights)))
#>    user  system elapsed 
#>   7.802   3.703  26.016

Functions in odbc

Name Description
dbConnect,OdbcDriver-method Connect to a ODBC compatible database
OdbcConnection Odbc Connection Methods
list_data_sources List Available Data Source Names
list_drivers List Available ODBC Drivers
OdbcDriver Odbc Driver Methods
odbc-tables Convenience functions for reading/writing DBMS tables
odbc Odbc driver
OdbcResult Odbc Result Methods
odbc-package odbc: Connect to ODBC Compatible Databases (using the DBI Interface)
No Results!

Last month downloads


License MIT + file LICENSE
LazyData true
LinkingTo Rcpp, BH
URL https://github.com/rstats-db/odbc
BugReports https://github.com/rstats-db/odbc/issues
SystemRequirements An ODBC3 driver manager and drivers.
Collate 'odbc.R' 'Driver.R' 'Connection.R' 'RcppExports.R' 'Result.R' 'Table.R' 'utils.R' 'zzz.R'
NeedsCompilation yes
Packaged 2017-02-06 16:17:34 UTC; jhester
Repository CRAN
Date/Publication 2017-02-07 10:08:57

Include our badge in your README