Connecting to Heroku PostgreSQL Using pgweb Locally

This article introduces how to connect to Heroku’s PostgreSQL database from your local machine using pgweb. pgweb is a browser-based, cross-platform PostgreSQL client.

sosedoff/pgweb: Cross-platform client for PostgreSQL databases

Check Database Information from Heroku

Heroku Data > [Database Details Page] > Settings > View Credentials

Or,

Personal apps | Heroku > [App Details Page] > Overview > Heroku Postgres > [Database Details Page] > Settings > View Credentials

Here you can get information such as host, db name, username, pw, URI, etc.

You should copy the individual information and the URI here.

Windows

For Windows, download pgweb_windows_amd64.exe.zip from Releases · sosedoff/pgweb, extract it, and run the exe. It will tell you to access http://localhost:8081/, so access it from your browser.

Paste the login information copied from Heroku and log in,

You can view table contents in a similar way to DB viewer or MySQL workbench.

If you add it to your PATH, you can access it like pgweb. I create a folder called C:/bin, add it to PATH, and move pgweb_windows_amd64.exe there.

You can also access using the —url option, similar to Linux.

Linux

I used WSL Ubuntu 16.04, but since it doesn’t use a package management system, it should work on any distribution.

curl -s https://api.github.com/repos/sosedoff/pgweb/releases/latest \
| grep linux_amd64.zip \
| grep download \
| cut -d '"' -f 4 \
| wget -qi - \
&& unzip pgweb_linux_amd64.zip \
&& rm pgweb_linux_amd64.zip \
&& sudo mv pgweb_linux_amd64 /usr/local/bin/pgweb

Now, with the URI copied earlier,

yuis@DESKTOP-FOOBAR:/mnt/c/Users/user/vm$ pgweb --url  postgres://kqwssyvvkvkhka:490...
# postgres://[USERNAME]:[PASSWORD]@[HOST]:[PORT]/[DBNAME]

This way, you can connect without having to enter database information.

If you write the following in .bashrc,

alias pgweb_aptnote="pgweb --url postgres://kqwssyvvkvkhka:$(echo $HEROKU_DBPW_APTNOTE)@ec.compute-1.amazonaws.com:5432"

You can log in with just $ pgweb_aptnote.

Afterword

It’s nice that installation isn’t complicated like MySQL workbench. It’s not heavy either. Seems quite good.