HoneySQL

Chicago Clojure

July 20, 2016

https://github.com/emidln/honeysql-talk

about me

{:name "Brandon Adams",
 :languages #{:clojure :java :c :python},
 :works-at "https://curiosity.com",
 :github "@emidln"}

<Plug> Curiosity.com </Plug>

{:languages #{:clojure :python},
 :databases #{:redis :postgresql :zookeeper :mongodb},
 :location "Ravenswood, Chicago, IL, USA",
 :hiring? true}

Why SQL?

• popular databases ...
  • Oracle, MySQL, PostgreSQL, MS SQL
  • SQLite, H2, and many more
• because my boss said so

SQL

SELECT * FROM foo WHERE id = 9 AND spam = 'blah';

UPDATE bar SET quux = 'spam' WHERE id = 37;

INSERT INTO pokedex (pokemon, name, level)
VALUES ('pikachu', 'Perry', 9);

What is HoneySQL?

• AST for SQL represented using Clojure data
• Helper library for friendly parameterized query construction
• Written by Justin Kramer (@jkk) and maintained at github.com/jkk/honeysql

Basic HoneySQL

Inserts

(-> (insert-into :cities)
    (values
     {:name "Chicago", :pizza-rank 1}
     {:name "New York", :pizza-rank 2}))

(-> (insert-into :cities)
    (columns :name :pizza-rank)
    (values ["Chicago" 1] ["New York" 2]))

Updates

(-> (update :cities) (sset {:rank 1}) (where [:= :name "New York"]))

(-> (update :daily-sales)
    (sset {:total (call :+ :total 1)})
    (where [:= :date (raw "current_date")]))

Queries

(-> (select :c.name :c.pizza-rank :p.population)
    (from [:cities :c])
    (join [:population :c] [:= :c.name :p.city-name])
    (order-by [:c.pizza-rank :asc])
    (limit 1))

Deletes

(-> (delete-from :cities) (where [:< :rank 1]))

HoneySQL Provides An AST

(=
 (-> (select :*) (from :foo) (where [:= :id 9] [:= :spam "blah"]))
 {:select '(:*),
  :from '(:foo),
  :where [:and [:= :id 9] [:= :spam "blah"]]})

and a library to take advantage of it

(-> {:select '(:email),
     :from '(:fancy-hat-customers),
     :where [:> :disposable-income 10000]}
    (merge-where
     [:or
      [:= :favorite-color "blue"]
      [:= :favorite-team "cubs"]
      [:= :lives-in "Chicago"]]))

{:select (:email),
 :from (:fancy-hat-customers),
 :where [:and
         [:> :disposable-income 10000]
         [:or
          [:= :favorite-color "blue"]
          [:= :favorite-team "cubs"]
          [:= :lives-in "Chicago"]]]}

You don't need HoneySQL if your queries are static

