Error in dbDriver("PostgreSQL") : could not find function "dbDriver"

Question

I have a shiny-server set up on an Amazon Web Services instance, I am trying to get my app.R onto it but am getting this error:

Error in dbDriver("PostgreSQL") : could not find function "dbDriver"
Calls: runApp ... sourceUTF8 -> eval -> eval -> ..stacktraceon.. -> get_query
Execution halted

I think it has to do with the library install of the package DBI, but I've tried installing it again on the instance and haven't been successful. Not sure what to try next..

Here's the whole image of the error and I can add any other information required:

Also I can confirm that the shiny-server is installed correctly because this page loads normally:

This is how I've tried to install my packages in the instance:

sudo su - -c "R -e \"install.packages(c('shiny', 'shinythemes', 'shinycssloaders', 'dplyr', 'xlsx','ggplot2','ggthemes','DT','stringr','RPostgreSQL','tidyr','dbplyr', DBI','splitstackshape'), repos='http://cran.rstudio.com/')\""

and dbDriver is a function in the DBI package

This is part of what my app.R code contains:

required_packages <- c("shiny", "shinythemes", "shinycssloaders", "dplyr", "xlsx","ggplot2","ggthemes","DT","stringr","RPostgreSQL","tidyr","dbplyr","DBI","splitstackshape"
                       ,"magrittr","tidyverse","shinyjs","data.table","plotly")
absent_packages <- required_packages[!(required_packages %in% installed.packages()[,"Package"])]
if(length(absent_packages)) install.packages(absent_packages)
set.seed(1)

get_query <- function(querystring){
  # create a connection

  # loads the PostgreSQL driver
  drv <- dbDriver("PostgreSQL")
  # creates a connection to the postgres database
  # note that "con" will be used later in each connection to the database
  con <- dbConnect(drv, dbname = "postgres",  host = "/var/run/postgresql", port = 5432, user = "postgres", password = "pw")
  on.exit(dbDisconnect(con))
  #rstudioapi::askForPassword("Database password")

  query <- eval(parse(text = querystring))
  return(query)
}

And these are the tables and connection info to the postgreSQL database on the same instance:

If I add DBI:: in front of dbConnect() and dbDisconnect() and used RPostgres::Postgres() as the driver in the dbConnect function I get this error:

Answer 1

Installing a package does not mean it is loaded into your namespace. Further, the use of dbDriver is deprecated, as shown in ?dbDriver:

These methods are deprecated, please consult the documentation of the individual backends for the construction of driver instances.

I suggest either explicitly loading DBI or using DBI:: with each call to its functions (not a bad idea anyway):

library(DBI)
get_query <- function(querystring){
  # create a connection
  # save the password that we can "hide" it as best as we can by collapsing it

  # creates a connection to the postgres database
  # note that "con" will be used later in each connection to the database
  con <- DBI::dbConnect(RPostgres::Postgres(), dbname = "postgres",  host = "/var/run/postgresql", port = 5432, user = "postgres", password = "pw")
  on.exit(DBI::dbDisconnect(con))
  #rstudioapi::askForPassword("Database password")

  query <- eval(parse(text = querystring))
  return(query)
}

(Again, you don't need to do both library(DBI) and use DBI::, you choose.)

I used RPostgres::Postgres() here, but this applies also to many other drivers, including RPostgreSQL::PostgreSQL(), RSQLite::SQLite(), and rodbc::odbc() (several others exist).

Further points, though I don't know what else you have going on here to be certain:

• making a connection each time you call this function can get "expensive"; consider connecting outside of this function and passing in your con object; if this is a one-or-two-times thing, then you might be alright as-is;
• the use of eval(parse(...)) seems wrong ... executing user-provided queries is flat-out dangerous, look up "SQL Injection" if you are not familiar. Why not just DBI::dbGetQuery(con, querystring)?