Connecting R with Retool databases

Queries and Resources
1

Really happy with Retool, we might start using it company-wise.
Quick question - what is the best way to connect our local R session with a Retool postgresql database? Using libraries such as RPostgreSQL failed (connection timed out with server creds from PGPASSWORD, port 30).
Thanks! :slight_smile:
Nenad

2

Found the solution with library RPostgres https://rpostgres.r-dbi.org/.

library(RPostgres)
library(DBI)
dsn_database = "..." # Specify the name of your Database
dsn_hostname = "..." # Host name such as aws-us-east-1-portal.4.dblayer.com
dsn_port = "5432" # Has to be this
dsn_uid = "..." # Specify your username. e.g. "admin"
dsn_pwd = "..." # Password

tryCatch({
drv <- dbDriver("PostgreSQL")
print("Connecting to Database…")
con <- DBI::dbConnect(RPostgres::Postgres(),
dbname = dsn_database,
host = dsn_hostname,
port = dsn_port,
user = dsn_uid,
password = dsn_pwd,
base::list(sslmode="require", connect_timeout="10"),
service = NULL)
print("Database Connected!")
},
error=function(con) {
print("Unable to connect to Database.")
})

dbListTables(con)

3

Further update for people struggling with connection. If there is an error message such as "Error: ERROR: Endpoint ID is not specified." just fix the following line, while selecting the endpoint of the database (first part of the dsn hostname before the dot, for example aws-us-east-1-portal from the example above) :

base::list(sslmode="require", connect_timeout="10",options="endpoint=aws-us-east-1-portal")