I really fail to understand why "writing HTML mixed with JS" is okay for React, and mixing regex with what-not is okay for...like...everyone, but it would not be okay for so many ORM people to actually have properly bound SQL in-place. Really boggles me down.
Besides - I've seen one of the best possible schema-on-the-fly-on-top-of-RDBMS systems that can ever exist, is in production, and even there it is much better every so often to fallback to either the Virtual SQL layer or to hardcore SQL when performance is needed. In all other occasions - well okay, but unless you actually have a proper MOP (meta-object-protocol or entity/relationship schema, name it what you like) in-place/available, the DB schema becomes the MOP and you are back to square one...
We use Knex a lot because it can be used dynamically. All newer systems are based on typescript, which is great, but not dynamic unless you do code gen. And that's quite a lot slower because of the compilation steps in our experience. With Knex we can have users building a massive query dynamically while seeing intermediate results immediately.
Unrelated but started using Kysely after struggling too much with knex and prisma, that's a breath of fresh air. Excellent native typing, good tooling, and no missing features.
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.
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.
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.
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.
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.
Besides - I've seen one of the best possible schema-on-the-fly-on-top-of-RDBMS systems that can ever exist, is in production, and even there it is much better every so often to fallback to either the Virtual SQL layer or to hardcore SQL when performance is needed. In all other occasions - well okay, but unless you actually have a proper MOP (meta-object-protocol or entity/relationship schema, name it what you like) in-place/available, the DB schema becomes the MOP and you are back to square one...
reply