Connecting to postgresql DB on Heroku #101

Closed
ccfontes opened this Issue Dec 14, 2012 · 21 comments

Projects

None yet

9 participants

Hi

I tried my best to connect to postgresql database without any success, so I came up with a fix, which is using Tomcat JDBC Connection Pool for the case of postgresql connections, as mentioned here:
http://stackoverflow.com/questions/13175655/trouble-connecting-to-postgresql-db-on-heroku-with-korma-clojure

Where to find the fix:
clojars: [org.clojars.ccfontes/korma "0.3.0-beta12-pgssl"]
source: https://github.com/ccfontes/Korma

It's working in my project.

It looks something like this, with main configuration from Tomcat JDBC website. (Should I make a pull request?)

(defmethod connection-pool "postgresql" [{:keys [subprotocol subname classname user password ssl sslfactory]}]
  (let [properties (doto (PoolProperties.)
                    (.setUrl (str "jdbc:" subprotocol ":" subname))
                    (.setDriverClassName classname)
                    (.setUsername user)
                    (.setPassword password)
                    (.setJmxEnabled true)
                    (.setTestWhileIdle false)
                    (.setTestOnBorrow true)
                    (.setValidationQuery "SELECT 1")
                    (.setTestOnReturn false)
                    (.setValidationInterval 30000)
                    (.setTimeBetweenEvictionRunsMillis 30000)
                    (.setMaxActive 100)
                    (.setInitialSize 10)
                    (.setMaxWait 10000)
                    (.setRemoveAbandonedTimeout 60)
                    (.setMinEvictableIdleTimeMillis 30000)
                    (.setMinIdle 10)
                    (.setLogAbandoned true)
                    (.setRemoveAbandoned true)
                    (.setJdbcInterceptors (str "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
                                               "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"))
                    (.setConnectionProperties  (str (if ssl "ssl=true;" "")
                                                    (if sslfactory (str "sslfactory=" sslfactory ";") ""))))
       cpds (doto (DataSource.)
              (.setPoolProperties properties))]
    {:datasource cpds}))
Member

Proffering a vote for this to be fixed, since baranosky asked for votes. Mostly out of an interest to make Korma a "just works" experience.

Throwing on another few votes (transitively via Heroku support requests I've received) to allow connection URLs as strings to be passed on to clojure.java.jdbc unmolested.

It occurs to me that fixing #64 will probably fix this.

Owner

korma-0.3.0-RC1 includes changes to help with this. Your feedback is needed to help make the API ideal for the most people.

how do I test it?

Maybe Korma should use clj-dbcp for pooling? It supports parsing Heroku-style URLs. I don't think c3p0 is that much better than Apache DBCP…

Or just take the parse-url function from clj-dbcp…

There is no problem parsing the URLs. There is an example here about how to parse it using java.net.URI.
The problem was that org.postgresql.ssl.NonValidatingFactory which is present in heroku URLs is not supported in c3p0.
If I knew about clj-dbcp before, wouldn't have rolled my own.

This issue was solved in some way since korma-0.3.0-RC1 (without changing the pooler), so I'm waiting for an answer on how to use it.

I used this StackOverflow example too… I mean, parsing URLs should be included in Korma.

org.postgresql.ssl.NonValidatingFactory

I didn't see it in Heroku URLs – maybe it's only in production plans, not the developer plan?

when using the DB outside the heroku network, sslfactory=org.postgresql.ssl.NonValidatingFactory must be added to the URL. I use it from my development machine.

@myfreeweb sorry, I forgot to mention the problem was for SSL connections in general, not just the sslfactory=org.postgresql.ssl.NonValidatingFactory parameter.
How did you solve it for your heroku DB? I'm still stuck with my own fork.

Okay, I figured it out.

clj-dbcp didn't pass the query string from your URL to the JDBC URL, here's my pull request that fixes it. Probably Korma's wrapper for C3P0 has the same problem.
I also made a wrapper for BoneCP that works fine.

I think the C3P0 wrapper should be extracted from Korma and Korma should not depend on any pooling library. Let users choose between DBCP, C3P0 and BoneCP.

or just switch to BoneCP ;-)

@myfreeweb I tried initially to do it with C3P0, since it's shipped along with korma, but it seemed not to support the SSL parameters (can someone confirm this?), that's why I tried Apache DBCP.
By the way, thanks for the fix to clj-dbcp. Are you using your own fork of korma with clj-dbcp or clj-bonecp-url?

I don't have a fork of korma!

I'm using clj-bonecp-url with original korma.

On Feb 22, 2013, at 2:41 AM, Carlos Cunha notifications@github.com wrote:

@myfreeweb I tried initially to do it with C3P0, since it's shipped along with korma, but it seemed not to support the SSL parameters (can someone confirm this?), that's why I tried Apache DBCP.
By the way, thanks for the fix to clj-dbcp. Are you using your own fork of korma with clj-dbcp or clj-bonecp-url?


Reply to this email directly or view it on GitHub.

Owner

@ccfontes have you tried the Korma-0.3.0-RC4 version that allows you to specify your own pooling library? (check out korma.db) I need more people trying it and liking it before I can release korma-0.3.0 with confidence.

@AlexBaranosky what are you talking about?!

It was possible to use any pooling library (any DataSource) even before RCs.

(when (nil? @korma.db/_default)
  (korma.db/default-connection {:pool {:datasource datasource}}))

RC4 and even the current master on github still has a dependency on c3p0 and explicitly imports it in korma.db so I can't exclude it. As a result, I have 611 KB of code I don't use in my projects.

so is it fixed? How to connect to heroku database using korma?

Finally got a chance to upgrade korma and test clj-bonecp-url and it works like a charm!
@myfreeweb, thank you for your hard work and also a big thanks to all korma developers! Btw, something about my db looks really fast now..

@podviaznikov, just follow instructions in https://github.com/myfreeweb/clj-bonecp-url, notice that with this configuration you can't [korma "0.3.0-RC5" :exclusions [c3p0]], cause @myfreeweb changes weren't merged yet - #127. You can use his fork for the meantime: [org.clojars.myfreeweb/korma "0.3.0-RC4" :exclusions [c3p0]], if you opt for BoneCP e.g., and don't want to bring c3p0 along. Drop here a question if you are still running into problems.

@AlexBaranosky dependency combination that worked using another pooling library: [korma "0.3.0-RC5"] / [org.clojars.myfreeweb/korma "0.3.0-RC4" :exclusions [c3p0]] and [clj-bonecp-url "0.1.0"].

I've send pull request #227
With this we can use like this:

  (defdb db (postgres {:host host
                          :port port
                          :db database
                          :user username
                          :password password
                          :props {:ssl true
                                  :sslfactory "org.postgresql.ssl.NonValidatingFactory"}})))

And heroku connection will work.

I'm having issues myself with 0.4.0 on Heroku, and can't find any specific documentation as to how was this fixed. Any pointers?

Member
immoh commented Oct 26, 2014

It is not clear to me if there's an issue with connection pool library (c3p0) or is it just the lack of support for properties in postgres helper function?

If it is the latter, you can always provide subname with url:

(postgres {:user "user" :pass "pass" :subname "//host:port/db?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory"})

Note that this will overwrite default subname created by Korma and you will need to include host, port and db in the subname string.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment