R and Database URLs

Note: This approach is now built into the dbx package


To use a DATABASE_URL with R, do:

Postgres

library(RPostgreSQL)
library(httr)

establishConnection <- function(url=Sys.getenv("DATABASE_URL"))
{
  cred <- parse_url(url)
  if (!identical(cred$scheme, "postgres")) stop("Invalid database url")
  if (is.null(cred$username)) cred$username <- ""
  if (is.null(cred$password)) cred$password <- ""
  if (is.null(cred$port)) cred$port <- 5432
  dbConnect(PostgreSQL(), host=cred$hostname, port=cred$port,
    user=cred$username, password=cred$password, dbname=cred$path)
}

con <- establishConnection()
dbGetQuery(con, "SELECT true AS success")

MySQL

library(RMySQL)
library(httr)

establishConnection <- function(url=Sys.getenv("DATABASE_URL"))
{
  cred <- parse_url(url)
  if (!identical(cred$scheme, "mysql")) stop("Invalid database url")
  if (is.null(cred$username)) cred$username <- "root"
  if (is.null(cred$password)) cred$password <- ""
  if (is.null(cred$port)) cred$port <- 3306
  dbConnect(MySQL(), host=cred$hostname, port=cred$port,
    user=cred$username, password=cred$password, dbname=cred$path)
}

con <- establishConnection()
dbGetQuery(con, "SELECT true AS success")

cake

Published August 10, 2015 · Tweet


You might also enjoy

Error Reporting in R

Securing Database Traffic with PgBouncer and Amazon RDS

Why and How to Keep Your Decryption Keys Off Web Servers


All code examples are public domain.
Use them however you’d like (licensed under CC0).