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.
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.
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.
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 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 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.
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.
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.
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).
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.