03-db-connections.Rmd

```{r db-connections, include = FALSE}
if(Sys.getenv("GLOBAL_EVAL") != "") eval_connections <- Sys.getenv("GLOBAL_EVAL")
eval_connections <- FALSE
```

```{r, eval = eval_connections, include = FALSE}
library(DBI)
library(odbc)
library(config)
library(keyring)
```

# Introduction to database connections

## Connect with the Connections pane
*Connect using the features of the RStudio IDE*

1. The connections pane (top right hand corner of the RStudio IDE) can guide through establishing database connections.

## Connecting via DSN
*Connect using defined Data Source Name (DSN). This requires an ODBC driver.*

1. Load the `DBI` and `odbc` packages
    ```{r, eval_connections = eval_connections}
    library(DBI)
    library(odbc)
    ```

2. Use `odbcListDatasources` to list available DSNs
    ```{r, eval = eval_connections}
    
    ```
    
3. Use `dbConnect` to connect to a database using the `odbc` function and a DSN
    ```{r, eval = eval_connections}
    
    ```
    
4. Disconnect using `dbDisconnect`
    ```{r, eval = eval_connections}
    
    ```

## Connect with a connection string
*Connect by specifying all connection details in `dbConnect`*

1. Use `dbConnect` and `odbc` to connect to a database, but this time all connection details are provided
    ```{r, eval = eval_connections}
    con <- dbConnect(odbc(),
                     driver = "postgresql",
                     host = "localhost",
                     user = "rstudio_dev",
                     pwd = "dev_user",
                     port = 5432,
                     database = "postgres",
                     bigint = "integer")
    ```
    
2. Disconnect using `dbDisconnect`
    ```{r, eval = eval_connections}
    
    ```
    
## Secure connection details
*Use `config`, `keyring`, or environment variables to secure connection credentials*

1. Load the `config` package
    ```{r, eval = eval_connections}
    library(config)
    ```
    
2. Get the current config using the `get` function and store the results in an object called `config`
    ```{r, eval = eval_connections}
    
    ```

3. Use `str` to investigate the contents of `config`
    ```{r, eval = eval_connections}
    
    ```

4. Connect using details provided in `config`
    ```{r, eval = eval_connections}
    con <- dbConnect(odbc(),
                     driver = config$driver,
                     host = config$host,
                     user = config$user,
                     pwd = config$pwd,
                     port = config$port,
                     database = config$dbname,
                     bigint = config$bigint)
    ```
    
5. Disconnect using `dbDisconnect`
    ```{r, eval = eval_connections}
    dbDisconnect(con)
    ```
    
6. Load the `keyring` package
    ```{r, eval = eval_connections}
    library(keyring)
    ```

7. Store the database username and password using `keyring`. The username is `rstudio_dev` and the password is `dev_user`
    ```{r, eval = eval_connections}
    key_set("postgres", "username")
    key_set("postgres", "password")
    ```
    
8. Use the stored credentials along with `dbConnect` to connect to the database
    ```{r, eval = eval_connections}
    con <- dbConnect(odbc(),
                     driver = "postgresql",
                     host = "localhost",
                     user = key_get("postgres", "username"),
                     pwd = key_get("postgres", "password"),
                     port = 5432,
                     database = "postgres",
                     bigint = "integer")
    ```

9. Discnonnect using `dbDisconnect`
    ```{r, eval = eval_connections}
    dbDisconnect(con)
    ```

10. The `.Renviron` file contains entries to create environment variables for `PG_USER` and `PG_PWD`. These variables can be read using `Sys.getenv()`.
    ```{r, eval = eval_connections}
    Sys.getenv("PG_USER")
    ```

11. Connect to the database using the credentials stored in `.Renviron` and `Sys.getenv()`
    ```{r, eval = eval_connections}
    con <- dbConnect(odbc(),
                     driver = "postgresql",
                     host = "localhost",
                     user = Sys.getenv("PG_USER"),
                     pwd = Sys.getenv("PG_PWD"),
                     port = 5432,
                     database = "postgres",
                     bigint = "integer")
    ```
    
12. Disconnect using `dbDisconnect`
    ```{r, eval = eval_connections}
    dbDisconnect(con)
    ```

13. Store connection details using `options()`
    ```{r}
    options(pg_user = "rstudio_dev", pg_pwd = "dev_user")
    ```

14. Connect using the credentials accessed via `getOption`
    ```{r}
    con <- dbConnect(odbc(),
                     driver = "postgresql",
                     host = "localhost",
                     user = getOption("pg_user"),
                     pwd = getOption("pg_pwd"),
                     port = 5432,
                     database = "postgres",
                     bigint = "integer")
    ```
    
15. Disconnect using `dbDisconnect`
    ```{r}
    dbDisconnect(con)
    ```

16. Interactively prompt users for input using `rstudioapi::askForPassword()`
    ```{r}
    con <- dbConnect(odbc(),
                     driver = "postgresql",
                     host = "localhost",
                     user = rstudioapi::askForPassword("DB User"),
                     pwd = rstudioapi::askForPassword("DB Password"),
                     port = 5432,
                     database = "postgres",
                     bigint = "integer")
    ```

17. Disconnect using `dbDisconnect`
    ```{r}
    dbDisconnect(con)
    ```