[exevp]

tl;dr: thank god it finally has been done.

Long version: i've been seriously frustrated with the state of ORM (in Javascript in particular) for years.

Javascript ORM are nice and handy if all you're doing is simple CRUD stuff. If you're starting with more complex relational queries (we're using an RDBMS so why wouldn't we?) you quickly reach the limits of what the ORM can map. If you start doing more complex aggregations or stuff like window functions and the likes, you most certainly have to fallback to raw queries, usually rendering the whole mapping function of the ORM completely useless.

Also projects like Knex.js (or for example HQL in the Java world) look nice at first but are mostly useless IMHO because they just replace SQL with another syntax you have to learn. Why stay with the language everybody familiar with RDBMS can speak if you can invent some useless abstraction, right? And please don't tell me you want to support multiple RDBMS in the same codebase. How often is this really an important use-case?

I really loved the way MyBatis did this in Java: instead of mapping tables to objects, mapping result sets to objects and leaving the full power of SQL to the developer.

Always wanted (and actually started something almost the same as you did some weeks ago) to basically do MyBatis in Javascript and never had the time to.

Thanks for getting it started.

[areactnativedev]

Very surprised by the no value you attach to Knex, I'm curious to get your view on the values I see in using it for a few years now.

I feel at ease with SQL and like to get as close to it as possible in my Node service. But Knex still appears to be highly valuable to me to, for instance, not care about managing DB connections, at least until they become critical for my use-case.

Not care about sanitising inputs and protecting myself from SQL injections.

Have more readable and maintainable code in my repositories than SQL in plain strings as a default. Yes I have some raw queries but 98% of my queries are easy to follow knex chains.

Not care about creating and maintaining code for migrations. Running them in transactions, keeping track of and running only the ones needed, ... so happy I didn't have to re-invent that and be the responsible of it never ever failing in production.

[exevp]

> not care about managing DB connections, at least until they become critical for my use-case.

That's something the db driver usually does. E.g. when using Postgres, the pg library already comes with the connection pooling. Haven't looked into the implementation in Knex but i'd suspect they just use the Pool class of pg (https://node-postgres.com/features/pooling).

> Not care about sanitising inputs and protecting myself from SQL injections.

That's also not that much of a concern when just binding parameters.

> Have more readable and maintainable code in my repositories than SQL in plain strings as a default. Yes I have some raw queries but 98% of my queries are easy to follow knex chains.

Comes with the cognitive cost of maintaining another abstraction for SQL.

> Not care about creating and maintaining code for migrations.

That's actually the one feature which made me use Knex for years (just for the migration part of course :) ). I didn't use the schema builder functions mostly, just a bunch of `knex.raw` calls in the migration files. But for the benefits you mentioned (transactions, bookkeeping) it is really useful.

[baumandm]

IMHO one of the key use cases for query builders like Knex is that you can programmatically build up a query in a very clean fashion.

Otherwise you need to resort to workarounds like concatenating strings, or building the biggest possible query and using boolean toggles to disable parts of your query.

[slooonz]

I don't undertand your frustration. Virtually every ORM I know has a query builder which as a .raw() function that lets you do anything you want.

[Seb-C]

Thanks for your comment!

As you say, one of the main arguments for query-builders is allowing the support of different RDBMS. But in my experience it not only never happens, but the abstraction is never perfect and makes any unsupported edge case impossible to solve without hacking around.

There is so much that can be done with SQL that would result in a mess of inefficient spaghetti code...

[exevp]

Also one of the bigger reasons why i dislike SQL abstractions: you usually pick your RDBMS for a reason, because of some vendor-specific features, extensions or something. Abstracting SQL away makes end up with the smallest common denominator which defeats the purpose of choosing one specific RDMBS.

So whenever you have reasons to spend some thoughs on the choice of RDBMS, you'll probably not want to abstract SQL away.

[Jarwain]

We actually went with knex/objection specifically because there was a planned migration to postgres that it would simplify, and we'd be able to migrate different databases in stages.

Plus it was integrated into feathers, and knex simplifies hook-based query building logic.