the problem is that, sometimes, I don't have only one table, but a query joining two+ tables.. so think I'll manage using db-query-with-resultset function

If you think of ways the clojure.java.jdbc API could support your use case better, feel free to open a JIRA issue here https://dev.clojure.org/jira/browse/JDBC

I have a jdbc query question. How do a construct a query or function which will not return nil values. For example,

(jdbc/query db-spec ["SELECT * from feedback"])

Hmmm… I can use filter for a single value:

(into {} (filter #(not (nil? (val %))) (first (jdbc/query db-spec ["SELECT * from feedback WHERE id = 1"]))))

@mark.davidson Can you explain your use case? Nil-punning generally means not needing to do what you're trying to do there...

@seancorfield I’m trying to read from a database, validate the rows with schema and then transform it to json for a REST api

I think I almost have it:

(jdbc/query db-spec ["SELECT * from feedbacks_resolution"] {:row-fn remove-nil})

Where

(defn remove-nil [row] (into {} (filter #(not (nil? (val %)))) row))

What's wrong with nil flowing into the JSON as null?

(our REST APIs can accept and return null in the JSON at work)

That would work. I’m not sure how to use s/defschema to accept nil values:

(s/defschema Resolution
  {:id                                   s/Int
   :feedbacks_id                         Integer
   :device_serial_number                 String
   (s/optional-key :developer_notes)     s/Any
   :device_server_ip                     s/Any
   :commands_sent                        Integer
   :disconnects                          Integer
   (s/optional-key :debug_server_ip)     s/Any

   (s/optional-key :debug_commands_sent) Integer
   :debug_disconnects                    Integer
   (s/optional-key :category_id)         s/Any
   (s/optional-key :resolution_id)       s/Any
   :is_resolved                          Boolean
   })

In spec it would be s/nilable

No idea about Schema

yea, I would rather use spec but I don’t have any great examples in here: https://github.com/metosin/compojure-api

Looks like s/maybe

Given a schema which defines a column with a string type, if nil is in the column then it seems to break validation

;; maybe
(s/validate (s/maybe s/Keyword) :a)
(s/validate (s/maybe s/Keyword) nil)

If your DB schema allows NULL, then your Schema for the data needs to reflect that.

I’ll try that out. Thanks.

@seancorfield Using s/maybe did the trick! Thanks for your help!

Cool! Glad that worked.

@mark.davidson since latest c-api alpha, you can use substitute Schemas with Specs, will work 80% of the time ok.