from Hacker News

Show HN: Kysely, a type-safe SQL query builder for TypeScript

by fernandohur on 6/21/23, 12:27 PM with 63 comments

• by sdcoffey on 6/21/23, 3:13 PM

  We've been using this in production for several months, and I'm a huge fan. Unopinionated but incredibly helpful. Big ups to the authors!

• by yashap on 6/21/23, 2:32 PM

  Have recently started using it, big fan, especially in concert with kysely-codegen (generate Kysely types from DB schema). The combo is similar to Prisma, but IMO better in practice - Prisma is too limited, too little support for any sort of custom/non-standard types. For example, using PostGIS with Prisma is a terrible experience, but fine with Kysely.

  Also, a query builder with generated types that match the DB schema has many of the advantages of an ORM, with IMO few of the disadvantages. I’m not spending so much time trying to figure out how to translate what I want to do in SQL into the ORM language, as it’s basically a thin, more type safe and composable layer over SQL.

• by beders on 6/21/23, 4:46 PM

  If you want to run SQL, write SQL.

  The majority of your SQL will not require building dynamic where clauses or (dog forbid) dynamic joins.

  Having your SQL as plain statements with simple placeholders (to create safe prepared statements) is the saner approach.

  Not only can you pluck them into your favorite SQL tools and analyzers, but you will not be surprised by terribly performing queries, because you created them dynamically without understanding their complexity.

  We've learned the hard lessons decades ago by misusing ORMs. While using alternative SQL syntax builders is avoiding many of those pitfalls, you will still inherit complexity by translating a SQL dialect to the builder pattern.

  It is not worth it IMHO.

• by paraph1n on 6/21/23, 2:49 PM

  How does it compare to zapatos?

  https://jawj.github.io/zapatos/

• by torte on 6/22/23, 1:30 AM

  Have used it for a smaller project for a bit now and I really like it as well. Concise code and I can still map it to actual SQL in my brain.

  What has been a bit non-intuitive for me though is the expression builder since the latest major version of kysely. When writing queries with `OR` conditions it always takes me a while to wrap my head around it again. It is also challening to make this easily readable with lots of dynamic `OR` conditions and I usually end up with a wrapper function which returns the array for the statements passed into the `or(` block. Could be improved in my opinion, otherwise a great tool

• by paulbares on 6/22/23, 4:35 AM

  I am building squashql-js for a slightly different use case (database agnostic SQL-like Typescript query builder among others) but Kysely and pypika (for Python) have been a great source of inspiration.

  https://github.com/squashql/squashql/blob/main/documentation...

• by bzzzt on 6/21/23, 2:29 PM

  This is only partially type-safe. Having a few SQL keywords abstracted into methods doesn't deliver a lot of value. I've found some value in type-safe mappings between database fields and language fields, at least you can directly see the problems in your code if some entity class has been changed. String-based field references will only fail at runtime so you need 100% unit tests to refactor with confidence.

• by peterisdirsa on 6/21/23, 2:22 PM

  For Java, JOOQ is great and offers better type safety than this. Additionally, JOOQ generates data model classes from the DB schema.

• by idbehold on 6/21/23, 4:16 PM

  See now this is what I thought that "Selectric Typeballs" post [1] was going to be. I think it would make for a better name than "Kysely" if I'm being honest.

  [1] https://news.ycombinator.com/item?id=36406352

• by LewisJEllis on 6/21/23, 7:30 PM

  Love Kysely, been rooting for it ever since it was first released. Paired with kysely-codegen, it's my favorite TS <> SQL interface. Does just enough, but not too much.

  Had lots of good experiences working with Knex.js over the years, and Kysely is the TS-native spiritual successor to Knex.

• by afavour on 6/21/23, 4:04 PM

  Has anyone used this and compared to the types provided by Knex? I’m largely very happy with Knex but when you start dealing with subqueries and the like the types fall apart. Suspect this kind of thing is pushing TS types to their limit, though.

• by sickcodebruh on 6/21/23, 4:45 PM

  I typed up a comment saying how much I’d love to drop this in alongside my Prisma code so I can keep using the ORM for simple things and have the option to drop into SQL as needed. It ended asking if anyone knew whether such a thing was possible. I decided to check the docs one more time and… here it is! https://github.com/valtyr/prisma-kysely

  I’m excited. I enjoy Prisma but SQL can be so expressive. Looking forward to trying this.

• by eevo on 6/21/23, 4:41 PM

  Doesn't seem like it performs result set nesting on joins? For example with the one to many of owner -> pet, I'd like the results to look something like `{ person: Person, pets: Pet[] }[]`. Knex doesn't do this either (afaict) - wrote a few "deep" queries with some convoluted lodash to group things up but mostly gave up and just live with raw resultsets.

  I guess I still prefer that to a full on ORM, but that's really the one missing feature I want from these SQL query builder libraries

• by threatofrain on 6/21/23, 5:23 PM

  Another "almost" like SQL library that I've heard floating around:

  https://github.com/drizzle-team/drizzle-orm

  I think I've reached the limits of Prisma and embarrassingly I'm thinking about ripping it out. The benefits of not having to constantly reference your DB schema and having IDE guidance is not matching up to the idiosyncrasy and incomplete DB support of Prisma.

• by jadbox on 6/21/23, 8:20 PM

  Kysely looks interesting, although I really like being able to write the query directly so I'm able to test the raw query directly. PgTyped is an interesting library I've used in the past where queries can be written as regular template strings that gets checked and responses become types. https://pgtyped.dev/docs/ts-file

• by ghnws on 6/21/23, 4:02 PM

  Fun fact: "Kysely" is "query" in Finnish.

• by igalklebanov on 6/21/23, 6:45 PM

  Hey :wave:

  Igal from Kysely here (I did not create it, Sami did).

  Our site is a constant WIP. We've recently revamped "Getting Started" and added a lot of examples. If you can't find something there, check the API docs site or JS docs in your IDE - everything is documented.

  We respond quite fast on discord if you've got any questions.

  Feel free to ask me questions here too. :)

• by programmarchy on 6/21/23, 5:23 PM

  Currently using knex, but this looks like a nice improvement. Getting the types via codegen is brilliant.

• by igalklebanov on 6/21/23, 7:52 PM

  Huge shoutout to our community, bloggers and youtubers!

  https://github.com/kysely-org/awesome-kysely

• by gareve on 6/21/23, 5:43 PM

  for the db schema definition for this tool, where does the source of truth lives?

  I'm trying to think what happens when a column gets deleted or added in the prod, ci, or dev db tier. Ideally those db schema changes should happen at the same time but real life doesn't work like that.