
Honey SQL – SQL as Clojure data structures - tosh
https://github.com/jkk/honeysql
======
christophilus
Curious to hear if anyone uses yesql or similar[1]. I'd _really_ be interested
in hearing from someone who has used Honey SQL and yesql and can
compare/contrast.

After dealing with ActiveRecord for the past few years, I'm ready to just be
able to write plain old SQL again. It seems like yesql is a pretty sweet
solution, but I haven't tried it in earnest.

[1]
[https://github.com/krisajenkins/yesql](https://github.com/krisajenkins/yesql)

~~~
ledgerdev
I've found templated queries(e.g. yesql) are far preferable for complex
selects/joins/subselects and just can't imagine using the data structure dsl
for such queries. One huge downside of dsl queries is that they NEVER support
the full sql syntax of any database, in my case postgres. The best part is
being able to write the query in a sql editor then copy/paste directly, rather
than needing to translate into a dsl.

On the other hand if you need dynamic queries, then yes dsl is more suited, as
well as for other query types where the dsl can be much better at handling
multiple records at once... like say inserting 10 rows at once.

So to sum up:

* complex select queries - templated queries really shine

* simple dynamic queries - templates for simple replacements

* complex dynamic queries - dsl

* inserts/updates/deletes - dsl

* portable sql - dsl

~~~
emidln
> I've found templated queries(e.g. yesql) are far preferable for complex
> selects/joins/subselects and just can't imagine using the data structure dsl
> for such queries

I work with lots of CTEs, UNIONS, JOINS, and sub-selects and can't imagine not
using data structures to validate individual parts of the query as I'm
designing it. Particularly given how trivial it is in HoneySQL (anywhere a
sub-select is legal you can insert a HoneySQL query map).

Further, I use a lot of the jsonb and postgis features of Postgres and
HoneySQL is a champ here. Not everything is supported out of the box[0], but
adding any missing clause or operator is just a quick defmethod away. Between
HoneySQL and clojure.java.jdbc, working with PostgreSQL's JSON support feels
like Mongo with the added abilities of custom types, transactions, and
function indexes.

I do have a limit with how far I'll take HoneySQL, but that limit is usually
something that belongs in a trigger or a sproc anyway.

[0] honeysql-postgres ([https://github.com/nilenso/honeysql-
postgres](https://github.com/nilenso/honeysql-postgres)) helps here by
providing some postgres-specific clauses/operators.

------
joncampbelldev
I'm currently using honeysql in clojure projects at work, it's definitely my
favourite option for interfacing with mysql (although would of course rather
use datomic, but then what patriotic clojure fan wouldn't?).

In case anyone needs it for mysql, the following adds basic support for "on
duplicate key update" clauses:

    
    
      (defmethod sql-format/format-clause :upsert [[_ updates] _]
        (let [parts (for [[column value] updates]
                      (str (sql-format/to-sql column) "=" (sql-format/to-sql value)))]
          (str "ON DUPLICATE KEY UPDATE " (string/join "," parts))))
    
      (sql-helpers/defhelper upsert [m [updates]]
        (assoc m :upsert updates))

------
lilactown
(Relative) Clojure noob here: what stops someone from developing a similar
library to honeysql, but instead of passing in vectors/maps, one created the
data structure in a plain quoted form and passed it in?

e.g. the first example in the README could be turned into:

    
    
        (def sqlmap 
            '(where (= :f.a "baz") 
                (from :foo (select [:a :b: :c])))
    

I understand that hiccup syntax can be awesome, but it seems like Clojure
loses a bit of the magic of LISP when everything has to be written in terms of
vectors/maps - what's wrong with a plain ol' list?

I imagine I'm probably missing something obvious or important, since I've only
dabbled in Clojure (and loved it!)

~~~
weavejester
Nested lists are harder to perform lookups on than maps.

If I want to know what the "from" part of the SQL query is, with HoneySQL I
can write:

    
    
        (:from query)
    

On the other hand, depending on your s-expression syntax, I'd either have to
find the table name positionally, or by walking the tree.

~~~
lilactown
That makes sense. Thanks for the reply!

------
auvrw
see also.

[https://www.hugsql.org/](https://www.hugsql.org/)

binds Clojure functions to SQL

\----

clojars stats:

    
    
        GitHublayerware/hugsql
        76,170 Downloads
    
        GitHubjkk/honeysql
        168,981 Downloads
    

\----

these two libraries could in principle work together (e.g. using hugsql's
`_-dbvec` variants): foundations go in plain sql, more complicated stuffs
generated out of Clojure.

for the simple sort of databasing that i've done so far in Clojure, hugsql
alone has sufficed: i tried honeysql. it boiled down to wanting to write some
plain SQL b/c i found it to be a more unique (good/bad, i dunno) approach than
writing SQL-in-language-_.

FOOTNOTE

how to escape the asterisk in the YC formatting language \--__--

------
tosh
Honey SQL is a great example for how elegant and powerful data-centric APIs
can be.

Also worth looking into are clojure.spec, Datomic, hiccup, rum

~~~
yogthos
I'd say Reagent would be a better example of a data-centric API than Rum. Both
are great libraries though.

------
mjmein
I recently started using honeysql and so far it has been great.

There is some learning curve figuring put how things work, but the advantages
of having SQL encoded as clojure data are worth it, in my opinion:

\- s-expressions everywhere means that structural editing works

\- allows for easy composing of sql queries - can very easily add custom
filters, joins, etc and build up queries dynamically

\- can easily add abstractions, for example something like
[https://github.com/metabase/toucan/blob/master/README.md](https://github.com/metabase/toucan/blob/master/README.md)

If you use postgresql, check out: [https://github.com/nilenso/honeysql-
postgres/blob/master/REA...](https://github.com/nilenso/honeysql-
postgres/blob/master/README.md)

------
zcam
There is also Hayt for CQL (cassandra). It's quite mature and used by yapster,
featured recently on juxt blog among others.

Queries are plain clj maps and there is also a thin dsl provided.

[https://github.com/mpenet/hayt](https://github.com/mpenet/hayt)

In our case we use it to generate prepared statements but we avoid using this
kind of query gen on live/hot paths.

------
zubairq
I like these Clojure SQL DSLs. I did a reactive Clojurescript DSL a couple of
years ago:

[https://www.youtube.com/watch?v=CzwikfCAdws](https://www.youtube.com/watch?v=CzwikfCAdws)

[https://www.youtube.com/watch?v=xwi2xxIXr8U](https://www.youtube.com/watch?v=xwi2xxIXr8U)

------
ashish01
Does anything comparable exists for Python?

~~~
cjauvin
I created this which is in a slightly similar spirit:
[https://github.com/cjauvin/little_pger](https://github.com/cjauvin/little_pger)

------
batbomb
isn't this just korma?

~~~
harperlee
I'm using korma for my project and it ends up being a pain. It is the one
library I want to get rid of.

The documentation isn't really great, and when falling out of the most vanilla
use cases, you need to fire up Google and pray to find posts from someone with
a similar problem, or go read the code, which always takes more time. I ended
up several times using "raw" and writing big queries in a string, as I
desisted to fight against the library. I'm planning on porting everything to
either honeysql or yesql whenever I have time.

In my opinion, korma was a good experiment from earlier clojure times, but the
approach it takes ends up suboptimal. Honeysql's approach seems more lean and
more integrated into the core language, so it is easier to operate
(disclaimer: I did not test it yet).

------
malberto
I came here looking for comments on the lines of "F# had this before microsoft
realized having F#". I came early

~~~
iswim
Do you mean F* and some of the work by Phil Wadler et al eg.
[http://homepages.inf.ed.ac.uk/wadler/papers/qdsl/curryon.pdf](http://homepages.inf.ed.ac.uk/wadler/papers/qdsl/curryon.pdf)

------
esaym
What the heck? This makes me hate SQL and Clojure....

~~~
joncampbelldev
You may find your comment turning less gray if you articulated the issues you
see with this lib.

I definitely would be interested, since I am a current user. Its always good
to get a differing perspective on your tools.

