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

Securing Sensitive Data in Rails

Introducing Dexter, the Automatic Indexer for Postgres

Rails, Meet Data Science


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