How to connect shiny apps in shiny io with postgresql of my computer?

shiny
1 
library(shiny)
library(config)
library(odbc)
library(DBI)

conn_args <- config::get("dataconnection", config = Sys.getenv("R_CONFIG_ACTIVE", "default"))

ui = fluidPage(
)

server = function(input,output,session){
  con = DBI::dbConnect(odbc::odbc(), driver = conf$driver, server = conf$server,
                         database = conf$database, UID = conf$uid, 
                         PWD = conf$pwd, port = conf$port)
}
shinyApp(ui, server)

with: config.yml...

default:
  dataconnection:
    driver: 'PostgreSQL ODBC Driver(ANSI)'
    server: '127.0.0.1'
    uid: 'postgres'
    pwd: 'postgres'
    port: 5432
    database: 'arede'
    
rsconnect:
  dataconnection:
    driver: 'PostgreSQL'
    server: 'myprodserver.company.com'
    uid: 'prod-account'
    pwd: 'prod-password'
    port: 5432
    database: 'prod-database'
2

Hi Santiago,
You have a couple of options for connecting your shiny app on shinyapps.io to a database.

Regarding connecting to a remote database in a shiny app, DEAN ATTALI wrote a nice article here, Persistent data storage in Shiny apps, check out the MySQL section.

The steps to connect your app to your PostgreSQL database won't be identical to Dean's approach, but odbc or RPostgreSQL packages will help. Here's some documentation for connecting to a PostgreSQL database.

3

Check out the Accessing Databases with odbc section of the shinyapps.io documentation.

When using the config package, you will want a section named "shinyapps", not "rsconnect".

5

HI EconomiCurtis,
Thanks for your answer. These steps were previously tested without success, using the config library. My idea is to buy a shinyapps.io license but without this solution I will not be able to opt for this license, since all my data is in the database, PostgreSQL.

My idea by attaching the code is to know where the possible error is.

The support of shinyapps.io could solve this doubt?

I appreciate your time and response,

6

I am unclear which steps you are referring to as being previously tested, but your config.yml does not contain a section "shinyapps", which is what R_CONFIG_ACTIVE is set to when running on shinyapps.io.

You then need to ensure that your database is accessible from the shinyapps.io servers, per the Firewalls sub-section of the documentation.

If you have tried this and it is still not working, please include the error you are experiencing.

2 Likes
7

Hi Josh,

Try again and create shinyapps in the configyml file, and continue giving error. Check in shinyapps io the logs and in the compilation of the program I get this error:

nanodbc / nanodbc.cpp: 950: 01000: [unixODBC] [Driver Manager] Can not open lib 'PostgreSQL ODBC Driver (ANSI)': file not found

Only the driver exists on my pc with that name.

config.yml:

´´´{r}
default:
dataconnection:
driver: 'PostgreSQL'
server: '127.0.0.1'
uid: 'postgres'
pwd: 'postgres'
port: 5432
database: 'arede'

shinyapps:
dataconnection:
driver: 'PostgreSQL ODBC Driver(ANSI)'
server: '127.0.0.1'
uid: 'postgres'
pwd: 'postgres'
port: 5432
database: 'arede'
´´´

8

The documentation enumerates the available driver names.

The server cannot be '127.0.0.1', as that refers to the host on which the application is running, which, in the case of deploying it to shinyapps.io, does not have your database running. You need to set it to a publicly available IP address or hostname that refers to where it is running.

1 Like