Hacker News Reader

Comment by poxrud | Hacker News Reader

I've used Knex, ActiveRecord and many other ORM's and query builders. At some point, beyond basic queries you start wasting time coming up with SQL statements and then having to convert them to your ORM/builder's syntax. I've reached the point now where it's just easier to stick with writing SQL and having a library that removes the possibility of sql injections. My current stack is postgres.js and dbmate for migrations.

View on HN

Knex isn’t an ORM, it’s a query builder. (Though the closely related library Bookshelf.js is an ORM).

I know your pain of translating SQL back to the ORM language, but what similar difficulty do you have for Knex? You have a query, and it’s isomorphic to the Knex query usually. There are a few things Knex doesn’t do, but it has the “raw” get-out-of-jail-free card for making part of the query just a string without sacrificing anything else.

Every project I’ve ever worked on has many closely related complex queries and writing them out as strings (even with placeholders) becomes either perilous as you write complex string building logic, or tediously repetitive.

You're being downvoted, but I completely agree. In the long run I believe most ORMs end up being a time sink. I like to put it that "they make the easy thing easy, and they make the hard stuff much harder". There are tons of operational benefits to using raw SQL.

The author of the slonik library for Postgres wrote this a couple years ago (note the slonik library also makes it trivial to write composable SQL statements): https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41.... I was working on a project where I had originally started using Knex, read this article and loved it, and ripped out everything to switch to slonik over a weekend. I'm so glad I did, it ended up being a huge benefit.

Slonik does _not_ make it possible (let alone trivial) to write composable queries. You're just doing string concatenation and string concatenation will bite you when things get complex enough.

There is also a big difference between an ORM and a query builder. Knex (and kysely, which is the only thing I'd use these days) allows you to write SQL that's just as complex as anything you'd write raw, complete with escape hatches if you need them. The criticisms of ORMs tend to be spot on, they are nice until they run into a wall, but that same thing simply does not apply to a robust query builder.

> Slonik does _not_ make it possible (let alone trivial) to write composable queries. You're just doing string concatenation and string concatenation will bite you when things get complex enough

In practice, I find this to be potayto/potahto. At the end you need the thing to output SQL that is going to run, and I've had a lot more problems trying to get Knex to output the SQL that I know works vs dealing with any string composition issues in Slonik.

More importantly, at the end of the day, when it comes to DB operations, all of the information you get from the DB is going to be output/logged in SQL. There is no way getting around the fact that you need to know SQL well to manage a DB. Knex just becomes another layer that you need to translate to and from ("the leakiest of abstractions" as I like to say). I'd rather focus my effort on becoming an expert in SQL than yet another popular-library-of-the-month.

1. You certainly need to know SQL 2. Composition is very different than concatenation and the differences become apparent as a project increases in complexity 3. I'd recommend checking out the current SOTA, especially with kysely (or Ecto if you ever get into Elixir projects)

I've used kysely extensively on a project built using sst.dev. I can't paste proprietary code, but I remember my head spinning over a particularly gnarly query (e.g. a bunch of window functions, aggregations, etc.) It was a million times more difficult to grok reading this in kysely's syntax than SQL.

Reply on news.ycombinator.com