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.
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.
> 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.
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, which uses template strings for SQL and has recently come a long way with its strong typing support.
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
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.
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.
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.
I've also tried:
https://knexjs.org/
https://www.npmjs.com/package/sql-template-strings ("out of date" since like 2016? 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?)