3 ms·

How does HN receive SQL builders in general? I feel like most of us agree ORMs are typically a bad idea. I feel like that almost instantly leaves the need for "

How does HN receive SQL builders in general? I feel like most of us agree ORMs are typically a bad idea. I feel like that almost instantly leaves the need for "something" to take its place. In my experience, it's typically been a query builder like this. I've also tried: https://knexjs.org/ https://knexjs.org/ https://www.npmjs.com/package/sql-template-strings https://www.npmjs.com/package/sql-template-strings ("out of date" since like 2016? https://www.npmjs.com/package/sql-template-tag https://www.npmjs.com/package/sql-template-tag might be better) Are query builders an anti pattern? People who are doing serious/logic heavy stuff with SQL, how do you avoid a query builder (if at all?)

nullwarp 4y ago
I'm a big fan of them over traditional ORMs and use knex in quite a few projects at this point. I find them a really good balance between full on ORM and just writing out pure SQL queries. Definitely going to give Kysely a try on my next project
deleted 4y ago
[deleted]
crdrost 4y ago
There's nothing wrong with query builders, no antipattern. In many ways it's similar to preferring C where you can really feel the assembly-language underneath you even if you're not writing it. If you have written enough raw SQL you do a similar thing by convention... So for example if you look at my raw SQL queries you will notice that I usually only select specific columns with one-letter or two-letter table prefixes for disambiguation (because I hate “adding a column” being a breaking change! ... I am flexible on the name size but I like the freedom to make my table names long and expressive if feasible, group them with prefixes that relate related tables, etc). Then in the FROM, I only use JOIN and LEFT JOIN unless there's really no other way, and all my inner JOINs come before my left ones. All of those have AS statements renaming them to one-character prefixes too, and they have a clear ON condition that connects them to the above blob (so always "AS s WHERE s.whatever = ...") even though that makes the queries longer to refactor when you want to rearrange the joins (you often have to shift all the OFs down by one and reverse which side of the equality comes first or some nonsense). Subqueries should move up to a WITH or should be rewritten as LEFT JOINs if feasible. You want to use structure to guide a reader through this thing that could be complicated... That structure could be lexical structure in the SQL itself or it could be syntactic structure from a wrapping language, I don't care so much. The real problem is having one source-of-truth for the database schema, and that problem is just barely tractable with current languages, but I don't see anybody who does it right.
- hot_gril 4y ago
  > In many ways it's similar to preferring C where you can really feel the assembly-language underneath you even if you're not writing it. When you write C, the ASM below it is much more complex. When use a query builder, the SQL beneath is about the same length and complexity. It's more like transpiling Swift to Java.
giraffe_lady 4y ago
I think you'll see the whole range of responses, even some very reasonable defenses of ORMs for some cases. I've come to prefer a yesql type approach that parses your sql into functions and provides a mechanism for applying functions to the bound data before running and the returned data after. Keeps things nicely separated and you can unit test your sql functions as application code.
hot_gril 4y ago
I don't like query builders because SQL is actually easier to write and read even when you're not using advanced features, but I don't consider them a serious problem like ORMs.
zzzeek 4y ago
> how does HN receive SQL builders in general? I feel like most of us agree ORMs are typically a bad idea. I haven't observed that at all, my anecdotal impression is quite the opposite. Have you taken a poll?
hn_throwaway_99 4y ago
Glad to see this posted. I totally agree - I think Kysely is a cool project but I do think query builders ARE generally an anti-pattern. I'm a huge fan of slonik, https://github.com/gajus/slonik#readme https://github.com/gajus/slonik#readme, which uses template strings for SQL and has recently come a long way with its strong typing support. This blog post by the author of slonik outlines why he thinks query builders are anti-pattern, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41.... No, I'm not him, but I strongly agree with most of his opinions here.
dns_snek 4y ago
Query builders can be amazing if they make it easy to logically compose highly dynamic queries, just like you would do with any other code. I haven't tried Kysely or Knex but I really enjoy working with Ecto in Elixir. https://hexdocs.pm/ecto/dynamic-queries.html https://hexdocs.pm/ecto/dynamic-queries.html

nullwarp 4y ago

I'm a big fan of them over traditional ORMs and use knex in quite a few projects at this point. I find them a really good balance between full on ORM and just writing out pure SQL queries. Definitely going to give Kysely a try on my next project

deleted 4y ago

[deleted]

crdrost 4y ago

There's nothing wrong with query builders, no antipattern. In many ways it's similar to preferring C where you can really feel the assembly-language underneath you even if you're not writing it. If you have written enough raw SQL you do a similar thing by convention... So for example if you look at my raw SQL queries you will notice that I usually only select specific columns with one-letter or two-letter table prefixes for disambiguation (because I hate “adding a column” being a breaking change! ... I am flexible on the name size but I like the freedom to make my table names long and expressive if feasible, group them with prefixes that relate related tables, etc). Then in the FROM, I only use JOIN and LEFT JOIN unless there's really no other way, and all my inner JOINs come before my left ones. All of those have AS statements renaming them to one-character prefixes too, and they have a clear ON condition that connects them to the above blob (so always "AS s WHERE s.whatever = ...") even though that makes the queries longer to refactor when you want to rearrange the joins (you often have to shift all the OFs down by one and reverse which side of the equality comes first or some nonsense). Subqueries should move up to a WITH or should be rewritten as LEFT JOINs if feasible. You want to use structure to guide a reader through this thing that could be complicated... That structure could be lexical structure in the SQL itself or it could be syntactic structure from a wrapping language, I don't care so much. The real problem is having one source-of-truth for the database schema, and that problem is just barely tractable with current languages, but I don't see anybody who does it right.

hot_gril 4y ago
> In many ways it's similar to preferring C where you can really feel the assembly-language underneath you even if you're not writing it. When you write C, the ASM below it is much more complex. When use a query builder, the SQL beneath is about the same length and complexity. It's more like transpiling Swift to Java.

giraffe_lady 4y ago

I think you'll see the whole range of responses, even some very reasonable defenses of ORMs for some cases. I've come to prefer a yesql type approach that parses your sql into functions and provides a mechanism for applying functions to the bound data before running and the returned data after. Keeps things nicely separated and you can unit test your sql functions as application code.

hot_gril 4y ago

I don't like query builders because SQL is actually easier to write and read even when you're not using advanced features, but I don't consider them a serious problem like ORMs.

zzzeek 4y ago

> how does HN receive SQL builders in general? I feel like most of us agree ORMs are typically a bad idea. I haven't observed that at all, my anecdotal impression is quite the opposite. Have you taken a poll?

hn_throwaway_99 4y ago

Glad to see this posted. I totally agree - I think Kysely is a cool project but I do think query builders ARE generally an anti-pattern. I'm a huge fan of slonik, https://github.com/gajus/slonik#readme https://github.com/gajus/slonik#readme, which uses template strings for SQL and has recently come a long way with its strong typing support. This blog post by the author of slonik outlines why he thinks query builders are anti-pattern, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41.... No, I'm not him, but I strongly agree with most of his opinions here.

dns_snek 4y ago

Query builders can be amazing if they make it easy to logically compose highly dynamic queries, just like you would do with any other code. I haven't tried Kysely or Knex but I really enjoy working with Ecto in Elixir. https://hexdocs.pm/ecto/dynamic-queries.html https://hexdocs.pm/ecto/dynamic-queries.html