Writing Composable SQL Using Knex and Pipelines
lackofimagination.org
lackofimagination.org
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...
It's okay... sometimes. But, as what the article is about, more often than not you need to start composing that SQL. SQL does not deal well with composability natively. Which means you need to bring your own solution to work around its limitations.
In theory, you could parse the SQL and build up some kind of AST on which composition could be built. Or, you could write a set of functions in the application language which somewhat resemble SQL, as demonstrated in the article, that build the same AST. It turns out the latter is considerably easier to implement.
This is an interesting problem that I'd like to learn more about. Have you read anything about it?
As an alternative to parsing the SQL yourself, DuckDB's https://duckdb.org/docs/api/python/relational_api allows you to compose SQL expressions efficiently and lazily, which I've used when playing around with things like https://gist.github.com/ajfriend/eea0795546c7c44f1c24ab0560a...
not sure what you really mean by this one. I've seen quite complex SQL code, pages long, not PL/SQL , but SQL with intersects, CTEs, recursion and all... and it composes.
it actually composes OK and on a very high level where reading skills similar to those needed to read math crypto-text are needed. is not easy, but the power you get from the declarative writing can never be matched by functional language in terms of readability and concise length. is the same with regexp - once you master it you never go back to writing for-loops slicing commas.
i don't really take this argument that you want a little ORM here and there to save time implementing CRUDs, but then you invest heavily into describing the AST of the SQL by means of .dot.dot.dot OOP composition, which eventually is "translated" (transpiled if u want) into SQL again. So basically you need to understand structure on the ORM API level, but then still understand it all (for debugging) on SQL level. Seems doing the same two times.
Besides not all queries are equally used statistically, and it is apparent you are going to want to optimize the critical stuff, while the trivial stuff - well you can compose CRUDs with simple join+binding and it's the same thing timewise.
The same as what everyone has meant, including the article. The naive solution is to simply write out every nearly the same query you need, but that starts to become difficult to maintain once you have more than a small handful of queries. More realistically you are going to want to introduce composition in a practical setting.
The SQL solution is views, but they are complicated by being dependent on runtime state. There is a lot of complexity involved in ensuring that everything is in the right state. While that may be a decent tradeoff for some applications, the types of applications that lean on these query builders tend to be of the type where they are trying to prove a business hypothesis and whatever gets there the fastest is the tradeoff that needs to be made. A set of functions in the application language that somewhat resemble SQL, which output a SQL query, are a lot easier to implement.
The alternative is to compose queries at "compile time". In the simplest case you might get away with string concatenation, but this too quickly becomes error prone at scale. While many languages of this nature are designed such that all concatenations are valid for this very reason, SQL is not. An innocuous and perfectly valid modification to a query can easily invalidate what was a previously valid concatenation. More realistically you are going to want to build an AST so that properties can be modified without reliance on exacting syntax. You can parse SQL to give you that AST, or you can go back to the aforementioned set of functions to generate the AST. The latter is considerably easier to implement.
Each approach has pluses and minus. As with anything, you have to pick your tradeoffs. But it is no surprise that a lot of developers choose the easy tradeoff, especially when a lot of those developers are developing systems where what is easy/fast is the most important tradeoff, not knowing if what they are writing will ever get used. Theoretical engineering perfection is moot if there is no customer to use the product of that engineering.
do you have an actual security/perf/DX issue w the knex "fluent api" demonstrated in TFA or are you just voicing a stylistic preference?
There isn't anything that can change remotely that would break the local code.
ORMs being in charge of table structure and queries give them an overhead view that helps deal with changes like that. Not perfect by any means, since you have to change the ORM queries anyway, but it's a little bit better since the model definition provides an abstraction for it, and since the queries are in the local language there are refactoring tools to help find and do the changes.
> "writing HTML mixed with JS" is okay for React
There's a third piece to frontend: CSS. There are additional abstractions on top of it in React-world to help limit its scope, to help prevent similar "break something somewhere else" problems, such as CSS Modules.
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.
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.
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.
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 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.
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.
It's also highly composable as SQL expressions can be embedded within SQL Builders which can themselves be embedded within other Query Builders.
The query builder also looks coupled to execution, litdb query builders and aren't coupled to a driver implementation, i.e. they're just used to generate SQL with parameters that could then be executed with any driver.
The author of the slonik library for Postgres wrote this a couple years ago (note the slonik library also makes it trivial to write composable SQL statements): https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41.... I was working on a project where I had originally started using Knex, read this article and loved it, and ripped out everything to switch to slonik over a weekend. I'm so glad I did, it ended up being a huge benefit.
There is also a big difference between an ORM and a query builder. Knex (and kysely, which is the only thing I'd use these days) allows you to write SQL that's just as complex as anything you'd write raw, complete with escape hatches if you need them. The criticisms of ORMs tend to be spot on, they are nice until they run into a wall, but that same thing simply does not apply to a robust query builder.
In practice, I find this to be potayto/potahto. At the end you need the thing to output SQL that is going to run, and I've had a lot more problems trying to get Knex to output the SQL that I know works vs dealing with any string composition issues in Slonik.
More importantly, at the end of the day, when it comes to DB operations, all of the information you get from the DB is going to be output/logged in SQL. There is no way getting around the fact that you need to know SQL well to manage a DB. Knex just becomes another layer that you need to translate to and from ("the leakiest of abstractions" as I like to say). I'd rather focus my effort on becoming an expert in SQL than yet another popular-library-of-the-month.
I know your pain of translating SQL back to the ORM language, but what similar difficulty do you have for Knex? You have a query, and it’s isomorphic to the Knex query usually. There are a few things Knex doesn’t do, but it has the “raw” get-out-of-jail-free card for making part of the query just a string without sacrificing anything else.
Every project I’ve ever worked on has many closely related complex queries and writing them out as strings (even with placeholders) becomes either perilous as you write complex string building logic, or tediously repetitive.