Connect to Postgresql

• Add next.jdbc, the Postgres JDBC driver, and HikariCP to your deps.edn.

  JDBC is how most everything on the JVM talks to their databases. next.jdbc is a wrapper of that for Clojure programs.

  All you need to do to be able to use the Postgres driver is to have it in your dependencies.

  HikariCP is a connection pooling library. You want this so that you don't need to establish a totally fresh connection on every database query.

  {:paths ["src"]
   :deps {org.clojure/clojure {:mvn/version "1.12.0"}
          ring/ring {:mvn/version "1.13.0"}
          metosin/reitit-ring {:mvn/version "0.7.2"}
          org.clojure/tools.logging {:mvn/version "1.3.0"}
          org.slf4j/slf4j-simple {:mvn/version "2.0.16"}
          hiccup/hiccup {:mvn/version "2.0.0-RC3"}
          com.github.seancorfield/next.jdbc {:mvn/version "1.3.955"}
          org.postgresql/postgresql {:mvn/version "42.7.4"}
          com.zaxxer/HikariCP {:mvn/version "6.0.0"}}
   :aliases {:dev {:extra-paths ["dev" "test"]
                   :extra-deps {nrepl/nrepl {:mvn/version "1.3.0"}
                                lambdaisland/kaocha {:mvn/version "1.91.1392"}}}
             :format {:deps {dev.weavejester/cljfmt {:mvn/version "0.13.0"}}}
             :lint   {:deps {clj-kondo/clj-kondo {:mvn/version "2024.09.27"}}}}}

• Add code to connect to Postgres into your system namespace.

  This should include the code to start and stop the connections as well as the code to attach the "db" to the system

  (ns example.system
    (:require [example.routes :as routes]
              [next.jdbc.connection :as connection]
              [ring.adapter.jetty :as jetty])
    (:import (com.zaxxer.hikari HikariDataSource)
             (org.eclipse.jetty.server Server)))
  
  (set! *warn-on-reflection* true)
  
  (defn start-db
    []
    (connection/->pool HikariDataSource
                       {:dbtype "postgres"
                        :dbname "postgres"
                        :username "postgres"
                        :password "postgres"}))
  
  (defn stop-db
    [db]
    (HikariDataSource/.close db))
  
  (defn start-server
    [system]
    (jetty/run-jetty
     (partial #'routes/root-handler system)
     {:port 9999
      :join? false}))
  
  (defn stop-server
    [server]
    (Server/.stop server))
  
  (defn start-system
    []
    (let [system-so-far {::db (start-db)}]
      (merge system-so-far {::server (start-server system-so-far)})))
  
  (defn stop-system
    [system]
    (stop-server (::server system))
    (stop-db (::db system)))

• Query the database in one of your handlers

  To get access to parts of the system, we use an :as-alias require in our routes along with destructuring. If we didn't use :as-alias then there would be circular namespace dependencies, which is no good. Not having the alias at all would be cumbersome as well.

  (ns example.routes
    (:require [clojure.tools.logging :as log]
              [example.system :as-alias system]
              [hiccup2.core :as hiccup]
              [next.jdbc :as jdbc]
              [reitit.ring :as reitit-ring]))
  
  (defn hello-handler
    [{::system/keys [db]} _request]
    (let [{:keys [planet]} (jdbc/execute-one!
                            db
                            ["SELECT 'earth' as planet"])]
      {:status 200
       :headers {"Content-Type" "text/html"}
       :body (str
              (hiccup/html
               [:html
                [:body
                 [:h1 (str "Hello, " planet)]]]))}))
  
  ...
  

• Verify that connecting to the database works.

  Visit the route you added database logic to. See that the query does get executed.

• Add a db function to your user namespace

  This way, when you are in the REPL you can write (user/db) to get access to your database for making ad-hoc queries.

  (ns user
    (:require [example.system :as system]))
  
  (def system nil)
  
  (defn start-system!
    []
    (if system
      (println "Already Started")
      (alter-var-root #'system (constantly (system/start-system)))))
  
  (defn stop-system!
    []
    (when system
      (system/stop-system system)
      (alter-var-root #'system (constantly nil))))
  
  (defn restart-system!
    []
    (stop-system!)
    (start-system!))
  
  (defn server
    []
    (::system/server system))
  
  (defn db
    []
    (::system/db system))