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.
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.
> 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.
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?
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.
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.
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.
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.
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'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:
(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:
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!)
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
--__--
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).
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