How do I read bool columns from SQLite into bool Clojure values with next.jdbc? SQLite stores booleans as 0/1

user_3937773 0
Viewed 338

Here's the smallest bit of code to see the problem.

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

  (def db-spec {:dbtype "sqlite" :dbname "example-db"})

  (jdbc/execute!
   db-spec
   ["create table if not exists users (name text, is_active bool)"])

  (jdbc/execute!
   db-spec
   ["insert into users (name, is_active) values (?, ?)" "alice" true])

  (jdbc/execute!
   db-spec
   ["select * from users"])
  ;; => [#:users{:name "alice", :is_active 1}]

I'd like is_active on that last line to be true or false. I don't want my application code to have to keep track of which columns are bool and do conversion after I get a result set.

jdbc clojure sqlite
1 Answers

next.jdbc gives you several ways to plug into how it creates Clojure objects from database values. The most general of these is by passing a :builder-fn option with a value created from builder-adapter and passing your own (or an existing) builder and a custom "read-column-by-index"

  (require '[next.jdbc.result-set :as rs])
  (defn sqlite-column-by-index-fn [builder ^ResultSet rs ^Integer i]
    (let [rsm ^ResultSetMetaData (:rsmeta builder)]
      (rs/read-column-by-index
       (if (re-find #"(?i)(bool|bit)" (.getColumnTypeName rsm i))
         (.getBoolean rs i)
         (.getObject rs i))
       rsm
       i)))
  (def sqlite-builder (rs/builder-adapter rs/as-maps sqlite-column-by-index-fn))
  (jdbc/execute!
   db/db-spec
   ["select id, \"email-confirmed?\" from user;"]
   {:builder-fn sqlite-builder})
  ;; => [#:user{:id 1, :email-confirmed? true} #:user{:id 2, :email-confirmed? false}]

NOTE: This won't always work if your using plan. For performance, plan doesn't realize values. In your reduction of a plan, accessing values of the result set will skip over the custom read-column-by-index stuff above. It's up to you to do the work in your reduction.

More info, from the author of next.jdbc, can be found at https://github.com/seancorfield/next-jdbc/blob/develop/doc/tips-and-tricks.md#sqlite and https://github.com/seancorfield/next-jdbc/issues/134

The rest of this answer is lagniappe. It's the "almost-there" and wrong turns that I made along the way. I'm leaving it as part of the answer for whatever value that may provide.

next.jdbc lets you extend the ReadableColumn protocol with custom functionality for converting values.

https://github.com/seancorfield/next-jdbc/blob/develop/doc/result-set-builders.md#readablecolumn

(extend-protocol result-set/ReadableColumn
  Integer
  (read-column-by-index [x mrs i]
    (if (re-find #"(?i)bool" (.getColumnTypeName mrs i))
      (if (= 1 x) true false)
      x)))

It might not be perfect. There's another function, read-column-by-label, that doesn't receive the ResultSetMetaData object that we can call getColumnTypeName on to test if the column type is a bool. read-column-by-label only receives the value and the column name. I'm not sure what the consequences of not overriding that function. Things appear to work with just read-column-by-index overridden.

Editing to make note of how this isn't a complete fix.

plan, for example, returns a reducible. The reduce function gets passed a "mapified" result set. Some of the implementations of some of the interfaces call read-column-by-label. I think it does this for performance reasons? So you can do something like select-keys without "building" the entire map. So if you want to do something like below with the reducer, then the above fix isn't sufficient.

  (def plan (jdbc/plan db-spec ["select * from users;"]))
  (defn reduce-fn [a b]
    (conj a (select-keys b [:users/name :users/is_active])))
  (r/reduce reduce-fn [] plan)
  ;; => [#:users{:name "alice", :is_active 1}
  ;;     #:users{:name "bob", :is_active 0}]

  (jdbc/execute! db-spec ["select * from users;"])
  ;; => [#:users{:name "alice", :is_active true, :age 1}
  ;;     #:users{:name "bob", :is_active false, :age 0}]
user_39377730
Related