(require '[clojure.java.jdbc :as jdbc])
(require 'clj-time.jdbc)
(defn find-foos-by-range [db start stop]
  (jdbc/query db ["SELECT * FROM foo WHERE ts BETWEEN ? and ?"]))

But if you need something a little more dynamic...

(let [id 9, spam "blah"]
  (cond-> "SELECT * FROM foo WHERE"
    (some? id) (str " id = " id)
    (and (some? id) (some? spam)) (str " AND ")
    (some? spam) (str "spam = " spam)))

Have you done this?

Spot any potential bugs?

SQL Injection?

I forgot to insert driver-specific placeholders and track the values in an array so I can pass those separately to JDBC.

String building?

I left out a space around spam. This will result in something like "WHEREspam=" if id isn't given.

Easy, right?

(require 'clojure.java.jdbc)
(let [id 9
      spam "blah"
      selector (fn [id spam]
                 (let [params (atom [])]
                   [(cond-> "SELECT * FROM foo WHERE"
                      (some? id) (str " id = " id)
                      (and (some? id) (some? spam)) (str " AND ")
                      (some? spam)
                        (#(do (swap! params conj %) (str " spam = ?"))))
                    @params]))
      [sql & params] (selector id spam)]
  (apply clojure.java.jdbc/query db sql params))

Yikes! Maybe something does this for us?

HoneySQL the AST (cont'd)

(require 'honeysql.core)
(require '[honeysql.helpers :refer :all])
(require 'clojure.java.jdbc)
(let [id 9
      spam "blah"
      selector (fn [id spam]
                 (-> (cond-> (-> (select :*) (from :foo))
                       (some? id) (merge-where [:= :id id])
                       (some? spam) (merge-where [:= :spam spam]))
                     honeysql.core/format))
      [sql & params] (selector id spam)]
  (apply clojure.java.jdbc/query db sql params))

AST Advantages

• Safety
  • Let the library worry about parameters
• Tooling
  • clojure.core
  • plumbing.core
  • your favorite utility library

By representing the AST as Clojure data structures, our core Clojure language can be used to easily manipulate a query

Sound familiar?

HoneySQL Notes (part 1)

• Maps nest as subqueries. If you want a map value to the JDBC layer use honeysql.format/value

  (-> (insert-into :meetups-pages)
      (values
       {:event 232391630,
        :meta (value
               {"keywords" ["USA"
                            "Illinois"
                            "Chicago"
                            "softwaredev"
                            "Clojurians"
                            "Meetup"],
                "geo.position" ["41.881966" "-87.632362"]})}))
  

• Vectors are handled by the individual clause. When it makes sense, it is used for aliasing.

  (from [:foo :f])
  

  (where [:= :bar "baz"])
  

  #sql/call [:+ :total 1]

HoneySQL Notes (part 2)

• Not all clauses have helpers. They are easy to define if you think it'll help readability:

  (defn union
    {:argslists '([& queries] [m & queries])}
    [& args]
    (let [[m & queries] (if (map? (first args)) args (cons {} args))]
      (assoc m :union (vec queries))))
  

• Raw SQL access is available, see honeysql.core/raw

  (raw "current_date")
  

  #sql/raw "current_date"

Extending HoneySQL Operators

(require 'honeysql.format)
(defmethod
 honeysql.format/fn-handler
 "json-eq"
 [_ outer-key inner-key val]
 (format
  "%s->>'%s' = %s"
  outer-key
  inner-key
  (honeysql.format/to-sql val)))
(-> (select :*) (from :foo) (where [:json-eq "extra" "foo-id" 5]))

Extending HoneySQL Clauses

(require 'honeysql.core)
(require '[honeysql.format :refer [comma-join format-clause to-sql]])
(require '[honeysql.helpers :refer :all])
(defmethod
 format-clause
 :returning
 [[_ fields] _]
 (str "RETURNING " (comma-join (map to-sql fields))))
(defhelper returning [m args] (assoc m :returning args))
(-> (update :foo)
    (sset
     {}
     {:count #honeysql.types.SqlCall{:name :+, :args (:count 1)}})
    (where [:= :id 99])
    (returning :count))

Using clojure.java.jdbc

(require '[clojure.java.jdbc :as jdbc] '[cheshire.core :as json])
(import 'org.postgresql.util.PGobject)
(defn value-to-json-pgobject [value]
  (doto (PGobject.)
        (.setType "jsonb")
        (.setValue (json/generate-string value))))

(extend-protocol
 jdbc/ISQLValue
 clojure.lang.IPersistentMap
 (sql-value [value] (value-to-json-pgobject value)))
(extend-protocol
 jdbc/IResultSetReadColumn
 org.postgresql.util.PGobject
 (result-set-read-column
  [pgobj metadata idx]
  (let [type (.getType pgobj), value (.getValue pgobj)]
    (if (#{"json" "jsonb"} type) (json/parse-string value true) value))))

Someone already did this for ...

Postgres: nilenso/honey-postgres

Joda Time: clj-time

Solid Alternatives

• yesql
  • SQL is already a DSL, why do we need another abstraction?
  • parses SQL into clojure functions
• HugSQL
  • inspired by yesql
  • parses SQL into clojure functions
  • supports more use cases than yesql
  • actively maintained

Exercises

See this talk's github for examples of extending and using HoneySQL using PGExercises.com. A Dockerfile is included with instructions in the README.md to get started.