Database connection (pooling) in component

[rrraaafff]

Hello,

I’m quite new to Stuart Sierra components and how properly work with them. In my project I’ve created database component that looks like this:

(defrecord Database [connection]
  component/Lifecycle
  (start [component]
    (migrate)
    (let [conn (or connection (jdbc/get-connection (env :database-url)))]
      (assoc component :connection conn)))
  (stop [component]
    (when-let [conn (:connection component)]
      (.close conn))
    (assoc component :connection nil)))

I have two worries (or misunderstandings) about such component.

1. If the db connection is created on start and close on stop, does it mean that during life of my web application (it can be quite long) I have always one connection opened? What would happen when two separate threats would use such connection?
2. Could you suggest me how could I rewrite this code to add a db connection pooling? For your info, I use Postgresql db.

I appreciate for any help. Thanks in advance.

regards

[jan]

@rrraaafff

2. Could you suggest me how could I rewrite this code to add a db connection pooling? For your info, I use Postgresql db.

You can use a Clojure wrapper for HikariCP, a JDBC connection pool. The off-the-shelf component available in org.danielsz/system can help you save a bit of boilerplate code.

(require '[system.components.hikari :as hikari])
(hikari/new-hikari-cp {:url (env :database-url)})

[rrraaafff]

Looks very good - thank you for mentioning it. Looks like solid solution that I can use out of the box.

Thanks a lot!

[Webdev_Tory]

I use Mount instead of Component, but that Hikari-CP solution is the same solution I use. Much better with PostGres than MySQL, btw. MySQL has proven pretty flaky and tough to work with for me so far.

[rrraaafff]

I was able to use Hikari-CP with HugSQL (using Component) without any real problems. It’s a nice combination.

[borkdude]

From an app I’m currently working on that uses conman (https://github.com/luminus-framework/conman):

(defrecord DatabaseComponent [connection pool-spec]
  com.stuartsierra.component/Lifecycle
  (com.stuartsierra.component/start
    [component]
    (if (:connection component)
      component
      (merge component (assoc (conman/connect! pool-spec)
                              :connection conn))))
  (com.stuartsierra.component/stop
    [component]
    (if-let [connection (:connection component)]
      (do (conman/disconnect! connection)
          (clojure.core/merge component {:connection nil}))
      component)))

(defn new-database
  ([cfg]
   (map->DatabaseComponent
    (let [db (get-in cfg [... :db])
          db (assoc db :jdbc-url (:jdbc db))]
      {:pool-spec db}))))

Note: untested - I had to reverse engineer this from a macro-expansion, since we use macros to make writing ss/components easier.