Clojure 与 PostgreSQL：优雅的构建 SQL 查询

Honey SQL 的基本使用

(sql/format {:select :* :from :users}) ; => ["SELECT * FROM users"]

增

(->> {:insert-into :users
	:values [{:username "username"
			  :level "level"
			  :is_admin false}]
	:returning :*}
   sql/format)
; => => ["INSERT INTO users (username, level, is_admin) VALUES (?, ?, FALSE) RETURNING *" "username" "level"]

改

(defn update-user
  [tx user_id & {:keys [password level realname]}]
  (->> {:update :users
        :where [:= user_id :user_id]
        :set (cond-> {}
               (some? password) (assoc :password_hashed (hash-password password))
               (some? level) (assoc :level level)
               (some? realname) (assoc :realname realname)
       sql/format
       (jdbc/execute! tx)))

(cond-> {} ;后面跟着偶数个 form，每组第一个是条件，第二个是动作
	(some? password) ; 如果传入了 password 参数，即 password != nil
	(assoc :password_hashed (hash-password password) ; 则更新 {} 为，{:password_hashed <xxx>}
	)

构造复杂查询条件

(defn fetch-one
  [tx & {:keys [machine_id natural_id]}]
  {:pre [(or (some? machine_id) (some? natural_id))]}
  (->> {:select [[:*]
                 [{:select [[[:json_build_object
                              [:inline "natural_id"] :warehouses.natural_id
                              [:inline "profile_name"]  [:-> :warehouses.meta [:inline "profile"] [:inline "name"]]]]]
                   :where [:= :warehouses.warehouse_id :machines.warehouse_id]
                   :from :warehouses}
                  :warehouse]]
        :from :machines
        :where (cond-> [:and]
                 (some? machine_id) (conj [:= :machine_id machine_id])
                 (some? natural_id) (conj [:= :natural_id natural_id]))
        :limit 1}
       sql/format
       log/spy 
       (db/execute! tx)
       first))

连接到数据库

使用 next.jdbc

使用 mount 管理状态

(defn init-pool [jdbc-url]
  (log/debug "connection to database: " jdbc-url)
  (connection/->pool com.zaxxer.hikari.HikariDataSource {:jdbcUrl jdbc-url}))

(defstate pool
  :start (init-pool (:jdbc-url (mount/args)))
  :stop (.close pool))

这么做有什么好处？

分页基础设施

游标分页器

(defn with-pagination-marker
  [dsl order keys marker]
  {:pre [(or (= :asc order) (= :desc order))
         (contains? dsl :select)
         (not (contains? dsl :order-by))]}
  (cond->  dsl
    (= (count keys) (count marker))
    (update  :where
             #(vector :and [(if (= :desc order) :< :>)
                            (cons :composite keys)
                            (cons :composite marker)] %))
    true (assoc :order-by [[(cons :composite keys) order]])))

• 查询第一页：在这里我们使用 (created_at, user_id) 升序排列，游标标记传入 nil 代表是第一页。

(sql/format (with-pagination-marker 
  {:select :* :limit [:inline 10] :from :users}
  :asc
  [:created_at :user_id]
  nil
  ))
;=> ["SELECT * FROM users ORDER BY (created_at, user_id) ASC LIMIT 10"]

• 接着第一页查询第二页：这里我们需要使用上次查询结果的最后一项的(created_at, user_id) 作为标记传入，以获得从某个特定的 (created_at, user_id)开始的后一页内容。

(sql/format (with-pagination-marker 
  {:select :* :limit [:inline 10] :from :users}
  :asc
  [:created_at :user_id]
  [#inst "2022-05-29T09:43:16.730-00:00" #uuid "8f62771e-22d1-46c4-89fe-b85dc8d88b3b"]
  ))
; =>["SELECT * FROM users WHERE ((created_at, user_id) > (?, ?)) ORDER BY (created_at, user_id) ASC LIMIT 10"
; #inst "2022-05-29T09:43:16.730-00:00"
; #uuid "8f62771e-22d1-46c4-89fe-b85dc8d88b3b"]

一个良好的 HTTP 查询接口

(defn- encode-marker [values]
  (-> values
      cbor/encode
      codecs/bytes->b64u
      codecs/bytes->str))

(defn- decode-marker [marker]
  (try (-> marker
           codecs/str->bytes
           codecs/b64u->bytes
           cbor/decode)
       (catch Exception _ nil)))
(def ^:private select-values (comp vals select-keys))
(defn paginated!
  [dsl conn order-by-keys order {:keys [limit marker]}]
  (-> dsl
      (with-pagination-marker order order-by-keys (decode-marker marker))
      (assoc :limit (or limit 20))
      (sql/format)
      ((partial execute! conn))
      ((partial array-map :items))
      (#(assoc % :marker (-> %
                             :items
                             last
                             (select-values order-by-keys)
                             encode-marker)))))