What arguments can I pass to dbConnect?

Question

To connect to a SQLite database, it is reasonably straightforward: you pass a driver and a path to the SQLite file.

For other DBI-compatible database backends (PostgreSQL, MySQL, etc.), what you can pass to dbConnect isn't obvious, nor well documented.

How do I find out what I can pass to dbConnect to form the connection?

Answer 1

The arguments available depend upon the backend, which is why dbConnect only shows driver and ... arguments.

Before we proceed, load the packages required for the database connection (RSQLite, RPostgreSQL, RMySQL or whatever).

---

To find the name of the driver

This should be the same as the name of the database backend. Currently supported values include "SQLite", "PostgreSQL", "MySQL", "Oracle", "JDBC".

The string must always be the name of a function in the DB package, so if in doubt search through the package (use, for example, ls("package:RSQLite")) and guess at names that look plausible.

---

To find the ... args

You can see the methods available for connection using showMethods.

showMethods("dbConnect")
## Function: dbConnect (package DBI)
## drv="character"
## drv="PostgreSQLConnection"
## drv="PostgreSQLDriver"
## drv="SQLiteConnection"
## drv="SQLiteDriver"

Get the method for the appropriate backend, with Driver in the name.

getMethod(dbConnect, "PostgreSQLDriver")
# The 'Method Definition' section of the output
## function (drv, ...) 
## postgresqlNewConnection(drv, ...)

The available arguments are are those of the function that is called inside the previous method definition.

?postgresqlNewConnection

args(postgresqlNewConnection)
## function (drv, user = "", password = "", host = "", dbname = "", 
##     port = "", tty = "", options = "", forceISOdate = TRUE) 

SQLite and MySQL call locally defined functions, which are a bit of a pain to access programmatically.

?SQLite

args(body(getMethod("dbConnect", "SQLiteDriver"))[[2]][[3]])
## function (drv, dbname = "", ..., loadable.extensions = TRUE, 
## cache_size = NULL, synchronous = "off", flags = SQLITE_RWC, 
## vfs = NULL) 
## function (...)

?MySQL

args(body(getMethod("dbConnect", "MySQLDriver"))[[2]][[3]])
## function (drv, dbname = NULL, username = NULL, password = NULL, 
## host = NULL, unix.socket = NULL, port = 0, client.flag = 0, 
## groups = "rs-dbi", default.file = NULL, ...

?OraSupport

# Oracle
args(.oci.Connect)
## function(drv, username = "", password = "", dbname = "", prefetch = FALSE, 
##     bulk_read = 1000L, stmt_cache = 0L, external_credentials = FALSE, 
##     sysdba = FALSE)

RJDBC works slightly differently. Rather than calling another subfunction, its dbConnect method accepts additional named args.

formalArgs(getMethod(dbConnect, "JDBCDriver"))
## [1] "drv"      "url"      "user"     "password" "..."