Postgresql DB connection failure on Heroku with Korma (Clojure)

Question

I am parsing postgresql uri in configuration settings on Heroku. But I can’t make it work. Any help would be greatly appreciated, I will probably miss something direct.

The code is used here.

(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 am retrieving from uri is as follows:

 {: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:

Since then I have refused to use Korma and switched to using Clojure.JDBC 0.2.3, which supports "connection-uri" and, therefore, ssl connections to db. Feed currently does not support this. I will post a problem on Github to resolve this connection method.

Answer 1

EDIT: There is no reason to use [org.clojars.ccfontes/korma "0.3.0-beta12-pgssl"] anymore. Read this one to find out more about this. In addition, please ignore the following instructions.

Added support for postgres SSL .

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

Defining a connection to the 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 the Tomcat JDBC Connection Pool and their sample configuration for the connection pool, so it may not be suitable for all needs, plus this is just a hack. Ideally, the original Korma project should integrate these changes or another possible solution.

I would like some feedback from other people, as it was checked only 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 redirects the SSL options to the base driver, and it just works.

This is using:

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

Answer 3

In your EDIT, you mentioned switching to clojure.java.jdbc, as it allowed you to enable SSL using the connection URI. You can use the same technique with Korma using the korma.db / defdb function, which allows you to specify your own connection URL and enable SSL using the query string as follows:

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

Answer 4

FWIW, here is the code I used to get clojure.java.jdbc db-spec (which I think is what Feed wants) from Heroku 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)))})