Hacker News Reader

Comment by christophilus | Hacker News Reader

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

View on HN

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.

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.

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

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.

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.

Reply on news.ycombinator.com