Writing Composable SQL Using Knex and Pipelines

I really fail to understand why &quot;writing HTML mixed with JS&quot; 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.<p>Besides - I&#x27;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&#x2F;relationship schema, name it what you like) in-place&#x2F;available, the DB schema becomes the MOP and you are back to square one...

Used knex before typescript was a thing, it was such a life saver.<p>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.<p>We were all in on knex and it was such a life saver, especially migrations.<p>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).<p>Made finding new queries that were unperformant really easy.<p>The composability was nice, but we didn&#x27;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 &quot;Find me patients that need X work Y weeks from now, now add a filter for medication type...&quot; was easy to script out.

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&#x27;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.

I’d encourage everyone, who finds this appealing to check out how Ecto works in Elixir. It’s all functional &amp; immutable goodies and pipelines are built into the language and idiomatic.<p>Definitely looks weird at first glance (and Ecto is kinda weird even if you’re familiar with Elixir), but it’s such a joy to use once you grok it.

I&#x27;ve also just released a type safe, SQL-like parameterized TypeScript&#x2F;JS ORM for SQLite, MySQL and PostgreSQL. I wrote it because I couldn&#x27;t find any other ORMs with type-safe query builders where joined tables and column references are type checked against my data models to enable design-time type checking and safe refactoring.<p>It&#x27;s also highly composable as SQL expressions can be embedded within SQL Builders which can themselves be embedded within other Query Builders.<p><a href="https:&#x2F;&#x2F;litdb.dev" rel="nofollow">https:&#x2F;&#x2F;litdb.dev</a>

I&#x27;ve used Knex, ActiveRecord and many other ORM&#x27;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&#x2F;builder&#x27;s syntax. I&#x27;ve reached the point now where it&#x27;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.

Unrelated but started using Kysely after struggling too much with knex and prisma, that&#x27;s a breath of fresh air. Excellent native typing, good tooling, and no missing features.

I like knex, but would love it more if all methods does not mutates the original data structure. A pure version of knex would be great

Here I was wondering how you managed to use K’nex (toy) to write code lol