Using DatabaseConnector"

oldJarFolder <- Sys.getenv("DATABASECONNECTOR_JAR_FOLDER")
Sys.setenv("DATABASECONNECTOR_JAR_FOLDER" = tempdir())


DatabaseConnector is an R package for connecting to various database platforms using Java's JDBC drivers.

Supported database platforms:

Obtaining drivers

Before DatabaseConnector can be used to connect to a database, the drivers need to be downloaded to a location in the local file system.

Obtaining drivers for SQL Server, Oracle, PostgreSQL, PDW, RedShift

For your convenience these JDBC drivers are hosted on the OHDSI GitHub pages, and can be downloaded using the downloadJdbcDrivers function. Before downloading, we need to specify the location on the local file system where the driver can be stored. We recommend setting the DATABASECONNECTOR_JAR_FOLDER environmental variable to the folder where the drivers are stored.

For example, in this vignette we will use the PostgreSQL driver. We specify the local folder we'd like to use for storing the PostgreSQL driver:

Sys.setenv("DATABASECONNECTOR_JAR_FOLDER" = "c:/temp/jdbcDrivers")

And next download the driver:

writeLines("DatabaseConnector JDBC drivers downloaded to 'c:/temp/jdbcDrivers'.")

Note that if we hadn't specified the DATABASECONNECTOR_JAR_FOLDER environmental variable, we would have to specify the pathToDriver argument when calling downloadJdbcDrivers.

Obtaining drivers for BigQuery, Netezza and Impala

Because of licensing reasons the drivers for BigQuery, Netezza and Impala are not included but must be obtained by the user. Type


for instructions on how to download these drivers. Once downloaded, you can use the pathToDriver argument of the connect, dbConnect, and createConnectionDetails functions. Just like for the other JDBC drivers, we recommend setting the DATABASECONNECTOR_JAR_FOLDER environmental variable instead.

Obtaining drivers for SQLite

For SQLite we actually don't use a JDBC driver. Instead, we use the RSQLite package, which can be installed using


Creating a connection

To connect to a database a number of details need to be specified, such as the database platform, the location of the server, the user name, password, and path to the driver. We can call the connect function and specify these details directly:

conn <- connect(dbms = "postgresql",
                server = "localhost/postgres",
                user = "joe",
                password = "secret")
writeLines("Connecting using PostgreSQL driver")

See ?connect for information on which details are required for each platform. Note that we did not need to specify the pathToDriver argument because we previously already set the DATABASECONNECTOR_JAR_FOLDER environmental variable.

Don't forget to close any connection afterwards:


Instead of providing the server name, it is also possible to provide the JDBC connection string if this is more convenient:

conn <- connect(dbms = "postgresql",
                connectionString = "jdbc:postgresql://localhost:5432/postgres",
                user = "joe",
                password = "secret")
writeLines("Connecting using PostgreSQL driver")

Sometimes we may want to first specify the connection details, and defer connecting until later. This may be convenient for example when the connection is established inside a function, and the details need to be passed as an argument. We can use the createConnectionDetails function for this purpose:

details <- createConnectionDetails(dbms = "postgresql",
                                   server = "localhost/postgres",
                                   user = "joe",
                                   password = "secret")
conn <- connect(details)
writeLines("Connecting using PostgreSQL driver")


The main functions for querying database are the querySql and executeSql functions. The difference between these functions is that querySql expects data to be returned by the database, and can handle only one SQL statement at a time. In contrast, executeSql does not expect data to be returned, and accepts multiple SQL statements in a single SQL string.

Some examples:

querySql(conn, "SELECT TOP 3 * FROM person")
data.frame(PERSON_ID = c(1,2,3), GENDER_CONCEPT_ID = c(8507, 8507, 8507), YEAR_OF_BIRTH = c(1975, 1976, 1977))
executeSql(conn, "TRUNCATE TABLE foo; DROP TABLE foo; CREATE TABLE foo (bar INT);")

Both function provide extensive error reporting: When an error is thrown by the server, the error message and the offending piece of SQL are written to a text file to allow better debugging. The executeSql function also by default shows a progress bar, indicating the percentage of SQL statements that has been executed. If those attributes are not desired, the package also offers the lowLevelQuerySql and lowLevelExecuteSql functions.

Querying using Andromeda objects

Sometimes the data to be fetched from the database is too large to fit into memory. In this case one can use the Andromeda package to store R data objects on file, and use them as if they are available in memory. DatabaseConnector can download data directly into Andromeda objects:

x <- andromeda()
querySqlToAndromeda(conn, "SELECT * FROM person", andromeda = x, andromedaTableName = "person)

Where x is now an Andromeda object with table person.

Querying different platforms using the same SQL

One challenge when writing code that is intended to run on multiple database platforms is that each platform has its own unique SQL dialect. To tackle this problem the SqlRender package was developed. SqlRender can translate SQL from a single starting dialect (SQL Server SQL) into any of the platforms supported by DatabaseConnector. The following convenience functions are available that first call the render and translate functions in SqlRender: renderTranslateExecuteSql, renderTranslateQuerySql, renderTranslateQuerySqlToAndromeda. For example:

persons <- renderTranslatequerySql(conn, 
                                   sql = "SELECT TOP 10 * FROM @schema.person",
                                   schema = "cdm_synpuf")

Note that the SQL Server-specific 'TOP 10' syntax will be translated to for example 'LIMIT 10' on PostgreSQL, and that the SQL parameter @schema will be instantiated with the provided value 'cdm_synpuf'.

Inserting tables

Although it is also possible to insert data in the database by sending SQL statements using the executeSql function, it is often convenient and faster to use the insertTable function:

insertTable(conn, "mtcars", mtcars, createTable = TRUE)

In this example, we're uploading the mtcars data frame to a table called 'mtcars' on the server, that will be automatically created.

DBI interface

DatabaseConnector implements the DBI interface for compatibility with other R packages. One can use the DBI functions instead of the ones described before, for example:

conn <- dbConnect(DatabaseConnectorDriver(), 
                  dbms = "postgresql",
                  server = "localhost/postgres",
                  user = "joe",
                  password = "secret")
writeLines("Connecting using PostgreSQL driver")
res <- dbSendQuery(conn, "SELECT TOP 3 * FROM person")
data.frame(PERSON_ID = c(1,2,3), GENDER_CONCEPT_ID = c(8507, 8507, 8507), YEAR_OF_BIRTH = c(1975, 1976, 1977))

SQLite support

DatabaseConnector also supports SQLite through the RSQLite package, mainly for testing and demonstration purposes. Provide the path to the SQLite file as the server argument when connecting. If no file exists it will be created:

conn <- connect(dbms = "sqlite", server = tempfile())

# Upload cars dataset as table:
insertTable(connection = conn, 
            tableName = "cars", 
            data = cars)

querySql(conn, "SELECT COUNT(*) FROM;")


Try the DatabaseConnector package in your browser

Any scripts or data that you put into this service are public.

DatabaseConnector documentation built on April 15, 2021, 9:06 a.m.