Clojure, SQLite, and the problems with Booleans.

SQLite doesn't have a true "boolean" affinity.

https://www.sqlite.org/datatype3.html#affname

3.1. Determination Of Column Affinity

The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:

If the declared type contains the string "INT" then it is assigned INTEGER affinity.

If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.

If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.

If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.

Otherwise, the affinity is NUMERIC.

That creates some trouble with some of the common Clojure/jdbc tools.

  (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 expected that last line to give me back an :is_active true, just like what I inserted.

This is kind of expected though. Where should the conversion happen? SQLite makes it clear. If you do an INSERT INTO user (name, active) VALUES ("Alice", true);, and then do a SELECT * FROM user;, you're going to get back (Alice, 1).

It that's what SQLite is going to give you, why should it be the responsibility of the jdbc driver to do any differently? Any deviation from the SQLite behavior will surely be unexpected (at least by people familiar with the SQLite behavior).

I don't have a good solution for this yet.

next.jdbc lets you extend the ReadableColumn protocol to add custom behavior for converting database types into Clojure types.

(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)))

That's not ideal though. It relies on getColumnTypeName of the ResultSetMetaData to determint if an INTEGER affinity column is semantically a boolean. But there's another function of the ReadableColumn protocol that doesn't receive a ResultSetMetaData object when it's called (`read-column-by-name`).

  (require '[next.jdbc :as jdbc])
  (jdbc/execute! db-spec ["drop table users"])
  (def db-spec {:dbtype "sqlite" :dbname "example-db"})

  (jdbc/execute!
   db-spec
   ["create table if not exists users (name text, is_active bool, age int)"])
  (jdbc/execute!
   db-spec
   ["insert into users (name, is_active, age) values (?, ?, ?), (?, ?, ?)"
    "alice" true 1
    "bob" false 0])

  (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}]

The code that uses the plan and reduce fail to convert the integers to booleans. This is because the the reduce function gets passed a map-like result set that implements some of the map-like behavior in a way that calls read-column-by-label. I think it does this for a performance reason. It doesn't build the entire map if you just want to peek into a few of the keys.

I'd still like to know of a bullet-proof fix. I see you can set a typeMap on a Java Connection object. Maybe there's something there? The next-jdbc docs mention an option for calling the Connection object setters.

:connection -- a hash map of camelCase properties to set on the Connection object after it is created; these correspond to .set* methods on the Connection class and are set via the Java reflection API (using org.clojure/java.data)
Show Comments