R/SQLConnection.R

In michael-lok/mlokFunctions: mlokFunctions

#' @title SQLConnection
#' @description A SQL connection framework to PostgreSQL databases.
#' @param db Database name as seen in credentials YAML file.
#' @param creds_path Location of YAML file containing credentials. Default is '~/security/creds.yaml'
#' @import R6
#' @export

SQLConnection <- R6Class("mlokConnection", cloneable = FALSE,

  private = list(
    db = NA,
    creds = NA,
    creds_path = '~/security/creds.yaml'),

  public = list(
    conn = NA,

    initialize = function(db, creds_path = private$creds_path){
      private$db = db
      private$creds = yaml.load_file(input = creds_path)[[db]]

      message(sprintf('Connecting to %s...', private$db))
      self$conn = dbConnect(PostgreSQL(),
                            dbname = private$creds$database,
                            host = private$creds$host,
                            port = private$creds$port,
                            user = private$creds$user,
                            password = private$creds$password)
      },

    query = function(query_str){
      results = dbGetQuery(self$conn, query_str)
      message(glue("----- Results: {nrow(results)}"))
      return(results)
      })
)