6 ms·
Writing Composable SQL Using Knex and Pipelines
- bearjaws 2y agoUsed 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.
- hu3 2y agoThanks for sharing your experience. Have you tried https://kysely.dev https://kysely.dev ? I'm interested in using a SQL builder in a new project and I'm undecided between knex and kysely.
- naranha 2y agoNot 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.
- hu3 2y agoThat was my impression too. Glad to have it confirmed. Thank you.
- williamdclt 2y agoI'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.
- bearjaws 2y ago> 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.
- thrw42A8N 2y agoI have never had any issues like this, and the type safety provided by Kysely has saved me a lot of bugs and potentially very serious issues.
- bearjaws 2y agoI 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.
- hu3 2y agoThe project will be TypeScript so it seems like a no-brainer. Thank you.
- phpnode 2y agokysely is an excellent successor to Knex, strongly recommended. Pair it with something like https://github.com/kristiandupont/kanel https://github.com/kristiandupont/kanel to generate types from your schema.
- weeksie 2y agoI'm using kysely with a database that has quite a few complex functions, etc. Lots of queries that require CTEs and so on. It's fantastic. Type safety with the codegen is amazing and catches a tremendous amount of simple bugs and typos which are super easy to make in raw SQL strings. 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.
- hn_throwaway_99 2y ago> 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).
- weeksie 2y agoI've been at it for a bit over 25 years as well and have worked on everything from telco billing projects to complex consumer applications. Query builders and ORMs are not the same thing, and it's odd that you conflate them. 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.
- tengbretson 2y agoThe decision not to use sails.js may have saved your company millions of dollars.
- evantbyrne 2y agoIgnoring mainstream js trends seems to be a safe bet in general.
- natpalmer1776 2y agoHere I was wondering how you managed to use K’nex (toy) to write code lol
- MrMcCall 2y agoI was fully ready to have my mind blown, but am kindof perversely happy that no one's that damn smart :-)
- nom 2y agowell, there is this: http://knexcomputer.blogspot.com http://knexcomputer.blogspot.com
- btown 2y agoWell, now that you mention it... https://www.youtube.com/watch?v=rdT1YT9AOPA https://www.youtube.com/watch?v=rdT1YT9AOPA
- anonzzzies 2y agoWe 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.
- h1fra 2y agoUnrelated 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.
- larodi 2y agoI 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...
- 9rx 2y ago> but it would not be okay for so many ORM people to actually have properly bound SQL in-place. 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.
- MathMonkeyMan 2y ago> In theory, you could parse the SQL and build up some kind of AST on which compatibility could be built. This is an interesting problem that I'd like to learn more about. Have you read anything about it?
- ajfriend 2y agoYou can compose SQL with https://ibis-project.org/tutorials/ibis-for-sql-users https://ibis-project.org/tutorials/ibis-for-sql-users, which is using https://github.com/tobymao/sqlglot https://github.com/tobymao/sqlglot to parse the SQL under the hood. As an alternative to parsing the SQL yourself, DuckDB's https://duckdb.org/docs/api/python/relational_api 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/eea0795546c7c44f1c24ab0560a846b9 https://gist.github.com/ajfriend/eea0795546c7c44f1c24ab0560a...
- mythz 2y agoI've also just released a type safe, SQL-like parameterized TypeScript/JS ORM for SQLite, MySQL and PostgreSQL. I wrote it because I couldn'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. It's also highly composable as SQL expressions can be embedded within SQL Builders which can themselves be embedded within other Query Builders. https://litdb.dev https://litdb.dev
- damidekronik 2y agoWhat's missing from https://kysely.dev/ https://kysely.dev/?
- mythz 2y agoLooks pretty good, but APIs look more stringly typed and APIs don't look natural, I prefer using SQL expressions with typed references. 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.
- fbn79 2y agoI 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
- poxrud 2y agoI've used Knex, ActiveRecord and many other ORM'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/builder's syntax. I've reached the point now where it'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.
- hn_throwaway_99 2y agoYou're being downvoted, but I completely agree. In the long run I believe most ORMs end up being a time sink. I like to put it that "they make the easy thing easy, and they make the hard stuff much harder". There are tons of operational benefits to using raw SQL. 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-bf410349856c 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.
- weeksie 2y agoSlonik does _not_ make it possible (let alone trivial) to write composable queries. You're just doing string concatenation and string concatenation will bite you when things get complex enough. 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.
- hn_throwaway_99 2y ago> Slonik does _not_ make it possible (let alone trivial) to write composable queries. You're just doing string concatenation and string concatenation will bite you when things get complex enough 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.
- maciejgryka 2y agoI’d encourage everyone, who finds this appealing to check out how Ecto works in Elixir. It’s all functional & immutable goodies and pipelines are built into the language and idiomatic. 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.
- pictur 2y ago[dead]
- deleted 2y ago[deleted]