Using next.jdbc with a Connection pool

As the scale of database use increases it becomes more efficient to continually re-use existing connections to the database, rather than create a new connection to execute each SQL statement.

A connection pool is a set of open connections that are used over and over again, enhancing the performance of the database and allowing the database to scale more efficiently.

Databases may provide their own connection pool (postgres has ..., h2 has ...). Hikari and c3p0 are commonly used database connection pool libraries

Configure next.jdbc with a connection pool

• Add connection pool library (question: if using a db connection pool, is this just the driver?)
• Require next.jdbc and next.jdbc.connection in the Clojure namespace where the connection pool will be used

{% tabs hikari="hikari", c3p0="C3P0", h2="H2 database", postgresql="PostgreSQL database" %}

{% content "hikari" %}

(ns my.main
  (:require
    [next.jdbc :as jdbc]
    [next.jdbc.connection :as connection])

  (:import
    (com.zaxxer.hikari HikariDataSource)))

Create a database specification

HikariCP requires :username instead of :user in the db-spec

(def ^:private db-spec {:dbtype "..." :dbname "..." :username "..." :password "..."})

When using a JDBC URL with a connection pool, use :jdbcUrl in the database spec instead of :dbtype, :dbname, etc)

{% content "c3p0" %}

(ns my.main
  (:require
    [next.jdbc :as jdbc]
    [next.jdbc.connection :as connection])

  (:import
    (com.mchange.v2.c3p0 ComboPooledDataSource PooledDataSource)))

{% content "h2" %}

{% endtabs %}

Execute with a connection pools

next.jdbc.connection/->pool takes a connection pool (Java Class) and a database specification.

(with-open [^HikariDataSource ds (connection/->pool HikariDataSource db-spec)]
    (jdbc/execute! ds ...)
    (jdbc/execute! ds ...)
    (do-other-stuff ds args)
    (into [] (map :column) (jdbc/plan ds ...)))

Configure next.jdbc with lifecycle management libraries

A connection pool has a start/stop lifecycle, so fits easily into lifecycle management libraries such as mount, component and integrant.

Start the database server connection pool

Assumes database is a separate service already running. Add a check for the status of the database before starting the components?

{% tabs mount="Mount", component="Component", integrant="Integrant" %}

{% content "mount" %}

{% content "component" %} next.jdbc.connection/component supports Component directly by creating a Component-compatible entity.

Example code from next.jdbc.connection/component

(component/start (connection/component HikariDataSource db-spec))

(ns practicalli.application
  (:require
    [com.stuartsierra.component :as component]
    [next.jdbc :as jdbc]
    [next.jdbc.connection :as connection])

  (:import
    (com.zaxxer.hikari HikariDataSource)))

(def ^:private db-spec {:dbtype "..." :dbname "..." :username "..." :password "..."})

(defn -main [& args]
  ;; connection/component takes the same arguments as connection/->pool:
  (let [ds (component/start (connection/component HikariDataSource db-spec))]
    (try
      ;; "invoke" the data source component to get the javax.sql.DataSource:
      (jdbc/execute! (ds) ...)
      (jdbc/execute! (ds) ...)
      ;; can pass the data source component around other code:
      (do-other-stuff ds args)
      (into [] (map :column) (jdbc/plan (ds) ...))
      (finally
        ;; stopping the component will close the connection pool:
        (component/stop ds)))))

{% content "integrant" %}

{% endtabs %}