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?
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?
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
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)
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"]})}))
(from [:foo :f])
(where [:= :bar "baz"])
#sql/call [:+ :total 1]HoneySQL Notes (part 2)
(defn union
{:argslists '([& queries] [m & queries])}
[& args]
(let [[m & queries] (if (map? (first args)) args (cons {} args))]
(assoc m :union (vec queries))))
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
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.