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

navigator.sendBeacon and Rails

Anonymizing IPs in Ruby

Verify Slack Requests in Rails


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