6 ms·

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.

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

joncampbelldev 9y ago
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
- brandonbloom 9y ago
  > 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.
  - hyperpape 9y ago
    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?
    - brandonbloom 9y ago
      It's explained a bit here: https://www.postgresql.org/docs/9.6/static/plpgsql-implementation.html 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.
ledgerdev 9y ago
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 9y ago
  > 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.
jsofra 9y ago
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 https://github.com/layerware/hugsql
jdminhbg 9y ago
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.
lsh 9y ago
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.
bschwindHN 9y ago
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/clojure/src/api_starter_kit 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.

joncampbelldev 9y ago

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

brandonbloom 9y ago
> 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.
- hyperpape 9y ago
  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?
  - brandonbloom 9y ago
    It's explained a bit here: https://www.postgresql.org/docs/9.6/static/plpgsql-implementation.html 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.

ledgerdev 9y ago

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 9y ago
> 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.

jsofra 9y ago

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 https://github.com/layerware/hugsql

jdminhbg 9y ago

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.

lsh 9y ago

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.

bschwindHN 9y ago

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/clojure/src/api_starter_kit 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.