By
How to Connect to Heroku Postgres from Local
Heroku rocks for easy deployment and maintenance of remote servers. It may cost a bit more than other hosting services, but you gain free minutes and hassle in exchange for paying a little more.
A common question when using Heroku is, "how can I connect to my app's database?"
You may want to do this for a number of reasons:
- You want to query your production database with SQL, and don't want to use the console
- Dataclips are too slow for you, you want to crank on SQL and move quickly
- You want to hook up cool dashboard reporting tools right to your database
- You want to build some jobs to extract data from your database and put it somewhere else
- and more....
Here's a quick tutorial on how to connect to your database on Heroku. In this example, we'll use Postico to demonstrate.
The first step is to grab your database credentials from Heroku. You can do this via two methods.
How To Get Your Heroku Postgres Database Settings and Credentials
The quickest method is right through your app dashboard on Heroku. Go to your app page, from here you can click the "Heroku Postgres" link, that will take you to the database settings page at data.heroku.com. You can also go directly to https://data.heroku.com/ and choose your correct database.
When you land on your Postgres settings page, you'll see something like this:
To view your database credentials, click on the "View Credentials" button. This will reveal your database credentials, such as host, user, port, password, and database name.
Now that you've got your database credentials handy, you're ready to connect from your local development SQL editor.
How To Connect Postico (Or Other SQL Editor) To Your Heroku Database
Ok, we've got our Heroku Postgres database settings and credentials. Now let's connect our local SQL Editor to the database. In this example we're using Postico.
The first step is to show the Postico favorites window.
Click "New Favorite", and you should see a new favorite form open up.
Now enter your Heroku Postgres database settings and credentials and click Connect. If successful, you should see a screen like this:
Boom! You're connected, and in business.
What About Heroku Dataclips?
Heroku offers a wonderful reporting tool called Dataclips. Dataclips let you write SQL right in the browser, and share those reports with others via URL, CSV file, or Google sheets. Here's what it looks like (this is from a cool project we're working on to help people claim unclaimed money owned to them):
SQLBot Makes Connected to Your Herkoku Postgres Dead-Simple
One cool feature we added to Heroku is the ability to connect to your Heroku Postgres database in a click of a button. How? Easy, just create a new connection, and click the "Connect to Heroku" button:
This will bring up a list of your Heroku Postgres databases, just choose one and SQLBot will automatically fill in all of your database credentials for you!
Conclusion
Connecting to your Heroku Postgres database is easy, and helpful when developing locally, troubleshooting, or building reports. Hopefully this guide helps! If you want a dead-easy way to push SQL reports into Slack or Basecamp, give SQLBot a try!
