Connect to PostgreSQL on Heroku Using ODBC and JDBC Drivers

Introduction

So, you have an app hosted on Heroku that uses Postgres as a database and you are looking for a way to connect to it? Well, you are in luck, this tutorial will walk you through how to connect to your PostgreSQL database on Heroku using standards based connectivity (ODBC and JDBC) from Progress DataDirect. Let’s get started.

Get Your Database URL from Heroku

You can get your Database URL using either Heroku CLI or from your Heroku dashboard. 

 Using Heroku CLI

  1. If you have Heroku CLI installed on your machine, then open your terminal/command prompt and run the following command

     

    heroku config:get DATABASE_URL -a <your_heroku_app_name>

     

  2. When you run the above command, you will get your database URL in the following format.

postgres://<username>:<password>@<hostname/server>/<databasename>

Using Heroku Dashboard

  1. If you don’t have the Heroku CLI installed, no worries, you can still get your connection info by logging in to your Heroku Dashboard.
  2. Go to the Heroku application that you intend to connect to, click on the settings tab and in Config Variables section, and click on the button Reveal Config Vars as shown in the below screenshot.

     

    Reveal Config Vars

     

  3. You should now see the list of configuration variables and copy the value of the key DATABASE_URL.  It will be in the format of

     

    postgres://<username>:<password>@<hostname/server>/<databasename>

     

  4. Note that this is not a recommended way of getting the database URL, if you want to use this in your application development, you should use the Heroku CLI for that. This is just a way for making it easy for users that are trying it out for the first time.

Using DataDirect PostgreSQL ODBC Driver

Download and Install

  1. Download PostgreSQL ODBC driver from here.
  2. Once the package is downloaded, unzip the package and run the Setup.exe.
  3. The installation process will be simple, just follow the instructions. Most of the time the default settings would be sufficient to install the driver successfully.

Configure and Test Connection

  1. Open ODBC Administrator (32 bit) if you have installed 32-bit driver or ODBC Administrator (64 bit) if you have installed 64-bit driver installed.
  2. Under User DSN tab, click on Add and choose DataDirect 7.1 PostgreSQL Wire Protocol as your driver as shown below.

     

    Create New Data Source

     

  3. On the Configuration form, Under General tab fill in the Host Name, Port and Database that you got from Heroku configuration value for DATABASE_URL as shown below.

     

    Wire Protocol Driver Setup

     

  4. Go to Security Tab, select Encryption Method as 1-SSL and uncheck Validate Server Certificate as shown below.

    NOTE: Disabling server certificate validation opens a serious security hole by allowing man in the middle attacks.  This is likely acceptable for test environments, but it is highly recommended that a server certificate issued by a recognized certificate authority be used in production environments.

     

    Security Setup

     

  5. Click on Test Connect, you should see a new prompt for credentials. Fill in the Username and password that you got from Heroku and click on OK. You should be able to connect to PostgreSQL on Heroku successfully.

Using DataDirect PostgreSQL JDBC Driver

Download and Install

  1. Download PostgreSQL ODBC driver from here.
  2. Once the package is downloaded, unzip the package and run the setup.exe.
  3. The installation process will be simple, just follow the instructions. Most of the time the default settings would be sufficient to install the driver successfully.

Configure and Test Connection

  1. To configure and connect to PostgreSQL on Heroku using the DataDirect JDBC driver, we will be using SQL Workbench
  2. Open SQL Workbench and go to File -> Connect Window, which will open a new window. On the bottom left of that window you will find a button named ‘Manage Drivers’. Click on it.
  3. Add a new driver by clicking on the new button. Give the name as ‘DataDirect PostgreSQL’ and browse the path to postgresql.jar which will be in lib folder of installed directory as shown below. Click on OK once you are finished.

     

    Manage Drivers

     

  4. You should be back on the Connect window. Create new connection, give any name to it, choose DataDirect PostgreSQL(com.ddtek.jdbc.postgresql.PostgreSQLDriver) as your driver.
  5. Fill in the URL for connection in the following format using the values from DATABASE_URL that you got from Heroku and credentials in respective fields as shown below.

     

    jdbc:datadirect:postgresql://<;Host name>:<port>;Database=<databasename>;EncryptionMethod=SSL;ValidateServerCertificate=false;

     

    Select Connection Profile

     

    NOTE: Disabling server certificate validation opens a serious security hole by allowing man in the middle attacks.  This is likely acceptable for test environments, but it is highly recommended that a server certificate issued by a recognized certificate authority be used in production environments.

     

  6. Click on Test button and you should be able to connect successfully. Click on OK and you should now be able to query your Heroku PostgreSQL database without any problem.

Next Steps

We hope this tutorial helped you to get connected to your PostgreSQL database on Heroku. If you are looking to access your PostgreSQL database using a REST API (OData), you can check out Progress DataDirect Cloud. This enables you to access your database through a standards based REST API – OData. Try it for free!

JDBC, ODBC TUTORIAL

Connect to PostgreSQL on Heroku Using ODBC and JDBC Drivers

DataDirect
connectors

Connect any data source to any application

DataDirect
Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers