3 Introduction to database connections

3.1 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.

3.2 Connecting via DSN

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

  1. Load the DBI and odbc packages

  2. Use odbcListDatasources to list available DSNs

  3. Use dbConnect to connect to a database using the odbc function and a DSN

  4. Disconnect using dbDisconnect

3.3 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

  2. Disconnect using dbDisconnect

3.4 Secure connection details

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

  1. Load the config package

  2. Get the current config using the get function and store the results in an object called config

  3. Use str to investigate the contents of config

  4. Connect using details provided in config

  5. Disconnect using dbDisconnect

  6. Load the keyring package

  7. Store the database username and password using keyring. The username is rstudio_dev and the password is dev_user

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

  9. Discnonnect using dbDisconnect

  10. The .Renviron file contains entries to create environment variables for PG_USER and PG_PWD. These variables can be read using Sys.getenv().

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

  12. Disconnect using dbDisconnect

  13. Store connection details using options()

  14. Connect using the credentials accessed via getOption

  15. Disconnect using dbDisconnect

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

  17. Disconnect using dbDisconnect