| ▲ | hu3 a day ago |
| Thanks for sharing your experience. 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. |
|
| ▲ | williamdclt a day ago | parent | next [-] |
| 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. |
| |
| ▲ | ajfriend a day ago | parent | next [-] | | One approach I've been enjoying recently in my personal use is to write a light wrapper around DuckDB to enable composable SQL snippets. Essentially like what I have here https://gist.github.com/ajfriend/eea0795546c7c44f1c24ab0560a..., but without the `|` syntax. 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. | |
| ▲ | bearjaws a day ago | parent | prev | next [-] | | > 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 a day ago | parent | prev [-] | | I 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. | | |
| ▲ | plopz a day ago | parent [-] | | I've had issues when needing to use sql functions. | | |
| ▲ | thrw42A8N a day ago | parent [-] | | What issue? Kysely has an example to do this. | | |
| ▲ | plopz a day ago | parent [-] | | This was one of the problems I ran into https://github.com/kysely-org/kysely/issues/664, not being able to use functions inside onDuplicateKeyUpdate | | |
| ▲ | thrw42A8N a day ago | parent [-] | | Basically no ORM supports such specific functionality of a specific DBMS. In any case you'd have to use raw SQL. I don't see the problem, this is expected. 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. |
|
|
|
|
|
|
| ▲ | naranha a day ago | parent | prev | next [-] |
| 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. |
| |
| ▲ | hu3 a day ago | parent [-] | | That was my impression too. Glad to have it confirmed. Thank you. |
|
|
| ▲ | bearjaws a day ago | parent | prev | next [-] |
| 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. |
| |
| ▲ | hu3 a day ago | parent [-] | | The project will be TypeScript so it seems like a no-brainer. Thank you. |
|
|
| ▲ | weeksie a day ago | parent | prev | next [-] |
| I'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 a day ago | parent [-] | | > 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 a day ago | parent [-] | | I'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. |
|
|
|
| ▲ | phpnode a day ago | parent | prev [-] |
| 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. |
