Hacker News Reader

Comment by bearjaws | Hacker News Reader

Used knex before typescript was a thing, it was such a life saver.

Everytime an ORM conversation would come up, I would bat it down, especially at the time where Bookshelf (now 100% dead) and Sails.js were popular.

We were all in on knex and it was such a life saver, especially migrations.

The only custom thing we did was an extension that would run explain on queries and flag anything that had a query cost over some value (I forget what it was).

Made finding new queries that were unperformant really easy.

The composability was nice, but we didn't need to use it all that much. It made one of our services that was essentially a query builder very easy to build. Something like "Find me patients that need X work Y weeks from now, now add a filter for medication type..." was easy to script out.

View on HN

Thanks for sharing your experience.

Have you tried https://kysely.dev ?

I'm interested in using a SQL builder in a new project and I'm undecided between knex and kysely.

Not OP, but I have converted a couple of projects from knex to kysely recently. With Typescript kysely is much better. With kysely-codegen you can generate typescript types for a pre existing schema too.

That was my impression too. Glad to have it confirmed. Thank you.

I'm finding all these SQL builders so painful to use. They closely match the syntax of SQL so you do need to know SQL, but now you also need to know the SQL builder's syntax. It's friction for juniorer devs to learn SQL. It's also now much more difficult to trace a SQL query back to the originating code.

They have two advantages: building SQL statements programmatically is cleaner than string concatenation, and they allow typing inference. The former is not so often necessary (and even when it is, it's often simple enough that string concatenation is _alright_). The latter is what makes me hesitate as a typing ayatollah, but even then I don't think it's worth it (I'll take the explicit typing, with the risk that we get it wrong, which would likely-although-not-surely be caught in tests).

I still need something for migrations and query execution, but I would only reach for query building in like 1% of cases.

> They closely match the syntax of SQL so you do need to know SQL, but now you also need to know the SQL builder's syntax.

I tend to agree, I recently tried Slonik and found it fun to use, but when I tried to incroporate it into an existing project I ran into ESM / TS issues (even with interop on, annoyingly). I also want to give pg-typed a try.

The only reason I would recommend it is I feel many systems tend to have some sort of dynamic query builder "UI" over time, especially in enterprise spaces, and a query builder solves these problems very well.

I have never had any issues like this, and the type safety provided by Kysely has saved me a lot of bugs and potentially very serious issues.

I've had issues when needing to use sql functions.

What issue? Kysely has an example to do this.

This was one of the problems I ran into https://github.com/kysely-org/kysely/issues/664, not being able to use functions inside onDuplicateKeyUpdate

Basically no ORM supports such specific functionality of a specific DBMS. In any case you'd have to use raw SQL. I don't see the problem, this is expected.

I don't see how not using Kysely would make it better overall - you simply write the raw SQL yourself as you would without it; but you gain so much type safety by using it.

One approach I've been enjoying recently in my personal use is to write a light wrapper around DuckDB to enable composable SQL snippets. Essentially like what I have here https://gist.github.com/ajfriend/eea0795546c7c44f1c24ab0560a..., but without the `|` syntax.

You're still writing SQL, so you don't need to learn a new syntax, but I find it more ergonomic for quick data exploration. I also have an easier time writing SQL from memory than I do writing the equivalent Pandas code.

I have used it in personal projects, it definitely feels like the succesor to it if you use TypeScript. The type safety at the schema level is fantastic.

I haven't used it in a professional setting, but if I was evaluating between Knex and Kysely I would use Kysely simply for the additional type safety, and it appears maintained.

The project will be TypeScript so it seems like a no-brainer. Thank you.

kysely is an excellent successor to Knex, strongly recommended. Pair it with something like https://github.com/kristiandupont/kanel to generate types from your schema.

I'm using kysely with a database that has quite a few complex functions, etc. Lots of queries that require CTEs and so on. It's fantastic. Type safety with the codegen is amazing and catches a tremendous amount of simple bugs and typos which are super easy to make in raw SQL strings.

People talking about writing raw SQL have never maintained a large project. Once you start string concatting queries and trying to remember which table aliases are which you're halfway to writing a custom, buggy query builder without type safety. It's a trap for young players.

> It's a trap for young players.

As someone with 25 years of experience using lots of ORMs and query builders in both Java and JavaScript/Typescript, I believe the exact opposite to be true. There are libraries now that let you write composable, safe SQL with type guarantees (I am a big fan of Slonik, but there are other libraries).

Every single time I've seen an ORM or query builder used for application software, eventually I see it becoming a big headache for operations. It usually makes debugging and and performance investigations more difficult, and I so often see developers "fighting with the tool" when they're just trying to get it to output the SQL they want it to.

In my experience, people who are fans of ORMs and query builders never have had to deal with significant scale and the operational difficulties that come with it (and I'm not talking about "Google scale", either, I'm just talking about a reasonably well known consumer site with moderately heavy traffic at times).

I've been at it for a bit over 25 years as well and have worked on everything from telco billing projects to complex consumer applications. Query builders and ORMs are not the same thing, and it's odd that you conflate them.

The project that I happen to be on now has a mid sized db (~70 ish) tables with quite a few custom functions and complex queries. I have been untangling a mess of slonik queries and there's an objective difference in the quality of the code and the maintainability between that and the kysely code that's replacing it.

The people I see drift toward things like slonik tend to be journeyman level developers and they often end up over their skis. Smart folks a lot of the time, but inexperienced.

The decision not to use sails.js may have saved your company millions of dollars.

Ignoring mainstream js trends seems to be a safe bet in general.

Reply on news.ycombinator.com