Whispering to the Elephant

And fixing Database Connection Error

You know what's nice? When you're creating a user account and then you restart the server and it stays.

Let's get us some of that.

Step 1 - New deps

By now you know the drill, we've got some deps to add, so let's open up deps.edn, and pop this lot in:

seancorfield/next.jdbc              {:mvn/version "1.0.409"}
honeysql                            {:mvn/version "0.9.10"}
nilenso/honeysql-postgres           {:mvn/version "0.2.6"}
org.postgresql/postgresql           {:mvn/version "42.2.12"}
hikari-cp                           {:mvn/version "2.11.0"}
buddy/buddy-sign                    {:mvn/version "3.1.0"}
buddy/buddy-hashers                 {:mvn/version "1.4.0"}

Step 2 - Migrations and Structure

Now we want to create a migration, let's call it migrations/v1__create-user-table.sql:

CREATE TABLE account (
     id SERIAL PRIMARY KEY,
     email    text NOT NULL UNIQUE,
     password text NOT NULL,
     created_at timestamp with time zone DEFAULT now() NOT NULL,
     updated_at timestamp with time zone DEFAULT now() NOT NULL
);

Step 3 - Wrapping the database connections

Now we need to have some layer to talk to our db:

(ns app.model.database
  (:require [app.server-components.config :refer [config]]
            [mount.core :refer [defstate]]
            [hikari-cp.core :as pool]
            [next.jdbc :as jdbc]
            [next.jdbc.result-set :as result-set]
            [next.jdbc.prepare :as p]
            [clojure.string :as str]
            [honeysql.core :as sql]
            [honeysql-postgres.format]))

(defn datasource-options []
  (merge {:auto-commit        true
          :read-only          false
          :connection-timeout 30000
          :validation-timeout 5000
          :idle-timeout       600000
          :max-lifetime       1800000
          :minimum-idle       10
          :maximum-pool-size  10
          :pool-name          "db-pool"
          :adapter            "postgresql"
          :register-mbeans    false}
    (:database-spec config)))

(defstate pool
  :start (pool/make-datasource (datasource-options))
  :stop (pool/close-datasource pool))

(defn snake->kebab [s]
  (str/replace s #"_" "-"))

(defn as-qualified-kebab-maps [rs opts]
  (result-set/as-modified-maps rs
    (assoc opts
      :qualifier-fn snake->kebab
      :label-fn snake->kebab)))

(def ^:private query-opts {:builder-fn as-qualified-kebab-maps})

(defn execute! [conn sql-map]
  (jdbc/execute! conn
    (sql/format sql-map :quoting :ansi)
    query-opts))

(defn execute-one! [conn sql-map]
  (jdbc/execute-one! conn
    (sql/format sql-map :quoting :ansi)
    query-opts))

Step 4 - Querying

So pathom, our graphql-like query parser needs to know about Postgres, so in app.server-components.pathom:

(ns app.server-components.pathom
  (:require
    [mount.core :refer [defstate]]
    ...
    [app.model.database :as pgdb]))

...

(assoc env
  ::pgdb/pool pgdb/pool ; <-- We add this
  :db @db-connection
  ...)

Step 5 - Mutations

Now let's adjust our app.model.session:

(ns app.model.session
  (:require
;;  [app.model.mock-database :as db]  ; <-- DELETE
    [app.model.database :as db]       ; <-- INSERT
    [datascript.core :as d]
    [com.fulcrologic.guardrails.core :refer [>defn => | ?]]
    [com.wsscode.pathom.connect :as pc :refer [defresolver defmutation]]
    [taoensso.timbre :as log]
    [clojure.spec.alpha :as s]
    [buddy.hashers :as hs]            ; <-- Also add
    [com.fulcrologic.fulcro.server.api-middleware :as fmw]))

Now we need to pass the db and queries to our mutations, so login goes from this:

(defmutation login [env {:keys [username password]}]
  {::pc/output [:session/valid? :account/name]}
  (log/info "Authenticating" username)
  (let [{expected-email    :email
         expected-password :password} (get @account-database username)]
    (if (and (= username expected-email) (= password expected-password))
      (response-updating-session env
        {:session/valid? true
         :account/name   username})
      (do
        (log/error "Invalid credentials supplied for" username)
        (throw (ex-info "Invalid credentials" {:username username}))))))

to this:

(defmutation login [{::db/keys [pool] :as env} {:keys [username password]}]
  {::pc/output [:session/valid? :account/name]}
  (log/info "Authenticating" username)
  (let [user (db/execute-one! pool
                              {:select [:email :password]
                               :from   [:account]
                               :where  [:= :email username]})
        {expected-email    :account/email
         expected-password :account/password} user]
    (if (and (= username expected-email) (hs/check password expected-password))
      (response-updating-session env
        {:session/valid? true
         :account/name   username})
      (do
        (log/error "Invalid credentials supplied for" username)
        (throw (ex-info "Invalid credentials" {:username username}))))))

and signup! from this:

(defmutation signup! [env {:keys [email password]}]
  {::pc/output [:signup/result]}
  (swap! account-database assoc email {:email    email
                                       :password password})
  {:signup/result "OK"})

to this:

(defmutation signup! [env {:keys [email password]}]
  {::pc/output [:signup/result]}
  (log/info "Signing Up" email)
  (let [hashed-password (hs/derive password)]
    (db/execute-one! pool
                     {:insert-into :account
                      :values [{:email email
                                :password hashed-password}]
                      :returning [:id]})
  {:signup/result "OK"}))

Step 6 - Config and Env vars

Finally let's adjust our src/main/config/dev.edn:

{:taoensso.timbre/logging-config {:level :debug}
 :database-spec {:username      "postgres"
                 :password      "password"
                 :server-name   "localhost"
                 :port-number   5432
                 :database-name "postgres"
                 :sslmode       "disable"}}

and our src/main/config/prod.edn:

{
...
:database-spec {:username      :env/POSTGRES_USER
                :password      :env/POSTGRES_PASSWORD
                :server-name   :env/POSTGRES_HOSTNAME
                :port-number   :env.edn/POSTGRES_PORT
                :database-name :env/POSTGRES_DB
                :sslmode       :env/POSTGRES_SSLMODE}}

PS: An Updated Example!

If you're having issues, I've updated my currently up-to-date version of the code here.

Acknowledgements

Thanks to Christopher O'Donnell for blog post series.