Connecting to a remote PostgreSQL Database on Heroku from Clojure

Sometimes it’s useful to be able to debug a local application running against a remote production (or staging) database. The app I’m currently working on is a Clojure app, running on Heroku, using PostgreSQL as a database. It wasn’t entirely obvious (to me) how to do this, but here’s how I did it in the end.

  1. First, this assumes you’re using at least [org.clojure/java.jdbc “0.2.3”] . I thought at first it required later, but 0.2.3 seems to work.
  2. Get your regular Heroku DB URL. This will be of the form ‘postgres://[user]:[password]@[host]:[port]/[database]’
  3. Form a new DB URL as follows (substituting in the tokens from above) : ‘jdbc:postgresql://[host]:[port]/[database]?user=[user]&password=[password]&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory’.
  4. Then for your db connection, use the map ‘{:connection-uri “[new-url]”}’

If I was going to do this frequently it would be easy to create a function to map the original Heroku URL to this remote debugging URL. Assuming you’ve parsed out the server, port, etc., the following gist will work as a basis for this.

(defn remote-heroku-db-spec [host port database username password]
{:connection-uri (str "jdbc:postgresql://" host ":" port "/" database "?user=" username "&password=" password

view raw
hosted with ❤ by GitHub