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.

Susian

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. – Celestecelestia

I'll take a look Craig. I think there is a db log in postgres.heroku – Susian

Here is the log message - FATAL: no pg_hba.conf entry for host "x.x.x.x", user "username", database "dbname", SSL off – Susian

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. – Celestecelestia

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! – Susian

I'd recommend messaging the korma google group: groups.google.com/forum/?fromgroups#!forum/sqlkorma – Rockandroll

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: <#22882132> – Febri

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.

Fervency

this might be useful if you want a library to parse the database urls: github.com/thoughtbot/heroku-database-url-to-jdbc – Lapland

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

Kerbing

(or port 80)? Why not (or port 5432)? – Eft

Answer 3

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"})

Overdraw

Answer 4

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)))})

Rockandroll

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. :( – Susian

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. – Susian