Honey SQL – SQL as Clojure data structures | Hacker News Reader

Honey SQL – SQL as Clojure data structures

github.com

118 pointstosh30 comments

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

I found yesql inadaquete for use with anything other than simple queries. Writing plain SQL is definitely nicer than an ORM, however, I find that manipulating SQL as data (honeysql) is far superior to .sql files with added magic (yesql).

As soon as I had to dynamically build queries (essential when moving beyond the complexity of "select this user record") I ran into issues with yesql's lack of power.

It also fits closer to one of the main principles of clojure, to be data-driven/data-first/just-use-data etc etc

> when moving beyond the complexity of "select this user record") I ran into issues with yesql's lack of power.

This is definitely true, however, I'd like to point out that static queries (either Yesql-style, or even stored procedures) can go a lot further than you'd initially expect. Unless you have a pressing application need for dynamic queries, you may consider static queries as they are more predictable with respect to performance and debugging.

Here's the main trick: Most of the time, you want dynamic filters, not dynamic selections. When this happens, you can just include the total set of filters and provide default parameters that are always true.

For example:

    SELECT id, name, created_at, score
    FROM players
    WHERE created_at BETWEEN '-infinity'::timestamp AND 'infinity'::timestamp
    ORDER BY score * -1

This will return all players, regardless of when they joined and will sort by score descending. You can parameterize the time range as well as the sort direction between +1 and -1. You can use EXPLAIN to prove to yourself that Postgresql is smart enough to make proper use of indexes.

Does postgres' plan caching detect that these filters are useless, if they're sometimes filled in and other times not? Or do you end up with one bad plan?

It's explained a bit here: https://www.postgresql.org/docs/9.6/static/plpgsql-implement...

In short, if your parameters are highly dynamic, plan caching will be disabled. This is still better than dynamic queries, because the results of parsing will be cached.

If you almost always use one particular set of filters, but just enough dynamic values that the cache plan is not useful, AND your bottleneck is planning, then you can prepare a fast path.

To do that, use a PREPARE statement or your language's SQL bindings for that. Essentially, you tell it "these are usually going to be the defaults, let's call that staticFoo". Then staticFoo and dynamicFoo are subject to independent plan caching. The fast path, staticFoo, will get a good cached query plan, and dynamicFoo will be subject to dynamic re-planning.

The braindead (and therefore "better", by some measure) approach is to just just create an additional query for your fast path. The tradeoff of course is violating the DRY principle.

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

> 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) helps here by providing some postgres-specific clauses/operators.

Have used Hugsql[1] and love it. I find I always hit the limitations of other DSL like solutions eventually and it costs a lot of time. I found I didn't often need the ability to compose the SQL as data that Honey SQL offers and Hugsql has the concept of 'snippets' to handle those few situations. I have found the ability to just write SQL as a template is simpler, no translation and unsupported operation problems. It is however a two edged sword, with out that convenience layer you get no abstraction over the SQL that you write so it may not work across different SQL implementations but personally that is not really something I value very much and value the ability to use all the features of the particular database I am targeting.

[1] https://github.com/layerware/hugsql

Having used Yesql (and having used ActiveRecord in the past), you'd be surprised how grating it can be to write and rewrite boilerplate queries along the lines of "SELECT * FROM column WHERE ID=:id". It's awesome for edge case queries where SQL really shines; it's annoying for backing simple CRUD apps.

it looks like the issues for the Yesql repo are disabled now, by the maintainer was getting quite snarky and rude about the community's assumption that PRs would be reviewed, bugs fixed, feedback taken on board etc. The general consensus among the frustrated community was "just use HugSQL". I did appreciate the Yesql README file though.

I have some example code[1] which demonstrates the usage of HugSQL, an alternative to HoneySQL and yesql. After some evaluation, HugSQL seemed to be the best choice but unfortunately this is a toy project and I don't remember the exact reasons for going in that direction.

https://github.com/bschwind/api-starter-kit/tree/master/apps...

EDIT: Actually I think the goal if I was to add more complex queries would be to use HugSQL for static, templated queries, and HoneySQL for highly dynamic queries that need conditions added or altered depending on input from other sources.

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

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

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.

That makes sense. Thanks for the reply!

see also.

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 --__--

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

hiccup<sql> was the first thing that came to mind. Also reminds me of http://will.thimbleby.net/misc/

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

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

If you use postgresql, check out: https://github.com/nilenso/honeysql-postgres/blob/master/REA...

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

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

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=xwi2xxIXr8U

Does anything comparable exists for Python?

I created this which is in a slightly similar spirit: https://github.com/cjauvin/little_pger

isn't this just korma?

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

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

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

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

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.

Reply on news.ycombinator.com