3 Introduction to database connections

3.1 Connect with the Connections pane

Connect using the features of the RStudio IDE

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

3.2 Connecting via DSN

Connect using defined Data Source Name (DSN). This requires an ODBC driver.

Load the DBI and odbc packages
```
library(DBI)
library(odbc)
```
Use odbcListDatasources to list available DSNs
```
odbcListDataSources()
```
Use dbConnect to connect to a database using the odbc function and a DSN
```
con <- dbConnect(odbc(), "Postgres Dev")
```
Disconnect using dbDisconnect
```
dbDisconnect(con)
```

3.3 Connect with a connection string

Connect by specifying all connection details in dbConnect

Use dbConnect and odbc to connect to a database, but this time all connection details are provided

con <- dbConnect(odbc(),
                 driver = "postgresql",
                 host = "localhost",
                 user = "rstudio_dev",
                 pwd = "dev_user",
                 port = 5432,
                 database = "postgres",
                 bigint = "integer")

Disconnect using dbDisconnect
```
dbDisconnect(con)
```

3.4 Secure connection details

Use config, keyring, or environment variables to secure connection credentials

Load the config package
```
library(config)
```
Get the current config using the get function and store the results in an object called config
```
config <- get()
```
Use str to investigate the contents of config
```
str(config)
```

Connect using details provided in config

con <- dbConnect(odbc(),
                 driver = config$driver,
                 host = config$host,
                 user = config$user,
                 pwd = config$pwd,
                 port = config$port,
                 database = config$dbname,
                 bigint = config$bigint)

Disconnect using dbDisconnect
```
dbDisconnect(con)
```
Load the keyring package
```
library(keyring)
```
Store the database username and password using keyring. The username is rstudio_dev and the password is dev_user
```
key_set("postgres", "username")
key_set("postgres", "password")
```

Use the stored credentials along with dbConnect to connect to the database

con <- dbConnect(odbc(),
                 driver = "postgresql",
                 host = "localhost",
                 user = key_get("postgres", "username"),
                 pwd = key_get("postgres", "password"),
                 port = 5432,
                 database = "postgres",
                 bigint = "integer")

Discnonnect using dbDisconnect
```
dbDisconnect(con)
```
The .Renviron file contains entries to create environment variables for PG_USER and PG_PWD. These variables can be read using Sys.getenv().
```
Sys.getenv("PG_USER")
```

Connect to the database using the credentials stored in .Renviron and Sys.getenv()

con <- dbConnect(odbc(),
                 driver = "postgresql",
                 host = "localhost",
                 user = Sys.getenv("PG_USER"),
                 pwd = Sys.getenv("PG_PWD"),
                 port = 5432,
                 database = "postgres",
                 bigint = "integer")

Disconnect using dbDisconnect
```
dbDisconnect(con)
```

Store connection details using options()

options(pg_user = "rstudio_dev", pg_pwd = "dev_user")

Connect using the credentials accessed via getOption

con <- dbConnect(odbc(),
                 driver = "postgresql",
                 host = "localhost",
                 user = getOption("pg_user"),
                 pwd = getOption("pg_pwd"),
                 port = 5432,
                 database = "postgres",
                 bigint = "integer")

Disconnect using dbDisconnect
```
dbDisconnect(con)
```

Interactively prompt users for input using rstudioapi::askForPassword()

con <- dbConnect(odbc(),
                 driver = "postgresql",
                 host = "localhost",
                 user = rstudioapi::askForPassword("DB User"),
                 pwd = rstudioapi::askForPassword("DB Password"),
                 port = 5432,
                 database = "postgres",
                 bigint = "integer")

Disconnect using dbDisconnect
```
dbDisconnect(con)
```

knitr::opts_chunk$set(connection = "con", max.print = 5)