PostgreSQL in Connection tab

rstudio
postgresql
#1

Firstly - I really like the idea behind the Connections tab. Love it.

Secondly - I can't seem to get it to recognize my connection to PostgreSQL. I am using RPostgreSQL package (and DBI and dbplyr). The connection is working, no problem there, but nothing shows in the connection tab and I do not have the option to selecting PostgreSQL database (just ODBC and Spark).

Many thanks!

#2

The package that provides the connection needs to tell the IDE about the connection in order for it to show up in the tab. RPostgreSQL doesn't do that yet. You have a couple of options:

  1. Ask the package developer to add the RStudio integration. We've documented how to do this here: https://rstudio.github.io/rstudio-extensions/connections-contract.html
  2. Use the odbc package, along with an ODBC PostgreSQL driver, to create the connection; this package has built-in RStudio integration. More about the odbc package here: http://db.rstudio.com/odbc
#3

I think what you need to do is connect to your Postgres via OBDC doing something like this:

db_connect <- function(){
    DBI::dbConnect(odbc::odbc(),
                          Driver       = "PostgreSQL",
                          servername   = "your_server",
                          database     = "dev",
                          UID          = keyringr::get_kc_account('redshift'),
                          PWD          = keyringr::decrypt_kc_pw('redshift'),
                          Port         = 5439)
}

Youll also want to make sure you have the correct odbc drivers installed on your machine in advance. Which you can get more information about here: https://db.rstudio.com/databases/postgresql ... although this page kind of implies that what you are doing sould work, unless i'm missreading it?

#4

Thank you both; I think I will forgo the Connections tab for a while, and stick to RPostgreSQL package, imperfect as it is.

#5

Hi, I've opened Issues on this and the other rstats-db connection packages asking to add the Contract (https://github.com/rstats-db/RPostgres/issues/128). There's a thread right now discussing if the Contract should maybe be implemented in DBI. So please hang in there, support should come in one way or another.

#6

So the same should apply to https://github.com/rstats-db/RMariaDB, right?

(This is such a great feature, I'm anxious to move toward that style of interaction with my database... :slight_smile: )

#7

Correct, that package would also need to implement the RStudio interface linked above if you want to interact with MariaDB via the connections pane.