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


You might also enjoy

16 New ML Gems for Ruby

Securing Sensitive Data in Rails

Introducing Dexter, the Automatic Indexer for Postgres


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