Working with databases on Heroku

Published: 2015-08-17

By: MJ Rossetti


The process differs, depending on the database provider (MySQL vs PostgreSQL), and your operating system (Mac OS or Windows OS).


Create Database

heroku addons:create cleardb:ignite

Connect to Database

In the Heroku application management console, find the Heroku config variable called CLEARDB_DATABASE_URL, which will resemble mysql://

In the ClearDB resource management console, navigate to the “Endpoint Information” page and locate Access Credentials: Username and Password.

In the ClearDB resource management console, locate SSL Certificates: ClearDB CA Certificate, Client Certificate, and Client Private Key. Download the files, using the Save File option.

NOTE: If you receive a message like The server returned an invalid certificate. Error 207 (net::ERR_CERT_INVALID)., then try downloading using Firefox or Safari (not Chrome).

Mac OS

Move the certificate files into a subdirectory of your ~/.ssh directory for future reference and usage.

NOTE: For this example we will use the ~/.ssh/gwu-badm-2301/ directory.

mkdir -p ~/.ssh/gwu-badm-2301/
ls -ltr ~/Downloads

cat ~/Downloads/cleardb-ca.pem
cat ~/Downloads/abc123-cert.pem
cat ~/Downloads/abc123-key.pem

mv ~/Downloads/cleardb-ca.pem ~/.ssh/gwu-badm-2301/
mv ~/Downloads/abc123-cert.pem ~/.ssh/gwu-badm-2301/
mv ~/Downloads/abc123-key.pem ~/.ssh/gwu-badm-2301/

ls -la ~/.ssh/gwu-badm-2301/

Modify SSL Key

cd ~/.ssh/gwu-badm-2301/
openssl rsa -in abc123-key.pem -out abc123-key-no-password.pem

Download and install Sequel Pro.

In Sequel Pro, create a new Standard connection using the following credentials:

Attribute Name Attribute Value (or instructions)
Name for personal reference
Username obtain from “Endpoint Information” page
Password obtain from “Endpoint Information” page
Database obtain from CLEARDB_DATABASE_URL
Port 3306 (or leave blank)
Connect using SSL true (check the box)
Key File choose the abc123-key-no-password.pem file
Certificate choose the abc123-cert.pem file
CA Cert choose the cleardb-ca.pem file

PRO-TIP: To avoid re-entering this configuration upon each subsequent connection, save the configuration to favorites, test the connection, and save again before connecting for the first time.

NOTE: To select certificate files, click the key file selector button, check “show hidden files” and navigate to ~/.ssh/gwu-badm-2301/.

Windows OS



Create Database

heroku addons:create heroku-postgresql:hobby-dev

Connect to Database

Obtain database credentials via the command line (heroku pg:credentials DATABASE -a appname) or the Heroku application management console - specifically the Heroku config variable called DATABASE_URL, which will resemble postgres://user_name:password@host_name:5432/database_name.

Mac OS

Download, install, and open pgAdmin or PSequel.

To connect with PSequel: click “Add Connection” then enter the credentials and click “Use SSL”; finally, click “Connect”.

Windows OS


Grant permissions to new database users