Clojure 数据库访问实战：next.jdbc、HoneySQL 与连接池管理

(require '[next.jdbc :as jdbc])

;; 方式一：数据库连接 map（每次新建连接，仅适合脚本/测试）
(def db {:dbtype "postgresql"
         :dbname "myapp"
         :host "localhost"
         :port 5432
         :user "app"
         :password "secret"})

;; 方式二：通过 get-datasource 构建 DataSource（推荐，交给连接池）
(def ds (jdbc/get-datasource db))

;; 方式三：直接用 JDBC URL
(def ds2 (jdbc/get-datasource
           {:jdbcUrl "jdbc:postgresql://localhost:5432/myapp"
            :username "app"
            :password "secret"}))

;; execute!：返回所有行（向量）
(jdbc/execute! ds ["SELECT * FROM users WHERE active = ?" true])
;; => [{:users/id 1, :users/name "Alice", :users/active true} ...]

;; execute-one!：只返回第一行（没有则返回 nil）
(jdbc/execute-one! ds ["SELECT * FROM users WHERE id = ?" 42])
;; => {:users/id 42, :users/name "Alice"}

;; 写操作：返回受影响行数（默认）或生成键
(jdbc/execute! ds
  ["INSERT INTO users (name, email) VALUES (?, ?)"
   "Bob" "bob@example.com"]
  {:return-keys true})          ; 返回生成的自增主键
;; => [{:generated_keys [101]}]

(require '[next.jdbc.result-set :as rs])

;; 默认：qualified maps（带表名前缀 :users/id）
(jdbc/execute! ds ["SELECT id, name FROM users"])
;; => [{:users/id 1, :users/name "Alice"}]

;; 无表名前缀
(jdbc/execute! ds ["SELECT id, name FROM users"]
  {:builder-fn rs/as-unqualified-maps})
;; => [{:id 1, :name "Alice"}]

;; 自定义列名处理（如转成小写关键字）
(jdbc/execute! ds ["SELECT ID, NAME FROM users"]
  {:builder-fn rs/as-maps
   :column-fn keyword
   :label-fn clojure.string/lower-case})

;; 数组/JSONB 列自动解析为 Clojure 数据结构（配合 :builder-fn rs/as-arrays? 等）

;; plan 返回可逐步消费的 reducible
(jdbc/plan ds ["SELECT * FROM events"]
  (fn [row] (process-event! row)))

(transduce
  (comp (map :events/amount) (filter pos?))
  +
  0
  (jdbc/plan ds ["SELECT amount FROM events"]))

(jdbc/with-transaction [tx ds]
  (jdbc/execute! tx ["UPDATE accounts SET balance = balance - ? WHERE id = ?" 100 1])
  (jdbc/execute! tx ["UPDATE accounts SET balance = balance + ? WHERE id = ?" 100 2]))

(jdbc/with-transaction [tx ds {:isolation :serializable
                               :read-only false}]
  ...)

;; 显式提交/回滚
(jdbc/with-transaction [tx ds]
  (try
    (jdbc/execute! tx ["INSERT ..."])
    (jdbc/commit tx)
    (catch Exception e
      (jdbc/rollback tx))))

(jdbc/with-transaction [tx ds]
  (create-order! tx order)              ; 内部 if-let 又开启 with-transaction
  (update-inventory! tx sku qty))
;; 内层操作合并到外层事务，统一提交/回滚

(jdbc/with-transaction [tx ds]
  (jdbc/execute! tx ["INSERT INTO audit ..."])
  (let [sp (jdbc/set-savepoint! tx)]
    (try
      (jdbc/execute! tx ["INSERT INTO orders ..."])
      (catch Exception _
        (jdbc/release-savepoint! tx sp)))))   ; 回滚到 savepoint

(require '[honey.sql :as sql]
         '[honey.sql.helpers :as h])

(sql/format {:select [:id :name]
             :from [:users]
             :where [:= :active true]})
;; => ["SELECT id, name FROM users WHERE active = ?" true]

;; 链式 helpers 写法
(sql/format
  (-> (h/select :id :name)
      (h/from :users)
      (h/where [:= :active true])
      (h/order-by [:id :desc])
      (h/limit 10)))
;; => ["SELECT id, name FROM users WHERE active = ? ORDER BY id DESC LIMIT 10" true]

(defn build-user-query [{:keys [name role active? min-id]}]
  (cond-> (h/select :*)
    (h/from :users)
    name     (h/where [:ilike :name (str "%" name "%")])
    role     (h/where [:= :role role])
    (some? active?) (h/where [:= :active active?])
    min-id   (h/where [:> :id min-id])
    :always  (h/order-by [:id :desc])))

;; 不同参数生成不同 SQL
(sql/format (build-user-query {:role "admin" :active? true}))
;; => ["SELECT * FROM users WHERE role = ? AND active = ? ORDER BY id DESC" "admin" true]

(sql/format (build-user-query {:name "alice"}))
;; => ["SELECT * FROM users WHERE name ILIKE ? ORDER BY id DESC" "%alice%"]

;; INSERT
(sql/format (h/insert-into :users
             (h/values [{:name "Alice" :email "a@example.com"}
                        {:name "Bob" :email "b@example.com"}])))
;; => ["INSERT INTO users (name, email) VALUES (?, ?),(?, ?)" "Alice" "a@example.com" "Bob" "b@example.com"]

;; UPDATE
(sql/format (-> (h/update :users)
                (h/set {:name "Alicia"})
                (h/where [:= :id 42])))
;; => ["UPDATE users SET name = ? WHERE id = ?" "Alicia" 42]

;; DELETE
(sql/format (-> (h/delete-from :users)
                (h/where [:and [:< :age 18] [:= :active false]])))
;; => ["DELETE FROM users WHERE age < ? AND active = ?" 18 false]

;; RETURNING（PostgreSQL）
(sql/format (-> (h/insert-into :users)
                (h/values [{:name "Carol"}])
                (h/returning :*)))
;; => ["INSERT INTO users (name) VALUES (?) RETURNING *" "Carol"]

(require '[next.jdbc :as jdbc]
         '[honey.sql :as sql]
         '[honey.sql.helpers :as h])

(defn find-users [ds filters]
  (jdbc/execute! ds
    (sql/format (build-user-query filters))
    {:builder-fn next.jdbc.result-set/as-unqualified-maps}))

(defn create-user! [ds user]
  (jdbc/execute-one! ds
    (sql/format (-> (h/insert-into :users)
                    (h/values [user])
                    (h/returning :*)))
    {:return-keys true}))

(defn batch-insert! [ds users]
  (jdbc/execute! ds
    (sql/format (h/insert-into :users (h/values users)))))

-- resources/sql/users.sql
-- :name find-user-by-id :? :1
-- :doc 根据 ID 查询用户
SELECT * FROM users WHERE id = :id;

-- :name find-active-users :? :*
SELECT * FROM users WHERE active = :active ORDER BY id;

-- :name insert-user! :insert :returning
INSERT INTO users (name, email)
VALUES (:name, :email)
RETURNING id, name, email;

-- :name update-user! :! :n
UPDATE users SET name = :name WHERE id = :id;

(require '[hugsql.core :as hugsql])

;; 生成命名空间内函数（snake_case 转 kebab-case）
(hugsql/def-db-fns "sql/users.sql")
;; => find-user-by-id, find-active-users, insert-user!, update-user!

;; 使用（传入连接对象）
(find-user-by-id ds {:id 42})
(find-active-users ds {:active true})
(insert-user! ds {:name "Dave" :email "d@example.com"})

-- :name search-users :? :*
-- :doc 动态条件搜索
SELECT * FROM users
WHERE 1 = 1
--~ (when (:name params) " AND name ILIKE :name")
--~ (when (:role params) " AND role = :role")
ORDER BY id;

(search-users ds {:name "ali" :role "admin"})

(require '[hikari-cp.core :as hikari])

(defonce datasource
  (hikari/make-datasource
    {:jdbc-url "jdbc:postgresql://localhost:5432/myapp"
     :username "app"
     :password "secret"
     ;; 核心调优参数
     :maximum-pool-size 20
     :minimum-idle 5
     :connection-timeout 30000        ; 获取连接超时(ms)
     :idle-timeout 600000
     :max-lifetime 1800000
     :initialization-fail-timeout 30000
     :connection-test-query "SELECT 1"}))

;; 生命周期管理：应用关闭时销毁
;; (hikari/close-datasource datasource)

(defn pool-metrics []
  (let [pool (:datasource hikari/*datasource*)]   ; 假设已构建
    {:active-connections (.getActiveConnections pool)
     :idle-connections   (.getIdleConnections pool)
     :total-connections  (.getTotalConnections pool)
     :pending-requests   (.getThreadsAwaitingConnection pool)}))

;; 健康检查端点（配合 Ring）
(defn health-check [_]
  (try
    (jdbc/execute-one! datasource ["SELECT 1"])
    {:status 200 :body {:status "ok" :pool (pool-metrics)}}
    (catch Exception e
      {:status 503 :body {:status "down" :error (.getMessage e)}})))

;; deps.edn
{:deps {migratus/migratus {:mvn/version "1.5.6"}
        com.github.seancorfield/next.jdbc {:mvn/version "1.3.874"}
        com.zaxxer/HikariCP {:mvn/version "5.1.0"}}
 :aliases {:migrate {:main-opts ["-m" "migratus.main"]}}}

;; 迁移配置（可放在 migratus.clj）
{:store {:type :jdbc
         :db {:dbtype "postgresql"
              :dbname "myapp"
              :host "localhost"
              :user "app"
              :password "secret"}}
 :migration-dir "resources/migrations"
 :init-script "resources/migrations/init.sql"}

-- 20260926090000-create-users.up.sql
CREATE TABLE users (
  id       BIGSERIAL PRIMARY KEY,
  name     TEXT NOT NULL,
  email    TEXT UNIQUE,
  active   BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 20260926090000-create-users.down.sql
DROP TABLE IF EXISTS users;

# 应用所有待执行迁移
clojure -M:migrate migrate

# 回滚最近一个迁移
clojure -M:migrate rollback

# 查看状态
clojure -M:migrate status

# 带配置运行
clojure -M:migrate --config config/migratus.edn migrate

;; 批量插入（HoneySQL 多行 VALUES 一次执行）
(defn batch-insert-events! [ds events]
  (jdbc/execute! ds
    (sql/format
      (-> (h/insert-into :events)
          (h/values events)))
    {:multi-rs? true}))

;; 或 prepared statement 批量
(jdbc/execute! ds
  ["INSERT INTO events (ts, kind) VALUES (?, ?)"]
  [{:params [t1 "click"]} {:params [t2 "view"]}])