Kysely: TypeScript SQL Query Builder

(github.com)

236 points | by cal85 11 days ago

23 comments

  • zerace 11 days ago
    I love Kysely! I use it for a couple of production applications and it works quite well. Type support is better than Knex, and Kysely is really lightweight so I can use it in my projects without being concerned about performance issues.

    Really, when you look at options like this, you start to break them down into 3 distinct categories:

    1. Raw adapter usage - writing SQL. Performant, but can get tedious at scale, and weird to add types to.

    2. Knex/Kysely, lightweight query builders. Readable, composable, and support types well, but a step removed from the performance of (1). Some would argue (1) is more universally understandable, too, although query builders make things easy for those more-familiar with programming languages than query languages.

    3. Full-fledged ORMs like TypeORM, Sequelize, Prisma. Often require much more ecosystem buy-in and come with their own performance issues and problems.

    I usually choose 1/2 depending on the project to keep things simple.

    I have pretty much had no issue with it so far. The only thing that I would call out is that you _must_ run a migration initially to set things up, or your queries will hang. This has stumped me a few times (despite being obvious after-the-fact). It also interfaces really well with postgres, and has nice support for certain features (like jsonb).

    • robertn702 11 days ago
      You might also want to consider pgTyped (https://github.com/adelsz/pgtyped). It's supposed to make SQL and TS work together. I haven't gotten around to using it yet but I hear good things.
      • drpotato 11 days ago
        Can weigh in here, we use pgtyped heavily at work and it’s really good. There are some constraints with the type inference on complex queries, but it’s a decent trade off IMO.
        • LunaSea 10 days ago
          Do you have a bit more information regarding said limitation? I'm guessing that dynamic queries would of course not be suitable but any other edge cases you have encountered?
    • gigatexal 10 days ago
      I mostly do work on the Python side of things and SQLAlchemy is the de facto ORM there. I hate it. It’s heavy. Opinionated. The happy path seems to be doing things in sessions where joins foreign keys and things are evaluated in the app instead of at the DB level (one can define relationships in models for the ORM to understand but not define explicit foreign key relationships at the DB table level, wtf? Thereby doing joins based on common columns…) and yet I can’t fault ORMs for their ability to get you going fast and help you iterate very quickly.

      I recently had the chance to start a new side project and trying to go the pure SQL route which I love was so slow in terms of productivity. When I could just model the tables via SQLAlchemy I was able to get to the meat of the thing I was making much quicker. What I didn’t like was all the additional cognitive overhead but I gained DB agnostic ways of querying my data and could use say SQLite for testing and then say Postgres for staging or production by just changing a config whereas if I write pure SQL I might get into issues where the dialects are different such that that flexibility is not there.

      In the end I am very conflicted. Oh, some context I began my professional life as a DBA and my first love was SQL. I like writing queries and optimizing them and knowing exactly what they’re doing etc.

    • mirekrusin 11 days ago
      We use in prod variant of no 1. [0]. Why? Because:

      * it's sql

      * it's extremely lightweight (built on pure, functional combinators)

      * it allows us to use more complex patterns ie. convention where every json field ends with Json which is automatically parsed; which, unlike datatype alone, allows us to create composable query to fetch arbitrarily nested graphs and promoting single [$] key ie. to return list of emails as `string[]` not `{ email: string }[]` with `select email as [$] from Users` etc.

      * has convenience combinators for things like constructing where clauses from monodb like queries

      * all usual queries like CRUD, exists etc. and some more complex sql-wise but simple function-api-wise ie. insertIgnore list of objects, merge1n, upsert etc all have convenient function apis and allow for composing whatever more is needed for the project

      We resort to runtime type assertions [1] which works well for this and all other i/o; runtime type assertions are necessary for cases when your running service is incorrectly attached to old or future remote schema/api (there are other protections against it but still happens).

      [0] https://github.com/appliedblockchain/tsql

      [1] https://github.com/appliedblockchain/assert-combinators

    • guntars 11 days ago
      I would prefer 1. if it had full type safety. I’m imagining some sort of a build process that looks for any .sql files in the project and spits out .sql.d.ts in the same directory with the input and output types according to the current schema of the database. Another nice thing about a setup like this imo would be that the .sql files would have the full support of the editor for completions and execution, unlike sql fragments in a Typescript file.
    • kristiandupont 11 days ago
      I made a tool that generates Typescript types out of a live Postgres database. I've had a request for Kysely support (https://github.com/kristiandupont/kanel/issues/273), but I more or less forgot about it. I would love to hear if that would be helpful.
    • 8n4vidtmkvmk 10 days ago
      Why must you run a migration initially? What does it do?
  • jakewins 11 days ago
    Dang, how do they implement the “parse text strings and generate types immediately available in auto-complete” thing?? I can see how you could do that with Rust Macros, but how do they do it in TS?

    Eg. this thing:

    blah.select(['pet.name as pet_name'])

    is inferred to return a type with a `pet_name` field, parsing the contents of the SQL expression?

    [Update]: whatafak lol TS has way more juice in it than I realized: https://github.com/koskimas/kysely/blob/master/src/parser/se...

    • kamilafsar 11 days ago
      The relevant TS feature is called template literal types:

      https://www.typescriptlang.org/docs/handbook/2/template-lite...

    • nikeee 11 days ago
      You can use these template literal types + infer to build an entire SQL parser. I did a POC that infers SQL query types by parsing the SQL query on a type level:

      https://github.com/nikeee/sequelts

      Building this parser is pretty cumbersome and supporting multiple SQL dialects would be lots of pain. While I'm not a fan of query builders per se, Kysely pretty much covers everything that my POC tried to cover (except that 0 runtime overhead). However, you get the option to use different DBMs in tests than in production (pg in prod, sqlite in tests), which is a huge benefit for a lot of people. sequelts was designed to work with sqlite only. And it's a hack.

    • eddsh1994 10 days ago
      The typescript type system is insane, I solved quite a few project Euler tasks with it in the type system itself (From peano numbers up). I learned to never question the TS community
  • yasserf 11 days ago
    This is really cool, will look into using it in future projects!

    I also made a tool (https://github.com/vramework/schemats) that generates the types directly from the db, which means whenever you do a DB migration your database types automatically update. Was forked from the original schemats library a couple years ago.

    I also created a lightweight library ontop of pg that is less of a query builder and more of a typed CRUD + SQL for non trivial queries (https://github.com/vramework/postgres-typed). Most queries I deal with in a day to day is usually crud so I find it a little easier, but it's much less powerful then Kysely! I fall more into the camp of writing complex queries in SQL with small helpers and writing simple ones with util functions and typescript.

    Edit: Will be looking into cleaning up docs and tests next month. Right now everything is in the ReadMe and examples

  • crdrost 11 days ago
    What a beautiful api. As a Knex user I appreciate the design influence of Knex, which similarly does not try to give you a full orm but just a structured builder for SQL queries. But I agree that Knex was always a little weird in that it only would execute the query if it was being listened for (so `query.getSql()` or something would get you the stringified query, while `await query` would actually execute it) and had some other quirks (in particular different ways of specifying the root table’s name at the position in the FROM depending on what you were doing).

    The TypeScript integration is nice too, I also have treated TS this way as “programmable autocomplete for VS Code.” I will say that doesn't make it super maintainable usually but that's not an issue for the 0.x.x releases of course.

  • satvikpendem 11 days ago
    Reminds me of Prisma in its type safety, any major differences? I see that it's a query builder but when I used them in the past, they honestly didn't feel that different to ORMs.

    In Rust, there is sqlx which lets you write SQL but checks at compile time whether the SQL is valid for the database, by connecting to the database, performing the transaction then rolling back, picking up and displaying any errors along the way.

    Now with Prisma, I like it since it provides one unified database schema that I can commit into git (which avoids the problem of overlapping migrations from team members simultaneously working on separate branches that then need to be merged back in; with a git compatible schema, we must handle merge conflicts) and be able to transport across languages. I recently ported a TypeScript project to Rust and the data layer at least was very easy due to this. I used Prisma Client Rust for it, which is the beauty of having a language agnostic API, you can generate your own clients in whatever language you want.

  • bastardoperator 11 days ago
    How does this compare to say Prisma? I want to write SQL more than I want to write Javascript. I got really hung up on writing joins with Prisma and don't want to use a raw query. How would this compare assuming they're comparable, thanks in advance.
    • dglass 11 days ago
      Prisma is good for writing simple selects, updates, and deletes. But the moment you need to write any kind of advanced query with joins, nested queries, unions, etc. it gets frustrating very quickly.

      Kysely and Knex are far more flexible for writing complex queries and don't get in your way.

    • LewisJEllis 11 days ago
      I see a kysely + kysely-codegen (generates types from DB schema) setup as comparable to Prisma in TS integration, with the added flexibility/closeness-to-SQL of the querybuilder.

      If you:

      - have used/liked Knex (or similar querybuilders) before

      - like the TS integration + type safety of Prisma

      - but find Prisma to be a bit too magic/heavy with its query engine and schema management

      - and/or just want to be closer to SQL

      then Kysely is what you're looking for.

    • ikurei 11 days ago
      The main difference to me is that with Prisma I don't need to do any SQL to get a project up and running. I can define the tables and relations from my code or from Prisma Studio, and in one swoop I get the db structure done and the types defined.

      I know my way around a database, but I'd rather not leave my code editor whenever I need to add a new column to a table.

      With Kysely you have to create the DB schema, and then write the types; with every change you need, you gotta do both again.

      (At least this seems true by default; as the project's readme mentions, there is a code generator[1] to generate the types from the DB schema; not quite the same but at least it's better than nothing.)

      Commenters say that writing complex queries with Kysely is easier, which makes me wonder if I could use Prisma except for those, since Kysely should be able to just generate the SQL query for me to handle to Prisma...

      [1]: https://github.com/RobinBlomberg/kysely-codegen

    • cypress66 11 days ago
      As someone who uses both prisma and knex (so something like kysely):

      Prisma is very very nice, it has maybe the best DX out there. My issue with it is performance. It is much slower than query builders or raw sql. It's also a huge black box, although I haven't had any issues, you're dealing with a complex beast.

      Knex (and other query builders) is nicer than raw sql, has good performance, and it's fairly transparent (it's not a 800 pound gorilla like Prisma)

    • chetanbhasin 11 days ago
      I think that is particularly what I like about this. With Prisma optimising your code is very hard because you cannot just customise joins like here. From the example, it seems that you can create joins and it also shows you in the documentation what the actual SQL for that will look like.

      I still have Prisma running on my projects, so it will be a bit hard to move now particularly because it has TS native migrations, which is another issue. If I wanted to use these outside of TypeScript (let's say another service or middleware), then it would be very hard.

    • fortunateregard 10 days ago
      Safeql[1] can also be a good middleground. Adds type checking to prisma raw queries.

      [1] https://safeql.dev/compatibility/prisma.html

    • seer 11 days ago
      you can try https://github.com/ivank/potygen - type gen from sql itself supports all kinds of complex stuff like views, ctes json objects etc.
  • skrebbel 11 days ago
    Also check out https://jawj.github.io/zapatos/ which has a similar non-ORM yet fully typesafe approach.
    • CuriouslyC 11 days ago
      Notably, in Zapatos you write SQL, whereas with this you use a query builder. Personally I think staying close to SQL is a feature, but YMMV.
      • gmac 11 days ago
        Yes — though there are also shortcut functions for basic CRUD (and also for lateral joins) which give you automatic typing.
    • lf-non 10 days ago
      I started using zapatos in a project but eventually moved away from it. Their shortcuts and lateral join support is nice, but you very often need to write raw sql and manually provide types and ensure they are correct for all these cases.

      It is also possible but awkward to use interpolations to construct very dynamic queries where based on filter conditions you need different joins or unions etc. I looked at some of the solutions that infer ts types from sql queries but eventually felt it was more maintenable to keep the dynamic query generation on the ts ide

      I found ts-sql-query [1] to be much better suited for my use cases. It is very feature rich and has very good support for various dialect specific features in all mainstream databases. Also Juan (the author) is very helpful with queries and suggestions.

      [1] https://ts-sql-query.readthedocs.io/en/stable/

  • Sharlin 11 days ago
    The word kysely (/ˈkysely/) is Finnish for "query", btw :)
    • frosted-flakes 10 days ago
      I was wondering what it meant. It's too bad English has a different pronunciation for "y", because the specified pronunciation Key-Seh-Lee means something totally different in Finnish (kiisseli, which is a type of dessert, a thick fruit soup that was a common tradition in my family).
      • Sharlin 10 days ago
        Heh, indeed. English doesn't really have the /y/ phoneme at all, so a word like "kysely" is pretty tricky to pronounce for a native English speaker.
  • hn_throwaway_99 10 days ago
    I think this is a really cool project, but for most use cases (not all, but most) I strongly believe query builders are an anti-pattern. I could go over the reasons, but when I saw this blog post, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41..., from the author of slonik years ago I had a eureka moment, and it explains the reasoning better than I could.

    Template strings in TypeScript allow you to safely write SQL directly, still with type checking: https://github.com/gajus/slonik#readme

    • hbrn 9 days ago
      I tend to agree. Query builders are a "cool" solution looking for a problem.

      That said, dynamic query building example looks way better in knex than in slonik. Proposed approach has it's benefits, but the code is much harder to read. And it gets exponentially worse with more complicated queries.

      The caveat, however, is that dynamic queries themselves are almost an anti-pattern, and should be avoided when possible.

  • jerryu 11 days ago
    Very cool!

    Not sure if you use a diagram tool to visualize your databases but I built ERD Lab specially for developers and would love to get your feedback.

    If you are on desktop/laptop you can login as guest. No registration required.

    Here is a 1 minute video of ERDLab in action. https://www.youtube.com/watch?v=9VaBRPAtX08

    What do you think about creating diagrams using the simple markup language in my tool?

  • FireInsight 10 days ago
    It's funny that "kysely" is a Finnish word (the author is a finn) meaning questionnair, pronounced more like kew-seh-lew, but the pronounciation instructions make it sound like "kiisseli" which is like a fruit-soup-dessert thing (https://en.m.wikipedia.org/wiki/Kissel).
    • bayesian_horse 10 days ago
      I remember it faintly from Finnishing the Finnish course on duolingo. I made it half-way through legendary, but I think I forgot most of it. Also it's unlikely to get more advanced content since it was a community contribution and Duolingo now switched to paid contributors only.
    • pandem 10 days ago
      A more relevant meaning is probably a query as in SQL-kysely means an SQL query. Feel bad for Finnish students though trying to google something about SQL queries now in Finnish as most results seem to be about this project.
  • bottlepalm 11 days ago
    I’m still hoping for a real ORM for JavaScript like Entity Framework. I find that devs who haven’t used EF don’t really understand the true power of an ORM.
  • iddan 10 days ago
    This is really cool, could come very handy for analysis work. For application development my go-to is Prisma (which also features a type safe query builder)
  • haywirez 11 days ago
    Objection is an incredible SQL ORM library from the same author, I learned a lot of concepts from it over the years.
  • HatchedLake721 11 days ago
    Koskimas, awesome work! Will we ever see a lightweight ORM like Objection.js on top of Kysely?
  • moomoo11 10 days ago
    koskimas is my favorite person in the world.

    Objection is amazing and I’m happy to see he is continuing with this. Whenever I had issues he was quick to help and fix issues. Amazing person, and deserves all our support.

    Next time I use node I will def check it out.

  • jokull 10 days ago
    Love this library and use it with kysely-codegen and D1
  • throwayyy479087 10 days ago
    Reminds me of pgtyped
  • MuffinFlavored 11 days ago
    How does HN receive SQL builders in general? I feel like most of us agree ORMs are typically a bad idea. I feel like that almost instantly leaves the need for "something" to take its place. In my experience, it's typically been a query builder like this.

    I've also tried:

    https://knexjs.org/

    https://www.npmjs.com/package/sql-template-strings ("out of date" since like 2016? https://www.npmjs.com/package/sql-template-tag might be better)

    Are query builders an anti pattern? People who are doing serious/logic heavy stuff with SQL, how do you avoid a query builder (if at all?)

    • crdrost 11 days ago
      There's nothing wrong with query builders, no antipattern.

      In many ways it's similar to preferring C where you can really feel the assembly-language underneath you even if you're not writing it.

      If you have written enough raw SQL you do a similar thing by convention... So for example if you look at my raw SQL queries you will notice that I usually only select specific columns with one-letter or two-letter table prefixes for disambiguation (because I hate “adding a column” being a breaking change! ... I am flexible on the name size but I like the freedom to make my table names long and expressive if feasible, group them with prefixes that relate related tables, etc). Then in the FROM, I only use JOIN and LEFT JOIN unless there's really no other way, and all my inner JOINs come before my left ones. All of those have AS statements renaming them to one-character prefixes too, and they have a clear ON condition that connects them to the above blob (so always "AS s WHERE s.whatever = ...") even though that makes the queries longer to refactor when you want to rearrange the joins (you often have to shift all the OFs down by one and reverse which side of the equality comes first or some nonsense). Subqueries should move up to a WITH or should be rewritten as LEFT JOINs if feasible.

      You want to use structure to guide a reader through this thing that could be complicated... That structure could be lexical structure in the SQL itself or it could be syntactic structure from a wrapping language, I don't care so much. The real problem is having one source-of-truth for the database schema, and that problem is just barely tractable with current languages, but I don't see anybody who does it right.

      • hot_gril 11 days ago
        > In many ways it's similar to preferring C where you can really feel the assembly-language underneath you even if you're not writing it.

        When you write C, the ASM below it is much more complex. When use a query builder, the SQL beneath is about the same length and complexity. It's more like transpiling Swift to Java.

    • hn_throwaway_99 10 days ago
      Glad to see this posted. I totally agree - I think Kysely is a cool project but I do think query builders ARE generally an anti-pattern.

      I'm a huge fan of slonik, https://github.com/gajus/slonik#readme, which uses template strings for SQL and has recently come a long way with its strong typing support.

      This blog post by the author of slonik outlines why he thinks query builders are anti-pattern, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41.... No, I'm not him, but I strongly agree with most of his opinions here.

    • nullwarp 11 days ago
      I'm a big fan of them over traditional ORMs and use knex in quite a few projects at this point. I find them a really good balance between full on ORM and just writing out pure SQL queries.

      Definitely going to give Kysely a try on my next project

    • dns_snek 10 days ago
      Query builders can be amazing if they make it easy to logically compose highly dynamic queries, just like you would do with any other code. I haven't tried Kysely or Knex but I really enjoy working with Ecto in Elixir.

      https://hexdocs.pm/ecto/dynamic-queries.html

    • giraffe_lady 11 days ago
      I think you'll see the whole range of responses, even some very reasonable defenses of ORMs for some cases.

      I've come to prefer a yesql type approach that parses your sql into functions and provides a mechanism for applying functions to the bound data before running and the returned data after. Keeps things nicely separated and you can unit test your sql functions as application code.

    • zzzeek 10 days ago
      > how does HN receive SQL builders in general? I feel like most of us agree ORMs are typically a bad idea.

      I haven't observed that at all, my anecdotal impression is quite the opposite. Have you taken a poll?

    • hot_gril 11 days ago
      I don't like query builders because SQL is actually easier to write and read even when you're not using advanced features, but I don't consider them a serious problem like ORMs.
  • lofaszvanitt 11 days ago
    Never understood why people use abstractions on SQL. SQL in itself is plain, simple, easy to use. No need for any high level abstractions. If you ever get next to a mysql console you can shove your abstractions up your wahzoo. If you know the basic level thing you can do whatever you want, in whatever environment you are in.
    • valzam 11 days ago
      I think the main problem is getting compile time errors and autocomplete for your queries. Something like Kysely doesn't fully solve this but it avoids dumb errors like typos in sql commands or hanging commas. That being said, writing pure sql with Jetbrains IDEs is pretty amazing since you can get autocomplete straight from your production db schema. We use sqlx in Go + pure SQL and it works pretty well. Very simple things take slightly longer but writing complex queries is more straightforward than trying to get the builder to do the right thing.
      • lofaszvanitt 10 days ago
        Oh man :DDD. You must be joking, right? :D
        • valzam 8 days ago
          Can you elaborate? Writing SQL and having the library only take care of converting from/to classes (or structs or whatever) in your code is common in various communities. What's so weird about it?
    • crooked-v 11 days ago
      One key thing is that a variety of query builders and ORMs - I'd point to TypeORM for JS/TS, for example - allow in-IDE verification against a set schema and type inference of the results in whatever language you're working in.
      • lofaszvanitt 11 days ago
        Ah, too much oo and type magic. 90% of things do not need those, it just increases development time many times fold.
        • hot_gril 10 days ago
          Query builders are a slight annoyance at worst, while ORMs can easily screw up the foundation of your system. You get extra compile-time checks with a query builder if you're using TS (a big piece of bloat on its own) instead of JS, which can be nice, but that's also what tests are for.
  • T3RMINATED 10 days ago
    [dead]