sql_on_the_wire_walkthrough.clj

(ns drewverlee.sql-on-the-wire-walkthrough
  (:require [honeysql.core :as sql]
            [honeysql.helpers :refer :all :as helpers]))


;; The goal is that a user of the app wants to retrieve the size of the TechCo building.

;; Will be showing how to achieve this goal concisely by letting the front end developer describe
;; the sql query while also improving and simplifying our authorization story. Lets say 

;; But we need to start the story at the beginning (before the browser app is compiled)
;; in order to reasonable understand how everything fits together.


;; Consider your database looks like this:

;; User
;; * id (int)

;; building
;; -----------
;; * name (string)
;; * area (int)
;; * user (ref/ int)

;; So a user can one more buildings.

;; So the sql query the user wants to perform looks like this:

"SELECT area FROM building WHERE name = TechCo"

;; We can create a datastructure which carries the same information:

(def sqlmap {:select [:building/area]
             :from   [:building]
             :where  [:= :building/name :?name]})

;; and use a library that knows how to do the conversation

(sql/format sqlmap :params {:name "TechCo"})
;; => ["SELECT area FROM building WHERE name = ?" "TechCo"]

;; At Compile time for the browser app, whose code contains this exact sqlmap,
;; takes the sqlmap and hashes it.

;; the backend stores a mapping from the hash to the sqlmap

(def h (hash sqlmap))
h
;; => -417433191

(def hash->sql {h sqlmap})
;; => {-417433191
;;     {:select [:area], :from [:building], :where [:= :building/name :?name]}}


;; the frontend stores the hash inside a function that makes a request to the backend and passes the hash of the query
;; and the building name as provided by the user.


{:on-click (fn [building-name] ('fetch-from-backend {:query h :params {:name building-name}}))}


;; the Browser will then send this request to the backend and as well as the users authorization information (in this case, their User ID).
;; At this point we have to back up and explain one strategy for handling authentication and authorization. Here is what happened when the user first went to the site:

;; * User goes to yoursite.com
;; * redirected to login screen controlled by auth server enters passwords which
;; * is sent to the auth server on success auth server redirects user back to
;;     yoursite.com and auth client library (in the browser app) now lets the user
;;     interact with the site as well as stores a jwt containing auth information (User ID)

;; Now moving forward again, the backend is receiving this request from the client:

{:jwt "..." :query-map {:query h :params {:name "TechCo"} }}


;; The backend can ask the auth server if this jwt is valid (nothing tampered with it.)
;; Now it knows that everything in the JWT can be trusted. So lets unwrap that auth information
;; and look at the full set of data the backend has.


(def request {:auth {:user-id 1} :query-map {:query h :params {:name "TechCo"} }})

;;  our authorization strategy is that a user can see any building they have access to in the database.
;; This means that if we modify our query to _inner join_  on the user table with the given user id


(let [{{user-id :user-id}           :auth
       {hash :query params :params} :query-map} request]
  (-> hash
    hash->sql
    (helpers/merge-from :user)
    (helpers/merge-join :building [:=  :user/id :building/user])
    (helpers/merge-where :and [:= :user/id :?id])

    (sql/format :params (assoc params :id user-id))))
;; => ["SELECT area FROM building, user INNER JOIN building ON id = user WHERE (name = ? AND id = ?)"
;;     "TechCo"
;;     1]

;; Lets review,

;; 1. front end dev wrote a sql query
;; 2. query was compiled to a hash and stored on front end. On the backend we store the hash->query
;; 3. At run time when the user enters an input we send that along with the hash and the authorization information to the backend.
;; 4. backend verifies the auth information, combines it with the unhashed query and parameters to create a sql query.

;; The net result is that we have eliminated a major class of errors by _not_ doing a number of translations that just
;; introduced friction.