Skip to content

Instantly share code, notes, and snippets.

@Ramblurr
Last active October 27, 2021 14:41
Show Gist options
  • Save Ramblurr/04b2b56827f23393ebcc49d339442d0b to your computer and use it in GitHub Desktop.
Save Ramblurr/04b2b56827f23393ebcc49d339442d0b to your computer and use it in GitHub Desktop.
Clojure next.jdbc and SQLite datetime goodness
;; 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}))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment