Lab 3: Configuring Database Access for your Application

Lab: Database Connection with Pro Drivers

In this lab, you will:

  • Install Posit Professional Drivers for PostgreSQL
  • Configure a system-wide DSN
  • Test the connection from R and Python
  • Deploy content that uses the DSN

The goal of this lab is to deploy a Shiny app that connects to a PostgreSQL database using a system DSN configured on the Connect server. The app visualizes synthetic on-time flight performance data.

The database server is hosted on localhost, the database is named ontime, and the connection uses the following credentials: username posit, password password. The Shiny application uses a DSN named ontime_db to connect to the database.

  1. Using the instructions found in the Pro Drivers Installation guide, install the Posit Professional ODBC Drivers on your Posit Connect server. Make sure to follow the instructions to create the /etc/odbcinst.ini file which contains the driver names and locations.

  2. Install odbcinst with sudo apt install odbcinst to check that the drivers for PostgreSQL have been installed correctly by running the following commands that list the names of the installed drivers (you should see [PostgreSQL] in the output). This step is optional but recommended to verify the installation.

    odbcinst -q -d

  3. Create a system DSN named ontime_db using the PostgreSQL driver, by adding the database connection information provided above to the file /etc/odbc.ini

    /etc/odbc.ini
    [ontime_db]
Driver = PostgreSQL
Server = localhost
Port = 5432
Database = ontime
UID = posit
PWD = password

  4. Go to the web interface of your Posit Connect server, log in as ashley (username and password are “ashley”), and click on the “Publish” button. Select “Import from Git”, use: https://github.com/fmichonneau/ontime_demo for the URL. Choose “main” for the branch, click on “Next”. Keep “app” as the directory containing the manifest.json file, enter a title for this application (such as “On Time App”) and click “Deploy Content”. This typically takes 3-5 minutes to build and deploy the application.

  5. Once the deployment is successful, if you configured the DSN correctly, you should be able to launch the Shiny application and see the data visualizations.