Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
100 lines (79 sloc) 2.92 KB
---
title: "Using alternate database backends for Cache"
author:
- "Alex M. Chubaty"
- "Eliot J. B. McIntire"
date: '`r strftime(Sys.Date(), "%B %d %Y")`'
output:
rmarkdown::html_vignette:
fig_width: 7
number_sections: yes
self_contained: yes
toc: yes
vignette: >
%\VignetteIndexEntry{Cache using postgresql}
%\VignetteDepends{DBI}
%\VignetteKeyword{Cache, postgresql}
%\VignetteEncoding{UTF-8}
%\VignetteEngine{knitr::rmarkdown}
editor_options:
chunk_output_type: console
---
```{r setup, include=FALSE}
knitr::opts_chunk$set(#cache = TRUE,
message = FALSE, warning = FALSE,
echo = TRUE, eval = FALSE)
```
# Cache database backends
By default, caching relies on a sqlite database for it's backend.
While this works in many situations, there are some important limitations of using sqlite for caching, including 1) speed; 2) concurrent transactions; 3) sharing database across machines or projects.
Fortunately, `Cache` makes use of `DBI` package and thus supports several database backends, including mysql and postgresql.
This vignette demonstrates how to use alternate database backends for caching.
# Storing database credentials
**Be careful not to save database credentials in version control.**
We recommend per-project credentials saved either in config files or environment variables, described below.
See <https://db.rstudio.com/best-practices/managing-credentials/> for other important best practices.
# PostgreSQL
## Using config files
Add the following to your project's `config.yml` file:
```{bash}
default:
cachedb:
driver: 'postgres'
server: 'localhost'
user: 'mydbuser'
password: 'mysecurepassword'
port: 5432
database: 'mydatabase'
```
Then, in your main project script, add the following:
```{r}
cachedb <- config::get("cachedb")
conn <- DBI::dbConnect(drv = RPostgres::Postgres(),
host = cachedb$server,
port = cachedb$port,
dbname = cachedb$database,
user = cachedb$user,
password = cachedb$password)
options("reproducible.conn" = conn) # sets the default connection globally
```
## Using environment variables
Add the following to your project's `.Renviron` file:
```{bash}
PGHOST="localhost"
PGPORT=5432
PGDATABASE="mydatabase"
PGUSER="mydbuser"
PGPASSWORD="mysecurepassword"
```
Then, in your main project script, add the following:
```{r}
readRenviron(".Renviron") ## alternatively, use global ~/.Renviron
conn <- DBI::dbConnect(drv = RPostgres::Postgres(),
host = Sys.getenv("PGHOST"),
port = Sys.getenv("PGPORT"),
dbname = Sys.getenv("PGDATABASE"),
user = Sys.getenv("PGUSER"),
password = Sys.getenv("PGPASSWORD"))
options("reproducible.conn" = conn) # sets the default connection globally
```
You can’t perform that action at this time.