Clojure next.jdbc and SQLite datetime goodness

next-jdbc-sqlite-datetimes.clj

;; problem: date, time, datetime, and timestamp types in sqlite are basically just strings or numbers.
;;          the sqlite jdbc driver doesn't coerce them into Timestamp, Date or Time types by default
;;          but we want to use proper java.time types!
;; 
;; solution: supply a builder-fn [0] that correctly returns java.sql.{Date,Time,Timestamp} types (and Boolean while were at it)
;;
;; [0]: https://cljdoc.org/d/seancorfield/next.jdbc/1.2.659/doc/getting-started/tips-tricks#sqlite

(def builder-fn
  (next.jdbc.result-set/builder-adapter
   next.jdbc.result-set/as-maps
   (fn [builder ^ResultSet rs ^Integer i]
     (let [rsm ^ResultSetMetaData (:rsmeta builder)
           col-type-name (.getColumnTypeName rsm i)]
       (next.jdbc.result-set/read-column-by-index
        (cond
          (#{"DATE"} col-type-name) (when-let [v (.getString rs i)] (java.time.LocalDate/parse v))
          (#{"TIME"} col-type-name) (.getTime rs i)
          (#{"DATETIME" "TIMESTAMP"} col-type-name) (.getTimestamp rs i)
          (#{"BIT" "BOOL" "BOOLEAN"} col-type-name) (.getBoolean rs i)
          :else (.getObject rs i))
        rsm
        i)))))

;; teach next.jdbc how to coerce from the java.sql.{Date,Time,Timestamp) types to java.time.{LocalDate,LocalTime,Instant}

(extend-protocol next.jdbc.result-set/ReadableColumn
  java.sql.Timestamp
  (read-column-by-label [^java.sql.Timestamp v _]
    (.toInstant v))
  (read-column-by-index [^java.sql.Timestamp v _2 _3]
    (.toInstant v))
  java.sql.Date
  (read-column-by-label [^java.sql.Date v _]
    (.toLocalDate v))
  (read-column-by-index [^java.sql.Date v _2 _3]
    (.toLocalDate v))
  java.sql.Time
  (read-column-by-label [^java.sql.Time v _]
    (.toLocalTime v))
  (read-column-by-index [^java.sql.Time v _2 _3]
    (.toLocalTime v)))

;; Using hugsql? Then make this builder-fn the default
;;   docs: https://www.hugsql.org/#adapter-next-jdbc

(hugsql/set-adapter! (next-adapter/hugsql-adapter-next-jdbc {:builder-fn builder-fn}))