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.

Answer 1

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.

Answer 2

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"]