Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Honey SQL – SQL as Clojure data structures

github.com

Post reply on HN
Print view View original

11–20 of 31 posts

Re: Honey SQL – SQL as Clojure data structures

#11

Post by brandonbloom »

joncampbelldev wrote: post #7
christophilus wrote: post #6

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 cl…

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

Re: Honey SQL – SQL as Clojure data structures

#14

Post by 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!)

Re: Honey SQL – SQL as Clojure data structures

#15

Post by yogthos »

tosh wrote: post #5

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

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

Re: Honey SQL – SQL as Clojure data structures

#16

Post by hyperpape »

brandonbloom wrote: post #11
joncampbelldev wrote: post #7

Earlier quoted context omitted.

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 cl…

> 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 res…

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?

Re: Honey SQL – SQL as Clojure data structures

#18

Post by weavejester »

lilactown wrote: post #14

(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 bi…

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.

Re: Honey SQL – SQL as Clojure data structures

#19

Post by brandonbloom »

hyperpape wrote: post #16
brandonbloom wrote: post #11

Earlier quoted context omitted.

> 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 res…

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.

Re: Honey SQL – SQL as Clojure data structures

#20

Post by auvrw »

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

Post reply on HN