Trouble connecting to postgresql DB on Heroku with Korma (Clojure)

Question

I am parsing the postgresql uri in my config settings on Heroku. But I cannot seem to get it working. Any help would be greatly appreciated, I'm probably missing something straight forward.

Here is the code used.

(def dev-db-info
  {:db "dbname"
   :user "username"})

(defn parse-db-uri
  [uri]
  (drop 1 (split uri #"://|:|@|/")))

(defn create-map-from-uri
  [uri]
  (let [parsed (parse-db-uri uri)]
  (zipmap [:user :password :host :port :db] parsed)))

(defn db-info
  []
  (if production?
    (create-map-from-uri (System/getenv "DATABASE_URL"))
    dev-db-info))

(defdb connected-db
   (postgres (db-info)))

The map I retrieve from the uri looks like this:

{:db "dbname"
 :port "5662"
 :host "ec2-url.compute-1.amazonaws.com"
 :password "pwd"
 :user "username"}

I get the following error:

Connections could not be acquired from the underlying database!

EDIT:

I have since given up on using Korma, and switched to using Clojure.JDBC 0.2.3 which supports "connection-uri" and therefore ssl connections to the db. Korma doesn't currently support this. I will file an issue on Github to allow this connection method.

Is there any way to get the error message from the underlying database engine - specifically, the error raised by PgJDBC, if that's what you're using? "connection refused" or "no pg_hba.conf entry for ...", etc. — Craig Ringer, Nov 01 '12 at 11:45
I'll take a look Craig. I think there is a db log in postgres.heroku — conorwade, Nov 01 '12 at 15:38
Here is the log message - FATAL: no pg_hba.conf entry for host "x.x.x.x", user "username", database "dbname", SSL off — conorwade, Nov 01 '12 at 15:51
Hmm. That'd be simple in a non-Heroku setup, but I have *no* idea how Heroku manages `pg_hba.conf`. Maybe that gives you something to start searching for, though. — Craig Ringer, Nov 01 '12 at 22:58
From what I have found it seems to be an issue with connecting without SSL. However I have found nothing with regards SSL connections on Korma! — conorwade, Nov 02 '12 at 14:32
I'd recommend messaging the korma google group: https://groups.google.com/forum/?fromgroups#!forum/sqlkorma — duelin markers, Nov 02 '12 at 21:42
This is an old post but I am just now learning about these connection settings with my current app. You mention that you used jdbc 0.2.3's connection-uri as a solution, but I am a beginner and don't know how to do this. The heroku Postgres docs also don't mention clojure with jdbc. Can you possibly help to get me over this hump. Here is a question I posted on stack a couple of days ago: — kurofune, Apr 07 '14 at 06:06

Carlos · Accepted Answer · 2013-06-20T16:04:35.453

EDIT: There's no reason to use [org.clojars.ccfontes/korma "0.3.0-beta12-pgssl"] anymore. Read this to know more about it. Also, please ignore the following instructions.

Added postgres SSL support.

In project.clj insert: [org.clojars.ccfontes/korma "0.3.0-beta12-pgssl"]

Defining a connection to a postgres database on heroku:

(ns app.db
    (:require [clojure.java.jdbc :as sql]
              [korma.db :as db]
              [clojure.string :as string])
    (:import (java.net URI)))

    (defn set-app-pg-db! [mode]
      (let [db-uri (java.net.URI. (System/getenv "DATABASE_URL"))]
        (->> (string/split (.getUserInfo db-uri) #":")
          (#(identity {:db (last (string/split (System/getenv "DATABASE_URL") #"\/"))
                       :host (.getHost db-uri)
                       :port (.getPort db-uri)
                       :user (% 0)
                       :password (% 1)
                       :ssl true
                       :sslfactory (when (= mode :dev) "org.postgresql.ssl.NonValidatingFactory")}))
          (db/postgres)
          (db/defdb app-pg-db))))

The fix uses Tomcat JDBC Connection Pool and their configuration sample for the connection pool, so it may not be well suited for everyone's needs, plus this is only a hack. Ideally the original Korma project should integrate these changes or other possible solution.

Would appreciate some feedback from other people since it was only tested in my own project. Thanks.

this might be useful if you want a library to parse the database urls: https://github.com/thoughtbot/heroku-database-url-to-jdbc — ackerleytng, Apr 01 '18 at 12:30

score 1 · Answer 2 · answered Jan 31 '13 at 12:31

Actually the solution is really simple and just works locally:

(defn- convert-db-uri [db-uri]
  (let [[_ user password host port db] (re-matches #"postgres://(?:(.+):(.*)@)?([^:]+)(?::(\d+))?/(.+)" db-uri)]
    {
      :user user
      :password password
      :host host
      :port (or port 80)
      :db db
    }))

(def db-spec (postgres
               (convert-db-uri
                (config/get "DATABASE_URL"))))

Where DATABASE_URL is "postgres://user:pw@host:port/dbname?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory"

It seems the db name forwards the SSL parameters to the underlying driver and it just works.

This is with:

[korma "0.3.0-beta9"]
[org.clojure/java.jdbc "0.1.3"]
[postgresql/postgresql "9.1-901.jdbc4"]

`(or port 80)`? Why not `(or port 5432)`? – Brad Koch Jul 28 '13 at 03:23 — Brad Koch, Jul 28 '13 at 03:23

score 1 · Answer 3 · edited Apr 03 '13 at 23:07

In your EDIT you mention switching to clojure.java.jdbc because it allowed you to enable SSL using the connection URI. You can use the same technique with Korma using the function korma.db/defdb which allows you to provide your own connection URL and enable SSL using the query string like this:

(defdb korma-db {:classname "org.postgresql.Driver"
                 :subprotocol "postgresql"
                 :subname "//localhost:5432/test?ssl=true"
                 :user "my-username"
                 :password "my-password"})

score 0 · Answer 4 · answered Nov 02 '12 at 15:07

0

FWIW, here's code I've used to get a clojure.java.jdbc db-spec (which I think is what Korma wants) from Heroku's DATABASE_URL.

(def db-uri (java.net.URI. (System/getenv "DATABASE_URL")))

(def user-and-password (clojure.string/split (.getUserInfo db-uri) #":"))

(def db
  {:classname "org.postgresql.Driver"
   :subprotocol "postgresql"
   :user (get user-and-password 0)
   :password (get user-and-password 1) ; may be nil
   :subname (if (= -1 (.getPort db-uri))
              (format "//%s%s" (.getHost db-uri) (.getPath db-uri))
              (format "//%s:%s%s" (.getHost db-uri) (.getPort db-uri) (.getPath db-uri)))})

answered Nov 02 '12 at 15:07

duelin markers

553
3
14

Thanks for the answer I tried your code, and unfortunately it didn't work. I made some edits for typos eg. line 6 - "def db" is now "defdb dbname". I worked until I made the connection and I got the same error. :( – conorwade Nov 02 '12 at 17:55
Is there maybe an option for adding an SSL connection to Korma, or the underlying JDBC? Argh this is quite frustrating now. I have the whole app ready to go except for this. – conorwade Nov 02 '12 at 17:57

Trouble connecting to postgresql DB on Heroku with Korma (Clojure)

4 Answers4

Linked